Tip	Understand Extended Security in PL/SQL procedure by AUTHID CURRENT_USER .
	SamOraTech.com By Sameer Wadhwa

(The following code is tested against 8.1 and 9i)

As of Oracle 8.1 you can add AUTHID CURRENT_USER in a procedure which does not allow the other user to do DML or select of the table by the procedure .

Let us understand this with the following simple example .

I created a procedure “TEST_AUTHID” in a user SCOTT , created public synonym on it and gave execute grants to another user SAM as follows :-

ora816 SamSQL :>connect scott/tiger

ora816 SamSQL :> create or replace procedure test_authid

2 is

3 Vempno number;

4 Vsal number;

5 BEGIN

6 select max(empno) into Vempno from scott.emp;

7 Dbms_output.put_line('Last EMPNO is '||Vempno);

8 insert into empdup(empno) values (Vempno+1)

9 returning empno into Vempno;

10 Dbms_output.put_line('New Employee no is '||to_char(Vempno));

11 update empdup set sal=19950

12 where empno=Vempno

13 returning sal into vsal;

14 dbms_output.put_line('Salary of New employee '||to_char(Vempno)||' is '||to_char(vsal,'$9,9999'));

15 END;

16 /

Procedure created

ora816 SamSQL :> create public synonym test_authid for test_authid;

Synonym created.

ora816 SamSQL :> grant execute on test_authid to SAM;

Grant succeeded.

Now connect with SAM user and execute it as follows :-

ora816 SamSQL :> connect sam/sampass

Connected.

ora816 SamSQL :> set serveroutput on;

ora816 SamSQL :> execute test_authid

Last EMPNO is 7936

New Employee no is 7937

Salary of New employee 7937 is $1,9950

PL/SQL procedure successfully completed.

Now here is the key thing :- The database user SAM, has only execute privilege on test_authid procedure . It does not have select ,insert ,update or delete privilege on table scott.emp , user sam can easily do all the DML on the table scott.emp by executing a procedure. In another words this has a great security risk , any one can write a small procedure and give execute grant to another user and that is it , The another user can do pretty much all the thing with the owner of the tables. But do not worry , you can avoid this by adding "AUTHID CURRENT_USER" clause in your pl/sql packages or procedure provided by Oracle 8.1 .

Now if we change the above procedure by putting "AUTHID CURRENT_USER" clause in it and execute again from SAM database user. You will get an error and oracle will not allow you to execute it.

ora816 SamSQL :> create or replace procedure test_authid

2 authid current_user

3 is

4 Vempno number;

5 Vsal number;

6 begin

7 select max(empno) into Vempno from scott.emp;

8 dbms_output.put_line('Last EMPNO is '||Vempno);

9 insert into empdup(empno) values (Vempno+1)

10 returning empno into Vempno;

11 dbms_output.put_line('New Employee no is '||to_char(Vempno));

12 update empdup set sal=19950

13 where empno=Vempno

14 returning sal into vsal;

15 dbms_output.put_line('Salary of New employee '||to_char(Vempno)||' is '||to_char(vsal,'$9,9999'));

16 end;

17 /

Procedure created.

ora816 SamSQL :> show errors;

No errors.

ora816 SamSQL :> connect sam/sampass

Connected.

ora816 SamSQL :> execute test_authid

BEGIN test_authid; END;

*

ERROR at line 1:

ORA-00942: table or view does not exist

ORA-06512: at "SCOTT.TEST_AUTHID", line 7

ORA-06512: at line 1

Elapsed: 00:00:00.60

ora816 SamSQL :>

The user SAM in above case could do neither DML on SCOTT.EMP table nor SELECT it. In above case, You have to give explicit grant to SAM user (“SQL> grant select on emp to sam; “) to select EMP table.

CASE 2

create or replace procedure scott.testproc

authid current_user

as

begin

for x in (select table_name from user_tables)

loop

dbms_output.put_line(x.table_name);

end loop;

end;

/

show errors

Now Execute this procedure from scott user, You will see the tables of scott user

Connected as SCOTT

SQL> execute testproc

BONUS

CREATE$JAVA$LOB$TABLE

DEPT

EMP

JAVA$CLASS$MD5$TABLE

SALGRADE

TEST

Now connect with SYS user , execute testproc and you will see the tables of sys user

Connected as SYS

SQL> execute scott.testproc;

ACCESS$

APPLY$_CONF_HDLR_COLUMNS

APPLY$_DEST_OBJ

APPLY$_DEST_OBJ_CMAP

APPLY$_DEST_OBJ_OPS

APPLY$_ERROR

APPLY$_ERROR_HANDLER

APPLY$_SOURCE_OBJ

APPLY$_SOURCE_SCHEMA

APPROLE$

AQ$_MESSAGE_TYPES

AQ$_PENDING_MESSAGES

< partial output ….>

AUTHID CURRENT_USER allows the user to execute the package or procedure with the invoker rights not the definer (who own the procedure/package).

In the case where INVOKER (who executes the procedure/package) is SCOTT user, the user_table of scott schema ran. In case where invoker is SYS user, user table of sys schema ran.

End of Tip
	Copyright © Sameer Wadhwa Oracle Techniques All Rights Reserved