What is mandatory auditing?

Oracle Database always audits certain database-related operations and writes them to the operating system audit files.

It includes the actions of any user who is logged in with the SYSDBA or SYSOPER privilege. This is called mandatory auditing.

Even if you have enabled the database audit trail (that is, setting the AUDIT_TRAIL parameter to DB), Oracle Database still writes mandatory records to operating system files.

Mandatory auditing includes the following operations:

* Database startup
* SYSDBA and SYSOPER logins
* Database shutdown

Source: Oracle Documentation

How to audit ALTER TABLE statements on a specific table

Enable auditing on specific table:

AUDIT ALTER ON SCOTT.T1;

Verify that the object is being audited:

SELECT OBJECT_NAME,OBJECT_TYPE,ALT
FROM  DBA_OBJ_AUDIT_OPTS
WHERE  OWNER ='SCOTT'
ORDER BY 1 ASC; 

Result:

|------------------------------------|
|object_name  | object_type  | alt   |
|-------------|--------------|-------|
|T1           | TABLE        | S/S   |
|------------------------------------|

Object is indeed being audited.

Make a change to the table:

ALTER TABLE SCOTT.T1
 ADD (age  number(2));

Check that the change is captured in the audit-trail:

SELECT TIMESTAMP, OS_USERNAME, OBJ_NAME,USERHOST, SESSIONID, USERNAME,ACTION_NAME, RETURNCODE
FROM   DBA_AUDIT_TRAIL
WHERE  OBJ_NAME = 'T1'
AND    OWNER = 'SCOTT'
ORDER  BY TIMESTAMP DESC;

|-----------------------------------------------------------------------------------------------------------|
|TIMESTAMP     | OS_USERNAME | OBJ_NAME  | USERHOST| SESSIONID | USERNAME | ACTION_NAME  | RETURNCODE |
|-----------------------------------------------------------------------------------------------------------|
|03.11.2013 08:32:28 | SCOTT | T1    | scottpc  | 172154    | SCOTT   | ALTER TABLE  | 0         |
|03.11.2013 08:32:18 | SCOTT | T1    | scottpc  | 172154 | SCOTT   | ALTER TABLE  | 1430      |
|03.11.2013 08:32:18 | SCOTT | T1    | scottpc  | 172154 | SCOTT   | ALTER TABLE  | 911       |
|01.11.2013 16:14:17 | SCOTT | T1    | scottpc  | 234766 | SCOTT   | ALTER TABLE  | 0         |
|-----------------------------------------------------------------------------------------------------------|

The return codes can be checked by using the oerr utility that comes with the oracle installed binaries, for example:

oerr ora 1430
01430, 00000, "column being added already exists in table"
// *Cause:
// *Action:

Note that other DDL statements, too, are captured in the audit trail for the table, such as DROP and TRUNCATE.
If you drop the table without purging the recyclebin, the auditing will still be in place, and if you query the DBA_OBJ_AUDIT_OPTS after a DROP without purge, it will show you the recyclebase name in the view.

How to audit CREATE SESSION

Audit all session connecting to the database:

audit create session [ whenever successful | not successful ];

To audit sessions created by a specific user:

audit create session by SCOTT [ whenever successful | not successful ];

If you omit the condition "whenever successful/not successful", Oracle will audit all connections, regardless of their success or failure.


To disable auditing:

noaudit create session;
noaudit create session by scott;

To verify that the auditing options has indeed been set:

SELECT * 
 FROM DBA_PRIV_AUDIT_OPTS 
 WHERE USER_NAME = 'SCOTT';

Result:

USER_NAME	PROXY_NAME	PRIVILEGE	SUCCESS	FAILURE
SCOTT		CREATE SESSION	BY ACCESS	BY ACCESS

The view DBA_PRIV_AUDIT_OPTS gives you "current system privileges being audited across the system and by user".
Note that a NULL value in the user_name column of DBA_PRIV_AUDIT_OPTS means system-wide auditing.

To find your audit info, use the query outlined here

How to use sqlerrm to reveal the meaning of audit information

As an example, the audit information may look like the following:

ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS';

SELECT
 os_username,
 userhost,
 timestamp,
 returncode
FROM  dba_audit_session
WHERE action_name = 'LOGON'
AND   returncode > 0
AND   trunc(timestamp) = to_date('08.07.2013', 'DD.MM.YYYY')
AND   username = 'TSF'
ORDER BY timestamp DESC;

Result:

OS_USERNAME USERHOST       TIMESTAMP            RETURNCODE
----------- -----------    -------------------  ---------- 
billyb      CHM06071130    08.07.2013 12:24:07  1017  
billyb      CHM06071130    08.07.2013 10:06:06  28000  

You can use the sqlerrm keyword to print the meaning of a return code, like this:

exec dbms_output.put_line(sqlerrm(-1017)) ;
exec dbms_output.put_line(sqlerrm(-28000)) ;

These commands will yield:

ORA-01017: invalid username/password; logon denied
ORA-28000: the account is locked