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.

Security: My Step on audit

$ sqlplus / as sysdba

SQL*Plus: Release 10.1.0.5.0 - Production on จ. พ.ค. 29 16:08:17 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options
$ export ORACLE_SID=stmdb
$ sqlplus / as sysdba

SQL*Plus: Release 10.1.0.5.0 - Production on จ. พ.ค. 29 16:08:32 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> show parameter audit_trail;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string NONE
SQL> alter system set audit_trail=db scope=spfile;

System altered.

SQL> create tablespace tbs_audit datafile '/oradata/stmdb/tbs_audit01.dbf' size 50M autoextend on next 5M;

Tablespace created.


SQL> create tablespace tbs_audit_indx datafile '/oradata/stmdb/tbs_audit_indx_01.dbf' size 10M autoextend on next 5M;

Tablespace created.

SQL> alter table aud$ move tablespace tbs_audit;

Table altered.

SQL> select index_name, tablespace_name from dba_indexes where table_name='AUD$';

INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SYS_IL0000000544C00040$$ SYSTEM
SYS_IL0000000544C00041$$ SYSTEM
I_AUD1 SYSTEM

SQL> select owner ,index_name, tablespace_name from dba_indexes where table_name='AUD$';

OWNER INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SYS SYS_IL0000000544C00040$$ SYSTEM
SYS SYS_IL0000000544C00041$$ SYSTEM
SYS I_AUD1 SYSTEM

SQL> select owner ,index_name, tablespace_name, status from dba_indexes where table_name='AUD$';

OWNER INDEX_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ------------------------------ --------
SYS SYS_IL0000000544C00040$$ SYSTEM VALID
SYS SYS_IL0000000544C00041$$ SYSTEM VALID
SYS I_AUD1 SYSTEM VALID

SQL> alter index I_AUD1 rebuild tablespace tbs_audit_indx;

Index altered.

SQL> create role rl_audit;

Role created.

SQL> grant audit any, audit system to rl_audit;

Grant succeeded.

SQL> create user sysaudit identified by pass default tablespace tbs_audit temporary tablespace temp
2 quota unlimited on tbs_audit quota unlimited on tbs_audit_indx;

User created.

SQL> revoke audit any,audit system from dba;

Revoke succeeded.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 419430400 bytes
Fixed Size 779660 bytes
Variable Size 396630644 bytes
Database Buffers 20971520 bytes
Redo Buffers 1048576 bytes
Database mounted.
Database opened.
SQL> show parameter audit_trail

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB

SQL> connect / as sysdba
Connected.
SQL> grant create session, rl_audit to sysaudit;

Grant succeeded.

SQL> grant all on sys.aud$ to sysaudit;

Grant succeeded.

SQL> connect sysaudit/pass
Connected.
SQL> select * from sys.aud$;

no rows selected

SQL> audit create session ;

Audit succeeded.