12c - Code Based Access Control (CBAC) part 1

One frequently asked question posed by stored procedure developers since version 7.0 was released in 1992 has been "why do I get an ora-1031 in PL/SQL but not in SQL*Plus directly".  I get this question on asktom a lot, I've written articles about it in Oracle magazine, I must have explained this thousands of times by now.

And now, it all has to change - roles and stored procedures are no longer like matter and anti-matter.  Staring in Oracle Database 12c a role may be granted to a bit of code, and that role will be active only while that procedure is executing.  Think about what that means - you can have a schema chock full of code - hundreds of packages - but have only one package that has a certain privilege at run time.  This will allow you to implement the concept of "least privileges" fully.  What could be more least privileges than granting a privilege to a specific bit of code?

Think about this from a SQL injection protection point of view.  You could either grant privilege X directly to the schema  - meaning this privilege would be available for every single stored unit in that schema to use at anytime, or you can grant this privilege to a role and then grant that role to that unit.  That unit and only that unit would be able to use privilege X at runtime.  If some other units in that schema had a SQL injection bug - they would not be able to utilize that privilege.

We'll take a look at this new capability from two perspectives - from that of a definers rights routine (the default) and from that of an invokers rights routine.  In the case of the definers rights routine, this new capability will only make sense when you use dynamic SQL.  In the case of the invokers rights routine, this new capability has a much larger impact and makes the use of invokers rights routines much wider than it was in the past.  I'll defer talking more about invokers rights routines until next time and we'll concentrate on definers rights routines for now.

Definers rights routines compile with the set of privileges granted directly to the owner of the procedure - roles are never enabled during the compilation of a compiled stored object.  This is true in Oracle Database 12c still - and is the reason this new capability only makes sense with dynamic SQL in a definers rights routine.  In order for the unit to compile, all of the privileges necessary for the static SQL and PL/SQL in the unit must be granted directly to the owner of the unit.  Therefore - any privileges granted via roles cannot be used for static SQL or PL/SQL.  The compilation would fail without the direct privilege.  However, any dynamically executed code would not be security checked until runtime, the compiler would not "see" this code.  And with CBAC - the set of privileges the dynamic SQL will be checked with will be all of the privileges granted directly to the owner of the unit and any privileges associated with roles granted to the unit.

So, if we start with a simple user and role:

ops$tkyte%ORA12CR1> create user a identified by a
  2  default tablespace users
  3  quota unlimited on users;
User created.

ops$tkyte%ORA12CR1> create role create_table_role;
Role created.

and then we grant some privileges to the user and the role:

ops$tkyte%ORA12CR1> grant create table to create_table_role;
Grant succeeded.

ops$tkyte%ORA12CR1> grant create session, create procedure to a;
Grant succeeded.

ops$tkyte%ORA12CR1> grant create_table_role to a with admin option;
Grant succeeded.

ops$tkyte%ORA12CR1> alter user a default role all except create_table_role;
User altered.

we are ready to start. Note that the user A has only the create session and create procedure privilege granted to the directly. They do have the CREATE TABLE privilege, but that privilege is granted via a role to the user - it will not be available to that user during the compilation of a stored unit, nor would it be available at runtime (until we grant it to the code itself).

So, let's create a procedure in this account:

ops$tkyte%ORA12CR1> connect a/a
Connected.

a%ORA12CR1> create or replace procedure p
  2  as
  3  begin
  4          execute immediate
  5          'create table t ( x int )';
  6  end;
  7  /
Procedure created.

Now, the procedure created successfully since we had the create procedure privilege, but if you try to run it you would receive:

a%ORA12CR1> exec p
BEGIN p; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "A.P", line 4
ORA-06512: at line 1

and in fact in 11g and before - that would be the only thing you would ever receive.  You would have to grant CREATE TABLE to the schema A - making it available to every single stored unit in that schema.  But in Oracle Database 12c - we can grant the CREATE_TABLE_ROLE to the procedure:

a%ORA12CR1> set role create_table_role;
Role set.

a%ORA12CR1> grant create_table_role to procedure p;
Grant succeeded.

a%ORA12CR1>
a%ORA12CR1> exec p

PL/SQL procedure successfully completed.

a%ORA12CR1> set linesize 40
a%ORA12CR1> desc t
 Name              Null?    Type
 ----------------- -------- ------------
 X                          NUMBER(38)

the CREATE TABLE privilege is now available to the stored procedure P and only that stored procedure.  No other bits of code in this schema would be able to create a table.

So, in short, dynamic SQL and PL/SQL executed within a definers rights routine can now take advantage of privileges granted to roles.  This will allow you to implement the concept of "least privileges" (and to use roles in definers rights routines).

In the next article, we'll look at this from the perspective of an invokers rights routine.  That is where this new capability gets really interesting!