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'
