connect / as sysdbacreate 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.