Code Based Access Control for Definer’s Rights and Invoker’s Rights Oracle Database 12C release 2 (12.2)

In Oracle 12C it’s possible to assign a role to a code procedure/function/package

GRANT role_name TO PROCEDURE|FUNCTION|PACKAGE code_name;

This kind of grant can be executed only by following user:

SYS
user with GRANT ANY ROLE privilege
user who own code and was granted the role with ADMIN OPTION
user who own code and was granted the role with DELEGATE OPTION

Top three options are commonly know. New option is DELEGATE OPTION. It enables to grant a role to procedure|function|package and nothing more.

Once the role is granted to a code the role is always enabled no matter if the code is created with PRAGMA AUTHID_USER or PRAGMA DEFINER and no matter if owner of the code calls it or other user.

But before I show how it works. Let’s see basics for pragma AUTHID DEFINER and pragma AUTHID CURRENT_USER

Let’s create as user SYS two simple roles

role test_role1 with create table system privilege

SQL SYS>
CREATE ROLE test_role1;
GRANT CREATE TABLE TO test_role1;

role test_role2 with create view system privilege

SQL SYS>
CREATE ROLE test_role2;
GRANT CREATE VIEW TO test_role2;

Then we create two users

User test_user1 and grant him role test_role1 and 2 direct privileges create session, create procedure

SQL SYS>
GRANT test_role1 TO test_user1 IDENTIFIED BY test_user1;
GRANT CREATE SESSION TO test_user1;
GRANT CREATE PROCEDURE TO test_user1;

User test_user2 and grant him role test_role2 and 2 direct privileges create session, create trigger

SQL SYS>
GRANT test_role2 TO test_user2 IDENTIFIED BY test_user2; 
GRANT CREATE SESSION TO test_user2; 
GRANT CREATE TRIGGER TO test_user2;

Connect as user test_user1 and verify his privileges

SQL TEST_USER1> 
SELECT * FROM session_roles;

ROLE
------------------------------
TEST_ROLE1

SQL TEST_USER1>
SELECT * FROM session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE PROCEDURE

Connect as user test_user2 and verify his privileges

SQL TEST_USER2> 
SELECT * FROM session_roles; 

ROLE 
------------------------------ 
TEST_ROLE2 

SQL TEST_USER1>

SELECT * FROM session_privs; 
PRIVILEGE 
---------------------------------------- 
CREATE SESSION 
CREATE VIEW 
CREATE TRIGGER

As user test_user1 create two procedures with different PRAGMA. In addition grant execute on them to test_user2. NOTE – AUTHID DEFINER is not required as it’s default.

SQL TEST_USER1>

CREATE OR REPLACE PROCEDURE test_definer
AUTHID 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;
/

GRANT EXECUTE ON test_definer TO test_user2;

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;
/

GRANT EXECUTE ON test_current_user TO test_user2;

Now it’s time to see impact of pragma AUTHID DEFINER and AUTHID CURRENT_USER on roles

SQL TEST_USER1>
SET SERVEROUTPUT ON
EXEC test_definer

---- Definer ----
Roles:
Privileges:
CREATE SESSION
CREATE PROCEDURE

SET SERVEROUTPUT ON
exec test_current_user

---- Current_user ----
Roles:
TEST_ROLE1
Privileges:
CREATE SESSION
CREATE TABLE
CREATE PROCEDURE

As you can see roles are turned off for AUTHID DEFINER pragma but still enabled for AUTHID CURRENT_USER. So for CURRENT_USER roles can be used with caluse EXECUTE IMMEDIATE.

The same code executed as user test_user2

SQL TEST_USER2>

SET SERVEROUTPUT ON
EXEC test_user1.test_definer

---- Definer ----
Roles:
Privileges:
CREATE SESSION
CREATE PROCEDURE

SET SERVEROUTPUT ON
exec test_user1.test_current_user

---- Current_user ----
Roles:
TEST_ROLE2
Privileges:
CREATE SESSION
CREATE VIEW
CREATE TRIGGER

In this case code test_definer code is executed with rights of test_user1 so CREATE PROCEDURE system privilege is visible. For AUTHID CURRENT_USER role TEST_ROLE2 of user_test2 is enabled and his system privileges.

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.

SQL TEST_USER1>

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 all roles.

SQL TEST_USER1>

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

Now it’s time to see impact of new option granting role to code. Following code fails because test_role1 was not granted to user_test1 with DELEGATE OPTION

SQL TEST_USER1 >

GRANT test_role1 TO PROCEDURE test_definer;

ORA-28705: The grantor does not have privileges 
to grant the role TEST_ROLE1 to the program units

GRANT test_role1 TO PROCEDURE test_current_user;

ORA-28705: The grantor does not have privileges 
to grant the role TEST_ROLE1 to the program units

So first the role test_role1 must be granted properly to user test_user1 as user SYS

SQL SYS>
GRANT test_role1 TO test_user1 WITH DELEGATE OPTION;

then grants will work

SQL TEST_USER1>

GRANT test_role1 TO PROCEDURE test_definer; 
GRANT test_role1 TO PROCEDURE test_current_user;

Let’s see how it impacts code executed as TEST_USER1

SQL TEST_USER1>

SET SERVEROUTPUT ON
EXEC test_definer

---- Definer ----
Roles:
TEST_ROLE1
Privileges:
CREATE SESSION
CREATE TABLE
CREATE PROCEDURE

SET SERVEROUTPUT ON
EXEC test_current_user

---- Current_user ----
Roles:
TEST_ROLE1
Privileges:
CREATE SESSION
CREATE TABLE
CREATE PROCEDURE

As you can see for both pragma the role TEST_ROLE1 is enabled because it’s granted to the code directly.

What about user TEST_USER2

SET SERVEROUTPUT ON
EXEC test_user1.test_definer

---- Definer ----
Roles:
TEST_ROLE1
Privileges:
CREATE SESSION
CREATE TABLE
CREATE PROCEDURE

SET SERVEROUTPUT ON
exec test_user1.test_current_user

---- Current_user ----
Roles:
TEST_ROLE1
TEST_ROLE2
Privileges:
CREATE SESSION
CREATE TABLE
CREATE VIEW
CREATE TRIGGER

Now for each pragma role TEST_ROLE1 is enabled. It’s very interesting concept but I expect it will cause a lot of surprises for developers.

Have a fun 🙂
Tomasz

DBAORA

oracle expert presents

Code Based Access Control for Definer’s Rights and Invoker’s Rights Oracle Database 12C release 2 (12.2)

Leave a Reply Cancel reply