Wednesday, March 4, 2009

Auditing transaction statements

# create the user
connect / as sysdba
create user robert identified by robert default tablespace users temporary tablespace temp;
grant create session to robert;
grant create table to robert;
alter user robert quota unlimited on users;

# create the table
connect robert/robert
create table test (text char(1));

# audit transactions on the TEST table
connect / as sysdba
AUDIT UPDATE TABLE, INSERT TABLE, DELETE TABLE BY robert BY ACCESS;

# perform an insert
connect robert/robert
insert into test values ('1');

# check the audit trail
connect / as sysdba
SELECT DBMS_LOB.SUBSTR(sqltext, 1000, 1)||'^'||DBMS_LOB.SUBSTR(sqlbind, 1000, 1) FROM aud$;