which audit options should be enabled

Enabling audit option for an Oracle Database is a smooth operation which needs a restart of the instance. But the real question comes after enabling the audit options: "Which audit options should we set?". As audit logging is a space consumptive operation it is important for the DBA's to carefully select the options to be logged. Otherwise the SYS.AUD$ table will grow unexpectedly. Moreover this table resides in the SYSTEM tablespace and even after changing the audit options and resizing the table will not help the tablespace to be resized which I dont prefer to have a large SYSTEM tablespace.

In my experience, i always hesitate to enable any DML (insert, delete, update and in this case also select) operation to be logged by database audit on application users (database users used by the application itself or the connection pooling). Depending on the intense usage of the application the DML logging may be disastrous as a lot of log will be produced in th SYS.AUD$ table. But the same DML commands may be logged on developer user accounts which is necessary in most of the cases. Of course enabling these kind of DML operations depends on the application itself or what is being expected from the database audit logs.

On the other hand, auditing DDL (create, drop, alter, truncate) operations should be enabled for auditing regardless of the user and object (Of course this also depends on the application behaviour but still should be forced to be audited).

To find which options to be audited exactly, the following query can be used. Which lists the most important System Privileges to be audited. Any user executing these kind of DML operations should be audited for further security surveillance.

SELECT 
  'audit ' || name || ';'
FROM 
  system_privilege_map
WHERE 
  (  name LIKE 'CREATE%'
  or name LIKE 'ALTER%'
  or name LIKE 'DROP%'
  or name LIKE 'EXECUTE%'
  or name LIKE 'GRANT%'
  or name LIKE 'BACKUP%'
  or name LIKE 'EXPORT%'
  or name LIKE 'IMPORT%'
  or name LIKE 'BECOME%'
  )
order by name;

After executing the output of the above script, enabled system privileges can be seen by selecting from the following dictionary view.

select * from dba_priv_audit_opts;

According to the application behaviour, even with the DML auditing only, application user may produce audit data which cannot be managed. In this case individual users should be audited on the DML operations accept the application user itself. It is as easy as adding "by " at the and of the audit statement.

audit drop any table by D_EPEKER;

Beyond auditing the system privileges, individual objects can also be audited. According to the application behaviour some of the tables may have significant importance and not only auditing the DML operations is sufficient but also the DDL operations should be audited to lower the security risks. As it is very hard to manage the operation of the audit data which will be produced by the application user on all tables, in this case individual database tables can be audited.

Keep in my that object level auditing can be both by session and by access. "By Access" audits every occurance of the event while "By Session" audits only the last occurance of the event within the same session. The decision of the level, again depends on what is expected from the audit logs.

audit update on SYS.T_TEST_TABLE by access;
audit update on SYS.T_TEST_TABLE by session;

select * from dba_obj_audit_opts;

Of course, these are my preferences while setting up the auditing option in oracle database. I usually always set the auditing of the privileges for all users as discussed above and leave the rest to the application developers and analysts as they are more aware of the application logic and the most ciritical objects to be audited.