Enable audit in oracle

Check audit settting

select name,value from v$parameter where name like 'audit%'
show parameter audit;

Change setting of audit_trail

Method 1

1
2
alter system set audit_trail=DB,Extended scope=spfile (cannot set both as Change Static Parameters Through the SPFILE)
alter system set audit_sys_operations=true scope=spfile

Method 2

  • add config in $ORACLE_HOME/dbs/init**.ora as below since db starting with pfile, error **’specified initialization parameter can not be modified’*
  • audit_trail=DB ( oracle9i not support ‘Extended’ in init*.ora file)
  • audit_sys_operations=true

Restart db

1
2
shutdown immediate;
startup;

Check audit option in db

select * from dba_stmt_audit_opts OR select * from dba_priv_audit_opts

Make some audit option

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
AUDIT ALTER TABLE;
AUDIT DELETE ANY TABLE;
AUDIT DROP ANY TABLE;
AUDIT AUDIT ANY;
AUDIT AUDIT SYSTEM;

AUDIT ALL BY FRED BY ACCESS;
audit select table, update table, delete table,
      insert table, execute procedure by FRED by access;

AUDIT SELECT, INSERT, DELETE
     ON jward.dept
     BY ACCESS
     WHENEVER SUCCESSFUL;

AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE
      BY ACCESS
      WHENEVER NOT SUCCESSFUL;

AUDIT SELECT, UPDATE, DELETE ON SYS.AUD$ BY ACCESS;
-------- disable them
NOAUDIT ALTER TABLE;
...

List audit record

1
2
3
4
select * from dba_audit_session order by timestamp desc
select * from dba_audit_trail order by timestamp desc
select * from dba_audit_object order by timestamp desc;
select * from dba_audit_statement order by timestamp desc;

House keep sys.aud$ to reduce its size

truncate sys.aud$ or delete sys.aud$

Trace user’s behavior

show parameter dump

  • Find background_dump_dest/alert_sid.log
  • This file would give you some info about user’s behavior in user_dump_dest/sid_ora_pid.trc

Example

A user Fred was missing someday in db, how to find out the reason. Follow these step

  • check alert.log
  • check sid_ora_pid.trc
  • check dba_audit_session and dba_audit_trail

Get the user’s role and privilege

  • USER_SYS_PRIVS, USER_TAB_PRIVS, USER_ROLE_PRIVS
  • DBA_SYS_PRIVS, DBA_TAB_PRIVS, DBA_ROLE_PRIVS

select * from DBA_role_privs

Misc

xming + putty + X11 forwarding
run $ORACLE_HOME/bin/dbca

Reference

Oracle audit command tips
Introduction to Simple Oracle Auditing
Configuring and Administering Auditing
Auditing Database Activity
Auditing Database Use
Verifying Security Access with Auditing
Starting Up a Database
Spfile and Init.ora Parameter File Startup of an Oracle9i Instance
Change Static Parameters Through the SPFILE
Oracle_9i_AUDIT_SOP
Oracle Audit 审计
Managing User Privileges and Roles