Why are statements executed by SYS not found in the DB audit trail?

Even if Oracles default auditing is set up, it surprised me that audit information did not appear in the audit trail, for statements that should be covered by default auditing.

Below are my findings.


First, let's check which audit privileges that are set already:

-- DBA_PRIV_AUDIT_OPTS: describes current system privileges being audited across the system and by user.
SELECT * 
FROM DBA_PRIV_AUDIT_OPTS;

USER_NAME	PROXY_NAME	PRIVILEGE	SUCCESS	FAILURE
		GRANT ANY PRIVILEGE	BY ACCESS	BY ACCESS
		ALTER DATABASE	BY ACCESS	BY ACCESS
		ALTER USER	BY ACCESS	BY ACCESS
		CREATE USER	BY ACCESS	BY ACCESS
		CREATE SESSION	BY ACCESS	BY ACCESS
		ALTER SYSTEM	BY ACCESS	BY ACCESS

Let's audit CREATE USER by user SYSTEM:

SQL> connect / as sysdba
SQL> AUDIT CREATE USER BY SYSTEM;

Check again that the audit trail that is set

SELECT * 
FROM DBA_PRIV_AUDIT_OPTS;

USER_NAME	PROXY_NAME	PRIVILEGE	SUCCESS	FAILURE
		GRANT ANY PRIVILEGE	BY ACCESS	BY ACCESS
		ALTER DATABASE	BY ACCESS	BY ACCESS
		ALTER USER	BY ACCESS	BY ACCESS
SYSTEM		CREATE USER	BY ACCESS	BY ACCESS
		CREATE SESSION	BY ACCESS	BY ACCESS
		ALTER SYSTEM	BY ACCESS	BY ACCESS

Log in as user SYS, and create a user:

SQL> connect sys/****@proddb01 as sysdba
SQL> connected.
SQL> CREATE USER VKAASA IDENTIFIED BY password 
SQL> DEFAULT TABLESPACE USERS
SQL> TEMPORARY TABLESPACE TEMP;

User created.

Log in as user SYSTEM, and create a user:

SQL> connect system/****
SQL> CREATE USER SCOTT IDENTIFIED BY password 
SQL> DEFAULT TABLESPACE USERS
SQL> TEMPORARY TABLESPACE TEMP;

User created.

To find the audit entry, check the DBA_COMMON_AUDIT_TRAIL. This view displays all standard and fine-grained audit trail entries, mandatory and SYS audit records written in XML format.

SELECT AUDIT_TYPE,EXTENDED_TIMESTAMP, DB_USER, OS_USER,USERHOST,OBJECT_NAME,STATEMENT_TYPE,SCN,SQL_TEXT,PRIV_USED
FROM DBA_COMMON_AUDIT_TRAIL 
WHERE PRIV_USED = 'CREATE USER'
AND TO_CHAR(extended_timestamp AT TIME ZONE DBTIMEZONE, 'YYYY-MM-DD HH24:MI:SS TZD') > TO_CHAR((SYSDATE-1),'YYYY-MM-DD HH24:MI:SS');

AUDIT_TYPE	EXTENDED_TIMESTAMP	DB_USER	OS_USER	USERHOST	OBJECT_NAME	STATEMENT_TYPE	SCN	SQL_TEXT	PRIV_USED
Standard Audit	17.12.2014 13:05:06,515954 +01:00	SYSTEM	oracle	myserver	scott	CREATE USER	153820329960	create user scott identified by * default tablespace users temporary tablespace temp	CREATE USER

The same audit entry can be found in DBA_AUDIT_TRAIL, which displays all standard audit trail entries.

SELECT OS_USERNAME,USERNAME,USERHOST,TIMESTAMP,OBJ_NAME,ACTION_NAME,RETURNCODE,PRIV_USED,SCN,SQL_TEXT
FROM DBA_AUDIT_TRAIL
WHERE PRIV_USED = 'CREATE USER'
AND TO_CHAR(extended_timestamp AT TIME ZONE DBTIMEZONE, 'YYYY-MM-DD HH24:MI:SS TZD') > TO_CHAR((SYSDATE-1),'YYYY-MM-DD HH24:MI:SS');

OS_USERNAME	USERNAME	USERHOST	TIMESTAMP	OBJ_NAME	ACTION_NAME	RETURNCODE	PRIV_USED	SCN	SQL_TEXT
oracle	SYSTEM	myserver	17.12.2014 13:05:06	scott	CREATE USER	0	CREATE USER	153820329960	create user scott identified by * default tablespace users temporary tablespace temp

So what happened to the other user, vkaasa, created by SYS? Shouldn't that leave an entry in the audit trail as well?

- You cannot audit actions by sys the same way. Trying to enable audit for user SYS will throw ORA-00983:

SQL> audit create user by sys;
audit create user by sys
*
ERROR at line 1:
ORA-00983: cannot audit or noaudit SYS user actions

Any user creation by sys, which has the sysdba privilege will not be audited. Donald Burleson at Burleson Consulting puts it this way:

"When a user with SYSDBA privileges connects to the database, the action is expected to be for administrative reasons only, such as shutdown, startup, add a service to the listener, etc. Generally, we would not expect these actions to be audited and therefore not picked up by the auditing mechanism.

However, the user who is connected as SYSDBA is a regular user, just like any other user, but with all powerful do anything privileges. This user could potentially alter the data in a table in any schema. Since the action is not audited, the trace of this data manipulation is hidden. In case of an investigation later, the audit trails will have no record of such manipulations ? a huge security hole"

However, any CREATE USER action executed by sys is recorded in the audit trail on disk:

SQL> show parameter audit_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/oracle/admin/proddb01/adump

cd /u01/oracle/admin/proddb01/adump
ls -latr 

-rw-r-----    1 oracle   dba            2754 Dec 17 13:03 proddb01_ora_56099020_20141217130109908038143795.aud
-rw-r-----    1 oracle   dba            1429 Dec 17 13:24 proddb01_ora_56426540_20141217132347629420143795.aud
-rw-r-----    1 oracle   dba           66871 Dec 17 13:27 proddb01_ora_58458244_20141217075116217921143795.aud
drwxrwxr-x    2 oracle   dba           73728 Dec 17 13:33 .
-rw-r-----    1 oracle   dba            2612 Dec 17 13:33 proddb01_ora_57475318_20141217133329412931143795.aud

Open the file proddb01_ora_56426540_20141217132347629420143795.aud:

Wed Dec 17 13:24:37 2014 +01:00
LENGTH : '243'
ACTION :[85] 'create user vkaasa identified by * default tablespace users temporary tablespace temp'
DATABASE USER:[3] 'SYS'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'myuser'
CLIENT TERMINAL:[7] 'myterminal'
STATUS:[1] '0'
DBID:[10] '3707088731'