Pete Finnigan's Oracle Security Forum


    
      
        Pete Finnigan's Oracle Security Forum
        (http://www.petefinnigan.com/forum/yabb/YaBB.cgi)
      

        Oracle Security >> Oracle Security >> Invoker & Definer used together, very confusin
        
(Message started by: Pete Finnigan on Sep 5^th, 2006, 9:18am)

Title: Invoker & Definer used together, very confusin
Post by Pete Finnigan on Sep 5^th, 2006, 9:18am

Hello DBAs,

I am confusing with Invoker and Definer procedures used together: user U4 does not need the EXECUTE privilege on U2.def procs ???

Below is the code:

CREATE USER:

Quote:

drop user u1 cascade;
drop user u2 cascade;
drop user u3 cascade;
drop user u4 cascade;
create user u1 identified by u1;
create user u2 identified by u2;
create user u3 identified by u3;
create user u4 identified by u4;

GRANT permissions:

Quote:

grant create procedure to u1;
grant create procedure to u2;
grant unlimited tablespace to u3;
grant create table to u3;
grant create session to U1,U2,U3,U4;

CREATE TABLE:

Quote:

connect U3/U3
CREATE TABLE t1 (x number);
GRANT INSERT ON U3.t1 TO U2;

CREATE DEFINER procs in user U2:

Quote:

connect U2/U2
Create or replace procedure U2.def AS
Begin

INSERT INTO U3.t1 VALUES(1);

commit;
End;
/
show errors
grant EXECUTE on U2.def to U1;

CREATE INVOKER procs in user U1:

Quote:

connect U1/U1
Create procedure U1.inv_def

AUTHID CURRENT_USER As
Begin

U2.def(); -- insert into U3.t1
End;
/
show errors

grant EXECUTE on U1.inv_def to U4;

Quote:

connect U4/U4
execute U1.inv_def;
show errors

connect U3/U3
select * from t1;

Executed Codes

Quote:

SQL> drop user u1 cascade;

User dropped.

SQL> drop user u2 cascade;

User dropped.

SQL> drop user u3 cascade;

User dropped.

SQL> drop user u4 cascade;

User dropped.

SQL> create user u1 identified by u1;

User created.

SQL> create user u2 identified by u2;

User created.

SQL> create user u3 identified by u3;

User created.

SQL> create user u4 identified by u4;

User created.

SQL> grant create procedure to u1;

Grant succeeded.

SQL> grant create procedure to u2;

Grant succeeded.

SQL> grant unlimited tablespace to u3;

Grant succeeded.

SQL> grant create table to u3;

Grant succeeded.

SQL> grant create session to U1,U2,U3,U4;

Grant succeeded.

SQL>
SQL> connect U3/U3
Connected.
SQL> CREATE TABLE t1 (x number);

Table created.

SQL> GRANT INSERT ON U3.t1 TO U2;

Grant succeeded.

SQL>
SQL> connect U2/U2
Connected.
SQL> Create or replace procedure U2.def AS
2 Begin
3
INSERT INTO U3.t1 VALUES(1);
4
commit;
5 End;
6 /

Procedure created.

SQL> show errors
No errors.
SQL> grant EXECUTE on U2.def to U1;

Grant succeeded.

SQL>
SQL> connect U1/U1
Connected.
SQL> Create procedure U1.inv_def
2
AUTHID CURRENT_USER As
3 Begin
4
U2.def(); -- insert into U3.t1
5 End;
6 /

Procedure created.

SQL> show errors
No errors.
SQL>
SQL>
SQL> grant EXECUTE on U1.inv_def to U4;

Grant succeeded.

SQL> connect U4/U4
Connected.
SQL> execute U1.inv_def;

PL/SQL procedure successfully completed.

SQL> show errors
No errors.
SQL>
SQL> connect U3/U3
Connected.
SQL> select * from t1
2 /

X
----------
1

SQL>
SQL> spool off

Title: Re: Invoker & Definer used together, very conf
Post by Pete Finnigan on Sep 6^th, 2006, 12:30am

download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14261/subprograms.htm#sthref1778

Basically AUTHID CURRENT_USER covers objects referenced by SQL not other PL/SQL objects. (The documentation doesn't mention MERGE as one of the SQL statements, but I'd assume that's a documentation bug).