Definer and Invoker’s rights

connect / as sysdba
create tablespace test ;
create user peter identified by peter default tablespace test quota unlimited on test ;
grant connect, resource to peter ;
create user tomas identified by tomas default tablespace test quota unlimited on test ;
grant create session,create table to tomas ;

connect peter/peter
create table test (id varchar2(100)) ;
insert into test values ('Works') ;
commit ;

create or replace procedure showuser1 authid definer as
v_syscontext varchar2(1000) ;
v_value varchar2(100) ;
begin
select sys_context('USERENV','CURRENT_USER') into v_syscontext from dual ;
dbms_output.put_line(v_syscontext);

select id into v_value from test ;
dbms_output.put_line(v_value) ;
end;
/

create or replace procedure showuser2 authid current_user as
v_syscontext varchar2(1000) ;
v_value varchar2(100) ;
begin
select sys_context('USERENV','CURRENT_USER') into v_syscontext from dual ;
dbms_output.put_line(v_syscontext);

select id into v_value from test ;
dbms_output.put_line(v_value) ;
end;
/

grant execute on showuser1 to tomas ;
grant execute on showuser2 to tomas ;

connect tomas/tomas 
 create table test (id varchar2(100)) ;
insert into test values ('Yeaah') ;
commit ;

TOMAS> exec peter.showuser1
PETER
Works
TOMAS> exec peter.showuser2
TOMAS
Yeaah
TOMAS> connect peter/peter
Connected.
PETER> create or replace procedure showuser2 authid current_user as
v_syscontext varchar2(1000) ;
v_value varchar2(100) ;
begin
select sys_context('USERENV','CURRENT_USER') into v_syscontext from dual ;
dbms_output.put_line(v_syscontext);

select id into v_value from test ;
dbms_output.put_line(v_value) ;

/* I am an evil developer, haha */
delete from test ;
insert into test values ('hacked') ;
commit ;
end;
/
Procedure created.
PETER> connect tomas/tomas
Connected.
TOMAS>
TOMAS> exec peter.showuser1
PETER
Works

PL/SQL procedure successfully completed.

TOMAS> exec peter.showuser2
TOMAS
Yeaah

PL/SQL procedure successfully completed.

TOMAS> exec peter.showuser2
TOMAS
hacked

PL/SQL procedure successfully completed.

Thanks to Steven Feuerstein.. now I am a little step forward to understanding privilege inheritance. But what now?

The answer:
https://community.oracle.com/message/14120331#14120331

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s