Auditing by the Numbers


There are over 60 controls in the DISA Oracle 12c Database Secure Technical Implementation Guide (STIG) that contain the word “audit” or “auditing”. At first that can seem like a really long and confusing list of things to configure, but it turns out a lot of those controls have identical fixes. Following is a consolidated list of the bare bones minimum auditing required by the Oracle 12c Database STIG.

The first thing to do is enable full Unified Auditing. This introduces several built-in access controls and tools that will make auditing of the database simpler. To enable Unified Auditing, complete the following steps:

  1. Confirm whether or not UA is already enabled. If the response to the following query is “TRUE”, then it is.
    SELECT * FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';
  2. If UA is not enabled, then follow the steps in Section 4.6.13.2 of the Oracle Database Upgrade Guide to turn it on.

Once enabled, the following audit policies represent the minimum required audits defined in the STIG. The STIG also allows for organization- or application-specific audits to be required, and includes several protections for the audit trail itself, which I will not go into with this particular post. As much as I’d love to do a more complete analysis of auditing guidelines under the STIG, I just don’t have the time this month to do it justice. I’ll keep it in mind for a future post or paper, though.

The six main areas of STIG-mandated audit policies that need to be created are:

  1. Successful and unsuccessful attempts to access, modify, or delete privileges, security objects, security levels, or categories of information (e.g., classification levels)
  2. Oracle Label Security administrative actions
  3. Starting and ending time for user access to the system, concurrent logons from different workstations
  4. All PL/SQL program initiations
  5. All account creations, modifications, disabling, and terminations
  6. All database parameter changes

The following script will create and activate an Oracle 12c audit policy for each of those areas:

-- audit granting and revocation of any privilege:
create audit policy stig_grant_privilege_actions actions grant, revoke;

-- audit all OLS administrative actions:
create audit policy stig_ols_admin_actions actions component = OLS all;

-- audit all user logon and logoff attempts:
create audit policy stig_user_logon_actions actions logon, logoff;

-- audit execution of any PL/SQL program unit:
create audit policy stig_execute_plsql_actions actions execute;

-- audit all user administration actions:
create audit policy stig_user_admin_actions actions create user, alter user, drop user, change password;

-- audit any database parameter changes, dynamic or static:
create audit policy stig_db_param_actions actions alter database, alter system, create spfile;

audit policy stig_grant_privilege_actions;
audit policy stig_ols_admin_actions;
audit policy stig_user_logon_actions;
audit policy stig_execute_plsql_actions;
audit policy stig_user_admin_actions;
audit policy stig_db_param_actions;

One thought on “Auditing by the Numbers

Leave a Reply to Five Thoughts on Oracle Security | Late Night Oracle Blog Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.