Understanding AUTHID CURRENT_USER in Oracle PL/SQL – Step by Step with Examples

1. What is AUTHID in PL/SQL?

In Oracle PL/SQL, a package, procedure, or function can run under two modes:

  • AUTHID DEFINER (default): Code runs with the privileges of the schema that owns the object.
  • AUTHID CURRENT_USER: Code runs with the privileges of the user who is calling the object.

If you don’t explicitly mention AUTHID, Oracle assumes AUTHID DEFINER.

2. Why AUTHID CURRENT_USER Matters

  • Useful in multi-schema applications where you want to allow different users to call the same code without creating synonyms everywhere.
  • Forces Oracle to check privileges of the invoker, not just the owner.
  • Can cause ORA-01031: insufficient privileges if the calling user does not have required grants.

3. Example 1 – Default Behavior (AUTHID DEFINER)

-- In schema HR
CREATE OR REPLACE PACKAGE emp_pkg AS
  PROCEDURE get_emp(p_id NUMBER);
END emp_pkg;
/

CREATE OR REPLACE PACKAGE BODY emp_pkg AS
  PROCEDURE get_emp(p_id NUMBER) IS
    v_name VARCHAR2(100);
  BEGIN
    SELECT first_name INTO v_name FROM employees WHERE 
     employee_id = p_id;
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);
  END;
END emp_pkg;
/
  • emp_pkg runs as HR (the owner).
  • If another user APPUSER executes emp_pkg.get_emp, they do not need direct SELECT on employees table.
  • Because privileges of HR are used.

4. Example 2 – AUTHID CURRENT_USER

-- In schema HR
CREATE OR REPLACE PACKAGE emp_pkg AUTHID CURRENT_USER AS
  PROCEDURE get_emp(p_id NUMBER);
END emp_pkg;
/

CREATE OR REPLACE PACKAGE BODY emp_pkg AS
  PROCEDURE get_emp(p_id NUMBER) IS
    v_name VARCHAR2(100);
  BEGIN
    SELECT first_name INTO v_name FROM employees WHERE 
employee_id = p_id;
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);
  END;
END emp_pkg;
/
  • Now, when APPUSER calls emp_pkg.get_emp, the query runs with APPUSER’s privileges.
  • If APPUSER does not have SELECT on HR.employees, they get ORA-01031 insufficient privileges.
  • To fix, grant the privilege:
GRANT SELECT ON hr.employees TO appuser;

5. Example 3 – With Synonym

-- In APPUSER schema
CREATE SYNONYM emp_pkg FOR hr.emp_pkg;

-- Now APPUSER can call
EXEC emp_pkg.get_emp(100);

But with AUTHID CURRENT_USER, the privileges still must exist for APPUSER. The synonym only simplifies object reference; it does not provide privileges.

6. Common Issues



  • Why does it work in one DB and not another?
            In one DB, user may already have GRANT SELECT on the table.
            In another DB, grants are missing.


  • Why synonyms don’t solve it?
            Synonym ≠ Privilege. You still need direct grants.


  • Why does Oracle sometimes add AUTHID CURRENT_USER?
            Some code generators (like EBS or APEX) enforce it automatically for security and                     multi-schema support.

7. Key Takeaways

  • AUTHID DEFINER (default) = runs as owner.
  • AUTHID CURRENT_USER = runs as caller (invoker).
  • Synonyms help with object names, not privileges.
  • Always check which user needs which grant when AUTHID CURRENT_USER is used.







Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment