19 | 08 | 2017
Latest Articles
Popular Articles

Security

Using Invoker's Rights Versus Definer's Rights (AUTHID Clause)

User Rating:  / 1
PoorBest 

Using Invoker's Rights Versus Definer's Rights (AUTHID Clause)


http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/subprograms.htm#i18574


A little gym

User U1 owns table T
User U1 has procedure PR_T1 with the authid current_user attribute
User U1 has procedure PR_T2 with the authid definer attribute

User U2 has the select any table privilege
User U2 has the execute any procedure privilege
User U2 can execute U1.PR_T2 and thus insert data into U1.T without the need to have insert privs on U1.T
User U2 can execute U1.PR_T1 but cannot insert data into U1.T since it lacks the insert privs on U1.T

 

SQL> grant create session,create table to U1 identified by welcome;

Grant succeeded.

SQL> grant create procedure,unlimited tablespace to U1;

Grant succeeded.

SQL> grant create session, select any table, execute any procedure to U2 identified by welcome;

Grant succeeded.

SQL> connect U1/welcome;
Connected.

SQL> create table T (c number);

Table created.

 

SQL> create or replace procedure PR_T1 ( n number )
2 authid current_user as
3 begin
4 insert into T values (n);
5 end;
6 /

Procedure created.

 

SQL> create or replace procedure PR_T2 ( n number )
2 authid definer as
3 begin
4 insert into T values (n);
5 end;
6 /

Procedure created.

 

SQL> conn U2/welcome
Connected.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
EXECUTE ANY PROCEDURE
SELECT ANY TABLE
CREATE SESSION

SQL> exec U1.PR_T2(100);

PL/SQL procedure successfully completed.

SQL> exec U1.PR_T1(100);

BEGIN U1.PR_T1(100); END;*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "U1.PR_T1", line 4
ORA-06512: at line 1

SQL> alter session set current_schema=U1;

Session altered.

SQL> exec U1.PR_T1(100);

BEGIN U1.PR_T1(100); END;*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "U1.PR_T1", line 4
ORA-06512: at line 1

SQL> conn / as sysdba
Connected.

SQL> column owner format a10
SQL> column object_name format a10
SQL> select owner,object_name,authid from dba_procedures where owner='U1';

OWNER      OBJECT_NAM AUTHID
---------- ---------- ------------
U1         PR_T1      CURRENT_USER
U1         PR_T2      DEFINER

 

Goal of this test was to show the difference between authid definer and authid current_user. During this test the select any table and execute any procedure privilege were used. Discussions about the security risks of the select any table and execute any procedure privilege are out of the scope of this article.