Comparison between default traditional auditing and default unified auditing

Applicable to Oracle Database versions 12.1 and onwards. By default, the databases are created with "Mixed-Mode" auditing enabled. This means that the unified auditing polices ORA_SECURECONFIG og ORA_LOGON_FAILURE are enabled. These policies actually covers a lot of what any DBA would typically want to audit, and overlaps the default (tradtitional) auditing implemented by running $ORACLE_HOME/rdbms/admin/secconf.sql.

Here is a table comparing what the two default settings under each auditing strategy:

My conclusion is that unless your customer specifically wants to continue with traditional auditing, take the opportunity to migrate to Unified Auditing policies implmemented in "Mixed-Mode" auditing. In such cases you should also disable the default traditional auditing by setting the parameter audit_trail to 'NONE'.

what is the difference between DBA_AUDIT_TRAIL and DBA_COMMON_AUDIT_TRAIL?

The difference between these views is that DBA_COMMON_AUDIT_TRAIL contains information from both standard and fine-grained auditing, while DBA_AUDIT_TRAIL only contains information from standard auditing.

DBA_AUDIT_TRAIL displays all standard audit trail entries. 

This view is populated only in an Oracle Database where unified auditing is not enabled. 

The SQL_BIND and SQL_TEXT columns are only populated if the AUDIT_TRAIL initialization parameter is set to 'db, extended'. 

DBA_COMMON_AUDIT_TRAIL displays 

• All standard audit trail entries 
• Fine-grained audit trail entries 
• Mandatory audit trail entries 
• SYS audit records written in XML format 

The SQL_BIND and SQL_TEXT columns are only populated if the AUDIT_TRAIL initialization parameter is set to 'db, extended' or 'xml, extended' or if the AUDIT_SYS_OPERATIONS initialization parameter is set to TRUE.

How to change configuration for your audit trail

This post is applicable from Oracle version 11.2 until present (Oracle 19 as per writing).

Check the current settings:

SELECT * 
FROM DBA_AUDIT_MGMT_CONFIG_PARAMS
ORDER BY AUDIT_TRAIL;

"PARAMETER_NAME"	"PARAMETER_VALUE"	"AUDIT_TRAIL"
DB AUDIT TABLESPACE	AUDDATA	FGA AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE	10000	FGA AUDIT TRAIL
AUDIT FILE MAX SIZE	10000	OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE	1000	OS AUDIT TRAIL
AUDIT FILE MAX AGE	5	OS AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL	1	STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE	AUDDATA	STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE	10000	STANDARD AUDIT TRAIL
AUDIT FILE MAX SIZE	10000	UNIFIED AUDIT TRAIL
AUDIT FILE MAX AGE	5	UNIFIED AUDIT TRAIL
DB AUDIT TABLESPACE	AUDDATA	UNIFIED AUDIT TRAIL
AUDIT WRITE MODE	IMMEDIATE WRITE MODE	UNIFIED AUDIT TRAIL
AUDIT FILE MAX SIZE	10000	XML AUDIT TRAIL
AUDIT FILE MAX AGE	5	XML AUDIT TRAIL
OS FILE CLEAN BATCH SIZE	1000	XML AUDIT TRAIL

I will now change the DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE for the XML AUDIT TRAIL from the default 5 days to 2 days.
This is the maximum age of an audit trail file before a new audit trail file gets created:

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML,
   audit_trail_property => DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,
   audit_trail_property_value => 2
) ;
END;
/

Verify that it was set successfully:

SELECT * 
 FROM DBA_AUDIT_MGMT_CONFIG_PARAMS 
 WHERE AUDIT_TRAIL='XML AUDIT TRAIL'
 AND PARAMETER_NAME='AUDIT FILE MAX AGE';

"PARAMETER_NAME"	"PARAMETER_VALUE"	"AUDIT_TRAIL"
AUDIT FILE MAX AGE	2	XML AUDIT TRAIL

For more examples, check the Oracle Documentation

Why is Oracle producing .aud files for internal sys-statements?

I have recently been in contact with Oracle support regarding an issue where my Oracle 18c database instance is sending audit information for internal statements, much similar to this:

Sun Jan 26 10:25:41 2020 +01:00 LENGTH : '401' ACTION :[147] 'select /*+ opt_param('parallel_execution_enabled', 'false') EXEC_FROM_DBMS_XPLAN */ * from gv$sql_plan where 1=0' DATABASE USER:[1] '/' PRIVILEGE :[4] 'NONE' CLIENT USER:[0] '' CLIENT TERMINAL:[7] 'UNKNOWN' STATUS:[1] '0' DBID:[10] '1325844924' SESSIONID:[1] '0' USERHOST:[26] 'myhost.mydomain.com' CLIENT ADDRESS:[0] '' ACTION NUMBER:[1] '3' Sun Jan 26 10:25:41 2020 +01:00 LENGTH : '375' ACTION :[121] 'SELECT * FROM gv$sql_plan where sql_id = 'a0f1h9d5muwa6' and inst_id = 1 and child_address = hextoraw('00000004FFF16130')' DATABASE USER:[1] '/' PRIVILEGE :[4] 'NONE' CLIENT USER:[0] '' CLIENT TERMINAL:[7] 'UNKNOWN' STATUS:[1] '0' DBID:[10] '1325844924' SESSIONID:[1] '0' USERHOST:[26] 'myhost.mydomain.com' CLIENT ADDRESS:[0] '' ACTION NUMBER:[1] '3'

If you have migrated to Unified Auditing, Oracle states that "audit records are only expected to be generated in database tables and OS spillover files (*.bin) under audit destination path."

However, dynamic SQL statements parsed or executed using DBMS_SQL package are being audited in the conventional *.aud type OS files.

To get rid of these messages piling up in your audit_dump_dir:

alter system set audit_sys_operations=FALSE scope=spfile;
shutdown immediate
startup

If setting audit_sys_operations to FALSE is not desirable, Oracle states that you can request a patch through the following bug number:


Bug 21133343 *.aud file is generated though unified auditing=true and audit_trail=none


Note that you will see the same phenomenon under the mixed-mode or classic auditing.
Oracle does not explisitly say they will provide a patch in this case though.

Documentation from Oracle support: Doc ID 2020881.1: "OS Audit Files *.aud are Still Generated After Migrating to Unified Audit"

How to find audit information about SELECT statements

This article is based on setup in a database running classical auditing in version 18.6.0.0.0, but should be possible to use in older versions, too.

After you have verified that your table is indeed being audited, you can move on to see exactly what was executed at a specific point in time.

To populate the columns SQL_BIND and SQL_TEXT, you need to make sure you gather extended auditing information in your database.

alter system set audit_trail=db, extended scope=spfile;
shutdown immediate 
startup

Now you can use the following query to find

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

TIMESTAMP	OS_USERNAME	OBJ_NAME	USERHOST	SESSIONID	USERNAME	ACTION_NAME	RETURNCODE	SQL_BIND	SQL_TEXT
07.08.2019	JOHN	EMP	jonsPC	174335	DBAADMIN	SELECT	0		SELECT * FROM SCOTT.EMP
06.08.2019	LISA	EMP	lisaspc	171886	LISA	SELECT	0
05.08.2019	FRED	DEPT	fredsPC	141131	SCOTT	SELECT	0

How to audit specific users' actions on other schemas' objects

This was executed on Oracle 12.2

To audit actions by users on another schema's objects, execute this:

audit insert table,update table,select table, delete table by scott by access; 

Verify that the auditing is in place:

SELECT audit_option,success,failure
FROM DBA_STMT_AUDIT_OPTS
WHERE USER_NAME='SCOTT';

You can see that the actions we want to audit for user SCOTT is indeed set:

audit_option	success	failure
SELECT TABLE	BY ACCESS	BY ACCESS
INSERT TABLE	BY ACCESS	BY ACCESS
UPDATE TABLE	BY ACCESS	BY ACCESS
DELETE TABLE	BY ACCESS	BY ACCESS

This will generate audit entries whenever scott tries to perform queries or DML on other schemas' tables.

In my case, I tested this by logging on to the database as user scott, and executed a delete statement against a test table called "yy", owned by the SALES schema:

sqlplus / as sysdba
grant create synonym to scott;

sqlplus scott/tiger@testdb01
SQL> create synonym yy for sales.yy;

Synonym created.

SQL> delete from yy where antall in (1,2);

7 rows deleted.

SQL> commit;

Commit complete.

This particular database writes its audit information to the operating system, as indicated by the audit_trail parameter:

sqlplus / as sysdba
SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      OS

My audit_file_dest resides inside the ADR directory structure:

show parameter audit_file_dest

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

Checking the /u01/oracle/admin/testdb01/adump I found the file and it showed:

Thu Apr  4 16:32:56 2019 +02:00
LENGTH: "276"
SESSIONID:[8] "14060442" 
ENTRYID:[2] "67" 
STATEMENT:[2] "14" 
USERID:[6] "SCOTT" 
USERHOST:[18] "MYDOMAIN\MYPC123" 
TERMINAL:[12] "MYPC123" 
ACTION:[1] "7" 
RETURNCODE:[1] "0" 
OBJ$CREATOR:[11] "SALES" 
OBJ$NAME:[2] "YY" 
OS$USERID:[6] "vegardk" 
DBID:[10] "2425899399"

The action codes are important. Number 7 indicates a DELETE statement, which is what I expected to find.
The entire list can be found here

How to count the number of LOGONS based on audit entries in DBA_AUDIT_SESSION

CREATE SESSION is audited by default

To get an idea of the number of sessions created by the different users, you can execute the following query against DBA_AUDIT_SESSION:

SELECT TO_DATE(TIMESTAMP,'dd.mm.yyyy') "logon  time",USERNAME, COUNT(*)
FROM    DBA_AUDIT_SESSION
WHERE   ACTION_NAME = 'LOGON'
AND     USERNAME IN ('JIM','DWIGHT','KEVIN')
AND     TRUNC(TIMESTAMP) BETWEEN '12.03.2019' AND '13.03.2019'
GROUP BY TO_DATE(TIMESTAMP,'dd.mm.yyyy'),USERNAME
ORDER BY 1;

logon time	USERNAME	COUNT(*)
12.03.2019	JIM	1
12.03.2019	DWIGHT	71
12.03.2019	KEVIN	1
13.03.2019	JIM	10
13.03.2019	DWIGHT	1
13.03.2019	KEVIN	1

How to check if fine-grained auditing (FGA) is enabled in your database

SELECT  POLICY_NAME,ENABLED,AUDIT_TRAIL
FROM  DBA_AUDIT_POLICIES;

Result:

POLICY_NAME	ENABLED	AUDIT_TRAIL
MY_FGAPOLICY_1	YES	DB+EXTENDED
MY_FGAPOLICY_2	YES	DB+EXTENDED
MY_FGAPOLICY_3	YES	DB+EXTENDED
MY_FGAPOLICY_4	YES	DB+EXTENDED

Find audit information about dropped users

If you have created your database with dbca, or have manually run the secconf.sql script after your database was created, the "DROP USER" is audited by default in Oracle 12c.
This is a part of what Oracle calls default auditing which I have written about in another post.

Here is how you can find information about dropped users:

select os_username,username,userhost,timestamp,obj_name,action_name,priv_used
from dba_audit_trail 
where action_name='DROP USER'
and to_char(cast ( timestamp as date),'dd.mm.yyyy') > '08.02.2019'
AND OBJ_NAME IN ('JIM','DWIGHT')
order by timestamp desc;

Result:

OS_USERNAME	USERNAME	USERHOST	TIMESTAMP	OBJ_NAME	ACTION_NAME	PRIV_USED
root	SYSTEM	hostname1.mydomain.com	12.02.2019 11:31:05	JIM	DROP USER	DROP USER
root	SYSTEM	hostname1.mydomain.com	12.02.2019 11:28:10	DWIGHT	DROP USER	DROP USER

Workaround for ORA-46372: audit file not found

In my environment, I had the following errors popping up in Cloud Control:

ORA-46372: audit file '/u01/oracle/audit/proddb01/ora_audit_10.bin' not found
ORA-17503: ksfdopn:4 Failed to open file
/u01/oracle/audit/proddb01/ora_audit_10.binin
ORA-17500: ODM err:File does not exist

It can be manually reproduced by executing the following query:

select count(1) from unified_audit_trail;

It seems to be related to BUG 26352615 - SPILLOVER AUDIT FILES CANNOT BE FOUND ORA-46372 for platform Linux x86-64. Per May 22ond 2018, it is still in development according to Oracle Support.

In the directory /u01/oracle/audit/proddb01, there is quite correctly no file named ora_audit_10.binin. However, there is one called ora_audit_10.bin

The following workaround worked for me:

Go to the directory /u01/oracle/audit/proddb01
Change the name of the file ora_audit_10.bin to ora_audit_10.binin

Test like this:

select count(1) from unified_audit_trail;

  COUNT(1)
----------
        25

Update 09.03.2020:

Oracle has released a patch for 26352615. It is available for a number of RUs. I just applied the patch for 12c Release 12.2.0.1.200114DBJAN2020RU, and it solved the problem outlined above.

How to toggle between "Mixed mode" Auditing, Traditional Auditing and Unified Auditing

This article is applicable to Oracle database versions 12.1 and onwards.

For newly created databases, mixed mode auditing is enabled by default through the predefined policy ORA_SECURECONFIG. 

 

Verify that the database is using "Mixed Mode" auditing

select parameter, value from v$option where parameter='Unified Auditing';

PARAMETER	VALUE
Unified Auditing	FALSE

Check for any enabled unified audit policies:

select policy_name, enabled_option
from audit_unified_enabled_policies;

POLICY_NAME	ENABLED_OPTION
ORA_SECURECONFIG	BY USER

If v$option shows FALSE for unified auditing AND the database have at least one enabled unified auditing policy, we are running in "Mixed Mode" auditing. 

In Mixed Mode Auditing, all of the existing auditing startup parameters for the database are still valid: AUDIT_TRAIL, AUDIT_FILE_DEST, AUDIT_SYS_OPERATIONS, and AUDIT_SYSLOG_LEVEL. So if your AUDIT_TRAIL is set to "DB", you can still use all the expected data dictionary views to obtain your audit information. If AUDIT_TRAIL is set to "OS", your auditing information will be sent to the location specified by the parameter AUDIT_FILE_DEST.

To enable "pure" Unified Auditing

1. Shutdown the database:

shutdown immediate

2. Relink the Oracle database binaries:

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME

3. Enable at least one unified audit policy. By default, two unified auditing policies are created when you create your a 12.2 database: ORA_SECURECONFIG and ORA_LOGON_FAILURES. The first one is enabled by the default, the last one is not. Let's enable the ORA_LOGIN_FAILURES, too:

audit policy ORA_LOGON_FAILURES;

Verify:

select parameter, value from v$option where parameter='Unified Auditing';

PARAMETER	VALUE
Unified Auditing	TRUE

select *
from audit_unified_enabled_policies;

POLICY_NAME	ENABLED_OPTION	ENTITY_NAME	ENTITY_TYPE	SUCCESS	FAILURE
ORA_LOGON_FAILURES	BY USERS	ALL USERS	USER	YES	YES
ORA_SECURECONFIG	BY USERS	ALL USERS	USER	YES	YES

If v$option shows TRUE for Unified Auditing AND we have at least one enabled unified auditing policy, we are using "Pure" Unified Auditing. 

It doesn't matter what all the Traditional Auditing parameters are set to at this point; they will not have any effect. 

Your audit information will from now on be written to the table AUDSYS.AUD$UNIFIED.

The SYS.AUD$ and SYS.FGA_LOG$ tables will still be accessible, but not used by the Oracle instance. They will only contain auditing records from before unified auditing was enabled. Consequently, your previously used queries based on familiar data dictionary views such as dba_audit_trail will only return information from before Unified Auditing was enabled.

The Oracle documentation provides a table which is very helpfull in determining the pros and cons of migrating to Unified Auditing. 

In my opinion, the most important drawback with Unfied Auditing is that it doesn't allow the auditing data to be written to the operating system.

To enable traditional Auditing

1. First, disable any unified audit policies that are currently enabled. Find the currently enabled policies:

select user_name, policy_name, enabled_opt, enabled_option
from audit_unified_enabled_policies;

USER_NAME	POLICY_NAME	ENABLED_OPT	ENABLED_OPTION
ALL USERS	ORA_LOGON_FAILURES	BY	BY USER
ALL USERS	ORA_SECURECONFIG	BY	BY USER

2. Take them out of audit. This step prevents the database from going into mixed mode auditing after you complete this procedure:

noaudit policy ORA_SECURECONFIG;
noaudit policy ORA_LOGON_FAILURES;

3. Shutdown the database:

shutdown immediate

4. Relink the Oracle database binaries:

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_off ioracle ORACLE_HOME=$ORACLE_HOME

5. Start the database

sqlplus / as sysdba
startup

The database should now be in Traditional Auditing mode. There will be no more entries logged to the unified_audit_trail. Your audit records will go to the SYS.AUD$ and SYS.FGA_LOG$ tables, or to the operating system, depending on your value for the parameter AUDIT_TRAIL.

More about disabling unified auditing policies can be found here

Another good source for more information about Unified Auditing is this article found at oracle-base.com

How to find the last password change a user made

Limit the search to those users who changed their password today:

select name,ctime "created", ptime "passw change time",ltime "last locked", lcount "# failed logins"
 from user$
 where trunc(ptime) = trunc(sysdate)
 order by 3 desc;

Example output:

NAME	created	passw change time	last locked	# failed logins
JIM	25.11.2016 02:25:38	12.12.2016 09:59:25		0
JOHN	25.11.2016 02:25:55	12.12.2016 09:53:19		0
JAMES	25.11.2016 02:25:54	12.12.2016 09:29:50		0

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'

How can I check which tables that are being audited in my database?

The experiences in this article is applicable to all Oracle versions using classic auditing setup.


To check which tables are currently being audited, use the views DBA_OBJ_AUDIT_OPTS and DBA_PRIV_AUDIT_OPTS.

DBA_PRIV_AUDIT_OPTS describes current system privileges being audited across the system and by user. USERNAME can hold three different values: 

1. The actual user name if by user auditing
2. ANY CLIENT if access by a proxy on behalf of a client is being audited; 
3. NULL for system-wide auditing

DBA_OBJ_AUDIT_OPTS describes auditing options on all objects. 

If no objects or system privileges is being audited, the queries will return zero rows as in the examples below:

SELECT *
FROM DBA_PRIV_AUDIT_OPTS;

no rows selected

SELECT *
FROM DBA_OBJ_AUDIT_OPTS;

no rows selected

Let's start auditing an object:

AUDIT ALL ON SCOTT.BASELINE_STG_TBL;

Audit succeeded.

You can also be more specific and only update certain actions:

AUDIT UPDATE,DELETE,INSERT ON SCOTT.BASELINE_STG_TBL;

Audit succeeded.

Verify that audit is enabled:

SELECT *
FROM DBA_OBJ_AUDIT_OPTS
WHERE OWNER = 'SCOTT'
AND object_name='BASELINE_STG_TBL';

OWNER	OBJECT_NAME	OBJECT_TYPE	ALT	AUD	COM	DEL	GRA	IND	INS	LOC	REN	SEL	UPD	REF	EXE	CRE	REA	WRI	FBK
SCOTT	BASELINE_STG_TBL	TABLE	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	-/-	-/-	-/-	-/-	-/-	S/S

The notation 'S/S' in the different columns of the DBA_OBJ_AUDIT_OPTS is "frequency" where frequency is either "By Session" (S) or "By Access" (A).

The placement of the hyphen, the "S" or the "A" indicates under what circumstances the auditing should be performed, as outlined below:

-/-: no auditing
S/-: auditing whenever successful
-/S: auditing whenever not successful

As can be seen by the result above, by default, auditing is done by SESSION, both for successful and unsuccessful attempts.
Let's turn off auditing for SELECTs

NOAUDIT SELECT ON SCOTT.BASELINE_STG_TBL;

Noaudit succeeded.

Verify that queries on the table are now not being audited:

SELECT *
 FROM DBA_OBJ_AUDIT_OPTS
 WHERE OWNER = 'SCOTT'
 AND object_name='BASELINE_STG_TBL';

OWNER	OBJECT_NAME	OBJECT_TYPE	ALT	AUD	COM	DEL	GRA	IND	INS	LOC	REN	SEL	UPD	REF	EXE	CRE	REA	WRI	FBK
SCOTT	BASELINE_STG_TBL	TABLE	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	-/-	S/S	-/-	-/-	-/-	-/-	-/-	S/S

Notice that the SEL column now shows a hyphen (-) since the privilege is not audited anymore.

The following statement

AUDIT SELECT ON SCOTT.BASELINE_STG_TBL BY ACCESS WHENEVER NOT SUCCESSFUL;

Would result in

OWNER	OBJECT_NAME	OBJECT_TYPE	ALT	AUD	COM	DEL	GRA	IND	INS	LOC	REN	SEL	UPD	REF	EXE	CRE	REA	WRI	FBK
SCOTT	BASELINE_STG_TBL	TABLE	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	-/A	S/S	-/-	-/-	-/-	-/-	-/-	S/S

and

AUDIT SELECT ON SCOTT.COUNTRY_TABLE BY ACCESS WHENEVER SUCCESSFUL;

would result in

OWNER	OBJECT_NAME	OBJECT_TYPE	ALT	AUD	COM	DEL	GRA	IND	INS	LOC	REN	SEL	UPD	REF	EXE	CRE	REA	WRI	FBK
SCOTT	BASELINE_STG_TBL	TABLE	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	S/S	A/-	S/S	-/-	-/-	-/-	-/-	-/-	S/S

Keep in mind that mandatory auditing is always turned on. 

Oracle 12.2 Documentation for DBA_OBJ_AUDIT_OPTS can be found here

What constitute "default auditing" under traditional auditing?

From the Oracle Documentation:

"When you use Database Configuration Assistant (DBCA) to create a new database, Oracle Database configures the database to audit the most commonly used security-relevant SQL statements and privileges"

and

"If you manually create a database, then you should run the secconf.sql script to apply the default audit settings to your database"

Oracle Database audits the following privileges by default:

ALTER ANY PROCEDURE

CREATE ANY LIBRARY

DROP ANY TABLE

ALTER ANY TABLE

CREATE ANY PROCEDURE

DROP PROFILE

ALTER DATABASE

CREATE ANY TABLE

DROP USER

ALTER PROFILE

CREATE EXTERNAL JOB

EXEMPT ACCESS POLICY

ALTER SYSTEM

CREATE PUBLIC DATABASE LINK

GRANT ANY OBJECT PRIVILEGE

ALTER USER

CREATE SESSION

GRANT ANY PRIVILEGE

AUDIT SYSTEM

CREATE USER

GRANT ANY ROLE

CREATE ANY JOB

DROP ANY PROCEDURE

Oracle Database audits the following SQL shortcuts by default:

ROLE

SYSTEM AUDIT

PUBLIC SYNONYM

DATABASE LINK

PROFILE

SYSTEM GRANT

Remember that secconf.sql turns on audting regardless of your database using Unified Auditing or Traditional Auditing.

How?

When executed manually, the DBA is prompted for which type of auditing style that should be considered:

sqlplus / as sysdba @secconf.sql

Session altered.


Profile altered.

Do you wish to configure 11g style Audit Configuration OR
Do you wish to configure 12c Unified Audit Policies?
Enter RDBMS_11G for former or RDBMS_UNIAUD for latter
Enter value for 1: RDBMS_11G
old   7:   USER_CHOICE := '&1';
new   7:   USER_CHOICE := 'RDBMS_11G';

What I have found, is that if you intend to harden your Traditional Auditing policies by executing the script in a database where the Unified Auditing polices exist but is disabled, or a database running in "Mixed mode" auditing, the script will fail:

DECLARE
*
ERROR at line 1:
ORA-46358: Audit policy ORA_ACCOUNT_MGMT already exists.
ORA-06512: at line 9

A simple workaround in such a case is to simply comment out the code that is irrelevant to your desired type of auditing, and rerun the script.

You can disable default auditing if you wish, see the section "Disabling and Enabling Default Audit Settings"

To check whether or not default auditing has been actived, you can query the view DBA_PRIV_AUDIT_OPTS, which describes current system privileges being audited across the system and by user. If the column USERNAME is NULL, you have introduced system-wide auditing.

Why does the TIMESTAMP# column in the AUD$ table contain NULL values?

According to Oracle Support Note 427296.1:

"In database version 10gR1 and above, the TIMESTAMP# column is obsoleted in favor of the new NTIMESTAMP# column."

So when exchanging the TIMESTAMP# with the NTIMESTAMP# column, my script works as intended, while it had previously showed NULL values:

SELECT DBID "CURRENT DBID" FROM V$DATABASE;

 SET TIMING ON
 SET LINES 200
 COL "Earliest" format a30
 col "Latest" format a30

 PROMPT Counting the DBIDs and the number of audit entries each
 PROMPT Could take a while...
 COL TIMESTAMP# FORMAT A3
 SELECT DBID,COUNT(*),MIN(NTIMESTAMP#) "Earliest", MAX(NTIMESTAMP#) "Latest"
 FROM AUD$
 GROUP BY DBID;

Output:

Counting the DBIDs and the number of audit entries each
 Could take a while...

       DBID   COUNT(*) Earliest                       Latest
 ---------- ---------- ------------------------------ ------------------------------
2367413790       1867 05.06.2014 14.01.30,193254     06.06.2014 06.17.08,485629

The views built upon AUD$, for example DBA_AUDIT_TRAIL and DBA_FGA_AUDIT_TRAIL, will of course reflect the correct columns from AUD$ (NTIMESTAMP#) in their own TIMESTAMP column.

Multiple DBIDs in AUD$

I wanted to test the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL package, with use_last_arch_timestamp to TRUE, to only purge records one month older than the minimum value found.

I found this value by using the function ADD_MONTHS to add 1 month on top of the minimum value found in AUD$

SET SERVEROUTPUT ON
 DECLARE
 currdate DATE;
 last_archtime DATE;

 BEGIN

 currdate := SYSTIMESTAMP;

 ---------------------------------------------------------
 -- Get the oldest timestamp from AUD$, then add one month.
 -- Use this timestamp as the last archive timestamp in
 -- procedure SET_LAST_ARCHIVE_TIMESTAMP
 ---------------------------------------------------------
 SELECT ADD_MONTHS(
   (
    SELECT MIN(TIMESTAMP) 
    FROM DBA_AUDIT_TRAIL
    ), 1)
 INTO last_archtime
 FROM DUAL;
 DBMS_OUTPUT.PUT_LINE('last_archtime: ' || last_archtime);

  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (
    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    LAST_ARCHIVE_TIME => last_archtime);

 END;
 /

Put all that in a file called set_last_timestamp_std.sql.
First, check the DBA_AUDIT_MGMT_LAST_ARCH_TS for the last archive timestamp:

SQL> SELECT * FROM DBA_AUDIT_MGMT_LAST_ARCH_TS;

No rows selected.

Execute the script created above:

sqlplus / as sysdba @set_last_timestamp_std.sql

 last_archtime: 07.02.2009

 PL/SQL-procedure executed.

Check the DBA_AUDIT_MGMT_LAST_ARCH_TS again:

AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS
 -------------------- ------------ ----------------------------------------
 STANDARD AUDIT TRAIL            0 07.02.2009 15.01.50,000000 +00:00

I was now ready to execute the manual cleanup. Before I did so, I wanted to get an idea about how many rows that should be purged:

SELECT COUNT(*)
FROM DBA_AUDIT_TRAIL
WHERE TIMESTAMP < (
SELECT ADD_MONTHS(
   (SELECT TIMESTAMP
    FROM (SELECT TIMESTAMP FROM DBA_AUDIT_TRAIL ORDER BY TIMESTAMP ASC)
    WHERE ROWNUM <=1), 1)
 FROM DUAL)
;

  COUNT(*)
----------
   126405

Compare with the total number of rows:

SQL> SELECT COUNT(*)FROM DBA_AUDIT_TRAIL;

  COUNT(*)
----------
  33 664,540

Sweet. 126405 records would be purged. I then executed:

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => TRUE);
END;
/

The purge succeeded. But when I checked the number of rows again, it still returned 126405 rows. What I found was that what Oracle was executing the following statement internally when using the DBMS_AUDIT_MGMT package:

DELETE FROM SYS.AUD$ WHERE DBID = 2367413790 AND NTIMESTAMP# < to_timestamp('2009-02-07 15:01:50', 'YYYY-MM-DD HH24:MI:SS.FF') AND ROWNUM <= 10000;

So I tested to select the rows using the same predicate that was used during the purge:

SQL> SELECT COUNT(*) FROM SYS.AUD$ WHERE DBID = 2367413790 AND NTIMESTAMP# < to_timestamp('2009-02-07 15:01:50', 'YYYY-MM-DD HH24:MI:SS.FF');

   COUNT(*)
 ----------
          0

checked again against the dba_audit_trail

SQL> SELECT COUNT(*) FROM DBA_AUDIT_TRAIL WHERE TIMESTAMP  < to_timestamp('2009-02-07 15:01:50', 'YYYY-MM-DD HH24:MI:SS.FF');

   COUNT(*)
 ----------
     126405

So there are indeed records that are older than '2009-02-07 15:01:50'. Why is it not caught when querying the AUD$ table, only the DBA_AUDIT_TRAIL? Of course! The AUD$ table also has a reference to the DBID. And since the database was recently cloned, it has cycled through another incarnation:

SQL> select DBID,MIN(NTIMESTAMP#)
2  FROM AUD$
3  GROUP BY DBID;

DBID MIN(NTIMESTAMP#)
---------- ----------------------------
2367413790 19.05.2014 07.07.13,675010
848951741 07.01.2009 13.01.50,802413

So the fact that minimum timestamp for DBID 2367413790 is 19.05.2014 is correct after all:

SQL> SELECT MIN(TIMESTAMP) FROM DBA_AUDIT_TRAIL WHERE DBID=2367413790;

MIN(TIMEST
----------
19.05.2014

In fact, the majority of the audit trail records are from a previous incarnation:

SQL> select count(*) from aud$ where dbid = 848951741;

COUNT(*)
----------
33 612,411

SQL> select DBID,MAX(NTIMESTAMP#)
2  FROM AUD$
3  GROUP BY DBID;

DBID MAX(NTIMESTAMP#)
---------- --------------------------------
2367413790 05.06.2014 08.42.59,749967
848951741 15.05.2014 21.41.52,247344

So the size of the AUD$ is 7481 MB:

SQL> SELECT SUM(BYTES)/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'AUD$';

SUM(BYTES)/1024/1024
--------------------
7481

Now the question is: since the procedure DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL with the parameter use_last_arch_timestamp set to TRUE only attempts to purge the rows from AUD$ that has the same DBID as the current database incarnation, will a "purge all" directive, indicated by use_last_arch_timestamp set to FALSE be equally selective? Since this is a test system, I tried it out by putting the following statements into a script:

SET LINES 200
SET SERVEROUTPUT ON

SELECT DBID,COUNT(*)
FROM AUD$
GROUP BY DBID;

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => FALSE);
END;
/

SELECT DBID,COUNT(*)
FROM AUD$
GROUP BY DBID;

Execute it:

sqlplus / as sysdba @clean_all_audit_trail.sql

Result:

DBID   COUNT(*)
---------- ----------
2367413790      52560
848951741   33612411

PL/SQL-procedure executed.

No rows selected.


SQL> SELECT SUM(BYTES)/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'AUD$';

SUM(BYTES)/1024/1024
--------------------
,0625

So a "purge all" directive will certainly wipe out all of your audit trail, regardless of the presence of multiple DBIDs.

Purging "up until a the last archive timestamp" will only select the audit entries for your current database incarnation's DBID.

Audit parameters. What do they mean - short description

When managing your audit trail, you will need to be familiar with these settings. Here is a short description of what they mean.

SQL> SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME LIKE '%audit%';

NAME	VALUE
audit_sys_operations	TRUE
audit_file_dest	/u01/oracle/admin/slyt/adump
audit_syslog_level
audit_trail	DB_EXTENDED

AUDIT_TRAIL
When set to "DB", Oracle directs audit records to the database audit trail (the SYS.AUD$ table), except for mandatory and SYS audit records, which are always written to the operating system audit trail.

When set to "DB,EXTENDED", oracle behaves as it would when AUDIT_TRAIL=DB, but also enables you to capture the SQL statement used in the action that was audited.

When set to "NONE", standard auditing is disabled

When set to "OS", Oracle directs all audit records to an operating system file.

When set to "XML", Oracle writes to the operating system audit record file in XML format

When set to "XML, EXTENDED", oracle behaves as it would with "AS AUDIT_TRAIL=XML", but also includes SQL text and SQL bind information

AUDIT_SYS_OPERATIONS
Enables or disables the auditing of top-level operations, which are SQL statements directly issued by users when connecting with SYSDBA or SYSOPER privileges.

AUDIT_FILE_DEST
specifies the operating system directory into which the audit trail is written when the AUDIT_TRAIL initialization parameter is set to os, xml, or xml,extended. It is also the location to which mandatory auditing information is written and, if so specified by the AUDIT_SYS_OPERATIONS initialization parameter, audit records for user SYS.

AUDIT_SYSLOG_LEVEL
Allows SYS and standard OS audit records to be written to the system audit log using the SYSLOG utility


Note: when you set the audit_trail parameter in the spfile, DO NOT use qotation marks around the values: Incorrect:

alter system set audit_trail='DB,EXTENDED' scope=spfile;

ORA-00096: invalid value DB,EXTENDED for parameter audit_trail, must be from among extended, xml, none, os, db

Correct:

alter system set audit_trail=db,extended scope=spfile;

System altered.

What is the difference between BY ACCESS and BY SESSION in Oracle Auditing?

The difference between BY ACCESS and BY SESSION lies in how the DBA_AUDIT_TRAIL data dictionary view records the actions that they capture:

BY ACCESS: Inserts one record in the audit trail for each audited statement.

BY SESSION: Inserts only one audit record in the audit trail, for each user and schema object, during a session that includes an audited action.

Example:

First, enable auditing on the table TABLE1:

AUDIT SELECT, UPDATE,DELETE,INSERT ON HR.TABLE1 BY SESSION WHENEVER SUCCESSFUL;

Secondly, assuming there is activity against TABLE1, check the audit trail:

SELECT  timestamp, os_username, obj_name,userhost, username,action,action_name, ses_actions
FROM    dba_audit_trail
WHERE   obj_name IN ('TABLE1')
AND     username = 'HR'
ORDER BY timestamp DESC;

The query in my case returned the following:

TIMESTAMP	OS_USERNAME	OBJ_NAME	USERHOST	USERNAME	ACTION	ACTION_NAME	SES_ACTIONS
04.07.2011 10:35:20	joe	TABLE1	laptop-joe	HR	103	SESSION REC	---------S------
08.02.2011 18:11:10	joe	TABLE1	appserver1	HR	2	INSERT
08.02.2011 18:11:10	joe	TABLE1	appserver1	HR	6	UPDATE
08.02.2011 18:11:10	jack	TABLE1	appserver1	HR	7	DELETE
08.02.2011 14:50:41	tina	TABLE1	workstation-tina	HR	103	SESSION REC	---S--S---S-----

For statements which are audited BY SESSION, the column SES_ACTIONS will indicate a pattern to view which operation we are talking about.
The 16 characters string in the SES_ACTION column represents different actions, one for each action type in the order (note that positions 14, 15, and 16 are reserved for future use):

* ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE, REFERENCES, and EXECUTE.

The characters are:

* - - None
* S - Success
* F - Failure
* B - Both

Finally, interpret the audit trail:
The first row in the table indicates that a successfull SELECT (the S is found on position 10) was executed on TABLE1, 04.07.2011 10:35:20.
The last row indicates a successful DELETE, INSERT and UPDATE (the S is found in position 4, 7 and 11) in TABLE1 during the session that was audited 08.02.2011 14:50:41.
The three rows where ACTION_NAME is INSERT, UPDATE and DELETE are self-explanatory.

At the tail of the AUDIT clause comes the directive WHENEVER [NOT] SUCCESSFUL which means that oracle should audit only SQL statements and operations that succeed, fail or result in errors. If you omit this clause, then Oracle Database performs the audit regardless of success or failure.

What is standard (or traditional) auditing?

From the Oracle Documentation:

"In standard auditing, you enable auditing of SQL statements, privileges, schema objects, and network or multitier activities. You can audit a specific schema table if you want."

You need to set AUDIT_TRAILto "DB" or "DB,EXTENDED", in order to start using standard auditing.
After this is done, you may execute the AUDIT statement to start auditing of different actions according to your need.
You can choose to write the audit-trail to the database itself, or to operating system files.

Oracle Database audits all data manipulation language (DML) operations, such as INSERT, UPDATE, MERGE, and DELETE on the SYS.AUD$ and SYS.FGA_LOG$ tables by non-SYS users. (It performs this audit even if you have not set audit options for the AUD$ and FGA_LOGS$ tables.)

Typically, non-SYS users do not have access to these tables, except if they have been explicitly granted access. If a non-SYS user tampers with the data in the SYS.FGA_LOG$ and SYS.AUD$ tables, then Oracle Database writes an audit record for each action.

To view the standard audit trail, query the views DBA_AUDIT_TRAIL and DBA_COMMON_AUDIT_TRAIL.

Source: Oracle 11g Documentation


Note that in later releases, Oracle is often refering to standard auditing as "Traditional Auditing", since Oracle 12c introduced the concept of Unfied Auditing.