Oracle 23c > Security

Enhanced Auditing in Oracle 23c: Granular Control at the Column Level

/ Data Security 23c, Oracle DB 23c New Features / By

Oracle Database 23c introduces a significant advancement in auditing capabilities with the ability to create audit policies on individual columns of tables and views. This granular control empowers organizations to fine-tune their audit trails, focusing on the most sensitive data elements and reducing audit overhead.

Key Enhancement: Column-Level Audit Policies

Prior to Oracle 23c, audit policies could only be applied at the object level, auditing all actions performed on a table or view. With this new feature, you can now selectively audit specific actions on individual columns, such as SELECT, INSERT, UPDATE, or DELETE. This provides several advantages:

  • Reduced Audit Trail Size: By auditing only the actions that directly affect sensitive columns, you can significantly reduce the volume of audit data generated, making it easier to manage and analyze.
  • Enhanced Security Focus: You can pinpoint auditing efforts on the most critical data elements, ensuring that any unauthorized access or modifications are captured.
  • Compliance Alignment: This feature can help organizations meet compliance requirements that mandate auditing of specific data fields.

Code Example: Implementing Column-Level Auditing

1. Create a Test Table:

CREATE TABLE employees (
   employee_id NUMBER,
   first_name VARCHAR2(50),
   last_name VARCHAR2(50),
   salary NUMBER,
   job_id VARCHAR2(10)
);

-- Insert sample data into the employees table
INSERT INTO employees VALUES (1, 'John', 'Doe', 50000, 'IT_PROG');
INSERT INTO employees VALUES (2, 'Jane', 'Smith', 60000, 'HR_REP');
INSERT INTO employees VALUES (3, 'Bob', 'Johnson', 45000, 'SA_REP');
COMMIT;

2. Create an Audit Policy for SELECT on Specific Columns:

CREATE AUDIT POLICY column_audit_policy
ACTIONS SELECT(salary, job_id) ON employees;

AUDIT POLICY column_audit_policy;

3. Simulate User Actions:

SELECT first_name, last_name FROM employees; -- Not audited
SELECT salary, job_id FROM employees;         -- Audited

4. Check the Audit Trail:

# output truncated:

SELECT EVENT_TIMESTAMP,
       DBUSERNAME,
       ACTION_NAME,
       OBJECT_SCHEMA,
       OBJECT_NAME,
       SQL_TEXT
FROM   UNIFIED_AUDIT_TRAIL
WHERE  OBJECT_NAME = 'EMPLOYEES'
ORDER BY EVENT_TIMESTAMP;

EVENT_TIMESTAMP    DBUSERNAME   ACTION_NAME  OBJECT_SCH    OBJECT_NAME SQL_TEXT
------------------ ------------ -----------  ------------- ----------  ------------------------------------
31-DEC-23 09.04.42 SYSTEM       SELECT       SYSTEM        EMPLOYEES   SELECT salary, job_id FROM employees

In Previous Versions

In Oracle versions prior to 23c, auditing at the column level was not directly available. To achieve similar functionality, you would have needed to create complex fine-grained auditing (FGA) policies or utilize triggers, both of which introduce additional overhead and complexity.

Conclusion

The introduction of column-level audit policies in Oracle 23c provides a powerful tool for organizations to enhance their security posture and meet compliance requirements. By selectively auditing actions on sensitive columns, you can reduce audit trail size, focus on critical data, and streamline compliance efforts. This feature marks a significant step forward in Oracle Database’s auditing capabilities, empowering organizations to achieve more granular and effective monitoring of their data.

Scroll to Top