Oracle Database Auditing Scripts - Audit Configuration & Monitoring

Oracle Database Auditing Scripts

Complete guide to Oracle database auditing with ready-to-run scripts for configuring, monitoring, and analyzing audit data.

Quick Audit Status Check

-- Check auditing mode (Traditional vs Unified)
SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

-- Current audit configuration
SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;

-- Audit trail status
SELECT AUDIT_TRAIL, STATUS FROM DBA_AUDIT_MGMT_CLEANUP_JOBS;

---

Unified Auditing (12c+)

Enable Unified Auditing

-- Check if unified auditing is enabled
SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

-- Create audit policy
CREATE AUDIT POLICY security_policy
ACTIONS
  CREATE TABLE,
  DROP TABLE,
  ALTER TABLE,
  CREATE USER,
  DROP USER,
  ALTER USER,
  GRANT,
  REVOKE;

-- Enable policy for all users
AUDIT POLICY security_policy;

-- Enable policy for specific users
AUDIT POLICY security_policy BY hr, scott;

View Unified Audit Trail

-- Recent audit events
SELECT
  EVENT_TIMESTAMP,
  DBUSERNAME,
  ACTION_NAME,
  OBJECT_SCHEMA,
  OBJECT_NAME,
  SQL_TEXT,
  RETURN_CODE
FROM UNIFIED_AUDIT_TRAIL
WHERE EVENT_TIMESTAMP > SYSDATE - 1
ORDER BY EVENT_TIMESTAMP DESC
FETCH FIRST 100 ROWS ONLY;

-- Failed login attempts
SELECT
  EVENT_TIMESTAMP,
  DBUSERNAME,
  OS_USERNAME,
  USERHOST,
  AUTHENTICATION_TYPE,
  RETURN_CODE
FROM UNIFIED_AUDIT_TRAIL
WHERE ACTION_NAME = 'LOGON'
  AND RETURN_CODE != 0
  AND EVENT_TIMESTAMP > SYSDATE - 7
ORDER BY EVENT_TIMESTAMP DESC;

-- Privilege usage
SELECT
  EVENT_TIMESTAMP,
  DBUSERNAME,
  ACTION_NAME,
  SYSTEM_PRIVILEGE_USED,
  OBJECT_SCHEMA,
  OBJECT_NAME
FROM UNIFIED_AUDIT_TRAIL
WHERE SYSTEM_PRIVILEGE_USED IS NOT NULL
  AND EVENT_TIMESTAMP > SYSDATE - 1
ORDER BY EVENT_TIMESTAMP DESC;

Audit Policies Management

-- View all audit policies
SELECT POLICY_NAME, ENABLED_OPT, USER_NAME, SUCCESS, FAILURE
FROM AUDIT_UNIFIED_ENABLED_POLICIES;

-- View policy details
SELECT * FROM AUDIT_UNIFIED_POLICIES
WHERE POLICY_NAME = '&policy_name';

-- Disable audit policy
NOAUDIT POLICY security_policy;

-- Drop audit policy
DROP AUDIT POLICY security_policy;

---

Traditional Auditing (Pre-12c Compatible)

Configure Traditional Auditing

-- Check audit_trail parameter
SHOW PARAMETER audit_trail;

-- Enable auditing (requires restart)
ALTER SYSTEM SET audit_trail = DB SCOPE=SPFILE;
-- Options: NONE, OS, DB, DB_EXTENDED, XML, XML_EXTENDED

-- Audit specific statements
AUDIT CREATE TABLE BY ACCESS;
AUDIT DROP ANY TABLE BY ACCESS;
AUDIT ALTER USER BY ACCESS;
AUDIT GRANT ANY PRIVILEGE BY ACCESS;

-- Audit object access
AUDIT SELECT ON hr.employees BY ACCESS;
AUDIT INSERT, UPDATE, DELETE ON hr.employees BY ACCESS;

-- Audit by user
AUDIT ALL BY scott BY ACCESS;

View Traditional Audit Trail

-- DBA audit trail
SELECT
  TIMESTAMP,
  USERNAME,
  ACTION_NAME,
  OBJ_NAME,
  SQL_TEXT,
  RETURNCODE
FROM DBA_AUDIT_TRAIL
WHERE TIMESTAMP > SYSDATE - 1
ORDER BY TIMESTAMP DESC;

-- Count by action type
SELECT
  ACTION_NAME,
  COUNT(*) AS event_count
FROM DBA_AUDIT_TRAIL
WHERE TIMESTAMP > SYSDATE - 7
GROUP BY ACTION_NAME
ORDER BY COUNT(*) DESC;

---

Security Monitoring Scripts

Privileged User Activity

-- DBA activity monitoring
SELECT
  EVENT_TIMESTAMP,
  DBUSERNAME,
  ACTION_NAME,
  OBJECT_SCHEMA,
  OBJECT_NAME,
  SQL_TEXT
FROM UNIFIED_AUDIT_TRAIL
WHERE DBUSERNAME IN (
  SELECT GRANTEE FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE = 'DBA'
)
AND EVENT_TIMESTAMP > SYSDATE - 1
ORDER BY EVENT_TIMESTAMP DESC;

-- SYS user activity
SELECT
  EVENT_TIMESTAMP,
  ACTION_NAME,
  OBJECT_NAME,
  SQL_TEXT
FROM UNIFIED_AUDIT_TRAIL
WHERE DBUSERNAME = 'SYS'
  AND EVENT_TIMESTAMP > SYSDATE - 1
ORDER BY EVENT_TIMESTAMP DESC;

DDL Changes Tracking

-- All DDL operations
SELECT
  EVENT_TIMESTAMP,
  DBUSERNAME,
  ACTION_NAME,
  OBJECT_SCHEMA,
  OBJECT_NAME,
  SQL_TEXT
FROM UNIFIED_AUDIT_TRAIL
WHERE ACTION_NAME IN (
  'CREATE TABLE', 'DROP TABLE', 'ALTER TABLE',
  'CREATE INDEX', 'DROP INDEX',
  'CREATE VIEW', 'DROP VIEW',
  'CREATE PROCEDURE', 'DROP PROCEDURE',
  'CREATE TRIGGER', 'DROP TRIGGER'
)
AND EVENT_TIMESTAMP > SYSDATE - 7
ORDER BY EVENT_TIMESTAMP DESC;

Data Access Monitoring

-- Access to sensitive tables
SELECT
  EVENT_TIMESTAMP,
  DBUSERNAME,
  ACTION_NAME,
  OBJECT_NAME,
  SQL_TEXT
FROM UNIFIED_AUDIT_TRAIL
WHERE OBJECT_NAME IN ('EMPLOYEES', 'SALARIES', 'CREDIT_CARDS')
  AND EVENT_TIMESTAMP > SYSDATE - 1
ORDER BY EVENT_TIMESTAMP DESC;

---

Audit Trail Management

Cleanup Old Audit Records

-- Set cleanup interval
BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    last_archive_time => SYSTIMESTAMP - 90  -- Keep 90 days
  );
END;
/

-- Manual cleanup
BEGIN
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    use_last_arch_timestamp => TRUE
  );
END;
/

-- Create automated cleanup job
BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    audit_trail_purge_interval => 24,  -- Hours
    audit_trail_purge_name => 'AUDIT_CLEANUP_JOB',
    use_last_arch_timestamp => TRUE
  );
END;
/

Audit Trail Space Usage

-- Unified audit trail size
SELECT
  OCCUPANT_NAME,
  OCCUPANT_SIZE/1024/1024 AS size_mb
FROM V$SYSAUX_OCCUPANTS
WHERE OCCUPANT_NAME LIKE '%AUDIT%';

-- Traditional audit trail size
SELECT
  SEGMENT_NAME,
  BYTES/1024/1024 AS size_mb
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME LIKE 'AUD$%'
ORDER BY BYTES DESC;

---

Common Audit Policies

SOX Compliance Policy

CREATE AUDIT POLICY sox_compliance
ACTIONS
  LOGON,
  CREATE USER, ALTER USER, DROP USER,
  GRANT, REVOKE,
  CREATE ROLE, DROP ROLE,
  ALTER SYSTEM;

AUDIT POLICY sox_compliance;

GDPR Data Access Policy

CREATE AUDIT POLICY gdpr_data_access
ACTIONS SELECT ON hr.employees,
        SELECT ON hr.personal_data,
        UPDATE ON hr.employees,
        DELETE ON hr.employees;

AUDIT POLICY gdpr_data_access;

Privileged Access Policy

CREATE AUDIT POLICY priv_access
PRIVILEGES
  ALTER SYSTEM,
  CREATE USER,
  DROP USER,
  ALTER DATABASE,
  GRANT ANY PRIVILEGE;

AUDIT POLICY priv_access;

---

Related Resources

• Security Analysis Scripts
• User Management Scripts
• ORA-28000: Account Locked
• Best Practices