Authid current_user, authid definer

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

pragma authid1

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

 

10 thoughts on “Authid current_user, authid definer

  1. its very helpful..it can save wasting couple of days if you encounter this page earlier while getting into troubles of roles and rights.

  2. Thank you very much…

    Your post really helpful and clear the concepts in detail. Thank you for sharing this useful information.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.