AUTHID CURRENT_USER, AUTHID DEFINER

Invoker Rights

Invoker rights is a method present in Oracle 8i and greater that is used to resolve references to database elements in a PL/SQL program unit. Using Invoker rights we can instruct Oracle if a given program unit should run with the authority of the definer or of the invoker. The result is that multiple schemas can share the same piece of code while accessing only the elements which belong to the invoker.

There are two 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.

Privileges Handling with DEFINER/CURRENT_USER 

For DEFINER RIGHTS objects execute privileges must be granted directly to the user; not to a role. With CURRENT USER rights can be granted to a role. AUTHID DEFINER (default) stored procedures are executed in environment equivalent to the one you get after SET ROLE NONE. 

In other words, roles are disabled for PL/SQL and any privileges granted via roles do not apply unless you created the procedure with AUTHID CURRENT_USER, in which case role privileges do apply. Note that executing such procedures is a bit more expensive because Oracle has to evaluate the privileges on every call. 

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 behaviour 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 CURRENT_USER
• anonymous block – because it’s using implicit pragma AUTHID CURRENT_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)

Source: http://dbaora.com/authid-current_user-authid-definer/