Oracle Database - Auditing

> Database > Oracle Database

1 - About

To answer this questions :

  • How can we find out who dropped this table
  • How people actually use your application or how they abuse it
  • How to identify the DDL actions performed the last 24 hours ?
  • If a developer overwrite some function, how can I tell who did it ?

To give you useful information over time :

  • Usage patterns
  • What's or not popular ?

3 - How to enable auditing ?

Just choose the level of audit (every page view in the website or an insert into a table) and install :

  • a Basic Auditing using the built-in features of the database
  • or a Custom Auditing using the system event trigger (BEFORE CREATE, BEFORE DROP and so, on)

Take in memory that the built-in auditing will go always faster than the custom one.

Advertising

3.1 - Built-in Audit

After making sure AUDIT_TRAIL was set in the ini.ora parameter file.

CREATE TABLE t1 ( x int ); 
 
audit INSERT ON t1 BY access;

3.2 - Custom Auditing

CREATE TABLE t2 ( x int );
 
CREATE TABLE t2_audit
AS
SELECT SYSDATE dt, a.*
  FROM v$session a
 WHERE 1=0;
 
CREATE INDEX t2_audit_idx ON t2_audit(sid,serial#);
 
CREATE TRIGGER t2_audit
after INSERT ON t2
BEGIN
  INSERT INTO t2_audit
  SELECT SYSDATE, a.*
    FROM v$session a
   WHERE sid = ( SELECT sid FROM V$mystat WHERE ROWNUM = 1 );
END;
/
db/oracle/oracle_db_audit.txt · Last modified: 2017/09/06 19:30 by gerardnico