Pete Finnigan's Oracle Security Forum - Invoker & Definer used together, very confusin

Forum

Home/Forum

Welcome, Guest. Please Login. Nov 21^st, 2024, 6:39pm News: If you would like to register contact the forum admin
Home \| Help \| Search \| Members \| Login

   Pete Finnigan's Oracle Security Forum
   Oracle Security
   Oracle Security (Moderator: Pete Finnigan)
   Invoker & Definer used together, very confusin

« Previous topic | Next topic »

Pages: 1

Reply | Notify of replies | Send Topic | Print

Author

Topic: Invoker & Definer used together, very confusin (Read 3036 times)

Pete Finnigan
PeteFinnigan.com Administrator

Oracle Security is easier if you design for it

View Profile | WWW | Email

Gender: male

Posts: 309

Invoker & Definer used together, very confusin
« on: Sep 5^th, 2006, 9:18am »

Quote | Modify

Hello DBAs,

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

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

IP Logged

Pete Finnigan (email:pete@petefinnigan.com)
Oracle Security Web site: http://www.petefinnigan.com
Forum: http://www.petefinnigan.com/forum/yabb/YaBB.cgi
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html