In the Field

Privilege Analysis

It’s a cool Oracle Database 12c security feature.

By Michelle Malcher Oracle ACE Director

March/April 2014

I must admit that I am excited about all of the security features that are part of the new Oracle Database 12c release. I am sure that everyone securing databases and providing audit details is just as excited (even if they don’t openly admit it). Of course, Oracle Database has provided a secure configuration and a strong security solution for a long time now, but Oracle Database 12c delivers new unified auditing and easier security maintenance.

For example, the new Privilege Analysis feature in Oracle Database 12c (part of the Oracle Database Vault option) provides a way to maintain least privilege by reporting on the permissions that a user is actually using and not just the permissions a user has been granted. Maintaining least privilege involves granting the privileges and access to objects that are required and revoking privileges and access that are not required.

You can grant, test, verify, modify, and maintain a set of least privilege roles and access rights.

Although audit reports of users with privileged access and what roles are granted can be pulled from the DBA_ROLE_PRIVS and DBA_TAB_PRIVS dictionary tables, these reports show only what permissions are available. With the new Privilege Analysis feature, reports can show which of these privileges are actually being used and provide information about the database, roles, and context privileges. The DBMS_PRIVILEGE_CAPTURE package will turn on the privileges analysis and generate the report:

(NAME => 'dba_capture_all_privs',
'privilege_analysis_example_for_all_users', TYPE => DBMS_PRIVILEGE_
(NAME => 'dba_capture_all_privs');

Querying the DBA_USED_PRIVS, DBA_USED_OBJPRIVS, DBA_UNUSED_PRIVS, and DBA_UNUSED_OBJPRIVS tables provides details for developing a script to create a role with the privileges used or a script to revoke unused privileges. Here’s an example:

--Generate a script 
--to revoke permissions
SELECT 'revoke '||OBJ_PRIV||'
on '||OBJECT_OWNER||'.'||
OBJECT_NAME||' from ' ||USERNAME||';'

This is very useful and simplifies the request to grant the same permissions to another user without overgranting permissions. With the Privilege Analysis feature, instead of granting a user full permissions on a schema or even granting a DBA role, you can grant, test, verify, modify, and maintain a set of least privilege roles and access rights.

With Privilege Analysis, audit questions have gone past the simple “who has what privilege?” to ask for more details. Privilege Analysis helps provide answers to the following questions:

Why are these permissions granted? The analysis shows what the user is accessing and verifies why the permissions are there. (The answer goes beyond stating that the user wanted the permissions or that this is what you grant all users of the application.)

Do users have access to more than they need? If you are maintaining least privilege, the answer here will be no. Because you are able to revoke privileges based on information in the DBA_UNUSED_PRIVS table, you can continue to maintain least privilege and verify that users have access only to the objects and roles they need.

How do you maintain least privilege? Privilege Analysis generates reports and information in tables to automate scripts for maintaining permissions instead of generating reports you can review to see what permissions are granted. This automation makes it easy to limit access to only the needed objects and roles.

Privilege Analysis is just one of the new security features in Oracle Database 12c. There are plenty of new features to look at in Oracle Database 12c, and you’ll find great information from the user group community—including the Independent Oracle Users Group (IOUG)—on how to take advantage of these features.

Next Steps


LEARN more about
 Oracle Database 12c
Oracle Database 12c security features
 Securing Oracle Database 12c: A Technical Primer


Photography byRicardo Gomez Angel,Unsplash