AUTHID CURRENT_USER and AUTHID DEFINER

Usually in seeded objects definition we see the key words AUTHID CURRENT_USER or AUTHID DEFINER like shown in the below screen shot


AUTHID clause tells oracle pl/sql engine whether this piece of code to be executed with invoker’s rights (CURRENT_USER), or with the Owner rights (DEFINER). If this clause is not specified, Oracle will default to AUTHID DEFINER.

AUTHID CURRENT_USER

This clause is used when you want to execute a piece of code with execute privilege of current user but not with the privilege of the user who defined the pl/sql code.

This is termed as “invoker rights”, the opposite of “definer rights”.

AUTHID DEFINER

AUTHID DEFINER is exactly opposite to AUTHID CURRENT_USER where a user can execute the pl/sql code with definer privileges. Using this clause is as same as granting public access to the pl/sql code.

CREATE OR REPLACE PROCEDURE
   myproc
   AUTHID DEFINER
AS . . . .

The above definer rights clause would be equivalent to this grant execute statement:

GRANT EXECUTE ON myproc TO public;

Note: If no AUTHID clause is specified Oracle will default to AUTHID DEFINER. Hence it’s suggestible to set AUTHID clause if not, an intruder may get access to privileges of definer which an intruder should not get.

Example

Create a procedure in SCOTT schema and make sure EMP table has access only on SCOTT schema

CREATE OR REPLACE PROCEDURE scott.XX_EMP_AUTHID_DEFINER AUTHID DEFINER
IS
BEGIN
INSERT INTO EMP VALUES        (7369, 'SMITH',  'CLERK',     7902,TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
COMMIT;
END XX_EMP_AUTHID_DEFINER;

CREATE OR REPLACE PROCEDURE scott.XX_EMP_AUTHID_USER AUTHID CURRENT_USER
IS
BEGIN
INSERT INTO EMP VALUES        (7369, 'SMITH',  'CLERK',     7902,TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
COMMIT;
END XX_EMP_AUTHID_USER;

Change to APPS schema and execute the above two procedures

Procedure XX_EMP_AUTHID_DEFINER work normally since it gets executed with definers privilege. But procedure XX_EMP_AUTHID_USER give error ‘table or view does not exits’ since EMP table access is not granted to APPS schema. If APPS schema is granted with access to EMP table then procedure XX_EMP_AUTHID_USER will work fine.

Reference:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/subprograms.htm#i18574
http://www.dba-oracle.com/t_authid_definer_rights.htm