When you audit a role, Oracle Database audits all system privileges that are directly granted to the role.

You can audit any role, including user-defined roles. If you create a common unified audit policy for roles with the ROLES audit option, then you must specify only common roles in the role list. When such a policy is enabled, Oracle Database audits all system privileges that are commonly and directly granted to the common role. The system privileges that are locally granted to the common role will not be audited. To find if a role was commonly granted, query the DBA_ROLES data dictionary view. To find if the privileges granted to the role were commonly granted, query the ROLE_SYS_PRIVS view.

To create a unified audit policy to capture role use, you must include the ROLES clause in the CREATE AUDIT POLICY statement.

CREATE AUDIT POLICY audit_roles_pol 
 ROLES IMP_FULL_DATABASE, EXP_FULL_DATABASE;

The CREATE AUDIT POLICY statement can audit roles in both the root and in PDBs.

The following example shows how to audit a predefined common role DBA.

CREATE AUDIT POLICY role_dba_audit_pol 
 ROLES DBA
 CONTAINER = ALL;

AUDIT POLICY role_dba_audit_pol;

System privilege auditing audits activities that successfully use a system privilege, such as READ ANY TABLE.

In this kind of auditing, SQL statements that require the audited privilege to succeed are recorded.

A single unified audit policy can contain both privilege and action audit options. Do not audit the privilege use of administrative users such as SYS. Instead, audit their object actions.

You can audit the use of almost any system privilege.

To find a list of auditable system privileges, you can query the SYSTEM_PRIVILEGE_MAP table.

For example:

SELECT NAME FROM SYSTEM_PRIVILEGE_MAP;

NAME
-------------
ALTER ANY CUBE BUILD PROCESS
SELECT ANY CUBE BUILD PROCESS
ALTER ANY MEASURE FOLDER
...

Similar to action audit options, privilege auditing audits the use of system privileges that have been granted to database users. If you set similar audit options for both SQL statement and privilege auditing, then only a single audit record is generated. For example, if two policies exist, with one auditing EXECUTE PROCEDURE specifically on the HR.PROC procedure and the second auditing EXECUTE PROCEDURE in general (all procedures), then only one audit record is written.

Privilege auditing does not occur if the action is already permitted by the existing owner and object privileges. Privilege auditing is triggered only if the privileges are insufficient, that is, only if what makes the action possible is a system privilege. For example, suppose that user SCOTT has been granted the SELECT ANY TABLE privilege and SELECT ANY TABLE is being audited. If SCOTT selects his own table (for example, SCOTT.EMP), then the SELECT ANY TABLE privilege is not used. Because he performed the SELECT statement within his own schema, no audit record is generated. On the other hand, if SCOTT selects from another schema (for example, the HR.EMPLOYEES table), then an audit record is generated. Because SCOTT selected a table outside his own schema, he needed to use the SELECT ANY TABLE privilege.

Several system privileges cannot be audited.

These privileges are:

• INHERIT ANY PRIVILEGE

• INHERIT PRIVILEGE

• TRANSLATE ANY SQL

• TRANSLATE SQL

The PRIVILEGES clause in the CREATE AUDIT POLICY statement audits system privilege use.

The CREATE AUDIT POLICY statement can audit users for ANY privileges.

Example 26-3 shows how to audit several ANY privileges of the user HR_MGR.

CREATE AUDIT POLICY hr_mgr_audit_pol
 PRIVILEGES DROP ANY TABLE, DROP ANY CONTEXT, DROP ANY INDEX, DROP ANY LIBRARY;

AUDIT POLICY hr_mgr_audit_pol BY HR_MGR;

The CREATE AUDIT POLICY statement can create an audit policy that uses a condition to audit a system privilege.

Example 26-4 shows how to use a condition to audit privileges that are used by two operating system users, psmith and jrawlins.

CREATE AUDIT POLICY os_users_priv_pol
 PRIVILEGES SELECT ANY TABLE, CREATE LIBRARY
 WHEN 'SYS_CONTEXT (''USERENV'', ''OS_USER'') IN (''psmith'', ''jrawlins'')'
 EVALUATE PER SESSION;

AUDIT POLICY os_users_priv_pol;

The UNIFIED_AUDIT_TRAIL data dictionary view lists system privilege audit events.

The following example shows a list of privileges used by the operating system user psmith.

SELECT SYSTEM_PRIVILEGE_USED FROM UNIFIED_AUDIT_TRAIL 
 WHERE OS_USERNAME = 'PSMITH' AND UNIFIED_AUDIT_POLICIES = 'OS_USERS_PRIV_POL';

SYSTEM_PRIVILEGE_USED
----------------------
SELECT ANY TABLE
DROP ANY TABLE

Oracle Database provides administrative user accounts that are associated with administrative privileges.

Table 26-1 lists default administrative user accounts and the administrative privileges with which they are typically associated.

^Footnote 1

PUBLIC refers to the user PUBLIC, which is the effective user when you log in with the SYSOPER administrative privilege. It does not refer to the PUBLIC role.

The CREATE AUDIT POLICY statement can audit administrative users.

The CREATE AUDIT POLICY statement can audit the SYS user.

Example 26-5 shows how to audit grants of the DBMS_FGA PL/SQL package by user SYS.

CREATE AUDIT POLICY dbms_fga_grants 
 ACTIONS GRANT
 ON DBMS_FGA;

AUDIT POLICY dbms_fga_grants BY SYS;

You can audit actions performed on specific objects, such as UPDATE statements on the HR.EMPLOYEES table.

The audit can include both DDL and DML statements that were used on the object. A single unified audit policy can contain both privilege and action audit options, as well as audit options set for multiple objects.

For tables that contain sensitive information, Oracle recommends that you include the ACTIONS ALL clause in the unified audit policy so that the audit record will capture indirect SELECT operations.

Auditing object actions can be broad or focused (for example, auditing all user actions or only a select list of user actions).

Table 26-2 lists the object-level standard database action options. Audit policies for the SELECT SQL statement will capture READ actions as well as SELECT actions.

The ACTIONS clause in the CREATE AUDIT POLICY statement creates a policy that captures object actions.

CREATE AUDIT POLICY my_simple_obj_policy
 ACTIONS SELECT ON OE.ORDERS, UPDATE ON HR.EMPLOYEES;

The CREATE AUDIT POLICY statement can audit actions on SYS objects.

Example 26-6 shows how to create an audit policy that audits SELECT statements on the SYS.USER$ system table. The audit policy applies to all users, including SYS and SYSTEM.

CREATE AUDIT POLICY select_user_dictionary_table_pol ACTIONS SELECT ON SYS.USER$;

AUDIT POLICY select_user_dictionary_table_pol;

The CREATE AUDIT POLICY statement can audit multiple actions on one object.

Example 26-7 shows how to audit multiple SQL statements performed by users jrandolph and phawkins on the app_lib library.

CREATE AUDIT POLICY actions_on_hr_emp_pol1
 ACTIONS EXECUTE, GRANT 
 ON app_lib;

AUDIT POLICY actions_on_hr_emp_pol1 BY jrandolph, phawkins;

The CREATE AUDIT POLICY statement can audit both actions and privileges on an object, using a single policy.

Example 26-8 shows how all EXECUTE and GRANT statements on the app_lib library using the CREATE LIBRARY privilege are audited.

CREATE AUDIT POLICY actions_on_hr_emp_pol2 
 PRIVILEGES CREATE LIBRARY
 ACTIONS EXECUTE, GRANT
 ON app_lib;

AUDIT POLICY actions_on_hr_emp_pol2 BY jrandolph, phawkins;

The CREATE AUDIT POLICY statement can audit all actions on a table.

You can use the ALL keyword to audit all actions. Oracle recommends that you audit all actions only on sensitive objects. ALL is useful in that it captures indirect SELECT operations. Example 26-9 shows how to audit all actions on the HR.EMPLOYEES table, except actions by user pmulligan.

CREATE AUDIT POLICY all_actions_on_hr_emp_pol 
 ACTIONS ALL ON HR.EMPLOYEES;

AUDIT POLICY all_actions_on_hr_emp_pol EXCEPT pmulligan;

The CREATE AUDIT POLICY statement can audit all actions in the database.

To prevent the scenario of a large number of audit records being generated and quickly filling up the audit trail, even for all the recursive actions for this audit policy configuration, include the ONLY TOPLEVEL clause in the CREATE AUDIT POLICY statement. As an alternative to ONLY TOPLEVEL, you can create the ACTIONS ALL policy using a condition to so that only a subset of records are captured.

Example 26-10 shows how to audit all actions in the entire database.

CREATE AUDIT POLICY all_actions_pol ACTIONS ALL ONLY TOPLEVEL;

AUDIT POLICY all_actions_pol;

The UNIFIED_AUDIT_TRAIL data dictionary view lists object action audit events.

For example:

SELECT ACTION_NAME, OBJECT_SCHEMA, OBJECT_NAME FROM UNIFIED_AUDIT_TRAIL 
WHERE DBUSERNAME = 'SYS';

ACTION_NAME OBJECT_SCHEMA OBJECT_NAME
----------- ------------- ------------
SELECT      HR            EMPLOYEES

You can audit functions, procedures, PL/SQL packages, and triggers.

Auditing can affect dynamic VPD policies, static VPD policies, and context-sensitive VPD policies.

• Dynamic policies: Oracle Database evaluates the policy function twice, once during SQL statement parsing and again during execution. As a result, two audit records are generated for each evaluation.

• Static policies: Oracle Database evaluates the policy function once and then caches it in the SGA. As a result, only one audit record is generated.

• Context-sensitive policies: Oracle Database executes the policy function once, during statement parsing. As a result, only one audit record is generated.

An audit policy created to audit an action on an editioned object will be applied to all its editions.

In addition, newly created objects in an edition will inherit unified audit policies from the existing edition.

You can find the editions in which audited objects appear by querying the OBJECT_NAME and OBJ_EDITION_NAME columns in the UNIFIED_AUDIT_TRAIL data dictionary view.

You can create unified audit policies that capture the use of the READ ANY TABLE and SELECT ANY TABLE system privileges.

Based on the action that the user tried to perform and the privilege that was granted to the user, the SYSTEM_PRIVILEGE_USED column of the UNIFIED_AUDIT_TRAIL data dictionary view will record either the READ ANY TABLE system privilege or the SELECT ANY TABLE system privilege. For example, suppose the user has been granted the SELECT ANY TABLE privilege and then performs a query on a table. The audit trail will record that the user used the SELECT ANY TABLE system privilege. If the user was granted READ ANY TABLE and performed the same query, then the READ ANY TABLE privilege is recorded.

You can create unified audit policies that capture READ object privilege operations.

CREATE AUDIT POLICY read_hr_employees
 ACTIONS SELECT ON HR.EMPLOYEES;

The unified audit trail captures SELECT behavior based on whether a user has the READ ANY TABLE or the SELECT ANY TABLE privilege.

Table 26-3 describes how the unified audit trail captures these actions.

You can create a unified audit policy that uses a SYS_CONTEXT namespace-attribute pair to specify a condition.

For example, this audit condition can apply to a specific user who may fulfil the audit condition, or a computer host where the audit condition is fulfilled.

If the audit condition is satisfied, then Oracle Database creates an audit record for the event. As part of the condition definition, you must specify whether the audited condition is evaluated per statement occurrence, session, or database instance.

The WHEN clause in the CREATE AUDIT POLICY statement defines the condition in the audit policy.

CREATE AUDIT POLICY oe_orders_pol
 ACTIONS UPDATE ON OE.ORDERS
 WHEN 'SYS_CONTEXT(''USERENV'', ''IDENTIFICATION_TYPE'') = ''EXTERNAL'''
 EVALUATE PER STATEMENT;

The CREATE AUDIT POLICY statement can audit access to SQL*Plus.

Example 26-11 shows how to audit access to the database with SQL*Plus by users who have been directly granted the roles emp_admin and sales_admin.

CREATE AUDIT POLICY logon_pol
 ACTIONS LOGON
 WHEN 'INSTR(UPPER(SYS_CONTEXT(''USERENV'', ''CLIENT_PROGRAM_NAME'')), ''SQLPLUS'') > 0'
 EVALUATE PER SESSION;

AUDIT POLICY logon_pol BY USERS WITH GRANTED ROLES emp_admin, sales_admin;

The CREATE AUDIT POLICY statement can audit actions that are not in specific hosts.

Example 26-12 shows how to audit two actions (UPDATE and DELETE statements) on the OE.ORDERS table, but excludes the host names sales_24 and sales_12 from the audit. It performs the audit on a per session basis and writes audit records for failed attempts only.

CREATE AUDIT POLICY oe_table_audit1
 ACTIONS UPDATE ON OE.ORDERS, DELETE ON OE.ORDERS 
 WHEN 'SYS_CONTEXT (''USERENV'', ''HOST'') NOT IN (''sales_24'',''sales_12'')' 
 EVALUATE PER SESSION;

AUDIT POLICY oe_table_audit1 WHENEVER NOT SUCCESSFUL;

The CREATE AUDIT POLICY statement can audit both system-wide and schema-specific actions.

Example 26-13 shows a variation of Example 26-12 in which the UPDATE statement is audited system wide. The DELETE statement audit is still specific to the OE.ORDERS table.

CREATE AUDIT POLICY oe_table_audit2
 ACTIONS UPDATE, DELETE ON OE.ORDERS
 WHEN 'SYS_CONTEXT (''USERENV'', ''HOST'') NOT IN (''sales_24'',''sales_12'')' 
 EVALUATE PER SESSION;

AUDIT POLICY oe_table_audit2;

The CREATE AUDIT POLICY statement can audit conditions.

Example 26-14 shows how to audit a condition based on each occurrence of the DELETE statement on the OE.ORDERS table and exclude user jmartin from the audit.

CREATE AUDIT POLICY sales_clerk_pol
 ACTIONS DELETE ON OE.ORDERS
 WHEN 'SYS_CONTEXT(''USERENV'', ''CLIENT_IDENTIFIER'') = ''sales_clerk'''
 EVALUATE PER STATEMENT;

AUDIT POLICY sales_clerk_pol EXCEPT jmartin;

The SYS_CONTEXT function can be used to find session IDs.

CONNECT SYS AS SYSDBA
Enter password: password

SELECT SYS_CONTEXT('USERENV', 'UNIFIED_AUDIT_SESSIONID') FROM DUAL;

SYS_CONTEXT('USERENV','UNIFIED_AUDIT_SESSIONID')
--------------------------------------------------------------------------------
2318470183

The SYS_CONTEXT function can find the session ID of a current non-administrative user session.

Example 26-16 shows how to find the unified audit session ID of a current user session for a non-administrative user.

CONNECT mblake@pdb_name
Enter password: password

SELECT SYS_CONTEXT('USERENV', 'UNIFIED_AUDIT_SESSIONID') FROM DUAL;

SYS_CONTEXT('USERENV','UNIFIED_AUDIT_SESSIONID')
--------------------------------------------------------------------------------
2776921346

The audit record conditions from a unified audit policy do not appear in the audit trail.

If the condition evaluates to true and the record is written, then the record appears in the audit trail. You can check the audit trail by querying the UNIFIED_AUDIT_TRAIL data dictionary view.

You can capture application context values in the unified audit trail.

This feature enables you to capture any application context values set by the database applications, while executing the audited statement.

If you plan to audit Oracle Label Security, then this feature captures session label activity for the database audit trail. The audit trail records all the values retrieved for the specified context-attribute value pairs.

The application context audit setting or the audit policy have session static semantics. In other words, if a new policy is enabled for a user, then the subsequent user sessions will see an effect of this command. After the session is established, then the policies and contexts settings are loaded and the subsequent AUDIT statements have no effect on that session.

Note that the application context audit policy applies only to the current PDB.

The AUDIT statement with the CONTEXT keyword configures auditing for application context values.

AUDIT CONTEXT NAMESPACE clientcontext3 ATTRIBUTES module, action, 
 CONTEXT NAMESPACE ols_session_labels ATTRIBUTES ols_pol1, ols_pol3
 BY appuser1, appuser2;

The NOAUDIT statement disables application context audit settings.

NOAUDIT CONTEXT NAMESPACE client_context ATTRIBUTES module,
CONTEXT NAMESPACE ols_session_labels ATTRIBUTES ols_pol1, ols_pol3
BY USERS WITH GRANTED ROLES emp_admin;

The AUDIT CONTEXT NAMESPACE statement can audit application context values.

Example 26-17 shows how to audit the clientcontext application values for the module and action attributes, by the user appuser1.

AUDIT CONTEXT NAMESPACE clientcontext ATTRIBUTES module, action 
BY appuser1;

The AUDIT CONTEXT NAMESPACE statement can audit application context values from Oracle Label Security.

Example 26-18 shows how to audit an application context for Oracle Label Security called ols_session_labels, for the attributes ols_pol1 and ols_pol2.

AUDIT CONTEXT NAMESPACE ols_session_labels ATTRIBUTES ols_pol1, ols_pol2;

The UNIFIED_AUDIT_POLICIES data dictionary view lists application context audit events.

The APPLICATION_CONTEXTS column of the UNIFIED_AUDIT_TRAIL data dictionary view shows application context audit data. The application contexts appear as a list of semi-colon separated values.

For example:

SELECT APPLICATION_CONTEXTS FROM UNIFIED_AUDIT_TRAIL 
 WHERE UNIFIED_AUDIT_POLICIES = 'app_audit_pol';

APPLICATION_CONTEXTS
----------------------------------------------------------
CLIENT_CONTEXT.APPROLE=MANAGER;E2E_CONTEXT.USERNAME=PSMITH

You must have the AUDIT_ADMIN role to audit Oracle Database Real Application Security events.

To access the audit trail, you can query the UNIFIED_AUDIT_TRAIL data dictionary view, whose Real Application Security-specific columns begin with XS_. If you want to find audit information about the internally generated VPD predicate that is created while an Oracle Real Application Security policy is being enabled, then you can query the RLS_INFO column.

Real Application Security-specific views are as follows:

• DBA_XS_AUDIT_TRAIL provides detailed information about Real Application Security events that were audited.

• DBA_XS_AUDIT_POLICY_OPTIONS describes the auditing options that were defined for Real Application Security unified audit policies.

• DBA_XS_ENB_AUDIT_POLICIES lists users for whom Real Application Security unified audit polices are enabled.

Oracle Database provides Real Application Security events that you can audit, such CREATE USER, UPDATE USER.

To find a list of auditable Real Application Security events that you can audit, you can query the COMPONENT and NAME columns of the AUDITABLE_SYSTEM_ACTIONS data dictionary view, as follows:

SELECT NAME FROM AUDITABLE_SYSTEM_ACTIONS WHERE COMPONENT = 'XS';

NAME
-------------
CREATE USER
UPDATE USER
DELETE USER
...

The unified audit trail can capture Oracle Database Real Application Security events for users, privileges, and roles.

The unified audit trail can capture Oracle Database Real Application Security security class and ACL audit events.

The unified audit trail can capture Oracle Database Real Application Security session audit events.

The unified audit trail can capture Oracle Database Real Application Security ALL events.

The CREATE AUDIT POLICY statement can create a unified audit policy for Oracle Real Application Security.

CREATE AUDIT POLICY audit_ras_pol
 ACTIONS COMPONENT=XS SWITCH USER, DISABLE ROLE;

The CREATE AUDIT POLICY statement can audit Real Application Security user account modifications.

Example 26-19 shows how to audit user bhurst's attempts to switch users and disable roles.

CREATE AUDIT POLICY ras_users_pol
 ACTIONS COMPONENT=XS SWITCH USER, DISABLE ROLE;

AUDIT POLICY ras_users_pol BY bhurst;

The CREATE AUDIT POLICY statement can set a condition for a Real Application Security unified audit policy.

Example 26-20 shows how to create Real Application Security unified audit policy that applies the audit only to actions from the nemosity computer host.

CREATE AUDIT POLICY ras_acl_pol
 ACTIONS DELETE ON OE.CUSTOMERS
 ACTIONS COMPONENT=XS CREATE ACL, UPDATE ACL, DELETE ACL
 WHEN 'SYS_CONTEXT(''USERENV'', ''HOST'') = ''nemosity'''
 EVALUATE PER INSTANCE;

AUDIT POLICY ras_acl_pol BY pfitch;

The DBA_XS_AUDIT_TRAIL data dictionary view lists Oracle Real Application Security audit events.

The following example queries the Real Application Security-specific view, DBA_XS_AUDIT_TRAIL:

SELECT XS_USER_NAME FROM DBA_XS_AUDIT_TRAIL 
WHERE XS_ENABLED_ROLE = 'CLERK'; 

XS_USER_NAME
-------------
USER2 

The UNIFIED_AUDIT_TRAIL data dictionary view automatically stores Oracle Recovery Manager audit events in the RMAN_column.

Unlike other Oracle Database components, you do not create a unified audit policy for Oracle Recovery Manager events.

However, you must have the AUDIT_ADMIN or AUDIT_VIEWER role in order to query the UNIFIED_AUDIT_TRAIL view to see these events. If you have the SYSBACKUP or the SYSDBA administrative privilege, then you can find additional information about Recovery Manager jobs by querying views such as V$RMAN_STATUS or V$RMAN_BACKUP_JOB_DETAILS.

The unified audit trail can capture Oracle Recovery Manager events.

Table 26-8 describes these events.

The UNIFIED_AUDIT_TRAIL data dictionary view lists Oracle Recovery Manager audit events.

Table 26-8 lists the columns in the UNIFIED_AUDIT_TRAIL data dictionary view that you can query to find Oracle Recovery Manager-specific audit data.

For example:

SELECT RMAN_OPERATION FROM UNIFIED_AUDIT_TRAIL 
WHERE RMAN_OBJECT_TYPE = 'DB FULL';

RMAN_OPERATION 
---------------
BACKUP 

As with all unified auditing, you must have the AUDIT_ADMIN role before you can audit Oracle Database Vault events.

To create Oracle Database Vault unified audit policies, you must set the CREATE AUDIT POLICY statement's COMPONENT clause to DV, and then specify an action, such as Rule Set Failure, and an object, such as the name of a rule set.

To access the audit trail, you can query the following views:

• UNIFIED_AUDIT_TRAIL

• AUDSYS.DV$CONFIGURATION_AUDIT

• AUDSYS.DV$ENFORCEMENT_AUDIT

In the UNIFIED_AUDIT_TRAIL view, the Oracle Database Vault-specific columns begin with DV_. You must have the AUDIT_VIEWER role before you can query the UNIFIED_AUDIT_TRAIL view.

In addition to these views, the Database Vault reports capture the results of Database Vault-specific unified audit policies.

Audited Oracle Database Vault users include administrators and users whose activities affect Database Vault enforcement policies.

These users are as follows:

• Database Vault administrators. All configuration changes that are made to Oracle Database Vault are mandatorily audited. The auditing captures activities such as creating, modifying, or deleting realms, factors, command rules, rule sets, rules, and so on. The AUDSYS.DV$CONFIGURATION_AUDIT data dictionary view captures configuration changes made by Database Vault administrators.

• Users whose activities affect Oracle Database Vault enforcement policies. The AUDSYS.DV$ENFORCEMENT_AUDIT data dictionary view captures enforcement-related audits

The audit trail in an Oracle Database Vault environment captures all configuration changes or attempts at changes to Database Vault policies.

It also captures violations by users to existing Database Vault policies.

You can audit the following kinds of Oracle Database Vault events:

• All configuration changes or attempts at changes to Oracle Database Vault policies. It captures both Database Vault administrator changes and attempts made by unauthorized users.

• Violations by users to existing Database Vault policies. For example, if you create a policy to prevent users from accessing a specific schema table during non-work hours, the audit trail will capture this activity.

The unified audit trail captures Oracle Database Vault realm events.

Table 26-9 describes these events.

The unified audit trail can capture Oracle Database Vault rule set and rule audit events.

The unified audit trail can capture Oracle Database Vault command rule audit events.

The unified audit trail can capture Oracle Database Vault factor events.

The unified audit trail can capture Oracle Database Vault secure application role audit events.

The unified audit trail can capture Oracle Database Vault Oracle Label Security audit events.

The unified audit trail can capture Oracle Database Vault Oracle Data Pump audit events.

The unified audit trail can capture Oracle Database Vault enable and disable audit events.

The ACTIONS and ACTIONS COMPONENT clauses in the CREATE AUDIT POLICY statement can create unified audit policies for Oracle Database Vault events.

CREATE AUDIT POLICY audit_dv
 ACTIONS CREATE TABLE, SELECT
 ACTIONS COMPONENT=DV Realm Violation ON "Database Vault Account Management";

The CREATE AUDIT POLICY statement can audit Oracle Database Vault realms.

Example 26-21 shows how to audit a realm violation on the HR schema.

CREATE AUDIT POLICY dv_realm_hr 
 ACTIONS SELECT, UPDATE, DELETE
 ACTIONS COMPONENT=DV Realm Violation ON "HR Schema Realm";

AUDIT POLICY dv_realm_hr EXCEPT psmith;

The CREATE AUDIT POLICY statement can audit Oracle Database Vault rule sets.

Example: Auditing an Oracle Database Vault Rule Set shows how to audit the Can Maintain Accounts/Profile rule set. The user dbv_acctmgr, who has the DV_ACCTMGR role and hence has privileges to manage user accounts and user profiles, is exempt from this audit policy.

CREATE AUDIT POLICY dv_rule_set_accts 
 ACTIONS CREATE USER, ALTER USER, ALTER PROFILE 
 ACTIONS COMPONENT=DV RULE SET FAILURE ON "Can Maintain Accounts/Profile";

AUDIT POLICY dv_rule_set_accts EXCEPT dbv_acctmgr;

The CREATE AUDIT POLICY statement can audit multiple Oracle Database Vault events.

Example 26-23 shows how to audit a realm violation and a rule set failure.

CREATE AUDIT POLICY audit_dv
 ACTIONS CREATE TABLE, SELECT
 ACTIONS COMPONENT=DV REALM VIOLATION ON "Oracle Enterprise Manager", Rule Set
 Failure ON "Allow Sessions";

AUDIT POLICY audit_dv EXCEPT psmith;

The CREATE AUDIT POLICY statement can audit Oracle Database Vault factors.

Example 26-24 shows how to audit two types of errors for one factor.

CREATE AUDIT POLICY audit_dv_factor
 ACTIONS COMPONENT=DV FACTOR ERROR ON "Database_Domain", Factor Validate Error ON "Client_IP";

AUDIT POLICY audit_dv_factor;

The UNIFIED_AUDIT_TRAIL data dictionary view lists Oracle Database Vault audited events.

The DV_* columns of the UNIFIED_AUDIT_TRAIL view show Oracle Database Vault-specific audit data.

For example:

SELECT DV_RULE_SET_NAME FROM UNIFIED_AUDIT_TRAIL 
WHERE ACTION_NAME = 'UPDATE';

DV_RULE_SET_NAME
-----------------------
Allow System Parameters

As with all unified auditing, you must have the AUDIT_ADMIN role before you can audit Oracle Label Security (OLS) events.

To create Oracle Label Security unified audit policies, you must set the CREATE AUDIT POLICY statement COMPONENT clause to OLS.

To audit user session label information, you use the AUDIT statement to audit application context values.

To access the audit trail, you can query the UNIFIED_AUDIT_TRAIL data dictionary view. This view contains Oracle Label Security-specific columns whose names begin with OLS_. If you want to find audit information about the internally generated VPD predicate that is created when you apply an Oracle Label Security policy to a table, then you can query the RLS_INFO column.

The unified audit trail can capture Oracle Label Security audit events.

To find a list of auditable Oracle Label Security events that you can audit, you can query the COMPONENT and NAME columns of the AUDITABLE_SYSTEM_ACTIONS data dictionary view.

For example:

SELECT NAME FROM AUDITABLE_SYSTEM_ACTIONS WHERE COMPONENT = 'Label Security';

NAME
-------------
CREATE POLICY
ALTER POLICY
DROP POLICY
...

Table 26-17 describes the Oracle Label Security audit events.

The ORA_OLS_SESSION_LABELS application context can capture user session label usage for each Oracle Database event.

The attributes used by this application context refer to Oracle Label Security policies. .

The syntax is the same as the syntax used for application context auditing. For example:

AUDIT CONTEXT NAMESPACE ORA_SESSION_LABELS ATTRIBUTES policy1, policy2;

Because the recording of session labels is not user-session specific, the BY user_list clause is not required for auditing Oracle Label Security application contexts.

To disable the auditing of user session label information, you use the NOAUDIT statement. For example, to stop auditing for policies policy1 and policy2, enter the following statement:

NOAUDIT CONTEXT NAMESPACE ORA_SESSION_LABELS ATTRIBUTES policy1, policy2;

The ACTIONS and ACTIONS COMPONENT clauses in the CREATE AUDIT POLICY statement can be used to create Oracle Label Security event audit policies.

CREATE AUDIT POLICY audit_ols
 ACTIONS SELECT ON OE.ORDERS
 ACTIONS COMPONENT=OLS ALL;

The AUDIT CONTEXT NAMESPACE statement can audit Oracle Label Security session label attributes.

Example 26-25 shows how to audit ORA_OLS_SESSION_LABELS application context attributes for the Oracle Label Security policies usr_pol1 and usr_pol2.

AUDIT CONTEXT NAMESPACE ORA_SESSION_LABELS ATTRIBUTES usr_pol1, usr_pol2;

The CREATE AUDIT POLICY statement can exclude users from policies.

CREATE AUDIT POLICY auth_ols_audit_pol
 ACTIONS SELECT ON HR.EMPLOYEES
 ACTIONS COMPONENT=OLS DROP POLICY, DISABLE POLICY;

AUDIT POLICY auth_ols_audit_pol EXCEPT ols_mgr;

The CREATE AUDIT POLICY statement can audit Oracle Label Security policy actions.

Example 26-27 shows how to audit the DROP POLICY, DISABLE POLICY, UNSUBSCRIBE OID events, and UPDATE and DELETE statements on the HR.EMPLOYEES table. Then this policy is applied to the HR and LBACSYS users, and audit records are written to the unified audit trail only when the audited actions are successful.

CREATE AUDIT POLICY generic_audit_pol
 ACTIONS UPDATE ON HR.EMPLOYEES, DELETE ON HR.EMPLOYEES
 ACTIONS COMPONENT=OLS DROP POLICY, DISABLE POLICY, UNSUBSCRIBE OID;

AUDIT POLICY generic_audit_pol BY HR, LBACSYS WHENEVER SUCCESSFUL;

The LBACSYS.ORA_GET_AUDITED_LABEL function can be used in a UNIFIED_AUDIT_TRAIL query to find audited Oracle Label Security session labels.

Example 26-28 shows how to use the LBACSYS.ORA_GET_AUDITED_LABEL function in a UNIFIED_AUDIT_TRAIL data dictionary view query.

SELECT ENTRY_ID, SESSIONID,
       LBACSYS.ORA_GET_AUDITED_LABEL( APPLICATION_CONTEXTS,'GENERIC_AUDIT_POL1') AS  SESSION_LABEL1,
       LBACSYS.ORA_GET_AUDITED_LABEL( APPLICATION_CONTEXTS,'GENERIC_AUDIT_POL2') AS  SESSION_LABEL2
FROM UNIFIED_AUDIT_TRAIL;
/

ENTRY_ID  SESSIONID  SESSION_LABEL1  SESSION_LABEL2
--------  ---------  --------------  --------------
       1       1023  SECRET          LEVEL_ALPHA
       2       1024  TOP_SECRET      LEVEL_BETA

The UNIFIED_AUDIT_TRAIL data dictionary view lists Oracle Label Security audit events.

The OLS_* columns of the UNIFIED_AUDIT_TRAIL view show Oracle Label Security-specific audit data. For example:

SELECT OLS_PRIVILEGES_USED FROM UNIFIED_AUDIT_TRAIL WHERE DBUSERNAME = 'psmith';

OLS_PRIVILEGES_USED
-------------------
READ
WRITEUP
WRITEACROSS

The session labels that the audit trail captures are stored in the APPLICATION_CONTEXTS column of the UNIFIED_AUDIT_TRAIL view. You can use the LBACSYS.ORA_GET_AUDITED_LABEL function to retrieve session labels that are stored in the APPLICATION_CONTEXTS column. This function accepts the UNIFIED_AUDIT_TRAIL.APPLICATION_CONTEXTS column value, and the Oracle Label Security policy name as arguments, and then returns the session label that is stored in the column for the specified policy.

You must have the AUDIT_ADMIN role to audit Oracle Machine Learning for SQL events.

To access the audit trail, you can query the UNIFIED_AUDIT_TRAIL data dictionary view.

The unified audit trail can capture Oracle Machine Learning for SQL audit events..

Table 26-18 describes these events.

The CREATE AUDIT POLICY statement ACTIONS and ON MINING MODEL clauses can be used to create Oracle Machine Learning for SQL event unified audit policies.

CREATE AUDIT POLICY dm_ops ACTIONS RENAME ON MINING MODEL hr.dm_emp;

The CREATE AUDIT POLICY statement can audit multiple Oracle Machine Learning for SQL operations.

Example 26-29 shows how to audit multiple Oracle Machine Learning for SQL operations by user psmith. Include the ON MINING MODEL schema_name.model_name clause for each event, and separate each with a comma. This example specifies the same schema_name.model name for both actions, but the syntax enables you to specify different schema_name.model_name settings for different schemas and data models.

CREATE AUDIT POLICY dm_ops_pol 
ACTIONS SELECT ON MINING MODEL dmuser1.nb_model, ALTER ON MINING MODEL dmuser1.nb_model;

AUDIT POLICY dm_ops_pol BY psmith;

The CREATE AUDIT POLICY statement can audit failed Oracle Machine Learning for SQL operations by a user.

Example 26-30 shows how to audit all failed Oracle Machine Learning for SQL operations by user psmith.

CREATE AUDIT POLICY dm_all_ops_pol ACTIONS ALL ON MINING MODEL dmuser1.nb_model;

AUDIT POLICY dm_all_ops_pol BY psmith WHENEVER NOT SUCCESSFUL;

The UNIFIED_AUDIT_TRAIL data dictionary view lists Oracle Machine Learning for SQL audit events.

The following example shows how to query the UNIFIED_AUDIT_TRAIL data dictionary view for Machine Learning for SQL audit events.

SELECT DBUSERNAME, ACTION_NAME, SYSTEM_PRIVILEGE_USED, RETURN_CODE,
OBJECT_SCHEMA, OBJECT_NAME, SQL_TEXT
FROM UNIFIED_AUDIT_TRAIL;

DBUSERNAME ACTION_NAME          SYSTEM_PRIVILEGE_USED     RETURN_CODE
---------- -------------------- ------------------------- -----------
OBJECT_SCHEMA        OBJECT_NAME
-------------------- --------------------
SQL_TEXT
--------------------------------------------------------------------------------
DMUSER1    CREATE MINING MODEL  CREATE MINING MODEL                 0
DMUSER1
BEGIN
  dbms_data_mining.create_model(model_name => 'nb_model',
                mining_function => dbms_data_mining.classification,
                data_table_name => 'dm_data',
                case_id_column_name => 'case_id',
                target_column_name => 'target');
END;
 
DMUSER1    SELECT MINING MODEL                                      0
DMUSER1              NB_MODEL
select prediction(nb_model using *) from dual
 
DMUSER2    SELECT MINING MODEL                                  40284
DMUSER1              NB_MODEL
select prediction(dmuser1.nb_model using *) from dual
 
DMUSER1    ALTER MINING MODEL                                       0
DMUSER1              NB_MODEL
BEGIN dbms_data_mining.rename_model('nb_model', 'nb_model1'); END;
 
 
DMUSER2    ALTER MINING MODEL                                   40284
DMUSER1              NB_MODEL
BEGIN dbms_data_mining.rename_model('dmuser1.nb_model1', 'nb_model'); END;
 
 
DMUSER2    ALTER MINING MODEL                                   40284
DMUSER1              NB_MODEL
BEGIN dbms_data_mining.rename_model('dmuser1.nb_model1', 'nb_model'); END;

The CREATE AUDIT POLICY statement COMPONENT clause must be set to DATAPUMP to create Oracle Data Pump unified audit policies.

You can audit Data Pump export (expdp) and import (impdp) operations.

As with all unified auditing, you must have the AUDIT_ADMIN role before you can audit Oracle Data Pump events.

To access the audit trail, query the UNIFIED_AUDIT_TRAIL data dictionary view. The Data Pump-specific columns in this view begin with DP_.

The unified audit trail can capture Oracle Data Pump events.

The unified audit trail captures information about both export (expdp) and import (impdp) operations.

The ACTIONS COMPONENT clause in the CREATE AUDIT POLICY statement can be used to create an Oracle Data Pump event unified audit policy.

CREATE AUDIT POLICY audit_dp_export_pol
 ACTIONS COMPONENT=DATAPUMP EXPORT;

The CREATE AUDIT POLICY statement can audit Oracle Data Pump import operations.

Example 26-31 shows how to audit all Oracle Data Pump import operations.

CREATE AUDIT POLICY audit_dp_import_pol
 ACTIONS COMPONENT=DATAPUMP IMPORT;

AUDIT POLICY audit_dp_import_pol;

The CREATE AUDIT POLICY statement can audit all Oracle Data Pump operations.

Example 26-32 shows how to audit both Oracle Database Pump export and import operations.

CREATE AUDIT POLICY audit_dp_all_pol
 ACTIONS COMPONENT=DATAPUMP ALL;

AUDIT POLICY audit_dp_all_pol BY SYSTEM;

The UNIFIED_AUDIT_TRAIL data dictionary view lists Oracle Data Pump audited events.

The DP_* columns of the UNIFIED_AUDIT_TRAIL view show Oracle Data Pump-specific audit data. For example:

SELECT DP_TEXT_PARAMETERS1, DP_BOOLEAN_PARAMETERS1, DP_WARNINGS1 FROM UNIFIED_AUDIT_TRAIL
WHERE AUDIT_TYPE = 'DATAPUMP';

DP_TEXT_PARAMETERS1                            DP_BOOLEAN_PARAMETERS1  DP_WARNINGS1
---------------------------------------------- ----------------------- –--------------------------------------------------------------------------

MASTER TABLE:  "SCOTT"."SYS_EXPORT_TABLE_01",  MASTER_ONLY: FALSE,     WARNING(S) ISSUED: ORA-39173: Encrypted data has been stored unencrypted in
JOB_TYPE: EXPORT,                              DATA_ONLY: FALSE,       dump
METADATA_JOB_MODE: TABLE_EXPORT,               METADATA_ONLY: FALSE,
JOB VERSION: 19.1.0.0,                         DUMPFILE_PRESENT: TRUE, 
ACCESS METHOD: DIRECT_PATH,                    JOB_RESTARTED: FALSE 
DATA OPTIONS: 0, 
DUMPER DIRECTORY: NULL  
REMOTE LINK: NULL, 
TABLE EXISTS: NULL, 
PARTITION OPTIONS: NONE 

(This output was reformatted for easier readability.)

You must have the AUDIT_ADMIN role to audit Oracle SQL*Loader direct path events.

To create SQL*Loader unified audit policies, you must set the CREATE AUDIT POLICY statement's COMPONENT clause to DIRECT_LOAD. You can audit direct path load operations only, not other SQL*Loader loads, such as conventional path loads.

To access the audit trail, you can query the DIRECT_PATH_NUM_COLUMNS_LOADED column in the UNIFIED_AUDIT_TRAIL data dictionary view.

The unified audit trail can capture SQL*Loader Direct Load Path events.

The unified audit trail captures information about direct path loads that SQL*Loader performs (that is, when you set direct=true on the SQL*Loader command line or in the SQL*Loader control file).

It also audits Oracle Call Interface (OCI) programs that use the direct path API.

The CREATE AUDIT POLICY statement ACTIONS COMPONENT clause can create unified audit policies for Oracle SQL*Loader direct path events.

CREATE AUDIT POLICY audit_sqlldr_pol
 ACTIONS COMPONENT=DIRECT_LOAD LOAD;

The CREATE AUDIT POLICY statement can audit Oracle SQL*Loader direct path load operations.

Example 26-31 shows how to audit SQL*Loader direct path load operations.

CREATE AUDIT POLICY audit_sqlldr_load_pol
 ACTIONS COMPONENT=DIRECT_LOAD LOAD;

AUDIT POLICY audit_sqlldr_load_pol;

The UNIFIED_AUDIT_TRAIL data dictionary view lists SQL*Loader direct path load audited events.

The DIRECT_PATH_NUM_COLUMNS_LOADED column of the UNIFIED_AUDIT_TRAIL view shows the number of columns that were loaded using the SQL*Loader direct path load method. For example:

SELECT DBUSERNAME, ACTION_NAME, OBJECT_SCHEMA, OBJECT_NAME, DIRECT_PATH_NUM_COLUMNS_LOADED FROM UNIFIED_AUDIT_TRAIL WHERE AUDIT_TYPE = 'DIRECT PATH API';

DBUSERNAME  ACTION_NAME OBJECT_SCHEMA OBJECT_NAME  DIRECT_PATH_NUM_COLUMNS_LOADED
----------- ----------- ------------- ------------ ------------------------------
RLAYTON     INSERT       HR            EMPLOYEES    4

An audit policy can be either a local audit policy, a CDB common audit policy, or an application common audit policy.

This applies to both unified audit policies and policies that are created using the AUDIT SQL statement.

• Local audit policy. This type of policy can exist in either the root (CDB or application) or the PDB (CDB or application). A local audit policy that exists in the root can contain object audit options for both local and common objects. Both local and common users who have been granted the AUDIT_ADMIN role can enable local policies: local users from their PDBs and common users from the root or the PDB to which they have privileges. You can enable a local audit policy for both local and common users and roles.

  You can create local audit policies for application local objects and application local roles, as well as system action options and system privilege options. You cannot enforce a local audit policy for a common user across all containers, nor can you enforce a common audit policy for a local user.

• CDB common audit policy. This type of policy is available to all PDBs in the multitenant environment. Only common users who have been granted the AUDIT_ADMIN role can create and maintain common audit policies. You can enable common audit policies only for common users. You must create common audit policies only in the root. This type of policy can contain object audit options of only common objects, and be enabled only for common users. You can enable a common audit policy for common users and roles only.

  You cannot enforce a common audit policy for a local user across all containers.

• Application common audit policy. Similar to CDB common audit policies, this type of policy is available to all PDBs in the multitenant environment. You can create common audit policies for application common objects and application common roles, as well as system action options and system privilege options. You can only create this type of policy in the application root container, but you can enable it on both application common users and CDB common users. If you want to audit objects, then ensure that these objects are application common objects. You can determine whether an object is an application common object by querying the SHARING column of the DBA_OBJECTS data dictionary view.

By default, audit policies are local to the current PDB, for both CDB and application scenarios.

The following table explains how audit policies apply in different multitenant environments.

For both mixed mode and unified auditing, 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. 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 PDBs. Operating system and XML audit trails for PDBs are stored in subdirectories of the directory specified by the AUDIT_FILE_DEST initialization parameter.

The CONTAINER clause is specific to multitenant environment use for the CREATE AUDIT POLICY statement.

CREATE AUDIT POLICY dict_updates 
 ACTIONS UPDATE ON SYS.USER$, 
  DELETE ON SYS.USER$, 
  UPDATE ON SYS.LINK$, 
  DELETE ON SYS.LINK$ 
  CONTAINER = ALL;

The CREATE AUDIT POLICY statement can create a local unified audit policy in either the root or a PDB.

When you create a local unified audit policy in the root, it only applies to the root and not across the multitenant environment.

The following example shows a local unified audit policy that has been created by the common user c##sec_admin from a PDB and applied to common user c##hr_admin.

CONNECT c##sec_admin@pdb_name
Enter password: password
Connected.

CREATE AUDIT POLICY table_privs
 PRIVILEGES CREATE ANY TABLE, DROP ANY TABLE
 CONTAINER = CURRENT;

AUDIT POLICY table_privs BY c##hr_admin;

The CREATE AUDIT POLICY statement can create a CDB common unified audit policy.

Example 26-39 shows a common unified audit policy that has been created by the common user c##sec_admin from the root and applied to common user c##hr_admin.

CONNECT c##sec_admin
Enter password: password
Connected.

CREATE AUDIT POLICY admin_pol
 ACTIONS CREATE TABLE, ALTER TABLE, DROP TABLE
 ROLES c##hr_mgr, c##hr_sup
 CONTAINER = ALL;

AUDIT POLICY admin_pol BY c##hr_admin;

You can query unified audit policy views from either the root or the PDB in which the action occurred.

You can perform the following types of queries:

• Audit records from all PDBs. The audit trail reflects audited actions that have been performed in the PDBs. For example, if user lbrown in PDB1 performs an action that has been audited by either a common or a local audit policy, then the audit trail will capture this action. The DBID column in the UNIFIED_AUDIT_TRAIL data dictionary view indicates the PDB in which the audited action takes place and to which the policy applies. If you want to see audit records from all PDBs, you should query the CDB_UNIFIED_AUDIT_TRAIL data dictionary view from the root.

• Audit records from common audit policies. This location is where the common audit policy results in an audit record. The audit record can be generated anywhere in the multitenant environment—the root or the PDBs, depending on where the action really occurred. For example, the common audit policy fga_pol audits the EXECUTE privilege on the DBMS_FGA PL/SQL package, and if this action occurs in PDB1, then the audit record is generated in PDB1 and not in the root. Hence, the audit record can be seen in PDB1.

  You can query the UNIFIED_AUDIT_TRAIL data dictionary view for the policy from either the root or a PDB if you include a WHERE clause for the policy name (for example, WHERE UNIFIED_AUDIT_POLICIES = 'FGA_POL').

The following example shows how to find the results of a common unified audit policy:

CONNECT c##sec_admin
Enter password: password
Connected.

SELECT DBID, ACTION_NAME, OBJECT_SCHEMA, OBJECT_NAME FROM CDB_UNIFIED_AUDIT_TRAIL WHERE DBUSERNAME = 'c##hr_admin';
46892-1
DBID        ACTION_NAME  OBJECT_SCHEMA  OBJECT_NAME
----------- -----------  -------------  -----------
653916017   UPDATE       HR             EMPLOYEES
653916018   UPDATE       HR             JOB_HISTORY
653916017   UPDATE       HR             JOBS 

You can change most properties in a unified audit policy, except for its CONTAINER setting.

You cannot alter unified audit policies in a multitenant environment. For example, you cannot turn a common unified audit policy into a local unified audit policy.

To find existing unified audit policies, query the AUDIT_UNIFIED_POLICIES data dictionary view. If you want to find only the enabled unified audit policies, then query the AUDIT_UNIFIED_ENABLED_POLICIES view. You can alter both enabled and disabled audit policies. If you alter an enabled audit policy, it remains enabled after you alter it.

After you alter an object unified audit policy, the new audit settings take place immediately, for both the active and subsequent user sessions. If you alter system audit options, or audit conditions of the policy, then they are activated for new user sessions, but not the current user session.

The ALTER AUDIT POLICY statement can modify a unified audit policy.

• Use the following syntax to alter a unified audit policy, you use the ALTER AUDIT POLICY statement.

  ALTER AUDIT POLICY  policy_name
  [ADD [privilege_audit_clause][action_audit_clause]
    [role_audit_clause] [ONLY TOPLEVEL] ]
  [DROP [privilege_audit_clause][action_audit_clause]
      [role_audit_clause] [ONLY TOPLEVEL]] 
  [CONDITION {DROP | audit_condition EVALUATE PER {STATEMENT|SESSION|INSTANCE}}] 
  

  In this specification:

  • ADD enables you to alter the following the following settings:

    • privilege_audit_clause describes privilege-related audit options. The detailed syntax for configuring privilege audit options is as follows:

      ADD privilege_audit_clause  :=  PRIVILEGES  privilege1 [, privilege2]
      

    • action_audit_clause and standard_actions describe object action-related audit options. The syntax is as follows:

      ADD action_audit_clause := {standard_actions | component_actions}
                                               [, component_actions ]
      standard_actions :=
           ACTIONS action1 [ ON {schema.obj_name
                                                | DIRECTORY directory_name
                                                | MINING MODEL schema.obj_name
                                                 }
                      ] 
                 [, action2 [ ON {schema.obj_name
                                                | DIRECTORY directory_name
                                                | MINING MODEL schema.obj_name  
                         }
                      ]
      

    • role_audit_clause enables you to add or drop the policy for roles. The syntax is:

      ADD role_audit_clause := ROLES role1 [, role2]
      

    • ONLY TOPLEVEL includes in the unified audit trail only the top-level SQL statements that are affected by this policy.

  • DROP enables you to drop the same components that are described for the ADD clause. For example:

    DROP role_audit_clause := ROLES role1 [, role2 ONLY TOPLEVEl]
    

  • CONDITION {DROP... enables you to add or drop a condition for the policy. If you are altering an existing condition, then you must include the EVALUATE PER clause with the condition. The syntax is:

    CONDITION 'audit_condition := function operation value_list'
    EVALUATE PER {STATEMENT|SESSION|INSTANCE}
    

    If you want to drop a condition, then omit the condition definition and the EVALUATE PER clause. For example:

    CONDITION DROP

The ALTER AUDIT POLICY statement can alter conditions in unified audit policies.

Example 26-41 shows how to change a condition in an existing unified audit policy.

ALTER AUDIT POLICY orders_unified_audpol
 ADD ACTIONS INSERT ON SCOTT.EMP
CONDITION 'SYS_CONTEXT(''ENTERPRISE'', ''GROUP'') =  ''ACCESS_MANAGER'''
EVALUATE PER SESSION;

The ALTER AUDIT POLICY statement can alter Oracle Label Security components in an audit policy.

Example 26-42 shows how to alter an Oracle Label Security component in an audit policy.

ALTER AUDIT POLICY audit_ols
 ADD ACTIONS SELECT ON HR.EMPLOYEES
 ACTIONS COMPONENT=OLS DROP POLICY, DISABLE POLICY, REMOVE POLICY;

The ALTER AUDIT POLICY statement can alter roles in a unified audit policy.

Example 26-43 shows how to add roles to a common unified audit policy.

CONNECT c##sec_admin
Enter password: password
Connected.

ALTER AUDIT POLICY RoleConnectAudit 
 ADD ROLES c##role1, c##role2;

The ALTER AUDIT POLICY statement can drop a condition from a unified audit policy.

Example 26-44 shows how to drop a condition from an existing unified audit policy.

ALTER AUDIT POLICY orders_unified_audpol
CONDITION DROP;

The AUDIT statement with the POLICY clause enables a unified audit policy, applying for all types of audit options, including object-level options.

The policy is enabled immediately in the current session and in any ongoing active sessions, including sessions for other users who are logged in.

You can enable the audit policy for individual users or for roles. Enabling the audit policy for roles allows you to enable the policy for a group of users who have been directly granted the role. When the role has been directly granted to a new user, then the policy automatically applies to the user. When the role is revoked from a user, then the policy no longer applies to the user.

You can check the results of the audit by querying the UNIFIED_AUDIT_TRAIL data dictionary view. To find a list of existing unified audit policies, query the AUDIT_UNIFIED_POLICIES data dictionary view.

The AUDIT statement lets you specify the following optional additional settings:

• Whether to apply the unified audit policy to one or more users or roles.To apply the policy to one or more users or roles, including administrative users who log in with the SYSDBA administrative privilege (such as SYS), use the BY clause. For example, to apply the policy to users SYS and SYSTEM:

  For example, to apply the policy to two users:

  AUDIT POLICY role_connect_audit_pol BY SYS, SYSTEM;

  To apply a policy to users who have been directly granted the DBA and CDB_DBA roles:

  AUDIT POLICY admin_audit_pol BY USERS WITH GRANTED ROLES DBA, CDB_DBA;

• Whether to exclude users from the unified audit policy. To exclude users from the audit policy, include the EXCEPT clause.

  For example:

  AUDIT POLICY role_connect_audit_pol EXCEPT rlee, jrandolph;
  

• Whether to create an audit record if the activity succeeds or fails. This method of auditing reduces the audit trail, helping you to focus on specific actions. This can aid in maintaining good database performance. Enter one of the following clauses:

  • WHENEVER SUCCESSFUL audits only successful executions of the user’s activity.

  • WHENEVER NOT SUCCESSFUL audits only failed executions of the user’s activity. Monitoring unsuccessful SQL statement can expose users who are snooping or acting maliciously, though most unsuccessful SQL statements are neither.

  For example:

  AUDIT POLICY role_connect_audit_pol WHENEVER NOT SUCCESSFUL;
  

  If you omit this clause, then both failed and successful user activities are written to the audit trail.

Note the following:

• The unified audit policy only can have either the BY, BY USERS WITH GRANTED ROLES, or the EXCEPT clause, but not more than one of these clauses for the same policy.

• If you run multiple AUDIT statements on the same unified audit policy but specify different BY users or different BY USERS WITH GRANTED ROLES roles, then Oracle Database audits all of these users or roles.

• If you run multiple AUDIT statements on the same unified audit policy but specify different EXCEPT users, then Oracle Database uses the last exception user list, not any of the users from the preceding lists. This means the effect of the earlier AUDIT POLICY ... EXCEPT statements are overridden by the latest AUDIT POLICY ... EXCEPT statement.

• You cannot use the EXCEPT clause for roles. It applies to users only.

• You can only enable common unified audit policies for common users or roles.

• You can enable a common audit policy only from the root and a local audit policy only from the PDB to which it applies.

The AUDIT POLICY statement can enable a unified audit policy.

• Use the following syntax to enable a unified audit policy:

  AUDIT POLICY { policy_auditing }
   [WHENEVER [NOT] SUCCESSFUL]
  

  In this specification:

  • policy_auditing refers to the following components:

    • The name of the unified audit policy. To find all existing policies, query the AUDIT_UNIFIED_POLICIES data dictionary view. To find currently enabled policies, query AUDIT_UNIFIED_ENABLED_POLICIES.

    • Users or roles to whom the unified audit policy applies. To apply the policy to one or more users (including user SYS), enter the BY clause. For example:

      BY psmith, rlee
      

      To apply the policy to one or more users to whom the list of roles are directly granted, use the BY USERS WITH GRANTED ROLES clause. For example:

      BY USERS WITH GRANTED ROLES HS_ADMIN_ROLE, HS_ADMIN_SELECT_ROLE

    • Users to exclude from the unified audit policy. To exclude one or more users from the policy, enter the EXCEPT clause. For example:

      EXCEPT psmith, rlee
      

      Mandatory audit records are captured in the UNIFIED_AUDIT_TRAIL data dictionary view for the AUDIT POLICY SQL statement. To find users who have been excluded in the audit records, you can query the EXCLUDED_USER column in the UNIFIED_AUDIT_TRAIL view to list the excluded users.

    You cannot enable the same audit policy with the BY, BY USERS WITH GRANTED ROLES, and EXCEPT clauses in the same statement. This action throws an error for the subsequent AUDIT statement with the conflicting clause

  • WHENEVER [NOT] SUCCESSFUL enables the policy to generate audit records based on whether the user's actions failed or succeeded.

The AUDIT POLICY statement can enable a unified audit policy using conditions, such as WHENEVER NOT SUCCESSFUL.

Example 26-46 shows how to enable a unified audit policy to record only failed actions by the user dv_admin.

AUDIT POLICY dv_admin_pol BY tjones
 WHENEVER NOT SUCCESSFUL;

The NOAUDIT statement with the POLICY clause can disable a unified audit policy.

In the NOAUDIT statement, you can specify a BY user or BY USERS WITH GRANTED ROLES role list, but not an EXCEPT user list. The disablement of a unified audit policy takes effect on subsequent user sessions.

You can find a list of existing unified audit policies by querying the AUDIT_UNIFIED_POLICIES data dictionary view.

You can disable a common audit policy only from the root and a local audit policy only from the PDB to which it applies.

The NOAUDIT statement can disable a unified audit policy using supported audit options.

• Use the following syntax to disable a unified audit policy:

  NOAUDIT POLICY {policy_auditing | existing_audit_options};
  

  In this specification:

  • policy_auditing is the name of the policy. To find all currently enabled policies, query the AUDIT_UNIFIED_ENABLED_POLICIES data dictionary view. As part of this specification, you optionally can include the BY or BY USERS WITH GRANTED ROLES clause, but not the EXCEPT clause.

  • existing_audit_options refers to AUDIT options that were available in releases earlier than Oracle Database 12c release 1 (12.1), such as the following:

    • SELECT ANY TABLE, UPDATE ANY TABLE BY SCOTT, HR

    • UPDATE ON SCOTT.EMP

  If the unified policy had been applied to all users, then you only need to specify the policy name. For example:

  NOAUDIT POLICY logons_pol;

The NOAUDIT POLICY statement disable a unified audit policy using filtering, such as by user name.

Example 26-47 shows examples of how to disable a unified audit policy for a user and for a role.

NOAUDIT POLICY dv_admin_pol BY tjones;

NOAUDIT POLICY dv_admin_pol BY USERS WITH GRANTED ROLES emp_admin;

The DROP AUDIT POLICY statement can be used to unified audit policies.

If a unified audit policy is already enabled for a session, the effect of dropping the policy is not seen by this existing session. Until that time, the unified audit policy's settings remain in effect. For object-related unified audit policies, however, the effect is immediate.

You can find a list of existing unified audit policies by querying the AUDIT_UNIFIED_POLICIES data dictionary view.

When you disable an audit policy before dropping it, ensure that you disable it using the same settings that you used to enable it. For example, suppose you enabled the logon_pol policy as follows:

AUDIT POLICY logon_pol BY HR, OE;

Before you can drop it, your NOAUDIT statement must include the HR and OE users as follows:

NOAUDIT POLICY logon_pol BY HR, OE;

You can drop a common audit policy only from the root and a local audit policy only from the PDB to which it applies.

To drop a unified audit policy, you must first disable it, and then run the DROP AUDIT POLICY statement to remove it.

The NOAUDIT POLICY and DROP AUDIT POLICY statements can disable and drop a unified audit policy.

Example 26-48 shows how to disable and drop a common unified audit policy.

CONNECT c##sec_admin
Enter password: password
Connected.

NOAUDIT POLICY dv_admin_pol;

DROP AUDIT POLICY dv_admin_pol

You must first create users and ensure that the user OE is active.

1. Log in to a PDB as user SYS with the SYSDBA administrative privilege.

   sqlplus sys@pdb_name as sysdba
   Enter password: password
   

   To find the available PDBs in a CDB, log in to the CDB root container and then query the PDB_NAME column of the DBA_PDBS data dictionary view. To check the current container, run the show con_name command.

2. Create the local user policy_admin, who will create the fine-grained audit policy.

   CREATE USER policy_admin IDENTIFIED BY password;
   GRANT CREATE SESSION, AUDIT_ADMIN TO policy_admin;
   

   Replace password with a password that is secure.

3. Create the local user account auditor, who will check the audit trail for this policy.

   CREATE USER policy_auditor IDENTIFIED BY password;
   GRANT CREATE SESSION, AUDIT_VIEWER TO policy_auditor;
   

4. The sample user OE will also be used in this tutorial, so query the DBA_USERS data dictionary view to ensure that OE is not locked or expired.

   SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = 'OE';
   

   The account status should be OPEN. If the DBA_USERS view lists user OE as locked and expired, log in as user SYSTEM and then enter the following statement to unlock the OE account and create a new password:

   ALTER USER OE ACCOUNT UNLOCK IDENTIFIED BY password;
   

   Replace password with a password that is secure. For greater security, do not give the OE account the same password from previous releases of Oracle Database.

Next, you are ready to create the unified audit policy.

1. Connect to the PDB as user policy_admin.

   CONNECT policy_admin@pdb_name
   Enter password: password
   

2. Create the following policy:

   CREATE AUDIT POLICY orders_unified_audpol 
     ACTIONS INSERT ON OE.ORDERS, UPDATE ON OE.ORDERS, DELETE ON OE.ORDERS, SELECT ON OE.ORDERS
     WHEN 'SYS_CONTEXT(''USERENV'', ''CLIENT_IDENTIFIER'') = ''robert''' 
       EVALUATE PER STATEMENT;
   
   AUDIT POLICY orders_unified_audpol;
   

   In this example, the AUDIT_CONDITION parameter assumes that the nondatabase user is named robert. The policy will monitor any INSERT, UPDATE, DELETE, and SELECT statements that robert will attempt. Remember that the user's CLIENT_IDENTITIFER setting that you enter in the policy is case sensitive and that the policy only recognizes the case used for the identity that you specify here. In other words, later on, if the user session is set to Robert or ROBERT, the policy's condition will not be satisfied.

To test the policy, use OE must try to select from the OE.ORDERS table.

1. Connect as user OE and then select from the OE.ORDERS table.

   CONNECT OE@pdb_name
   Enter password: password
   
   SELECT COUNT(*) FROM ORDERS;
   

   The following output appears:

     COUNT(*)
   ----------
          105
   

2. Connect as user policy_auditor and then check if any audit records were generated.

   CONNECT policy_auditor@pdb_name
   Enter password: password
   
   col dbusername format a10
   col client_identifier format a20
   col sql_text format a29
   
   SELECT DBUSERNAME, CLIENT_IDENTIFIER, SQL_TEXT FROM UNIFIED_AUDIT_TRAIL 
    WHERE SQL_TEXT LIKE '%FROM ORDERS%';
   

   The following output appears:

   no rows selected
   

3. Reconnect as user OE, set the client identifier to robert, and then reselect from the OE.ORDERS table.

   CONNECT OE@pdb_name
   Enter password: password
   
   EXEC DBMS_SESSION.SET_IDENTIFIER('robert');
   
   SELECT COUNT(*) FROM ORDERS;
   

   The following output should appear:

     COUNT(*)
   ----------
          105
   

4. Reconnect as user auditor and then check the audit trail again.

   CONNECT policy_auditor@pdb_name
   Enter password: password
   
   SELECT DBUSERNAME, CLIENT_IDENTIFIER, SQL_TEXT FROM UNIFIED_AUDIT_TRAIL 
    WHERE SQL_TEXT LIKE '%FROM ORDERS%';
   

   This time, because robert has made his appearance and queried the OE.ORDERS table, the audit trail captures his actions:

   DBUSERNAME CLIENT_IDENTIFIER SQL_TEXT
   ---------- ----------------- ----------------------------
   OE         robert            SELECT COUNT(*) FROM ORDERS;
   

If you no longer need the components of this tutorial, then you can remove them.

1. Connect as user policy_admin, and then manually disable and drop the orders_unified_audpol policy.

   CONNECT policy_admin@pdb_name
   Enter password: password
   
   NOAUDIT POLICY orders_unified_audpol;
   DROP AUDIT policy orders_unified_audpol;
   

   (Unified audit policies reside in the SYS schema, not the schema of the user who created them.)

2. Connect to SQL*Plus as user SYSTEM.

   CONNECT SYSTEM@pdb_name
   Enter password: password
   

3. Drop users policy_admin and policy_auditor.

   DROP USER policy_admin;
   DROP USER policy_auditor;
   

4. If you want, lock and expire OE, unless other users want to use this account:

   ALTER USER OE PASSWORD EXPIRE ACCOUNT LOCK;

The ORA_RAS_POLICY_MGMT predefined unified audit policy audits policies for all Oracle Real Application Security administrative actions on application users, roles, and policies.

The following CREATE AUDIT POLICY statement describes the ORA_RAS_POLICY_MGMT audit policy. By default, this policy is not enabled.

CREATE AUDIT POLICY ORA_RAS_POLICY_MGMT
 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;

For STIG compliance, enable the ORA_RAS_POLICY_MGMT unified audit policy for all users.

AUDIT POLICY ORA_RAS_POLICY_MGMT;

The ORA_RAS_SESSION_MGMT predefined unified audit policy audits policies for all run-time Oracle Real Application Security session actions and namespace actions.

The following CREATE AUDIT POLICY statement describes the ORA_RAS_SESSION_MGMT policy. By default, this policy is not enabled.

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;

For STIG compliance, enable the ORA_RAS_SESSION_MGMT for failed operations.

AUDIT POLICY ORA_RAS_SESSION_MGMT WHENEVER NOT SUCCESSFUL;