Thursday, July 18, 2013

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

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> 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!


Blogger Parthiban Nagarajan said....

Interesting! Looking forward for "Part-2"!

Thu Jul 18, 02:03:00 PM EDT  

Blogger bhav22 said....

Very interesting. Thank you Tom

Mon Jul 22, 05:54:00 PM EDT  

Anonymous Stephen said....

Now if I could only grant execute on a function/procedure within a package to a role. Something like grant execute on my_pkg.p to scott;. Instead of the entire package.

Thu Jul 25, 09:10:00 PM EDT  

Anonymous Oracle Connections said....

do upload part 2 of the blog soon, part one is quite interesting

Tue Jul 30, 04:34:00 AM EDT  

Anonymous Anonymous said....

It would create a maintenance nightmare.

Thu Aug 08, 08:51:00 AM EDT  

Blogger Thomas Kyte said....


I think it would create global cooling.

there - now we both have said un-backed up opinions.

care to elaborate? Since, well, you know - many major programming languages have similar concepts already.

the concept of least privilege does not make the (sloppy) programmers life "easier". The concept of least privilege increased the burden on the developer to think about what they are doing. The (sloppy) programmer would love to just have DBA and let everything do anything.

The more individual grants, at a lower level of granularity, that are proven out to be "just what is needed" leads to more secure applications. Given we read about SQL injection (*still*, amazingly *still* in the year 2013) shows that the need to build secure applications - to take a little bit of time - is beyond necessary.

Thu Aug 08, 09:07:00 AM EDT  

Anonymous Anonymous said....

It is not un-backed opinion, it is practical experience. In every development environment there is a certain level of disorder. When code is migrated from development to testing to pre-production to production, many things do go wrong. By adding another layer of "what privileges do each of the stored procedures need", the probability of missing something out increases. More moving parts equals more problems. What happened to the "keep it simple"?

Fri Aug 09, 08:47:00 AM EDT  

Blogger Thomas Kyte said....


what happened to "keep it secure"?

like I said - some (sloppy) programmers would LOVE to have DBA.

"it is so simple, everything just works, I can do whatever I want"

however, is simple in that case smart? correct? secure?

If you do not know what privileges each of the stored procedures need, you haven't done your job.

the concept of least privileges does not exist to make your life "simple", it exists to make systems secure.

that is practically experienced all of the time. Over grant and bad things happen. Grant exactly, precisely, only that which you need and less bad things happen.

Fri Aug 09, 08:55:00 AM EDT  

Anonymous Anonymous said....

however, is simple in that case smart? correct? secure?

It is too late. Can you think of any large user of Oracle where they would be willing to re-visit each stored procedure to figure out what privileges can be granted and would doing so make the code secure?

What about schema comparison tools? Would they be able to figure out such privilege differences at stored procedure level?

Security cannot be implemented on a piece meal basis. Oracle introduced case sensitive passwords in 11g after an article was published pointing to weakness in password algorithm. Oracle still does not accept spaces and certain other characters in passwords. Network ACLs were implemented in 11gr2.

Security in most places is passing certain audit requirements. No one really cares if the code is actually secure or not. If that was not the case, SQL injection would not be happening in 2013.

It is sad but true that security is always tightened reactively instead of proactively. Security features coming out few years apart do not do anything to increase security.

Fri Aug 09, 01:46:00 PM EDT  

Blogger Thomas Kyte said....


it is *never* too late. Do you think that perhaps some large government installations aren't right now, at this very moment - rethinking "security" and least privileges from the ground up (you know, to prevent "leaks" of data in the future)...

do you not think that large financial institutions likewise are not?

what about schema comparison tools - sure, as long as they are coded to look for it - they can find anything. it is after all stored nicely in a data dictionary (try that in your programs *outside* the database)

you are absolutely right that security cannot be implemented piece meal. However - it has to be implemented doesn't it. according to you - we should stick with doing things the way we did them ten years ago because "hey, it is too late you know".

ops$tkyte%ORA11GR2> create user abc identified by "foo bar";

User created.

ops$tkyte%ORA11GR2> grant create session to abc;

Grant succeeded.

ops$tkyte%ORA11GR2> connect abc/"foo bar"

abc%ORA11GR2> alter user abc identified by "!@#$%^&* ()_+=";

User altered.

abc%ORA11GR2> connect abc/"!@#$%^&* ()_+=";

we don't allow spaces or special characters?

and *what the heck do passwords have to do with this feature*???

umm - that would be *nothing*, it would be changing the subject.

Security features coming out few years apart do not do anything to increase security.

that has got to be the most uninformed comment I've ever seen here.

Change Security to High Availability, to Ease of Use, to Self managing, to anything - you'll see how "not really smart" that statement sounds.

It gives you the definite ability to *increase* security. Without new security features - you'll not really be able to advance forward too much.

According to you - no software should implement any new features - it doesn't do anything.


Fri Aug 09, 04:08:00 PM EDT  

Anonymous Hubert said....

This is gorgeous!

Thu Aug 22, 03:12:00 AM EDT  

Anonymous Oracle Certification said....

Thanks for sharing this....

Qadir Shaikh.

Wed Dec 18, 07:19:00 AM EST  


<< Home