Oracle Database auditing with Oracle unified auditing

How to use Oracle Unified Auditing to meet your auditing requirements for Oracle Database

For any company requiring auditing requirements for their Oracle database or databases they should consider using Oracle’s unified auditing. And here’s why:

Oracle unified auditing is a new auditing feature which was introduced first in Oracle 12.1 and is now Oracle’s recommended auditing option. Traditional auditing is deprecated from Oracle 21c. There are many reasons to add auditing to your database, these could be and not limited to – notification of unauthorized changes to important data, deter users, suspicious activity, unauthorized use of database and many other reasons. To enable unified auditing it requires the relinking of the Oracle binaries with the new uniaud_on and a restart of the instances.  It is highly recommended to place auditing inside it’s own tablespace, this can be done by allocating a tablespace for the unified auditing data. The audit trail and audit policies can be maintained by those users granted the AUDIT_ADMIN role.  For users that need to view the audit information, the AUDIT_VIEWER role can be assigned.

Oracle unified auditing advantages:

• Single unified audit trail, simplifies managing multiple audit trails.
• Predefined audit policies which may be useful out of the box.
• Easily create audit policies to suit your company’s auditing requirements.
• Auditing performance is improved for both reads and writes (12.2+).  Performance impacts are lower than traditional auditing.
• Once enabled unified auditing is always on, no more needing to set different initialization parameters.

Oracle unified auditing recommendations:

• Keep unified auditing in a separate tablespace.
• Consider a retention policy for your auditing information.
• Target auditing that is required only, collecting unnecessary audit information increases impacts on performance and increases storage use.

Some handy SQL to check whether Unified Auditing is enabled and currently in use:

Determine whether unified auditing is enabled within the database:

SELECT value from v$option where parameter = ‘Unified Auditing’;

Checking which policies are enabled:

SELECT policy_name, enabled_opt, user_name FROM audit_unified_enabled_policies

Check the default policies and conditions for pre-defined policies ORA_SECURECONFIG and ORA_LOGON_FAILURES:

SELECT policy_name,audit_option,condition_eval_opt,audit_condition FROM audit_unified_policies WHERE  policy_name in (‘ORA_SECURECONFIG’,’ORA_LOGON_FAILURES’) order by 1;