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.