What constitute "default auditing" under Unified Auditing?

Under Mixed-mode auditing and Pure Unified Auditing, the policies ORA_SECURECONFIG and ORA_LOGON_FAILURES are audited by default.

From the documentation:

ORA_SECURECONFIG audits the same default audit settings from Oracle Database Release 11g.
It tracks the use of a number of privileges such as ALTER ANY TABLE, GRANT ANY PRIVILEGE, and CREATE USER. The actions that it tracks include ALTER USER, CREATE ROLE, LOGON, and other commonly performed activities.

There are a couple of other policies that are not enabled by default but certainly worth auditing. These are:

ORA_DATABASE_PARAMETER audits commonly used Oracle Database parameter settings: ALTER DATABASE, ALTER SYSTEM, and CREATE SPFILE.

ORA_ACCOUNT_MGMT audits the commonly used user account and privilege settings: CREATE USER, ALTER USER, DROP USER, CREATE ROLE, DROP ROLE,ALTER ROLE, SET ROLE, GRANT, and REVOKE.

Enabling these is as simple as executing the following SQL against the database as a privileged user:

AUDIT POLICY ORA_DATABASE_PARAMETER;
AUDIT POLICY ORA_ACCOUNT_MGMT;

How to create a unfied auditing policy that captures logons from privileged users

By default, only unsuccessful logon attempts are audited by mandatory auditing under Unified Auditing.
The policy used for this purpose is ORA_LOGON_FAILURES, and it will audit both privileged and non-privileged users' attempts to logon to the database.

If you want to audit all privileged users that have successfully logged onto the database, you need to create a new policy.

Here is how:

 CREATE AUDIT POLICY PRIVILEGED_USER_LOGONS
   ACTIONS  LOGON
   WHEN 'SYS_CONTEXT (''USERENV'',''CURRENT_USER'') IN (''SYS'',''SYSTEM'')'
   EVALUATE PER SESSION; 

Start using it:

AUDIT POLICY PRIVILEGED_USER_LOGONS;

The resulting audit record can be found immediately afterwards, with this query against UNIFIED_AUDIT_TRAIL:

select os_username "os user",
       userhost "host",
       authentication_type "authtype",
       dbusername "db user",
       client_program_name "client",
       event_timestamp "time",
       action_name "action",
       system_privilege_used "sys priv",
       unified_audit_policies "aud pol"
from UNIFIED_AUDIT_TRAIL 
where event_Timestamp = (select max(event_Timestamp) from UNIFIED_AUDIT_TRAIL)
order by event_timestamp desc;

Result:

os user	host	authtype	db user	client	time	action	sys priv	aud pol
oracle	myserver.mydomain.com	(TYPE=(OS));(CLIENT ADDRESS=((PROTOCOL=beq)(HOST=192.168.0.34)));	SYS	sqlplus@myserver.mydomain.com (TNS V1-V3)	10.03.2022 11:38:14,972147	LOGON	SYSDBA	PRIVILEGED_USER_LOGONS

How to use dbms_metadata to generate DDL for a Unified Auditing Policy

SQL> set long 5000
SQL> select DBMS_METADATA.GET_DDL('AUDIT_POLICY','ORA_LOGON_FAILURES')  from dual;

DBMS_METADATA.GET_DDL('AUDIT_POLICY','ORA_LOGON_FAILURES')
--------------------------------------------------------------------------------

   CREATE AUDIT POLICY "ORA_LOGON_FAILURES" ACTIONS  LOGON

Comparison between default traditional auditing and default unified auditing

Applicable to Oracle Database versions 12.1 and onwards. By default, the databases are created with "Mixed-Mode" auditing enabled. This means that the unified auditing polices ORA_SECURECONFIG og ORA_LOGON_FAILURE are enabled. These policies actually covers a lot of what any DBA would typically want to audit, and overlaps the default (tradtitional) auditing implemented by running $ORACLE_HOME/rdbms/admin/secconf.sql.

Here is a table comparing what the two default settings under each auditing strategy:

My conclusion is that unless your customer specifically wants to continue with traditional auditing, take the opportunity to migrate to Unified Auditing policies implmemented in "Mixed-Mode" auditing. In such cases you should also disable the default traditional auditing by setting the parameter audit_trail to 'NONE'.

What constitute "mandatory auditing" under Unified Auditing?

• Activities from administrative users such as SYSDBA, SYSBACKUP, and SYSKM. 
•  The following audit-related activities are mandatorily audited:

CREATE AUDIT POLICY	AUDIT	EXECUTE of the DBMS_FGA PL/SQL package
ALTER AUDIT POLICY	NOAUDIT	EXECUTE of the DBMS_AUDIT_MGMT PL/SQL package
DROP AUDIT POLICY	Access to sensitive columns in the optimizer dictionary tables.	ALTER TABLE attempts on the AUDSYS audit trail table
Top level statements by the administrative users SYS, SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, and SYSKM, until the database opens	All user-issued DML statements on the SYS.AUD$ and SYS.FGA_LOG$ dictionary tables	Any attempts to modify the data or metadata of the unified audit internal table. SELECT statements on this table are not audited by default or mandatorily.
All configuration changes that are made to Oracle Database Vault

The audit information can be found in the view UNIFIED_AUDIT_TRAIL.
Documentation for Mandatory Unified Auditing in Oracle 12.2 can be found here

How to modify a unified auditing policy to make exceptions based on login information

The audit policy had been created as follows:

create audit policy all_select_policy actions select;

Here is how you can alter an existing policy so that the policy will make an exception for session created by user "DBAADMIN"

alter audit policy ALL_SELECT_POLICY 
condition 'SYS_CONTEXT (''USERENV'',''CURRENT_USER'') NOT IN (''DBAADMIN'')' 
evaluate per Session;

Documentation can be found here
The oracle-supplied policy ORA_LOGIN_FAILURES automatically audits all failed login attempts. You can alter it to exclude certain uninteresting connections, such as for example DBSNMP, like this:

alter audit policy ORA_LOGON_FAILURES
condition 'SYS_CONTEXT (''USERENV'',''CURRENT_USER'') NOT IN (''DBSNMP'')'
evaluate per session;

For more information about the SYS_CONTEXT function, check the official 12.2 documentation.

Why is Oracle producing .aud files for internal sys-statements?

I have recently been in contact with Oracle support regarding an issue where my Oracle 18c database instance is sending audit information for internal statements, much similar to this:

Sun Jan 26 10:25:41 2020 +01:00 LENGTH : '401' ACTION :[147] 'select /*+ opt_param('parallel_execution_enabled', 'false') EXEC_FROM_DBMS_XPLAN */ * from gv$sql_plan where 1=0' DATABASE USER:[1] '/' PRIVILEGE :[4] 'NONE' CLIENT USER:[0] '' CLIENT TERMINAL:[7] 'UNKNOWN' STATUS:[1] '0' DBID:[10] '1325844924' SESSIONID:[1] '0' USERHOST:[26] 'myhost.mydomain.com' CLIENT ADDRESS:[0] '' ACTION NUMBER:[1] '3' Sun Jan 26 10:25:41 2020 +01:00 LENGTH : '375' ACTION :[121] 'SELECT * FROM gv$sql_plan where sql_id = 'a0f1h9d5muwa6' and inst_id = 1 and child_address = hextoraw('00000004FFF16130')' DATABASE USER:[1] '/' PRIVILEGE :[4] 'NONE' CLIENT USER:[0] '' CLIENT TERMINAL:[7] 'UNKNOWN' STATUS:[1] '0' DBID:[10] '1325844924' SESSIONID:[1] '0' USERHOST:[26] 'myhost.mydomain.com' CLIENT ADDRESS:[0] '' ACTION NUMBER:[1] '3'

If you have migrated to Unified Auditing, Oracle states that "audit records are only expected to be generated in database tables and OS spillover files (*.bin) under audit destination path."

However, dynamic SQL statements parsed or executed using DBMS_SQL package are being audited in the conventional *.aud type OS files.

To get rid of these messages piling up in your audit_dump_dir:

alter system set audit_sys_operations=FALSE scope=spfile;
shutdown immediate
startup

If setting audit_sys_operations to FALSE is not desirable, Oracle states that you can request a patch through the following bug number:


Bug 21133343 *.aud file is generated though unified auditing=true and audit_trail=none


Note that you will see the same phenomenon under the mixed-mode or classic auditing.
Oracle does not explisitly say they will provide a patch in this case though.

Documentation from Oracle support: Doc ID 2020881.1: "OS Audit Files *.aud are Still Generated After Migrating to Unified Audit"

How to manuall purge your unified auditing audit trail

This is how I purged my unified audit trail on an Oracle 12.2 test instance.

First, check the LAST_ARCHIVE_TS:

SELECT * FROM DBA_AUDIT_MGMT_LAST_ARCH_TS;

If you want to keep some of your audit records, set a timestamp before which all records should be purged. In this case, I want to purge everything, so I set it to SYSTIMESTAMP:

BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (
    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    LAST_ARCHIVE_TIME => SYSTIMESTAMP);
END;
/

Purge the audit trail:

BEGIN
  DBMS_AUDIT_MGMT.clean_audit_trail(
   audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
   use_last_arch_timestamp => TRUE);
END;
/

If you want to purge all records, run the same procedure but without the "use_last_arch_timestamp" directive:

BEGIN
  DBMS_AUDIT_MGMT.clean_audit_trail(
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED
   );
END;
/

The dbms_audit_mgmt is documented here

How to create a unified auditing policy

For example, create a policy to audit the "create session" privilege:

create audit policy audit_cre_session
  privileges create session;

Start auditing of the policy:

audit policy audit_cre_session;

In the next example, I am creating a policy to audit DML statements on a specific table:

create audit policy audit_dml_emp
actions delete on scott.emp,
        insert on scott.emp,
        update on scott.emp
;

Create a separate policy for auditing of queries against a specific table:

create audit policy audit_select_emp
actions select on scott.emp
;

Finally, start auditing both policies:

audit policy audit_dml_emp;
audit policy audit_select_emp;

The results of the auditing can be observed through the unified_audit_trail view:

select audit_type,
       os_username,
       userhost,
       terminal,
       authentication_type,
       dbusername,
       client_program_name,
       event_timestamp,
       action_name,
       return_code,
       object_name,
       sql_text,
       system_privilege_used,
       unified_audit_policies
from unified_audit_trail
order by event_timestamp desc;

If you later need to modify a policy, use

alter audit policy audit_dml_emp drop actions delete on scott.emp;

to reverse your change back to its original state:

alter audit policy audit_dml_emp add actions delete on scott.emp;

12.2 documentation here

19c documentation here

Roles necessary to administer and view the Unified Audit Trail

Oracle 12c provides two new roles for auditing purposes:

AUDIT_ADMIN for audit configuration/administration
AUDIT_VIEWER for viewing and analyzing audit data

The following can role was granted by myself to allow a senior developer to view the audit information generated in a test database:

grant audit_viewer to scott;

https://docs.oracle.com/database/121/DBSEG/release_changes.htm#DBSEG000
https://docs.oracle.com/database/121/DBSEG/auditing.htm#DBSEG617

How to toggle between "Mixed mode" Auditing, Traditional Auditing and Unified Auditing

This article is applicable to Oracle database versions 12.1 and onwards.

For newly created databases, mixed mode auditing is enabled by default through the predefined policy ORA_SECURECONFIG. 

 

Verify that the database is using "Mixed Mode" auditing

select parameter, value from v$option where parameter='Unified Auditing';

PARAMETER	VALUE
Unified Auditing	FALSE

Check for any enabled unified audit policies:

select policy_name, enabled_option
from audit_unified_enabled_policies;

POLICY_NAME	ENABLED_OPTION
ORA_SECURECONFIG	BY USER

If v$option shows FALSE for unified auditing AND the database have at least one enabled unified auditing policy, we are running in "Mixed Mode" auditing. 

In Mixed Mode Auditing, all of the existing auditing startup parameters for the database are still valid: AUDIT_TRAIL, AUDIT_FILE_DEST, AUDIT_SYS_OPERATIONS, and AUDIT_SYSLOG_LEVEL. So if your AUDIT_TRAIL is set to "DB", you can still use all the expected data dictionary views to obtain your audit information. If AUDIT_TRAIL is set to "OS", your auditing information will be sent to the location specified by the parameter AUDIT_FILE_DEST.

To enable "pure" Unified Auditing

1. Shutdown the database:

shutdown immediate

2. Relink the Oracle database binaries:

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME

3. Enable at least one unified audit policy. By default, two unified auditing policies are created when you create your a 12.2 database: ORA_SECURECONFIG and ORA_LOGON_FAILURES. The first one is enabled by the default, the last one is not. Let's enable the ORA_LOGIN_FAILURES, too:

audit policy ORA_LOGON_FAILURES;

Verify:

select parameter, value from v$option where parameter='Unified Auditing';

PARAMETER	VALUE
Unified Auditing	TRUE

select user_name, policy_name, enabled_opt, enabled_option
from audit_unified_enabled_policies;

USER_NAME	POLICY_NAME	ENABLED_OPT	ENABLED_OPTION
ALL USERS	ORA_LOGON_FAILURES	BY	BY USER
ALL USERS	ORA_SECURECONFIG	BY	BY USER

If v$option shows TRUE for Unified Auditing AND we have at least one enabled unified auditing policy, we are using "Pure" Unified Auditing. 

It doesn't matter what all the Traditional Auditing parameters are set to at this point; they will not have any effect. 

Your audit information will from now on be written to the table AUDSYS.AUD$UNIFIED.

The SYS.AUD$ and SYS.FGA_LOG$ tables will still be accessible, but not used by the Oracle instance. They will only contain auditing records from before unified auditing was enabled. Consequently, your previously used queries based on familiar data dictionary views such as dba_audit_trail will only return information from before Unified Auditing was enabled.

The Oracle documentation provides a table which is very helpfull in determining the pros and cons of migrating to Unified Auditing. 

In my opinion, the most important drawback with Unfied Auditing is that it doesn't allow the auditing data to be written to the operating system.

To enable traditional Auditing

1. First, disable any unified audit policies that are currently enabled. Find the currently enabled policies:

select user_name, policy_name, enabled_opt, enabled_option
from audit_unified_enabled_policies;

USER_NAME	POLICY_NAME	ENABLED_OPT	ENABLED_OPTION
ALL USERS	ORA_LOGON_FAILURES	BY	BY USER
ALL USERS	ORA_SECURECONFIG	BY	BY USER

2. Take them out of audit. This step prevents the database from going into mixed mode auditing after you complete this procedure:

noaudit policy ORA_SECURECONFIG;
noaudit policy ORA_LOGON_FAILURES;

3. Shutdown the database:

shutdown immediate

4. Relink the Oracle database binaries:

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_off ioracle ORACLE_HOME=$ORACLE_HOME

5. Start the database

sqlplus / as sysdba
startup

The database should now be in Traditional Auditing mode. There will be no more entries logged to the unified_audit_trail. Your audit records will go to the SYS.AUD$ and SYS.FGA_LOG$ tables, or to the operating system, depending on your value for the parameter AUDIT_TRAIL.

More about disabling unified auditing policies can be found here

Another good source for more information about Unified Auditing is this article found at oracle-base.com

How to check if Unified Auditing is enabled in your database

Check that Unified Auditing is enabled with the following query:

col parameter format a20
col value format a20
set lines 200
select parameter, value from v$option where parameter='Unified Auditing';
exit

Output shows that it is enabled:

PARAMETER            VALUE
-------------------- --------------------
Unified Auditing     TRUE

How to find the currently enabled unified auditing policies in an Oracle 12c database

Oracle 12c comes with two auditing policies enabled by default: ORA_SECURECONFIG and ORA_LOGON_FAILURES.
You can find the currently enabled policies in the AUDIT_UNIFIED_ENABLED_POLICIES view.

The query below will reveal what the currently enabled policies will actually audit

select policy_name,audit_option,condition_eval_opt
from audit_unified_policies
where policy_name in (  select policy_name 
                        from audit_unified_enabled_policies);
                        

The result shows that the default Unfied Auditing Setup covers many of the actions you would certainly want to audit, like DROP USER, ALTER SYSTEM etc:

POLICY_NAME	AUDIT_OPTION	CONDITION_EVAL_OPT
ORA_SECURECONFIG	LOGMINING	NONE
ORA_SECURECONFIG	TRANSLATE ANY SQL	NONE
ORA_SECURECONFIG	EXEMPT REDACTION POLICY	NONE
ORA_SECURECONFIG	PURGE DBA_RECYCLEBIN	NONE
ORA_SECURECONFIG	ADMINISTER KEY MANAGEMENT	NONE
ORA_SECURECONFIG	DROP ANY SQL TRANSLATION PROFILE	NONE
ORA_SECURECONFIG	ALTER ANY SQL TRANSLATION PROFILE	NONE
ORA_SECURECONFIG	CREATE ANY SQL TRANSLATION PROFILE	NONE
ORA_SECURECONFIG	CREATE SQL TRANSLATION PROFILE	NONE
ORA_SECURECONFIG	CREATE EXTERNAL JOB	NONE
ORA_SECURECONFIG	CREATE ANY JOB	NONE
ORA_SECURECONFIG	GRANT ANY OBJECT PRIVILEGE	NONE
ORA_SECURECONFIG	EXEMPT ACCESS POLICY	NONE
ORA_SECURECONFIG	CREATE ANY LIBRARY	NONE
ORA_SECURECONFIG	GRANT ANY PRIVILEGE	NONE
ORA_SECURECONFIG	DROP ANY PROCEDURE	NONE
ORA_SECURECONFIG	ALTER ANY PROCEDURE	NONE
ORA_SECURECONFIG	CREATE ANY PROCEDURE	NONE
ORA_SECURECONFIG	ALTER DATABASE	NONE
ORA_SECURECONFIG	GRANT ANY ROLE	NONE
ORA_SECURECONFIG	DROP PUBLIC SYNONYM	NONE
ORA_SECURECONFIG	CREATE PUBLIC SYNONYM	NONE
ORA_SECURECONFIG	DROP ANY TABLE	NONE
ORA_SECURECONFIG	ALTER ANY TABLE	NONE
ORA_SECURECONFIG	CREATE ANY TABLE	NONE
ORA_SECURECONFIG	DROP USER	NONE
ORA_SECURECONFIG	CREATE USER	NONE
ORA_SECURECONFIG	AUDIT SYSTEM	NONE
ORA_SECURECONFIG	ALTER SYSTEM	NONE
ORA_LOGON_FAILURES	LOGON	NONE
ORA_SECURECONFIG	CREATE DATABASE LINK	NONE
ORA_SECURECONFIG	DROP DATABASE LINK	NONE
ORA_SECURECONFIG	ALTER USER	NONE
ORA_SECURECONFIG	CREATE ROLE	NONE
ORA_SECURECONFIG	DROP ROLE	NONE
ORA_SECURECONFIG	SET ROLE	NONE
ORA_SECURECONFIG	CREATE PROFILE	NONE
ORA_SECURECONFIG	DROP PROFILE	NONE
ORA_SECURECONFIG	ALTER PROFILE	NONE
ORA_SECURECONFIG	ALTER ROLE	NONE
ORA_SECURECONFIG	CREATE DIRECTORY	NONE
ORA_SECURECONFIG	DROP DIRECTORY	NONE
ORA_SECURECONFIG	ALTER DATABASE LINK	NONE
ORA_SECURECONFIG	CREATE PLUGGABLE DATABASE	NONE
ORA_SECURECONFIG	ALTER PLUGGABLE DATABASE	NONE
ORA_SECURECONFIG	DROP PLUGGABLE DATABASE	NONE
ORA_SECURECONFIG	EXECUTE	NONE

If the $ORACLE_HOME/rdbms/admin/secconf.sql script was created during database creation, you will have some other policies in your database, too, but they won't be enabled by default. These can be found by executing the following statement:

select policy_name,count(audit_option) "number of audits"
from AUDIT_UNIFIED_POLICIES
where policy_name not in (select unique policy_name from audit_unified_enabled_policies )
group by policy_name
order by 2 desc;

In my database, the following collection shows up as being created, but not yet enabled:

POLICY_NAME	number of audits
ORA_RAS_POLICY_MGMT	33
ORA_CIS_RECOMMENDATIONS	26
ORA_RAS_SESSION_MGMT	14
ORA_ACCOUNT_MGMT	9
ORA_DATABASE_PARAMETER	3