Andrea Danti - Fotolia
Using Oracle 12c Unified Auditing to set database audit policies
Oracle Database 12c's built-in Unified Auditing feature streamlines the database auditing process, including creation and implementation of audit policies. Here's how that works.
Most database engines have audit trail capabilities via a facility that logs important database operations outside of embedded application controls. Things changed a bit with Oracle's audit trail when the company introduced its Unified Auditing feature as part of Oracle Database 12c in 2013.
Database administrators (DBAs) can use both the old database auditing facility and Oracle 12c Unified Auditing simultaneously in an approach called mixed mode auditing that's enabled by default when new databases are created. Alternatively, databases can be configured with Unified Auditing alone -- and the new facility does provide a few benefits to DBAs.
Chief among these benefits is the ability to unify audit trails from the database engine, plus Oracle Recovery Manager, Database Vault, Label Security and other components, in a single location and format -- hence the feature's name. Unifying the different audit trails in one place lessens management duties, while increasing audit trail security. Oracle database auditing performance is also improved, especially in Oracle Database 12c Release 2 -- i.e., 12.2 -- where the audit trail is stored in SYS.ADU$UNIFIED, a partitioned table.
Another benefit to Oracle 12c Unified Auditing is the ability it gives DBAs to create audit policies and then assign them to different users, similar to assigning system privileges to users based their roles. In addition, audit policies can now have conditions and exclusions for more granular control over when the audit trail record is written. Let's look more closely at how to use the audit policy capabilities.
Ready to roll on database audits
Oracle 12c Unified Auditing includes a few canned audit policies that contain commonly audited actions so a DBA can begin the auditing process more quickly. These predefined audit policies can be seen by querying the AUDIT_UNIFIED_POLICIES view for those policy names that begin with the ORA_ prefix. For example, the ORA_LOGON_FAILURES policy audits failed logons, and ORA_ACCOUNT_MGMT audits actions to create, alter and drop users and roles, as well as GRANT and REVOKE statements.
To assign an audit policy to a specific user or group of users, an AUDIT POLICY command similar to this one is issued:
SQL> AUDIT POLICY ora_account_mgmt BY system,my_dba_user;
Audit succeeded.
The users designated as SYSTEM and MY_DBA_USER are now covered under this audit policy. To illustrate the policy in use, the SYSTEM user can grant an application role to another user:
SQL> connect system/manager
Connected.
SQL> grant app_role to scott;
Grant succeeded.
Now the unified audit trail is queried to show that the action took place. The DBA_COMMON_AUDIT_TRAIL view lets us access the traditional audit trail and the unified one from a single location:
SQL> SELECT db_user,statement_type,sql_text FROM dba_common_audit_trail;
DB_USER STATEMENT_TYPE SQL_TEXT
--------- --------------- -----------------------------------
SYSTEM SYSTEM GRANT grant app_role to scott
Build your own Oracle audit policies
Beyond the canned policies, DBAs can easily create their own audit policies with Oracle 12c Unified Auditing, similar to the following example:
SQL> CREATE AUDIT POLICY app_audit_policy
2 ACTIONS SELECT ON oe.orders, INSERT ON hr.employees;
Audit policy created.
The new audit policy can then be enabled for all users of the database:
SQL> AUDIT POLICY app_audit_policy;
Audit succeeded.
To instead apply the policy to specific users, the BY clause is used, as was shown earlier. When no users are defined via that clause, the AUDIT POLICY command applies to all the database users. Conversely, in order to exclude some users from the audit policy actions, the existing policy needs to be disabled with the NOAUDIT POLICY command; then, the audit policy must be told to exclude the desired users -- in this case, both SYS and SYSTEM -- before it can be implemented:
SQL> NOAUDIT POLICY app_audit_policy;
Noaudit succeeded.
SQL> AUDIT POLICY app_audit_policy except sys,system;
Audit succeeded.
Something else to keep in mind as you begin using Oracle 12c Unified Auditing is that some administrator functions applied to audit policies in the new approach aren't noticed by end users until they establish a new connection.
For example, if a DBA drops an audit policy, it will stay in effect for current user sessions. New sessions then won't have the policy applied to them. Unified audit policy details are read into a user session's private memory when the session connects to the database instance, whereas policy changes made in the traditional audit facility take effect immediately.
