By Troy Kitch on Mar 10, 2014
This is the fourth and final excerpt in Chapter 1 of Securing Oracle Database 12c: A Technical Primer ebook from Oracle Press. You can read more about controlling data access and restricting privileged data in part 1, part 2 and part 3 of these blog posts. Look for more chapters coming soon, or get your own copy of the complimentary ebook. Thanks for reading.
Controlling Privileged Users
System privileges and powerful roles give significant control of the database, including the ability to view all data and make changes to the data. Some administrative users need these powerful privileges for maintenance, tuning, and backups, but they don’t need access to all of the data. Even though the administrative users are trusted, it is important to secure company data assets and personal information even from these privileged accounts in order to prevent unauthorized use by insiders or attackers.
Oracle Database Vault provides several kinds of operational controls within the database including realms, which enforce limits on access to specified objects such as tables and views. After creating a Database Vault realm, objects are added to the realm and database users can be designated as realm participants. This provides access only to the realm participants, and excludes other users, even if they have powerful system privileges like SELECT ANY TABLE that would otherwise allow them to access the objects in the realm.
The following illustration shows an example of two realms, protecting database schemas containing human resources (HR) and finance (FIN) data. Once enabled, the realms prevent privileged administrative users or other application owners from using their elevated privileges to access data. The privileged application owner HR is prevented from accessing data inside the FIN realm, and even an administrator with the DBA role is unable to access data in the HR and FIN realms.
In addition to regular realms, Oracle Database 12c adds the ability to create mandatory realms. A regular realm will block the use of system privileges such as SELECT ANY TABLE if the user is not a realm participant, but it doesn’t block the schema owner or other users who gain access to the data using object privileges. Mandatory realms prevent access by anyone who is not a realm participant. One popular use for a mandatory realm is to continue to protect sensitive data during patching and upgrades, when an administrator needs to make changes to the application schema but should not have access to the data tables in that schema.
When Oracle Database Vault is configured, a couple of additional users are created. The first of these is the Database Vault owner, who can create and manage realms to control access to sensitive data. The second user is the Database Vault account manager, who has the responsibility for creating users in the database. While a single user could perform both functions, the ability to divide these duties among multiple users allows for separation of duty as described earlier. Furthermore, there is a DVOWNER role that can be granted to other users to delegate the ability to manage Database Vault realms. This role should be granted to administrators who are responsible for the security configuration of the database, rather than the general database administrator.
The following illustration shows the use of the Database Configuration Assistant for enabling Oracle Database Vault. Management of Database Vault requires the use of these specialized users and roles. The SYSDBA administrative privilege cannot be used for realm or user management when Database Vault is enabled.
Managing Granted Privileges
Following the principle of least privilege means that each user, and each role granted to users, should have only the minimal set of privileges needed to perform their intended function. While there are dictionary tables to show which permissions and roles have been granted, it is much harder to determine which ones are actually needed. This is especially true in systems that have been in use for some time, since privilege and role grants tend to accumulate and it is difficult to know when it is safe to revoke them.
Oracle Database Vault 12c includes a new feature called Privilege Analysis that captures privileges as they are used at run-time and generates a series of reports. These reports can be used to find privileges that may no longer be needed or even to generate scripts to revoke unused privileges automatically.
Here is an example to enable the privilege capture for all users of the database.
SQLPLUS> BEGIN DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE
(NAME => 'dba_capture_all_privs',
DESCRIPTION => 'privilege_analysis_for_all_users',
TYPE => DBMS_PRIVILEGE_CAPTURE.G_DATABASE);
PL/SQL procedure successfully completed.
After a suitable interval to capture the privileges used during normal operation, the DBA_USED_PRIVS and DBA_UNUSED_PRIVS views will reveal which privileges have been used and, more importantly, which granted privileges have not been used.
SQLPLUS> BEGIN DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT
(NAME => 'dba_capture_all_privs');
PL/SQL procedure successfully completed.
SQLPLUS>SELECT USERNAME, USED_ROLE, SYS_PRIV, OBJ_PRIV, USER_PRIV,
SQLPLUS> SELECT USERNAME, USED_ROLE, SYS_PRIV, OBJ_PRIV, USER_PRIV, OBJECT_OWNER, OBJECT_NAME
A script to revoke the unused privileges could be generated using a SQL statement like the following. An administrator should review the script to verify the list of privileges to be revoked before executing it.
--Generate a script to revoke
SQLPLUS> SELECT 'revoke '||OBJ_PRIV||' on '||OBJECT_OWNER||'.'||
OBJECT_NAME||' from '||USERNAME||';'
The views available to display the information generated in the privilege capture are as follows.
|DBA_PRIV_CAPTURES||Lists information about existing privilege analysis policies.|
|DBA_USED_PRIVS DBA_UNUSED_PRIVS||Lists the privileges that have (or have not) been used for reported privilege analysis policies.|
|DBA_USED_OBJPRIVS DBA_UNUSED_OBJPRIVS||Lists the object privileges that have (or have not) been used for reported privilege analysis policies.|
|DBA_USED_OBJPRIVS_PATH DBA_UNUSED_OBJPRIVS_PATH||Lists the object privileges that have (or have not) been used for reported privilege analysis policies. It includes the object privilege grant paths.|
|DBA_USED_SYSPRIVS DBA_UNUSED_SYSPRIVS||Lists the system privileges that have (or have not) been used for reported privilege analysis policies.|
|DBA_USED_SYSPRIVS_PATH DBA_UNUSED_SYSPRIVS_PATH||Lists the system privileges that have (or have not) been used for reported privilege analysis policies. It includes the system privilege grant paths.|
|DBA_USED_PUBPRIVS||Lists all the privileges for the PUBLIC role that have been used for reported privilege analysis policies.|
|DBA_USED_USERPRIVS DBA_UNUSED_USERPRIVS||Lists the user privileges that have (or have not) been used for reported privilege analysis policies.|
|DBA_USED_USERPRIVS_PATH DBA_UNUSED_USERPRIVS_PATH||Lists the user privileges that have (or have not) been used for reported privilege analysis policies. It includes the user privilege grant paths.|