Effective auditing requires that audit policies be selective and focused. This ensures that the audit records generated are what is needed to support forensic analysis, and compliance, without generating unnecessary audit records.

The most common activities to audit includes but are not limited to the following

• Failed logins
• Any login from outside of the application or monitoring tools
• Data Definition Language – creating, dropping, or changing database objects
• Data Control Language – especially create user, alter user, privilege and role grants
• Oracle Data Pump import operations
• Any Oracle Database Vault activity or rule violation
• Any SYSDBA or database administrator activity

The top three tips to get started on auditing activities with Oracle Database with unified auditing are as follows:

1. Do not duplicate mandatory audit configurations which are always on in the Oracle database.
2. Use the predefined unified audit policies provided in Oracle Database, Oracle Data Safe, or Oracle Audit Vault and Database Firewall (AVDF).
3. Create custom audit policies (unified audit or fine-grained) for specialized needs.

You can fine-tune unified audit policies with conditions and enforced on specific users to reduce audit volume. You may want to use conditional enablement features for use cases, such as the following:

• Monitor access to sensitive data outside the trusted application path to focus only on the activity that matters.
• Monitor any activity from ad-hoc or power users who typically have access to query the data outside the trusted application paths.

An audit policy is a named group of audit settings that enable you to audit a particular aspect of user behavior in the database.

You can create audit policies that monitor a wide range of activities, such as the following:

• User accounts (including administrative users who log in with the SYSDBA administrative privilege), roles, and privileges

• Object actions, such as dropping a table or a running a procedure

• Application context values

• Activities from other Oracle Database products, such as Oracle Database Real Application Security, Oracle Recovery Manager, or Oracle Data Pump.

Oracle Database provides three ways for you to create audit policies:

• Use predefined unified audit policies for auditing the most common security relevant activities. The predefined audit policies enable you to follow certain industry standards, such as the Center for Internet Security Recommendations or the Security Technical Implementation Guide standards. Predefined policies are also available for common audit tasks such as failed logins, and for other Oracle products, such as Oracle Database Real Application Security and Oracle Database Vault. The predefined audit policies should be sufficient for most auditing needs, but if they are not, then you can create custom audit policies or fine-grained audit policies.
• Create custom unified audit policies for more specific activities. Custom unified audit policies enable you to audit a wide range of activities, such as auditing the use of roles or actions performed on objects like tables. You use the CREATE AUDIT POLICY statement to create the unified audit policy, and the AUDIT statement to enable it. The CREATE AUDIT POLICY syntax is flexible enough for you to build in conditions, for example, or audit application context values.
• Create fine-grained audit policies for more granular audit needs. Fine-grained audit policies are not unified audit policies; you use the DBMS_FGA PL/SQL package to create a fine-grained audit policy. Fine-grained audit policies enable you to include conditions and event handlers. For example, you can send alerts to an administrator if a user violates the audit policy. You can also audit specific rows of a table based on the value in a certain column with fine-grained audit.

Certain security sensitive database activities are always audited and such audit configurations cannot be disabled.

Activities that are always audited include but are not limited to the following:

• Activities of administrative users such as SYSDBA, SYSBACKUP, and SYSKM when the database is down is always audited.
• Any DDL or DML attempts on UNIFIED_AUDIT_TRAIL or the underlying dictionary tables in AUDSYS schema is always audited. These operations are not permitted by design. The unified audit trail resides in a read-only table in the AUDSYS schema.

Mandatorily audited activities will have audit policy by name ORA$MANDATORY in the UNIFIED_AUDIT_POLICIES column of the UNIFIED_AUDIT_TRAIL data dictionary view. The ORA$MANDATORY is always listed first in this column, if there are other unified audit policies that are tracking mandatorily audited activities. The SYSTEM_PRIVILEGE_USED column shows the type of administrative privilege that was used for the activity.

The following activities are mandatorily audited in Oracle Database:

Oracle Database provides predefined unified audit policies that cover commonly used security-relevant audit settings.

• About Auditing Activities with the Predefined Unified Audit Policies
  Oracle Database has a set of predefined unified audit policies that address most auditing needs.
• Secure Options Predefined Unified Audit Policy
  The ORA_SECURECONFIG unified audit policy provides audit options using Oracle Database security best practices.
• Oracle Database Parameter Changes Predefined Unified Audit Policy
  The ORA_DATABASE_PARAMETER policy audits commonly used Oracle Database parameter modification commands.
• User Account and Privilege Management Predefined Unified Audit Policy
  The ORA_ACCOUNT_MGMT policy audits commonly used user account and privilege settings.
• Center for Internet Security Recommendations Predefined Unified Audit Policy
  The ORA_CIS_RECOMMENDATIONS policy performs audits that the Center for Internet Security (CIS) recommends.
• Security Technical Implementation Guide Predefined Unified Audit Policies
  You can use predefined unified audit policies to implement Security Technical Implementation Guide (STIG) audit requirements.
• ORA_DICTIONARY Sensitive Column Queries Predefined Unified Audit Policy
  The ORA$DICTIONARY_SENS_COL_ACCESS predefined audit policy audits the sensitive columns in the Oracle Optimizer dictionary tables.
• Oracle Database Real Application Security Predefined Audit Policies
  You can use predefined unified audit policies for Oracle Database Real Application Security events.
• Oracle Database Vault Predefined Unified Audit Policy for DVSYS and LBACSYS Schemas
  The ORA_DV_SCHEMA_CHANGES (previously called ORA_DV_AUDPOL) predefined unified audit policy audits Oracle Database Vault DVSYS and LBACSYS schema objects.
• Oracle Database Vault Predefined Unified Audit Policy for Default Realms and Command Rules
  The ORA_DV_DEFAULT_PROTECTION (previously called ORA_DV_AUDPOL2) predefined unified audit policy audits the Oracle Database Vault default realms and command rules.
• Oracle Label Security Predefined Unified Audit Policy for LBACSYS Objects
  The ORA_OLS_SCHEMA_CHANGES predefined unified audit policy audits objects that are owned by the Oracle Label Security LBACSYS user.

SELECT DISTINCT POLICY_NAME FROM AUDIT_UNIFIED_POLICIES WHERE ORACLE_SUPPLIED = 'YES';

PRIVILEGES ALTER ANY TABLE, CREATE ANY TABLE, DROP ANY TABLE,
            CREATE ANY PROCEDURE, DROP ANY PROCEDURE, ALTER ANY PROCEDURE,
            GRANT ANY PRIVILEGE, GRANT ANY OBJECT PRIVILEGE, GRANT ANY ROLE,
            AUDIT SYSTEM, CREATE EXTERNAL JOB, CREATE ANY JOB,
            CREATE ANY LIBRARY,
            EXEMPT ACCESS POLICY,
            CREATE USER, DROP USER,
            ALTER DATABASE, ALTER SYSTEM,
            CREATE PUBLIC SYNONYM, DROP PUBLIC SYNONYM,
            CREATE SQL TRANSLATION PROFILE, CREATE ANY SQL TRANSLATION
PROFILE,
            DROP ANY SQL TRANSLATION PROFILE, ALTER ANY SQL TRANSLATION
PROFILE,
            TRANSLATE ANY SQL,
            EXEMPT REDACTION POLICY,
            PURGE DBA_RECYCLEBIN, LOGMINING,
            ADMINISTER KEY MANAGEMENT, BECOME USER,
            ADMINISTER FINE GRAINED AUDIT POLICY,
            ADMINISTER REDACTION POLICY,
            ADMINISTER ROW LEVEL SECURITY POLICY,
            GRANT ANY SCHEMA PRIVILEGE,
            CREATE ANY DOMAIN, ALTER ANY DOMAIN,
            DROP ANY DOMAIN,
            CREATE ANY MLE, ALTER ANY MLE, DROP ANY MLE,
            ADMINISTER SQL FIREWALL
 ACTIONS ALTER USER, CREATE ROLE, ALTER ROLE, DROP ROLE,
            SET ROLE, CREATE PROFILE, ALTER PROFILE,
            DROP PROFILE, CREATE DATABASE LINK,
            ALTER DATABASE LINK, DROP DATABASE LINK,
            CREATE DIRECTORY, DROP DIRECTORY,
            CREATE PLUGGABLE DATABASE,
            DROP PLUGGABLE DATABASE,
            ALTER PLUGGABLE DATABASE,
            ALTER DATABASE DICTIONARY,
            EXECUTE ON REMOTE_SCHEDULER_AGENT.ADD_AGENT_CERTIFICATE;

AUDIT POLICY ORA_SECURECONFIG;

ACTIONS ALTER DATABASE, ALTER SYSTEM, CREATE SPFILE;

AUDIT POLICY ORA_DATABASE_PARAMETER;

ACTIONS CREATE USER, ALTER USER, DROP USER, CREATE ROLE, DROP ROLE,
  ALTER ROLE, SET ROLE, GRANT, REVOKE;

AUDIT POLICY ORA_ACCOUNT_MGMT;

PRIVILEGES SELECT ANY DICTIONARY, ALTER SYSTEM
ACTIONS CREATE USER, ALTER USER, DROP USER,
        CREATE ROLE, DROP ROLE, ALTER ROLE,
        GRANT, REVOKE, CREATE DATABASE LINK,
        ALTER DATABASE LINK, DROP DATABASE LINK,
        CREATE PROFILE, ALTER PROFILE, DROP PROFILE,
        CREATE SYNONYM, DROP SYNONYM,
        CREATE PROCEDURE, DROP PROCEDURE,
        ALTER PROCEDURE, ALTER SYNONYM, CREATE FUNCTION,
        CREATE PACKAGE, CREATE PACKAGE BODY,
        ALTER FUNCTION, ALTER PACKAGE, ALTER SYSTEM,
        ALTER PACKAGE BODY, DROP FUNCTION,
        DROP PACKAGE, DROP PACKAGE BODY,
        CREATE TRIGGER, ALTER TRIGGER,
        DROP TRIGGER;

AUDIT POLICY ORA_CIS_RECOMMENDATIONS;

PRIVILEGES ALTER SESSION
ACTIONS CREATE FUNCTION, ALTER FUNCTION, DROP FUNCTION,
	CREATE PACKAGE, ALTER PACKAGE, DROP PACKAGE,
	CREATE PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE,
	CREATE TRIGGER, ALTER TRIGGER, DROP TRIGGER,
	CREATE PACKAGE BODY, ALTER PACKAGE BODY,
	DROP PACKAGE BODY,
	CREATE TYPE, ALTER TYPE, DROP TYPE,
	CREATE TYPE BODY, ALTER TYPE BODY, DROP TYPE BODY,
	CREATE LIBRARY, ALTER LIBRARY, DROP LIBRARY,
	CREATE JAVA, ALTER JAVA, DROP JAVA,
	CREATE OPERATOR, ALTER OPERATOR, DROP OPERATOR,
	CREATE TABLE, ALTER TABLE, DROP TABLE,
	CREATE VIEW, ALTER VIEW, DROP VIEW,
	CREATE MATERIALIZED VIEW, ALTER MATERIALIZED VIEW,
	DROP MATERIALIZED VIEW,
	CREATE ASSEMBLY, ALTER ASSEMBLY, DROP ASSEMBLY,
	CREATE SYNONYM, ALTER SYNONYM, DROP SYNONYM,
	CREATE USER, ALTER USER, DROP USER,
	GRANT, REVOKE,
	CREATE ROLE, ALTER ROLE, DROP ROLE, SET ROLE,
	CREATE PROFILE, ALTER PROFILE, DROP PROFILE,
	CREATE LOCKDOWN PROFILE, ALTER LOCKDOWN PROFILE,
	DROP LOCKDOWN PROFILE,
	ALTER SYSTEM, ALTER DATABASE, ALTER PLUGGABLE DATABASE,
	CREATE SPFILE, ALTER DATABASE DICTIONARY,
	ADMINISTER KEY MANAGEMENT,
	EXECUTE ON DBMS_JOB, EXECUTE ON DBMS_RLS,
	EXECUTE ON DBMS_REDACT, EXECUTE ON DBMS_TSDP_MANAGE,
	EXECUTE ON DBMS_TSDP_PROTECT,
	EXECUTE ON DBMS_NETWORK_ACL_ADMIN,
	EXECUTE ON DBMS_SCHEDULER
ACTIONS COMPONENT = OLS ALL';

AUDIT POLICY ORA_STIG_RECOMMENDATIONS;

ACTIONS ALL ONLY TOPLEVEL;

AUDIT POLICY ORA_ALL_TOPLEVEL_ACTIONS BY SYS, SITEADMIN;

ACTIONS LOGON, LOGOFF;

AUDIT POLICY ORA_LOGIN_LOGOUT WHENEVER NOT SUCCESSFUL;

ACTIONS COMPONENT=XS
  CREATE USER, UPDATE USER, DELETE USER, 
  CREATE ROLE, UPDATE ROLE, DELETE ROLE, GRANT ROLE, REVOKE ROLE, 
  ADD PROXY, REMOVE PROXY, 
  SET USER PASSWORD, SET USER VERIFIER, SET USER PROFILE,
  CREATE ROLESET, UPDATE ROLESET, DELETE ROLESET,
  CREATE SECURITY CLASS, UPDATE SECURITY CLASS, DELETE SECURITY CLASS, 
  CREATE NAMESPACE TEMPLATE, UPDATE NAMESPACE TEMPLATE, DELETE NAMESPACE TEMPLATE,
  CREATE ACL, UPDATE ACL, DELETE ACL, 
  CREATE DATA SECURITY, UPDATE DATA SECURITY, DELETE DATA SECURITY, 
  ENABLE DATA SECURITY, DISABLE DATA SECURITY, 
  ADD GLOBAL CALLBACK, DELETE GLOBAL CALLBACK, ENABLE GLOBAL CALLBACK;

AUDIT POLICY ORA_RAS_POLICY_MGMT;

CREATE AUDIT POLICY ORA_RAS_SESSION_MGMT
 ACTIONS COMPONENT=XS 
  CREATE SESSION, DESTROY SESSION, 
  ENABLE ROLE, DISABLE ROLE, 
  SET COOKIE, SET INACTIVE TIMEOUT, 
  SWITCH USER, ASSIGN USER,
  CREATE SESSION NAMESPACE, DELETE SESSION NAMESPACE,
  CREATE NAMESPACE ATTRIBUTE, GET NAMESPACE ATTRIBUTE, SET NAMESPACE ATTRIBUTE,
  DELETE NAMESPACE ATTRIBUTE;

AUDIT POLICY ORA_RAS_SESSION_MGMT WHENEVER NOT SUCCESSFUL;

Apart from mandatorily audited activities and predefined unified audit policies enabled by default in the Oracle database, you may need to provision additional unified audit policies based on your security and compliance needs.

• Auditing Most Commonly Used Security-Relevant Activities
  Oracle Database provides a set of predefined unified audit policies that you can choose from for the most common security-relevant activities.
• Auditing SQL Statements, Privileges, and Other Activities of Interest
  You can create custom audit policies to track access to certain objects, actions or use of privileges, or use of Oracle Database components, such as Oracle Label Security. You can conditionally enable them to reduce audit volume.
• Value-Based Fine-Grained Audit Activities
  Use fine-grained auditing if you want to perform value-based auditing to audit access to certain rows based on values in specific columns or if you want to integrate with event handlers within the Oracle database.

A common audit configuration is visible and enforced across all PDBs.

Audit configurations are either local or common. The scoping rules that apply to other local or common phenomena, such as users and roles, all apply to audit configurations.

PDBs support the following auditing options:

• Object auditing

  Object auditing refers to audit configurations for specific objects. Only common objects can be part of the common audit configuration. A local audit configuration cannot contain common objects.

• Audit policies

  Audit policies can be local or common:

  • Local audit policies

    A local audit policy applies to a single PDB. You can enforce local audit policies for local and common users in this PDB only. Attempts to enforce local audit policies across all containers result in an error.

    In all cases, enforcing of a local audit policy is part of the local auditing framework.

  • Common audit policies

    A common audit policy applies to all containers. When you create a common audit policy, prefix the name with C## or c## (for example, c##all_select_pol). This policy can only contain actions, system privileges, common roles, and common objects. You can apply a common audit policy only to common users. Attempts to enforce a common audit policy for a local user across all containers result in an error.

A common audit configuration is stored in the SYS schema of the root. A local audit configuration is stored in the SYS schema of the PDB to which it applies.

Audit trails are stored in the SYS or AUDSYS schemas of the relevant CDB or PDB container. Operating system and XML audit trails for PDBs are stored in subdirectories of the directory specified by the AUDIT_FILE_DEST (deprecated) initialization parameter.

Oracle Database provides different types of data dictionary and dynamic views for use with unified auditing.