Oracle Database Audit and Privilege Management

Revoking Public Privileges

To identify and revoke public privileges, use the following query:

SQL> SELECT table_name FROM dba_tab_privs WHERE owner='SYS' AND privilege = 'EXECUTE' AND grantee='PUBLIC' AND table_name LIKE 'UTL%';

Users with DBA Role

SQL> SELECT grantee FROM dba_role_privs WHERE granted_role = 'DBA';

Users with SYSDBA and SYSOPER Privileges

SQL> SELECT * FROM V$PWFILE_USERS;

Audit Parameters and Session Auditing

Check the current audit parameters:

SHOW PARAMETER AUDIT_FILE_DEST || AUDIT_SYS_OPERATIONS || AUDIT_TRAIL;

Common Audit Commands:

• SQL> AUDIT session;
• SQL> AUDIT session by user;
• SQL> AUDIT TABLE BY hr WHENEVER NOT SUCCESSFUL;
• SQL> AUDIT DROP ANY TABLE BY hr BY SESSION;
• SQL> AUDIT UPDATE ON hr.employees BY ACCESS;
• SQL> AUDIT network;

Failed Connection Attempts

Query to identify failed login attempts in the last 7 days:

SELECT * FROM DBA_AUDIT_SESSION WHERE ACTION_NAME ='LOGON' AND NVL(RETURNCODE,0) != 0 AND TIMESTAMP BETWEEN (SYSTIMESTAMP - 7) AND SYSTIMESTAMP ORDER BY timestamp DESC;

View All Audit Views

SELECT view_name FROM dba_views WHERE view_name LIKE '%AUDIT%' ORDER BY view_name;

Value-Based Auditing

Example of a trigger for value-based auditing:

CREATE OR REPLACE TRIGGER mi_disparador 
AFTER UPDATE OF salary ON hr.employees 
REFERENCING NEW AS NEW OLD AS OLD 
FOR EACH ROW 
BEGIN 
  IF :old.salary != :new.salary THEN 
    INSERT INTO audit_employees 
    VALUES (sys_context('userenv','os_user'), sysdate, sys_context('userenv','ip_address'), :new.employee_id, 'salary', :old.salary, :new.salary); 
  END IF; 
END;

Fine-Grained Auditing (FGA) Policy

dbms_fga.add_policy (
  object_schema=>'hr', 
  object_name=>'employees', 
  policy_name=>'auditoria_sueldos_empleados', 
  audit_condition=>'salary>30000', 
  audit_column=>'salary', 
  handler_schema=>'USER_MAIL', 
  handler_module=>'PR_ENVIA_MAIL', 
  enable=>true, 
  statement_types=>'select', 
  audit_trail=> DBMS_FGA.DB, 
  audit_column_opts=> DBMS_FGA.ANY_COLUMNS
);

Tablespace Management for Auditing

View Tablespace

SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;

Move Tablespace Location

BEGIN 
  SYS.DBMS_AUDIT_MGMT.set_audit_trail_location( 
    audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 
    audit_trail_location_value => 'TS_AUDIT'
  ); 
END;

Create New Tablespace

CREATE TABLESPACE TS_AUDIT DATAFILE '/home/oracle/app/oracle/oradata/eps1/audit01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M;

Audit Trail Maintenance

Initialization and Cleanup

BEGIN 
  SYS.DBMS_AUDIT_MGMT.init_cleanup( 
    audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, 
    default_cleanup_interval => 24 /* hours */
  ); 
END;

BEGIN 
  SYS.DBMS_AUDIT_MGMT.set_last_archive_timestamp( 
    audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, 
    last_archive_time => SYSTIMESTAMP-5
  ); 
END;

Maximum Archive Age

SELECT * FROM dba_audit_mgmt_last_arch_ts;

Manual Cleanup

BEGIN 
  SYS.DBMS_AUDIT_MGMT.clean_audit_trail( 
    audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, 
    use_last_arch_timestamp => TRUE
  ); 
END;

Scheduled Cleanup Job

BEGIN 
  SYS.DBMS_AUDIT_MGMT.create_purge_job( 
    audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, 
    audit_trail_purge_interval => 24, 
    audit_trail_purge_name => 'JOB_AUDITORIA', 
    use_last_arch_timestamp => TRUE
  ); 
END;