There are 2 interesting PRAGMA which can be defined for procedures/functions and packages:
AUTHID CURRENT_USER – specify CURRENT_USER
to indicate that the code executes with the privileges of CURRENT_USER
. This clause also specifies that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of CURRENT_USER
. External names in all other statements resolve in the schema in which the code resides. All roles for CURRENT_USER are active for dynamic code (EXECUTE IMMEDIATE) and disabled for static compilation.
AUTHID DEFINER – specify DEFINER
to indicate that the code executes with the privileges of the owner of the schema in which the package resides and that external names resolve in the schema where the code resides. This is the default and creates a definer’s rights package. All roles for DEFINER are disabled for dynamic code(EXECUTE IMMEDIATE) and static compilation.
In this article I want to show specific feature for AUTHID CURRENT_USER enabled roles for dynamic code.
Basics for pragma DEFINER and pragma AUTHID CURRENT_USER
Let’s create simple role with one option create table
create role test_role; grant create table to test_role;
Then we create test_user and grant him role test_role and 2 direct privileges create session, create procedure
grant test_role to test_user identified by test_user; grant create session to test_user; grant create procedure to test_user;
We connect as user test_user and verify his privileges
SQL> select * from session_roles; ROLE ------------------------------ TEST_ROLE SQL> select * from session_privs; PRIVILEGE ---------------------------------------- CREATE SESSION CREATE TABLE CREATE PROCEDURE
Let’s create simple procedure as user test_user with default pragma AUTHID DEFINER and verify his privileges. It’s default pragma if you don’t specify it.
SQL> create or replace procedure test_definer is begin dbms_output.put_line('---- Definer ----'); dbms_output.put_line('Roles:'); for i in (select role from session_roles) loop dbms_output.put_line(i.role); end loop; dbms_output.put_line('Privileges:'); for i in (select privilege from session_privs) loop dbms_output.put_line(i.privilege); end loop; end; / Procedure created. SQL> set serveroutput on SQL> exec test_definer ---- Definer ---- Roles: Privileges: CREATE SESSION CREATE PROCEDURE PL/SQL procedure successfully completed.
As you can see only direct grant are visible. Roles are disabled. Situation is different for pragma AUTHID CURRENT_USER.
SQL> create or replace procedure test_current_user authid current_user is begin dbms_output.put_line('---- Current_user ----'); dbms_output.put_line('Roles:'); for i in (select role from session_roles) loop dbms_output.put_line(i.role); end loop; dbms_output.put_line('Privileges:'); for i in (select privilege from session_privs) loop dbms_output.put_line(i.privilege); end loop; end; / Procedure created. SQL> exec test_current_user ---- Current_user ---- Roles: TEST_ROLE Privileges: CREATE SESSION CREATE TABLE CREATE PROCEDURE PL/SQL procedure successfully completed.
So for pragma AUTHID CURRENT_USER all roles/privileges are enabled so you can use them for EXECUTE IMMEDIATE.
Typical surprise encountered by developer is behavior of anonymous block BEGIN END. BEGIN..END block is working with pragma AUTHID CURRENT_USER no roles are blocked.
BEGIN execute immediate 'create table ok(id number)'; END; / anonymous block completed desc ok Name Null Type ---- ---- ------ ID NUMBER drop table ok; table OK dropped.
when developer is trying to make procedure from this block it stops to work because procedure is created with default pragma AUTHID DEFINER which blocks roles.
create procedure test_p as BEGIN execute immediate 'create table ok(id number)'; END; / PROCEDURE TEST_P compiled SQL> exec test_p Error report: ORA-01031: insufficient privileges ORA-06512: at "TEST_USER.TEST_P", line 4
Mutual calls pragma DEFINER and pragma AUTHID CURRENT_USERS
Another case is what happens if procedure with pragma DEFINER calls pragma AUTHID CURRENT_USER and vice versa. Let’s create another two procedures:
- test_definer_calls – has default pragma DEFINER and calls procedures with pragma authid current_user and definer
- test_authid_calls – has pragma AUTHID CURRENT_USER and calls procedures with pragma authid current_user and definer
SQL> create or replace procedure test_definer_calls is begin dbms_output.put_line(chr(10)||'definer calls definer'); test_definer; dbms_output.put_line(chr(10)||'definer calls authid'); test_current_user; dbms_output.put_line(chr(10)||'definer calls definer'); test_definer; dbms_output.put_line(chr(10)||'definer calls authid'); test_current_user; end; / PROCEDURE TEST_DEFINER_CALLS compiled create or replace procedure test_authid_calls authid current_user is begin dbms_output.put_line(chr(10)||'authid calls authid'); test_current_user; dbms_output.put_line(chr(10)||'authid calls definer'); test_definer; dbms_output.put_line(chr(10)||'authid calls authid'); test_current_user; dbms_output.put_line(chr(10)||'authid calls definer'); test_definer; end; / PROCEDURE TEST_AUTHID_CALLS compiled
When you call procedure test_definer_calls (defined with DEFINER) the procedure blocks all roles so subsequent calls of procedures/functions etc can’t see any roles no matter what kind of pragma they use.
SQL> exec test_definer_calls anonymous block completed definer calls definer ---- Definer ---- Roles: Privileges: CREATE SESSION CREATE PROCEDURE definer calls authid ---- Current_user ---- Roles: Privileges: CREATE SESSION CREATE PROCEDURE definer calls definer ---- Definer ---- Roles: Privileges: CREATE SESSION CREATE PROCEDURE definer calls authid ---- Current_user ---- Roles: Privileges: CREATE SESSION CREATE PROCEDURE
When you call procedure test_authid_calls (defined with CURRENT_USER) the procedure doesn’t block any roles so subsequent calls of procedures/functions can use the roles if they are specified with AUTHID CURRENT_USER.
SQL> exec test_authid_calls anonymous block completed authid calls authid ---- Current_user ---- Roles: TEST_ROLE Privileges: CREATE SESSION CREATE TABLE CREATE PROCEDURE authid calls definer ---- Definer ---- Roles: Privileges: CREATE SESSION CREATE PROCEDURE authid calls authid ---- Current_user ---- Roles: TEST_ROLE Privileges: CREATE SESSION CREATE TABLE CREATE PROCEDURE authid calls definer ---- Definer ---- Roles: Privileges: CREATE SESSION CREATE PROCEDURE
SET ROLE in session for pragma DEFINER and pragma AUTHID CURRENT_USER
One of way to turn on/off roles is using command SET ROLE
or
DBMS_SESSION.SET_ROLE(role_cmd varchar2);
but you need to remember it’s possible only for:
- PL/SQL defined with pragma AUTHID_USER
- anonymous block – because it’s using implicit pragma AUTHID_USER
and doesn’t work for:
- within PL/SQL defined with pragma AUTHID DEFINER
- raises exception when code with pragma DEFINER calls (code with pragma CURRENT_USER with SET ROLE)
Hope this helps 🙂
Tomasz
Superb!!! thanks a lot,,it is really useful information.
I’m happy I could help 🙂
its very helpful..it can save wasting couple of days if you encounter this page earlier while getting into troubles of roles and rights.
Happy I could help 🙂
Really I need it very badly … 🙂 Thanks A tonn
Beautifully explained! Thanks for this.
Cool 🙂
Very useful post
the concept are very clear and nice. Thank You for sharing information
Thank you very much…
Your post really helpful and clear the concepts in detail. Thank you for sharing this useful information.