Understanding AUTHID CURRENT_USER in Oracle PL/SQL

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

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