One of the many nice new features in 12c database: code based access control

Topic of this blog is a nice new feature in 12c, not the plsql package I built that’s using it. So here’s the story..

For one of our customers we needed to have a simple schema comparison tool that would be able to check, as part of application deployment activity, whether  there is any discrepancy between the schema that was used to build the application (.ear) files and the target schema of the deployment. Of course there are quite a few schema comparison tools out there in the wild, including those from Oracle corp like what’s offered in sql developer and in cloud control, but none met our requirement that it must be possible to ship this compare-schema-code as yet another deployment artifact and have it run automatically as part of the deployment.

After some consideration we decided to look into the SYS owned DBMS_METADATA PLSQL package which is available in each Oracle database. Quote from doc: “The DBMS_METADATA package provides a way for you to retrieve metadata from the database dictionary as XML or creation DDL and to submit the XML to re-create the object.” We wanted to use the packaged procedure DBMS_METADATA.GET_DDL to retrieve the DDL of each and every object in a source database schema (e.g. the one used for building the application deployment artifacts), store this DDL in a table and then export and ship this table to the target environment and use it for comparison. The format of our schema-comparison tool should be a PLSQL packaged procedure with EXECUTE privilege granted to those database users who need it. The Metadata API (mdAPI) has an Oracle dictionary that contain lots of views (starting with “KU$_..”) with SELECT privilege on them granted to PUBLIC and that also contain a CURRENT_USERID checking security clause and a check whether or not the invoker of the view has the SELECT_CATALOG_ROLE. For example in the definition of ku$_table_objnum_view view (see ?/rdbms/admin/catmetviews.sql)

AND (SYS_CONTEXT('USERENV','CURRENT_USERID') IN (o.owner_num, 0)

OR EXISTS ( SELECT * FROM sys.session_roles WHERE role='SELECT_CATALOG_ROLE' ))

this filter translates to: invoker of DBMS_METADATA API must be

1. the owner of the objects whose metadata is being retrieved
2. user SYS
3. a user having the SELECT_CATALOG_ROLE turned on

so to be able to retrieve an object’s metadat using DBMS_METADATA plsql package one of the following needs to be true:

1. our solution plsql code needs to be in the same database schema as the application objects
2. the userid running our plsal code needs to be SYS (CURRENT_USERID=0)
3. when the stored plsql code is run the SELECT_CATALOG_ROLE needs to be turned on

As we want to make our plsql procedure available in a central schema, only the last option is viable. The SYS owned DBMS_METADATA package itself runs with invokers rights (AUTHID CURRENT_USER) and as such relies on the privileges in the security context of the invoker. Since the DBMS_METADATA plsql package is called by our own plsql package TBS_UTIL, i.e. the invoker of DBMS_METADATA, it depends on whether or not our own TBS_UTIL package is defined with definer rights (AUTHID DEFINER) or invoker rights. If our package TBS_UTIL would be defined with invoker’s rights then any user with EXECUTE privilege on TBS_UTIL and executing it should have the SELECT_CATALOG_ROLE enabled in its session. If, however, we decided to create our TBS_UTIL package with DEFINER rights then we would be in trouble since the SELECT_CATALOG_ROLE role that the owner of TBS_UTIL package must have cannot be used when TBS_UTIL is being executed. Since we don’t want to grant the SELECT_CATALOG_ROLE role to every database user that needs to be able to execute the TBS_UTIL package we need to solve our privilege problem. Fortunately, and that’s the whole topic of this blog, does Oracle database 12 c have this nice new fature called CBAC.

What’s CBAC again?..Remember RBAC (Role Based Access Control) where roles granted to users determine the sets of permissions to be granted those users and ABAC (Attribue based Access Control) where runtime evaluation of (dynamically changing) attributes such as time of day, location or strength of authentication method are used in access control decisions ? Here’s another sibling: CBAC (Code Based Access Control).  The role privileges are being granted to the plsql code next to being granted to the definer of the plsql code, so this means following two GRANT statements should be run by user SYS

-- 1st : grant the role to the definer of the plsql package

GRANT SELECT_CATALOG_ROLE TO TBS_UTIL;

-- 2nd : issue the CBAC statement itself
GRANT SELECT_CATALOG_ROLE TO PACKAGE UTILITY.TBS_UTIL;

If the definer of plsql package TBS_UTIL, user UTILITY,  hasn’t  been granted the SELECT_CATALOG_ROLE role the CBAC statement will fail as you can see below:

GRANT SELECT_CATALOG_ROLE TO PACKAGE UTILITY.TBS_UTIL
 *
 ERROR at line 1:
 ORA-01924: role 'SELECT_CATALOG_ROLE' not granted or does not exist

but after we have granted the SELECT_CATALOG_ROLE role back to the owner UTILITY of the plsql package TBS_UTIL:

GRANT SELECT_CATALOG_ROLE TO UTILITY;
Grant succeeded.

the grant can be given

GRANT SELECT_CATALOG_ROLE TO PACKAGE UTILITY.TBS_UTIL;
Grant succeeded.

and the user C1 can call the packaged procedure UTILITY.TBS_UTIL.GET_SOURCE_DDL without any privilege issues. Without the CBAC statement the user C1 gets an ORA-31603 error ( “object \”%s\” of type %s not found in schema \”%s\””) when invoking the packaged procedure UTILITY.TBS_UTIL.GET_SOURCE_DDL

In the example shown below the central schema containing our solution plsql package TBS_UTIL is called UTILITY.
DBMS_METADATA is owned by SYS and created with invoker’s rights (AUTHID CURRENT_USER)
Our plsql package can be identified  using UTILITY.TBS_UTIL and has a packaged procedure GET_SOURCE_DDL, the TBS_UTIL package is created with definer rights (AUTHID DEFINER)
EXECUTE privilege on the TBS_UTIL package has been granted to database user C1
The schema whose METADATA we are after in this example is called PM_OWNER and it contains a packaged procedure with a plsql function called GETBIN:

C1> exec utility.tbs_util.get_source_ddl('PM_OWNER')

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "UTILITY.TBS_UTIL", line 83
ORA-06512: at "UTILITY.TBS_UTIL", line 478
ORA-31603: object "GETBIN" of type FUNCTION not found in schema "PM_OWNER"
ORA-06512: at "UTILITY.TBS_UTIL", line 541
ORA-06512: at line 1

What if definer user UTILITY suddenly would wear a blackhat one morning and decide to recreate the package code TBS_UTIL using CREATE OR REPLACE clause (thereby preserving any grants) with something nasty that became possible due to the privileges associated with the SELECT_CATALOG_ROLE role? A security officer’s nightmare, that’s what it would be. Fortunately it turns out that the CBAC statement needs to be repeated when Oracle finds out that the code, that has been granted a role, has changed. It will fail until user SYS has granted the SELECT_CATALOG_ROLE role to the plsql package TBS_UTIL back again.

More on Code Based Access Control  in the Oracle documentation can be found here.

Happy CBAC’ing!