Auditing Object Actions

You can use the CREATE AUDIT POLICY statement to audit object actions.

About Auditing Object Actions

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.

Note: The CREATE END USER SECURITY CONTEXT, ATTACH END USER SECURITY CONTEXT, and DETACH END USER SECURITY CONTEXT actions that were introduced as part of Oracle Deep Data Security will not be audited by default when an audit policy is configured for ACTIONS ALL as they may generate a large volume of audit records.

Object Actions That Can Be Audited

Auditing object actions can be broad or focused (for example, auditing all user actions or only a select list of user actions). The following table lists the object-level standard database action options. Audit policies for the SELECT SQL statement will capture READ actions as well as SELECT actions.

Object SQL Action That Can Be Audited
Directory AUDIT, GRANT, READ
Function AUDIT, EXECUTE, GRANT
Java schema objects (source, class, resource) AUDIT, EXECUTE, GRANT
Library EXECUTE, GRANT
Materialized views ALTER, AUDIT, COMMENT, DELETE, INDEX, INSERT, LOCK, SELECT, UPDATE
Mining Model AUDIT, COMMENT, GRANT, RENAME, SELECT
Object type ALTER, AUDIT, GRANT
Package AUDIT, EXECUTE, GRANT
Procedure (including triggers) AUDIT, EXECUTE, GRANT
Sequence ALTER, AUDIT, GRANT, SELECT
Table ALTER, AUDIT, COMMENT, DELETE, FLASHBACK, GRANT, INDEX, INSERT, LOCK, MERGE, RENAME, SELECT, UPDATE
Table or view column ALL, ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, SELECT, UPDATE
View AUDIT, COMMENT, DELETE, FLASHBACK, GRANT, INSERT, LOCK, MERGE, RENAME, SELECT, UPDATE

Related Topics

Guidelines for Column Level Auditing and Virtual Columns

When you create unified audit policies for columns, you should be aware of guidelines for handling virtual columns.

Configuring an Object Action Unified Audit Policy

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

Use the following syntax to create a unified audit policy that audits object actions:

<pre class="copy"><code>CREATE AUDIT POLICY policy_name  ACTIONS action1 [, action2 ON object1] [, action3 ON object2];</code></pre>

For example:

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

Note that you can audit multiple actions on multiple objects, as shown in this example.

You can build complex object action unified audit policies, such as those that include conditions. Remember that after you create the policy, you must use the AUDIT statement to enable it.

Related Topics

Example: Auditing Actions on SYS Objects

The CREATE AUDIT POLICY statement can audit actions on SYS objects. The following example 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;

Example: Auditing Multiple Actions on One Object

The CREATE AUDIT POLICY statement can audit multiple actions on one object. The following example 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;

Example: Auditing GRANT and REVOKE Operations on an Object

The CREATE AUDIT POLICY statement can audit GRANT and REVOKE operations on objects, such as tables.

Enabling auditing on GRANT operations on an object automatically enables the audit of REVOKE operations on the object as well.

The following example shows how to audit GRANT and REVOKE operations on an object.

CREATE AUDIT POLICY grant_revoke_pol
ACTIONS GRANT ON HR.EMPLOYEES;

AUDIT POLICY grant_revoke_pol;

The UNIFIED_AUDIT_TRAIL view captures the relevant information for a grant operation as shown in the following query. The grantee name (to whom the privilege is granted) is recorded in the TARGET_USER column.

SELECT DBUSERNAME, OBJECT_PRIVILEGES, ACTION_NAME, OBJECT_SCHEMA, OBJECT_NAME, TARGET_USER
FROM UNIFIED_AUDIT_TRAIL
WHERE ACTION_NAME IN ('GRANT', 'REVOKE');

Example: Auditing Both Actions and Privileges on an Object

The CREATE AUDIT POLICY statement can audit both actions and privileges on an object, using a single policy. The following example 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;

You can audit directory objects. For example, suppose you create a directory object that contains a preprocessor program that the ORACLE_LOADER access driver will use. You can audit anyone who runs this program within this directory object.

Example: Auditing an Action on a Table Column

The CREATE AUDIT POLICY statement can audit actions on table or view columns. The following example shows how to create an audit policy that audits SELECT statements on the SALARY column of the HR.EMPLOYEES table.

CREATE AUDIT POLICY emp_hr_emp_sal_access_pol
 ACTIONS SELECT(SALARY) ON HR.EMPLOYEES;

AUDIT POLICY emp_hr_emp_sal_access_pol;

Example: Auditing All Actions on a Table

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. The following example shows how to audit all actions on the HR.EMPLOYEES table 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 BY pmulligan;

Related Topics

Example: Auditing All Actions in the Database

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

Ensure that you include the ONLY TOPLEVEL clause to audit only the top-level user initiated actions. Consider adding conditions when you use the ACTIONS ALL clause to further reduce the audit volume.

Note: Use ACTIONS ALL auditing with caution. Do not enable it for users who must perform online transaction processing (OLTP) workloads. This will avoid generating a large number of audit records.

The following example 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;

Related Topics

Example: Deep Data Security Audit Policy

Oracle Deep Data Security enables application developers and security architects to define and enforce application-level security requirements directly at the database layer. Actions for configuring Deep Data Security can be audited through unified auditing as shown in the following example.

Oracle Deep Data Security extends and modernizes Oracle Virtual Private Database and Real Application Security, moving from earlier procedural PL/SQL and API-driven controls to declarative policies in SQL. Oracle recommends using Deep Data Security to ensure identity propagation, database-enforced authorizations and audit compliance.

You can create a custom unified audit policy to audit the commands used to configure Deep Data Security.

CREATE AUDIT POLICY deep_data_security_pol
  ACTIONS
    CREATE END USER,
    ALTER END USER,
    DROP END USER;

AUDIT POLICY deep_data_security_pol;

Just like auditing the actions of regular database users, configuring audit policies for database actions like SELECT, INSERT, UPDATE, DELETE, etc would capture the actions done by end users. To identify the end users who performed a specific actions two new columns have been introduced to the UNIFIED AUDIT TRAIL: END_USER_NAME and END_USER_SECURITY_CONTEXT_ID.

END_USER_NAME and END_USER_SECURITY_CONTEXT_ID are populated when an end user performs an audited action. However, they are not populated when a regular database user performs audited actions. Therefore, END_USER_NAME and END_USER_SECURITY_CONTEXT_ID can be used to identify and validate actions taken by end users as part of Deep Data Security.

The following example shows how to validate Deep Data Security entries in the UNIFIED_AUDIT_TRAIL.

SELECT EVENT_TIMESTAMP,
       DBUSERNAME,
       END_USER_NAME,
       END_USER_SECURITY_CONTEXT_ID,
       ACTION_NAME,
       SQL_TEXT
FROM UNIFIED_AUDIT_TRAIL
WHERE END_USER_NAME IS NOT NULL
ORDER BY EVENT_TIMESTAMP DESC;

Related Topics

How Object Action Unified Audit Policies Appear in the Audit Trail

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

Auditing Functions, Procedures, Packages, and Triggers

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

Points to consider:

Auditing of Oracle Virtual Private Database Predicates

The unified audit trail automatically captures the predicates that are used in Oracle Virtual Private Database (VPD) policies.

You do not need to create a unified audit policy to capture the VPD predicate audit information.

This type of audit enables you to identify the predicate expression that was run as part of a DML operation and thereby help you to identify other actions that may have occurred as part of the DML operation. For example, if a malicious attack on your database is performed using a VPD predicate, then you can track the attack by using the unified audit trail. In addition to predicates from user-created VPD policies, the internal predicates from Oracle Label Security and Oracle Real Application Security policies are captured as well. For example, Oracle Label Security internally creates a VPD policy while applying an OLS policy to a table. Oracle Real Application Security generates a VPD policy while enabling an Oracle RAS policy.

The unified audit trail writes this predicate information to the RLS_INFO column of the UNIFIED_AUDIT_TRAIL data dictionary view. If you have fine-grained audit policies, then the RLS_INFO column of these views captures VPD predicate information as well.

The audit trail can capture the predicates and their corresponding policy names if multiple VPD policies are enforced on the object. The audit trail captures the policy schema and policy name to enable you to differentiate predicates that are generated from different policies. By default, this information is concatenated in the RLS_INFO column, but Oracle Database provides a function in the DBMS_AUDIT_UTIL PL/SQL package that enables you to reformat the results in an easy-to-read format.

The following example shows how you can audit the predicates of a VPD policy:

  1. Create the following VPD policy function:

    CREATE OR REPLACE FUNCTION auth_orders(
  schema_var IN VARCHAR2,
  table_var  IN VARCHAR2
 )
 RETURN VARCHAR2
 IS
  return_val VARCHAR2 (400);
 BEGIN
  return_val := 'SALES_REP_ID = 159';
  RETURN return_val;
 END auth_orders;
/

  2. Create the following VPD policy:

    BEGIN
  DBMS_RLS.ADD_POLICY (
    object_schema    => 'oe',
    object_name      => 'orders',
    policy_name      => 'orders_policy',
    function_schema  => 'sec_admin',
    policy_function  => 'auth_orders',
    statement_types  => 'select, insert, update, delete'
   );
 END;
/

  3. Create and enable the following the unified audit policy:

    CREATE AUDIT POLICY oe_pol
 ACTIONS SELECT ON OE.ORDERS;

AUDIT POLICY oe_pol;

  4. Connect as user OE and query the OE.ORDERS table.

    CONNECT OE@pdb_name
Enter password: password

SELECT COUNT(*) FROM ORDERS;

  5. Connect as a user who has been granted the AUDIT_ADMIN role, and then query the UNIFIED_AUDIT_TRAIL data dictionary view.

    CONNECT sec_admin@pdb_name
Enter password: password

SELECT RLS_INFO FROM UNIFIED_AUDIT_TRAIL;

    Output similar to the following should appear:

    ((POLICY_TYPE=[3]'VPD'),(POLICY_SCHEMA=[9]'SEC_ADMIN'),(POLICY_NAME=[13]'ORDERS_POLICY'),(PREDICATE=[16]'SALES_REP_ID=159'));

  6. To extract these details and add them to their own columns, run the appropriate function from the DBMS_AUDIT_UTIL PL/SQL package.

    For unified auditing, you must run the DBMS_AUDIT_UTIL.DECODE_RLS_INFO_ATRAIL_UNI function.

    For example:

    SELECT DBUSERNAME, ACTION_NAME, OBJECT_NAME, SQL_TEXT,
  RLS_PREDICATE, RLS_POLICY_TYPE, RLS_POLICY_OWNER, RLS_POLICY_NAME
  FROM TABLE (DBMS_AUDIT_UTIL.DECODE_RLS_INFO_ATRAIL_UNI
  (CURSOR (SELECT * FROM UNIFIED_AUDIT_TRAIL)));

    The reformatted audit trail output appears similar to the following:

    DBUSERNAME ACTION_NAME OBJECT_NAME SQL_TEXT

---------- ----------- ----------- ---------------------------
RLS_PREDICATE       RLS_POLICY_TYPE RLS_POLICY_OWNER RLS_POLICY_NAME

------------------ ---------------  ---------------- ---------------
OE         SELECT      ORDERS      SELECT COUNT(*) FROM ORDERS
SALES_REP_ID = 159  VPD             SEC_ADMIN        ORDERS_POLICY

Related Topics

Audit Policies for Oracle Virtual Private Database Policy Functions

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

Unified Auditing with Editioned Objects

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.

Related Topics