Skip to content
Oracle DBA Scripts & Database Utilities

Set Default Database Audit (set_default_db_audit.sql)

What This Script Does

Section titled “What This Script Does”

This script configures comprehensive database auditing for compliance requirements (such as SOX), enabling audit trails for DDL operations, privilege usage, and administrative actions. Essential for security monitoring and regulatory compliance in production environments.

The Script

Section titled “The Script”
set echo off
set feedback off
set term off


rem : The following are other relevant audit that applies to everyone in the database


COLUMN name    NOPRINT new_value name


SELECT name
  FROM sys.v_$database;


set term on


prompt
prompt This script will set the default SOX auditing on the database
prompt
accept spool_dir prompt 'Enter path for spool file : '  default ./


spool &spool_dir.&name._set_default_db_audit.log


set echo on
set feedback on
--to be performed by sysdba
--audit select, insert, update, delete on sys.aud$ by access;
--
AUDIT TABLE BY ACCESS;
AUDIT CLUSTER BY ACCESS;
AUDIT CONTEXT BY ACCESS;
AUDIT DATABASE LINK BY ACCESS;
AUDIT DIMENSION BY ACCESS;
AUDIT DIRECTORY BY ACCESS;
AUDIT INDEX BY ACCESS;
AUDIT PROCEDURE BY ACCESS;
AUDIT PROFILE BY ACCESS;
AUDIT PUBLIC DATABASE LINK BY ACCESS;
AUDIT PUBLIC SYNONYM BY ACCESS;
AUDIT ROLE BY ACCESS;
AUDIT ROLLBACK SEGMENT BY ACCESS;
AUDIT SEQUENCE BY ACCESS;
AUDIT SYNONYM BY ACCESS;
AUDIT SYSTEM AUDIT BY ACCESS;
AUDIT SYSTEM GRANT BY ACCESS;
AUDIT TABLESPACE BY ACCESS;
AUDIT TRIGGER BY ACCESS;
AUDIT TYPE BY ACCESS;
AUDIT USER BY ACCESS;
AUDIT VIEW BY ACCESS;
AUDIT ALTER SEQUENCE BY ACCESS;
AUDIT ALTER TABLE BY ACCESS;
AUDIT COMMENT TABLE BY ACCESS;
AUDIT ALTER DATABASE BY ACCESS;
AUDIT ALTER SYSTEM BY ACCESS;
AUDIT GRANT ANY ROLE BY ACCESS;
AUDIT RESTRICTED SESSION BY ACCESS;
AUDIT ADMINISTER DATABASE TRIGGER BY ACCESS;
AUDIT BECOME USER BY ACCESS;
AUDIT AUDIT ANY BY ACCESS;
AUDIT GRANT ANY PRIVILEGE BY ACCESS;
AUDIT SYSDBA BY ACCESS;
AUDIT SYSOPER BY ACCESS;
--
rem audit execute procedure by access;
rem audit select sequence by access;
rem audit create session by access;
rem audit insert table by access;
rem audit delete table by access;
rem audit update table by access;
--
spool off


set feedback off
set echo off


prompt
prompt Log of this session is found in -  &spool_dir.&name._set_default_db_audit.log
prompt


set echo on
set feedback on

Parameters

Section titled “Parameters”

The script prompts for:

  • spool_dir: Directory path for audit log file (defaults to current directory)

Usage

Section titled “Usage”
-- Basic usage (will prompt for spool directory)
@set_default_db_audit.sql


-- Example when prompted:
-- Enter path for spool file: /opt/oracle/admin/audit/

What Gets Audited

Section titled “What Gets Audited”

The script enables auditing for:

  • DDL Operations: CREATE, ALTER, DROP for tables, indexes, procedures, etc.
  • Security Operations: User creation, role grants, privilege assignments
  • Administrative Actions: ALTER DATABASE, ALTER SYSTEM commands
  • Privileged Access: SYSDBA and SYSOPER connections
  • Database Objects: All object types including tables, views, sequences

Sample Output

Section titled “Sample Output”
This script will set the default SOX auditing on the database


Enter path for spool file : /opt/oracle/admin/audit/


Audit succeeded.
Audit succeeded.
Audit succeeded.
...
[Multiple audit statements executed]
...


Log of this session is found in - /opt/oracle/admin/audit/PRODDB_set_default_db_audit.log

Important Notes

Section titled “Important Notes”
  • Privilege Required: Must be run as SYSDBA
  • Audit Trail: Ensure AUDIT_TRAIL parameter is properly configured
  • Storage: Monitor audit trail storage requirements
  • Performance: Auditing may impact performance in high-transaction environments