Understanding AUTHID Clause In Oracle

It is used when we want a routine to be executed with the privileges of the invoker and NOT with the privileges of the user who created the routine.

It is used when we want a routine to be executed with the privileges of the user who created the routine and NOT with the privileges of the user who executed the routine.

The AUTHID DEFINER is Default behavior.

SQL> SHOW USER
USER is "COURSE"

SQL> SELECT * FROM emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5001                    10
      7698 BLAKE      MANAGER         7839 01-MAY-81       2851                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2451                    10
      7566 JONES      MANAGER         7839 02-APR-81       2976                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3001                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3001                    20
      7369 SMITH      CLERK           7902 17-DEC-80        801                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1601        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1251        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1251       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1501          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1101                    20
      7900 JAMES      CLERK           7698 03-DEC-81        951                    30
      7934 MILLER     CLERK           7782 23-JAN-82       1301                    10

14 rows selected.

SQL> GRANT SELECT ON emp TO user1;

Grant succeeded.

SQL> SHOW USER
USER is "USER1"

--This is Same As Creating The Procedure With "AUTHID DEFINER" Clause.

SQL> CREATE OR REPLACE PROCEDURE proc1
  2  AS
  3   nCnt NUMBER;
  4  BEGIN
  5   SELECT COUNT(*)
  6   INTO nCNT
  7   FROM COURSE.emp;
  8
  9   DBMS_OUTPUT.PUT_LINE('Count Of COURSE.emp Table ::: '||nCNT);
 10  END;
 11  /

Procedure created.

SQL> GRANT EXECUTE ON proc1 TO user2;

Grant succeeded.

SQL> SHOW USER
USER is "USER2"

SQL> EXEC USER1.proc1;
Count Of COURSE.emp Table ::: 14

PL/SQL procedure successfully completed.

SQL> SHOW USER
USER is "COURSE"

SQL> SELECT * FROM emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5001                    10
      7698 BLAKE      MANAGER         7839 01-MAY-81       2851                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2451                    10
      7566 JONES      MANAGER         7839 02-APR-81       2976                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3001                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3001                    20
      7369 SMITH      CLERK           7902 17-DEC-80        801                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1601        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1251        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1251       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1501          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1101                    20
      7900 JAMES      CLERK           7698 03-DEC-81        951                    30
      7934 MILLER     CLERK           7782 23-JAN-82       1301                    10

14 rows selected.

SQL> GRANT SELECT ON emp TO user1;

Grant succeeded.

SQL> SHOW USER
USER is "USER1"

SQL> CREATE OR REPLACE PROCEDURE proc1
  2  AUTHID CURRENT_USER
  3  AS
  4   nCnt NUMBER;
  5  BEGIN
  6   SELECT COUNT(*)
  7   INTO nCNT
  8   FROM COURSE.emp;
  9
 10   DBMS_OUTPUT.PUT_LINE('Count Of COURSE.emp Table ::: '||nCNT);
 11  END;
 12  /

Procedure created.

SQL> GRANT EXECUTE ON proc1 TO user2;

Grant succeeded.

SQL> SHOW USER
USER is "USER2"

SQL> EXEC USER1.proc1;
BEGIN USER1.proc1; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "USER1.PROC1", line 6
ORA-06512: at line 1