Auditing the Use of Privileges

Because one of the main roles of auditing is to allow you to audit the security of your database, Oracle gives you the option of auditing the use of specific Oracle privileges. In Oracle 12c, auditing the use of specific Oracle privileges can be done using Unified Auditing^[1], which consolidates and enhances previous auditing functionalities. Unified Auditing is enabled by default in Oracle 12c. Here are the steps to audit the use of specific privileges:
Steps to Audit Specific Oracle Privileges

1. Enable Unified Auditing (if not already enabled)

   Verify whether Unified Auditing is enabled. Run the following query to check:

       SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';
       

   If Unified Auditing is disabled, you need to enable it by relinking the Oracle binaries. This typically requires restarting the database and administrative privileges.

2. Check the Audit Policies

   Unified Auditing uses audit policies. You can either use pre-defined policies or create your own custom policies. Check the existing policies:

       SELECT POLICY_NAME FROM AUDIT_UNIFIED_POLICIES;
       

3. Create a Custom Audit Policy for Privileges

   Use the CREATE AUDIT POLICY command to create a custom policy. For example, to audit the use of the CREATE TABLE privilege:

       CREATE AUDIT POLICY audit_create_table_privileges
       PRIVILEGES CREATE TABLE;
       

4. Apply the Audit Policy

   Apply the policy to specific users or roles:

   • To apply the policy to all users:

             AUDIT POLICY audit_create_table_privileges;
             

   • To apply the policy to a specific user:

             AUDIT POLICY audit_create_table_privileges BY USER user_name;
             

5. Verify the Audit Policy

   Confirm that the audit policy is applied:

       SELECT POLICY_NAME, AUDIT_OPTION, USER_NAME
       FROM AUDIT_UNIFIED_ENABLED_POLICIES;
       

6. Review the Audit Trail

   The audit trail can be reviewed using:

   • Database View: Check the UNIFIED_AUDIT_TRAIL view for audit records.

             SELECT EVENT_TIMESTAMP, USERNAME, ACTION_NAME, SQL_TEXT
             FROM UNIFIED_AUDIT_TRAIL
             WHERE ACTION_NAME = 'CREATE TABLE';
             

   • File-Based Audit Logs: If audit logs are configured to be written to OS files, check the designated directory.

---

Notes:

• Predefined Policies: Oracle has some predefined policies for auditing common administrative and security-related actions. Use these if they meet your needs.
• Privilege-specific Auditing: This approach ensures only the use of specific privileges is audited, reducing audit trail size and enhancing performance.
• Performance Considerations: Excessive auditing can impact database performance. Ensure you audit only necessary privileges.

In the earlier lesson on auditing SQL statements, you learned about some of the ways to audit the use of system privileges. When you choose to audit TABLE statements, Oracle will create an audit trail for the CREATE TABLE, DROP TABLE, and TRUNCATE TABLE statements. You can track when these statements are executed, which in turn will indicate if the user had the appropriate system privileges to execute the statements. You can also audit specific system privileges by indicating a specific system privilege as the option with the AUDIT statement, as with the statement

AUDIT DELETE ANY TABLE

If a user issues a SQL statement that invokes the DELETE ANY TABLE system privilege with this type of auditing turned on, Oracle will write a record to the audit log. Keep in mind that this record will be written only if the specific privilege of DELETE ANY TABLE is invoked by the user. If a user has DELETE privileges on a specific table in a schema, those privileges will be used, and the DELETE ANY TABLE system privilege will not be invoked.

You can use a few keyword shortcuts to audit system privileges. These keywords can be used with the AUDIT command to turn on auditing for a number of privileges. The shortcuts are as follows:

1. CONNECT audits the CREATE SESSION privilege.
2. RESOURCE audits the ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE PROCEDURE, CREATE ROLLBACK SEGMENT, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, CREATE TABLESPACE, and CREATE VIEW privileges.
3. DBA audits the AUDIT SYSTEM, CREATE PUBLIC DATABASE LINK, CREATE PUBLIC SYNONYM, CREATE ROLE, and CREATE USER privileges.
4. ALL PRIVILEGES audits the use of all system privileges.

In the next lesson, you will learn how to audit access to specific database objects.

[1]Unified Auditing: Unified Auditing in Oracle is a comprehensive auditing solution that provides a centralized and streamlined approach to audit data management. It allows for fine-grained auditing by using policies and conditions, enabling administrators to specify exactly which actions and events should be audited based on various criteria such as user, time, or object. This approach simplifies audit configuration and improves performance compared to traditional auditing methods.