Tuesday, August 03, 2010

Audit SQL with audit_trail db,extended

Nothing new on this article, I just want to post about audit_trail=db_extended. Because I want to audit SQL statements and find out it.
db,extended - Enables database auditing and directs all audit records to the database audit trail (the SYS.AUD$ table). In addition, populates the SQLBIND and SQLTEXT CLOB columns of the SYS.AUD$ table.
SQL> alter system set audit_trail=db_extended scope=spfile;

System altered.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 786759680 bytes
Fixed Size 1339400 bytes
Variable Size 633343992 bytes
Database Buffers 146800640 bytes
Redo Buffers 5275648 bytes
Database mounted.
Database opened.

SQL> show parameter audit_trail

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB_EXTENDED

SQL> AUDIT ALL BY surachart BY ACCESS;

Audit succeeded.

SQL> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY surachart BY ACCESS;

Audit succeeded.
Login user and do something:
SQL> connect surachart
<Operations>
.
.
.
Then Check...
SQL> show user;
USER is "SYS"

SQL> select sqltext from aud$ where userid='SURACHART';
select * from tab
delete from sdo_geor_ddl__table$$
select count(*) from test
delete from test where rownum <=1
I saw SQL statements from sqltext column in aud$ table.

No comments: