Auditing SQL Statements in Oracle Database
Key Concepts
1. SQL Auditing
SQL Auditing in Oracle Database involves tracking and recording SQL statements executed by users. This is crucial for security and compliance purposes, allowing administrators to monitor and review database activities.
Example:
Enabling SQL statement auditing for a specific user:
AUDIT INSERT, UPDATE, DELETE ON Employees BY scott;
2. Fine-Grained Auditing (FGA)
Fine-Grained Auditing (FGA) allows for more granular auditing of SQL statements. It enables auditing based on specific conditions, such as the value of a column or the time of execution.
Example:
Enabling FGA to audit SELECT statements on the Salary column of the Employees table:
BEGIN
DBMS_FGA.ADD_POLICY(
object_schema => 'HR',
object_name => 'Employees',
policy_name => 'Audit_Salary',
audit_column => 'Salary',
audit_condition => 'Salary > 50000'
);
END;
3. Unified Audit Trail
The Unified Audit Trail consolidates audit records from various sources into a single location. This makes it easier to manage and review audit data.
Example:
Enabling the Unified Audit Trail:
ALTER SYSTEM SET AUDIT_TRAIL = 'XML, EXTENDED' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
4. Audit Policies
Audit policies define what actions should be audited. They can be applied to specific users, roles, or system-wide.
Example:
Creating an audit policy to track all DDL statements:
CREATE AUDIT POLICY ddl_audit_policy
ACTIONS
CREATE TABLE, ALTER TABLE, DROP TABLE;
5. Audit Views
Oracle provides several views to query audit data, such as DBA_AUDIT_TRAIL and UNIFIED_AUDIT_TRAIL. These views contain detailed information about audited actions.
Example:
Querying the Unified Audit Trail to find all audit records:
SELECT * FROM UNIFIED_AUDIT_TRAIL;
6. Audit Exceptions
Audit exceptions allow you to exclude certain users or actions from being audited. This can be useful for reducing noise in the audit trail.
Example:
Creating an audit exception to exclude a specific user:
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
audit_trail_property => DBMS_AUDIT_MGMT.EXCLUDE_INTERNAL_SQL,
audit_trail_value => TRUE
);
END;
7. Audit Cleanup
Audit data can accumulate over time, consuming storage space. Audit cleanup involves periodically purging old audit records to manage storage efficiently.
Example:
Creating a job to clean up audit records older than 90 days:
BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
audit_trail_purge_interval => 24,
audit_trail_purge_name => 'Unified_Audit_Purge_Job',
use_last_arch_timestamp => TRUE
);
END;
8. Audit Profiles
Audit profiles allow you to define a set of audit settings that can be applied to multiple users or roles. This simplifies the management of audit configurations.
Example:
Creating an audit profile for sensitive data access:
CREATE AUDIT PROFILE sensitive_data_profile
ACTIONS
SELECT ON sensitive_table,
UPDATE ON sensitive_table,
DELETE ON sensitive_table;
9. Audit Reports
Audit reports provide a summary of audited activities. These reports can be generated manually or scheduled to run periodically.
Example:
Generating an audit report for the last 30 days:
SELECT * FROM UNIFIED_AUDIT_TRAIL
WHERE EVENT_TIMESTAMP >= SYSDATE - 30;
10. Audit Trail Maintenance
Maintaining the audit trail involves ensuring that audit data is stored securely and that the audit trail is not tampered with. This includes setting appropriate permissions and monitoring the audit trail for anomalies.
Example:
Setting permissions to restrict access to audit data:
GRANT SELECT ON UNIFIED_AUDIT_TRAIL TO audit_admin;
REVOKE SELECT ON UNIFIED_AUDIT_TRAIL FROM public;
11. Audit Trail Encryption
Encrypting the audit trail ensures that sensitive audit data is protected from unauthorized access. This is particularly important for compliance with data protection regulations.
Example:
Enabling encryption for the Unified Audit Trail:
ALTER SYSTEM SET AUDIT_SYS_OPERATIONS = TRUE;
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE;
12. Audit Trail Compression
Compressing the audit trail reduces storage requirements and improves performance. Oracle provides options to compress audit data at rest.
Example:
Enabling compression for the Unified Audit Trail:
ALTER SYSTEM SET AUDIT_TRAIL_COMPRESSION = TRUE;
13. Audit Trail Archiving
Archiving the audit trail involves moving old audit records to a separate storage location. This helps in managing the size of the active audit trail and preserving historical audit data.
Example:
Creating an archive for the Unified Audit Trail:
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
last_archive_time => SYSDATE - 90
);
END;
14. Audit Trail Monitoring
Monitoring the audit trail involves regularly reviewing audit records to detect unusual activities. This helps in identifying potential security threats and compliance issues.
Example:
Creating a monitoring report for failed login attempts:
SELECT * FROM UNIFIED_AUDIT_TRAIL
WHERE ACTION_NAME = 'LOGON' AND RETURN_CODE != 0;
15. Audit Trail Integration
Integrating the audit trail with external security information and event management (SIEM) systems allows for centralized monitoring and analysis of audit data.
Example:
Exporting audit data to a SIEM system:
BEGIN
DBMS_AUDIT_MGMT.EXPORT_UNIFIED_AUDIT_RECORDS(
export_dir => '/audit_export',
export_file_name => 'unified_audit_export.xml'
);
END;