Auditing SYS user in 10g

There are two parameters that one need to set to audit SYS users in 10g.

The AUDIT_SYS_OPERATIONS static parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. All audit records are written to the OS audit trail.

The AUDIT_FILE_DEST parameter specifies the OS directory used for the audit trail when the os, xml and xml,extended options are used. It is also the location for all mandatory auditing specified by the AUDIT_SYS_OPERATIONS parameter.

AUDIT_TRAIL: You can view audit information in TEXT format or in XML format depending on the parameter audit trail setting.

SQL> show parameter audit
NAME TYPE VALUE
----------------------- --------- -------------------------------------------

audit_file_dest string D:\ORACLE\PRODUCT\10.2.0\DB_1
\RDBMS\AUDIT
audit_sys_operations boolean FALSE
audit_trail string NONE

SQL>
we set audit_trail parameter to value "xml, extended"
http://www.oracle-base.com/articles/10g/Auditing_10gR2.php
SQL> alter system set audit_trail=xml,extended scope=spfile;
System altered.
SQL> show parameter audit

SQL> show parameter audit

NAME TYPE VALUE

----------------------- --------- -------------------------------------------
audit_file_dest string D:\ORACLE\PRODUCT\10.2.0\DB_1 \RDBMS\AUDIT audit_sys_operations boolean FALSE

audit_trail string NONE


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

SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 79693180 bytes
Database Buffers 79691776 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.


SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string D:\ORACLE\PRODUCT\10.2.0\DB_1
\RDBMS\AUDIT
audit_sys_operations boolean FALSE
audit_trail string XML, EXTENDED

SQL> alter system set audit_file_dest='D:\oracle\product\10.2.0\admin\rock\audit' scope=spfile;
System altered.


SQL> alter system set audit_sys_operations=true scope=spfile;
System altered.
SQL>

shutdown & restart the database.

SQL> show parameter audit
NAME TYPE VALUE
------------------------ ------------ ----------- ------------------------------
audit_file_dest string D:\ORACLE\PRODUCT\10.2.0ADMIN
\ROCK\AUDIT
audit_sys_operations boolean TRUE
audit_trail string XML, EXTENDED

SQL> create user xyz222
2 identified by xyz222;
User created.
SQL>

NOw we will check whether these commands are audited or not, I will run below query.

set linesize 3333
column db_user format a10
column os_user format a24
column os_host format a20
column extended_timestamp format a20
column sql_text format a300


select db_user, os_user, os_host, TO_CHAR(EXTENDED_TIMESTAMP,'fmDd-MM-YYYY HH:MI:SS AM'), sql_text
from v$xml_audit_trail
where db_user in ('sys', 'SYS', '/')
order by 4
/


DB_USER OS_USER OS_HOST TO_CHAR(EXTENDED_TIMES SQL_TEXT
---------- ------------------------ -------------------- ----------------------
/ ABAMCO\rakesh.kumar ABAMCO\ABM-D0113 26-12-2008 3:47:23 PM select db_user, os_uSER, os_host, TO_CHAR(EXTENDED_TIMESTAMP,'fmDd-MM-YYYY HH:MI:SS AM'), sql_textfrom v$xml_audit_trailwhere db_user in ('sys', 'SYS', '/')order by 4

/ ABAMCO\rakesh.kumar ABAMCO\ABM-D0113 26-12-2008 3:48:38 PM create user xyz222identified by *

/ ABAMCO\rakesh.kumar ABAMCO\ABM-D0113 26-12-2008 3:48:56 PM select db_user, os_uSER, os_host, TO_CHAR(EXTENDED_TIMESTAMP,'fmDd-MM-YYYY HH:MI:SS AM'), sql_textfrom v$xml_audit_trailwhere db_user in ('sys', 'SYS', '/')order by 4

Auditing other users than sys.
http://www.oracle-base.com/articles/10g/Auditing_10gR2.php