Audit and Compliance Scripts

Audit and Compliance Scripts

Comprehensive scripts for implementing database auditing, monitoring user activities, and ensuring compliance with security policies.

Unified Audit Setup

Enable Unified Auditing

-- Check current audit status
SELECT parameter, value
FROM v$option
WHERE parameter = 'Unified Auditing';

-- Create unified audit policy for sensitive operations
CREATE AUDIT POLICY sensitive_operations_policy
    PRIVILEGES CREATE TABLE, DROP TABLE, ALTER TABLE,
               CREATE USER, DROP USER, ALTER USER,
               CREATE ROLE, DROP ROLE,
               GRANT ANY PRIVILEGE, GRANT ANY ROLE
    ACTIONS DELETE ON hr.employees,
            UPDATE ON hr.employees,
            INSERT ON hr.employees,
            SELECT ON hr.salary_details
    ROLES DBA, HR_ADMIN;

-- Enable the audit policy
AUDIT POLICY sensitive_operations_policy;

-- Create policy for failed login attempts
CREATE AUDIT POLICY failed_login_policy
    ACTIONS LOGON
    WHEN 'SYS_CONTEXT(''USERENV'', ''AUTHENTICATION_TYPE'') != ''DATABASE'''
    EVALUATE PER SESSION;

AUDIT POLICY failed_login_policy WHENEVER NOT SUCCESSFUL;

User Activity Audit Policy

-- Create comprehensive user activity audit
CREATE AUDIT POLICY user_activity_policy
    ACTIONS SELECT, INSERT, UPDATE, DELETE, EXECUTE
    WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') NOT IN (''SYS'', ''SYSTEM'')'
    EVALUATE PER STATEMENT;

-- Apply policy to specific users
AUDIT POLICY user_activity_policy BY users_to_audit;

-- Apply policy to specific schemas
AUDIT POLICY user_activity_policy ON hr.employees, hr.departments;

Traditional Auditing (Pre-12c)

Standard Audit Configuration

-- Enable standard auditing
ALTER SYSTEM SET audit_trail = DB_EXTENDED SCOPE = SPFILE;
-- Restart database after setting

-- Audit user logon/logoff
AUDIT SESSION;

-- Audit DDL operations
AUDIT CREATE TABLE, ALTER TABLE, DROP TABLE BY ACCESS;
AUDIT CREATE USER, ALTER USER, DROP USER BY ACCESS;

-- Audit DML on sensitive tables
AUDIT SELECT, INSERT, UPDATE, DELETE ON hr.employees BY ACCESS;
AUDIT SELECT ON hr.salary_details BY ACCESS WHENEVER SUCCESSFUL;

-- Audit privilege usage
AUDIT CREATE ANY TABLE BY ACCESS;
AUDIT SELECT ANY TABLE BY ACCESS;
AUDIT GRANT ANY PRIVILEGE BY ACCESS;

Audit Trail Analysis

Unified Audit Trail Queries

-- Recent audit events summary
SELECT
    event_timestamp,
    dbusername,
    action_name,
    object_schema,
    object_name,
    sql_text,
    return_code,
    client_program_name,
    os_username,
    userhost
FROM unified_audit_trail
WHERE event_timestamp > SYSTIMESTAMP - INTERVAL '24' HOUR
ORDER BY event_timestamp DESC
FETCH FIRST 100 ROWS ONLY;

-- Failed login attempts
SELECT
    event_timestamp,
    dbusername,
    os_username,
    userhost,
    authentication_type,
    action_name,
    return_code,
    COUNT(*) OVER (
        PARTITION BY dbusername, userhost
        ORDER BY event_timestamp
        RANGE BETWEEN INTERVAL '1' HOUR PRECEDING AND CURRENT ROW
    ) as attempts_per_hour
FROM unified_audit_trail
WHERE action_name = 'LOGON'
    AND return_code != 0
    AND event_timestamp > SYSTIMESTAMP - INTERVAL '7' DAY
ORDER BY event_timestamp DESC;

-- Privilege usage analysis
SELECT
    dbusername,
    used_privilege,
    object_schema,
    object_name,
    COUNT(*) as usage_count,
    COUNT(DISTINCT TRUNC(event_timestamp)) as days_used,
    MIN(event_timestamp) as first_used,
    MAX(event_timestamp) as last_used
FROM unified_audit_trail
WHERE used_privilege IS NOT NULL
    AND event_timestamp > SYSTIMESTAMP - INTERVAL '30' DAY
GROUP BY dbusername, used_privilege, object_schema, object_name
ORDER BY usage_count DESC;

DDL Change Tracking

-- Track schema changes
SELECT
    event_timestamp,
    dbusername,
    action_name,
    object_schema,
    object_name,
    object_type,
    sql_text,
    client_program_name
FROM unified_audit_trail
WHERE action_name IN (
    'CREATE TABLE', 'ALTER TABLE', 'DROP TABLE',
    'CREATE INDEX', 'DROP INDEX',
    'CREATE VIEW', 'DROP VIEW',
    'CREATE PROCEDURE', 'CREATE FUNCTION', 'CREATE PACKAGE'
)
    AND event_timestamp > SYSTIMESTAMP - INTERVAL '7' DAY
ORDER BY event_timestamp DESC;

-- Track permission changes
SELECT
    event_timestamp,
    dbusername,
    action_name,
    object_schema,
    object_name,
    target_user,
    sql_text
FROM unified_audit_trail
WHERE action_name IN ('GRANT', 'REVOKE')
    AND event_timestamp > SYSTIMESTAMP - INTERVAL '30' DAY
ORDER BY event_timestamp DESC;

Compliance Reporting

User Access Report

-- Comprehensive user access report
WITH user_privs AS (
    SELECT
        grantee,
        privilege,
        admin_option,
        'SYSTEM' as priv_type
    FROM dba_sys_privs
    WHERE grantee NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'XDB')

    UNION ALL

    SELECT
        grantee,
        owner || '.' || table_name || ' (' || privilege || ')' as privilege,
        grantable as admin_option,
        'OBJECT' as priv_type
    FROM dba_tab_privs
    WHERE grantee NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'XDB')

    UNION ALL

    SELECT
        grantee,
        granted_role as privilege,
        admin_option,
        'ROLE' as priv_type
    FROM dba_role_privs
    WHERE grantee NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'XDB')
)
SELECT
    u.username,
    u.account_status,
    u.created,
    u.last_login,
    u.profile,
    up.priv_type,
    COUNT(*) as privilege_count,
    LISTAGG(up.privilege, ', ') WITHIN GROUP (ORDER BY up.privilege) as privileges
FROM dba_users u
LEFT JOIN user_privs up ON u.username = up.grantee
WHERE u.username NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'XDB')
GROUP BY u.username, u.account_status, u.created, u.last_login, u.profile, up.priv_type
ORDER BY u.username, up.priv_type;

Data Access Audit Summary

-- Summary of data access by user and table
SELECT
    dbusername,
    object_schema,
    object_name,
    action_name,
    COUNT(*) as access_count,
    COUNT(DISTINCT TRUNC(event_timestamp)) as days_accessed,
    MIN(event_timestamp) as first_access,
    MAX(event_timestamp) as last_access,
    COUNT(DISTINCT sql_text) as unique_queries
FROM unified_audit_trail
WHERE action_name IN ('SELECT', 'INSERT', 'UPDATE', 'DELETE')
    AND object_schema NOT IN ('SYS', 'SYSTEM')
    AND event_timestamp > SYSTIMESTAMP - INTERVAL '30' DAY
GROUP BY dbusername, object_schema, object_name, action_name
ORDER BY access_count DESC;

Security Compliance Checks

Password Policy Compliance

-- Check password policy settings
SELECT
    profile,
    resource_name,
    limit
FROM dba_profiles
WHERE resource_type = 'PASSWORD'
ORDER BY profile, resource_name;

-- Users with non-expiring passwords
SELECT
    username,
    account_status,
    expiry_date,
    profile,
    created,
    password_change_date
FROM dba_users
WHERE account_status = 'OPEN'
    AND (expiry_date IS NULL OR expiry_date > SYSDATE + 365)
    AND username NOT IN ('SYS', 'SYSTEM')
ORDER BY username;

-- Check for default passwords
SELECT
    username,
    account_status
FROM dba_users_with_defpwd
WHERE username NOT IN (
    SELECT username
    FROM dba_users
    WHERE account_status != 'OPEN'
);

Privilege Analysis

-- Users with excessive privileges
SELECT
    grantee,
    COUNT(*) as dangerous_privs
FROM dba_sys_privs
WHERE privilege IN (
    'ALTER SYSTEM', 'ALTER DATABASE',
    'CREATE ANY TABLE', 'DROP ANY TABLE',
    'SELECT ANY TABLE', 'UPDATE ANY TABLE',
    'DELETE ANY TABLE', 'GRANT ANY PRIVILEGE',
    'GRANT ANY ROLE', 'CREATE USER', 'DROP USER'
)
    AND grantee NOT IN ('SYS', 'SYSTEM', 'DBA')
GROUP BY grantee
ORDER BY dangerous_privs DESC;

-- Analyze role hierarchies
WITH role_tree AS (
    SELECT
        granted_role,
        grantee,
        admin_option,
        LEVEL as depth,
        SYS_CONNECT_BY_PATH(granted_role, ' -> ') as role_path
    FROM dba_role_privs
    START WITH granted_role IN ('DBA', 'RESOURCE', 'CONNECT')
    CONNECT BY PRIOR grantee = granted_role
)
SELECT * FROM role_tree
WHERE grantee NOT IN ('SYS', 'SYSTEM')
ORDER BY depth, granted_role, grantee;

Audit Management

Audit Trail Maintenance

-- Create procedure for audit trail archival
CREATE OR REPLACE PROCEDURE archive_audit_trail(
    p_days_to_keep IN NUMBER DEFAULT 90
) IS
    v_archived_count NUMBER;
    v_archive_date DATE;
BEGIN
    v_archive_date := SYSDATE - p_days_to_keep;

    -- Archive to history table
    INSERT INTO audit_trail_archive
    SELECT * FROM unified_audit_trail
    WHERE event_timestamp < v_archive_date;

    v_archived_count := SQL%ROWCOUNT;

    -- Delete archived records
    DELETE FROM unified_audit_trail
    WHERE event_timestamp < v_archive_date;

    COMMIT;

    DBMS_OUTPUT.PUT_LINE('Archived ' || v_archived_count || ' audit records');

    -- Update statistics
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname => 'AUDSYS',
        tabname => 'AUD$UNIFIED'
    );

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

-- Schedule audit trail maintenance
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name => 'AUDIT_TRAIL_MAINTENANCE',
        job_type => 'PLSQL_BLOCK',
        job_action => 'BEGIN archive_audit_trail(90); END;',
        start_date => SYSTIMESTAMP,
        repeat_interval => 'FREQ=WEEKLY; BYDAY=SUN; BYHOUR=2',
        enabled => TRUE,
        comments => 'Weekly audit trail archival and cleanup'
    );
END;
/

Audit Performance Impact Analysis

-- Monitor audit overhead
SELECT
    name,
    value
FROM v$sysstat
WHERE name IN (
    'audit records generated',
    'audit file writes',
    'audit file size',
    'audit queue flush waits'
)
ORDER BY name;

-- Check audit trail size
SELECT
    segment_name,
    segment_type,
    ROUND(bytes/1024/1024, 2) as size_mb,
    tablespace_name
FROM dba_segments
WHERE owner = 'AUDSYS'
ORDER BY bytes DESC;

Custom Compliance Reports

SOX Compliance Report

CREATE OR REPLACE PROCEDURE generate_sox_compliance_report(
    p_start_date IN DATE,
    p_end_date IN DATE
) IS
    v_report CLOB;
    v_section VARCHAR2(32000);
BEGIN
    v_report := 'SOX Compliance Report' || CHR(10) ||
                '=====================' || CHR(10) ||
                'Period: ' || p_start_date || ' to ' || p_end_date || CHR(10) || CHR(10);

    -- Section 1: User Access Changes
    v_section := 'User Access Changes:' || CHR(10);
    FOR rec IN (
        SELECT
            event_timestamp,
            dbusername,
            action_name,
            target_user,
            sql_text
        FROM unified_audit_trail
        WHERE action_name IN ('CREATE USER', 'DROP USER', 'ALTER USER', 'GRANT', 'REVOKE')
            AND event_timestamp BETWEEN p_start_date AND p_end_date
        ORDER BY event_timestamp
    ) LOOP
        v_section := v_section ||
            TO_CHAR(rec.event_timestamp, 'DD-MON-YY HH24:MI') || ' - ' ||
            rec.dbusername || ' - ' || rec.action_name || ' - ' ||
            NVL(rec.target_user, 'N/A') || CHR(10);
    END LOOP;

    v_report := v_report || v_section || CHR(10);

    -- Section 2: Data Modifications on Financial Tables
    v_section := 'Financial Data Modifications:' || CHR(10);
    FOR rec IN (
        SELECT
            event_timestamp,
            dbusername,
            action_name,
            object_schema,
            object_name,
            return_code
        FROM unified_audit_trail
        WHERE action_name IN ('INSERT', 'UPDATE', 'DELETE')
            AND object_schema = 'FINANCE'
            AND event_timestamp BETWEEN p_start_date AND p_end_date
        ORDER BY event_timestamp
    ) LOOP
        v_section := v_section ||
            TO_CHAR(rec.event_timestamp, 'DD-MON-YY HH24:MI') || ' - ' ||
            rec.dbusername || ' - ' || rec.action_name || ' on ' ||
            rec.object_schema || '.' || rec.object_name || CHR(10);
    END LOOP;

    v_report := v_report || v_section;

    -- Output or store report
    DBMS_OUTPUT.PUT_LINE(v_report);

    -- Store in compliance_reports table
    INSERT INTO compliance_reports (
        report_type,
        report_date,
        report_content,
        generated_by
    ) VALUES (
        'SOX_COMPLIANCE',
        SYSDATE,
        v_report,
        USER
    );

    COMMIT;
END;
/

Real-time Security Monitoring

-- Create view for security dashboard
CREATE OR REPLACE VIEW v_security_dashboard AS
SELECT
    'Failed Logins (24h)' as metric,
    COUNT(*) as value
FROM unified_audit_trail
WHERE action_name = 'LOGON'
    AND return_code != 0
    AND event_timestamp > SYSTIMESTAMP - INTERVAL '24' HOUR

UNION ALL

SELECT
    'Privilege Grants (7d)' as metric,
    COUNT(*) as value
FROM unified_audit_trail
WHERE action_name IN ('GRANT', 'REVOKE')
    AND event_timestamp > SYSTIMESTAMP - INTERVAL '7' DAY

UNION ALL

SELECT
    'Schema Changes (7d)' as metric,
    COUNT(*) as value
FROM unified_audit_trail
WHERE action_name LIKE '%TABLE%'
    OR action_name LIKE '%INDEX%'
    AND event_timestamp > SYSTIMESTAMP - INTERVAL '7' DAY

UNION ALL

SELECT
    'Sensitive Data Access (24h)' as metric,
    COUNT(*) as value
FROM unified_audit_trail
WHERE object_schema IN ('HR', 'FINANCE', 'PAYROLL')
    AND action_name = 'SELECT'
    AND event_timestamp > SYSTIMESTAMP - INTERVAL '24' HOUR;

Best Practices

1. Policy Design: Create focused audit policies for specific compliance needs
2. Performance: Monitor audit overhead and adjust policies accordingly
3. Retention: Implement appropriate retention periods for audit data
4. Analysis: Regularly review audit trails for anomalies
5. Archival: Archive old audit data to maintain performance
6. Security: Protect audit trails from unauthorized modification
7. Automation: Automate compliance reporting and anomaly detection