As a Remote DBA over the years, I have implemented auditing at a number of customers and have found it to be easy to implement, low overhead and very useful.  In my opinion, every production database should have at least basic auditing enabled.  In this blog, I will give a quick overview of the different types of Auditing and how to implement them.

Oracle has basically three flavors of auditing: Mandatory, Standard and Fine-Grained.

Mandatory auditing happens automatically and records database startup/shutdown as well as SYSDBA and SYSOPER logins to the location specified by the AUDIT_FILE_DEST parameter.  On Windows, it writes these records to the Event Viewer.  On Unix, you can optionally set the AUDIT_SYSLOG_LEVEL parameter and have these events go to the Unix syslog. 

Standard auditing is enabled by setting the AUDIT_TRAIL parameter and writes its records to the SYS.AUD$ table.  The following are valid values for this parameter:

NONE = disables standard auditing

OS = writes audit records to an OS file

DB = writes audit records to the SYS.AUD$ table in the database 

DB, EXTENDED = writes audit records to the SYS.AUD$ and includes the complete SQL statement that was executed along with any bind values

XML = writes audit records to an OS file in XML format

XML, EXTENDED = writes audit records to an OS file in XML format plus records the SQL statement and bind values to SYS.AUD$

The database will need to be restarted for it to take effect.  Once you have enabled auditing, you have to tell Oracle which statements you want to audit.  At a minimum, you should audit logon/logoff, user privileges, the use of system privileges and changes to the database (alter database or alter system).   It would be a big mistake to think you can audit every statement against every table without a performance impact and an overload of useless data.  You should think about your data and decide what needs to be audited.  For example, you may have an EMPLOYEE table that contains salary information.  You can keep track of any updates to this table by running AUDIT INSERT,UPDATE, DELETE ON EMPLOYEE BY ACCESS

In addition to your specific needs, there are some commands that I think should be audited in every database:

        ·        AUDIT ALTER ANY PROCEDURE BY ACCESS;
·        AUDIT ALTER ANY TABLE BY ACCESS;
·         AUDIT ALTER DATABASE BY ACCESS;
·         AUDIT ALTER SYSTEM BY ACCESS;
·         AUDIT CREATE ANY EDITION;
·         AUDIT CREATE ANY JOB BY ACCESS;
·         AUDIT CREATE ANY LIBRARY BY ACCESS;
·         AUDIT CREATE ANY PROCEDURE BY ACCESS;
·         AUDIT CREATE ANY TABLE BY ACCESS;
·         AUDIT CREATE EXTERNAL JOB BY ACCESS;
·         AUDIT DROP ANY EDITION;
·         AUDIT DROP ANY PROCEDURE BY ACCESS;
·         AUDIT DROP ANY TABLE BY ACCESS;
·         AUDIT ALTER PROFILE BY ACCESS;
·         AUDIT ALTER USER BY ACCESS;
·         AUDIT AUDIT SYSTEM BY ACCESS;
·         AUDIT CREATE PUBLIC DATABASE LINK BY ACCESS;
·         AUDIT CREATE SESSION BY ACCESS;
·         AUDIT CREATE USER BY ACCESS;
·         AUDIT DROP PROFILE BY ACCESS;
·         AUDIT DROP USER BY ACCESS;
·         AUDIT EXEMPT ACCESS POLICY BY ACCESS;
·         AUDIT GRANT ANY OBJECT PRIVILEGE BY ACCESS;
·         AUDIT GRANT ANY PRIVILEGE BY ACCESS;
·         AUDIT GRANT ANY ROLE BY ACCESS;
·         AUDIT ROLE BY ACCESS;

One word of caution about auditing “create session”.  This will record every logon/logoff that occurs and is extremely important from a security perspective, but can impose a performance overhead if you have a lot of connection activity. If this is a problem for you, rather than not auditing “create session”, it would be better to examine your application to see if you can reduce the amount of logon/logoff activity.  The best way to minimize connection activity is through the use of a connection pool, where you create a bunch of database connections and the application uses them and then releases them as needed. 

Fine-grained auditing enables you to create policies that define specific conditions that must take place for the audit to occur. This enables you to monitor data access based on content. It provides granular auditing of queries, and INSERT, UPDATE, and DELETE operations. For example, a CFO must track access to financial records to guard against employee snooping, with enough detail to determine what data was accessed. It is not enough to know that SELECT privilege was used by a specific user on a particular table. Fine-grained auditing provides this deeper functionality.

In general, fine-grained audit policies are based on simple, user-defined SQL predicates on table objects as conditions for selective auditing. During fetching, whenever policy conditions are met for a row, the query is audited.

For example, you can use fine-grained auditing to audit the following types of actions:

        ·         Accessing a table outside of normal working hours
·         Logging in from a particular IP address
·         Selecting or updating a particular table column
·         Modifying a value in a table column

Fine-grained auditing records are stored in the SYS.FGA_LOG$ table. To find the records have been generated for the audit policies that are in effect, you can query the DBA_FGA_AUDIT_TRAIL view. The DBA_COMMON_AUDIT_TRAIL view combines both standard and fine-grained audit log records.  The DBA_AUDIT_TRAIL view contains standard Auditing records.

To create a fine-grained audit policy, use the DBMS_FGA.ADD_POLICY procedure. This procedure creates an audit policy using the supplied predicate as the audit condition. Oracle Database executes the policy predicate with the privileges of the user who created the policy. The maximum number of fine-grained policies on any table or view object is 256. Oracle Database stores the policy in the data dictionary table, but you can create the policy on any table or view that is not in the SYS schema. After you create the fine-grained audit policy, it does not reside in any specific schema, although the definition for the policy is stored in the SYS.FGA$ data dictionary table.  You cannot modify a fine-grained audit policy after you have created it. If you need to modify the policy, drop it and then recreate it.

The syntax for the ADD_POLICY procedure is:

DBMS_FGA.ADD_POLICY(
   object_schema      VARCHAR2,
   object_name        VARCHAR2,
   policy_name        VARCHAR2,
   audit_condition    VARCHAR2,
   audit_column       VARCHAR2,
   handler_schema     VARCHAR2,
   handler_module     VARCHAR2,
   enable             BOOLEAN,
   statement_types    VARCHAR2,
   audit_trail        BINARY_INTEGER IN DEFAULT,
   audit_column_opts  BINARY_INTEGER IN DEFAULT);

In this specification:

        ·         object_schema: Specifies the schema of the object to be audited. (If NULL, the current log-on user schema is assumed.)
·         object_name: Specifies the name of the object to be audited.
·         policy_name: Specifies the name of the policy to be created. Ensure that this name is unique.
·         audit_condition: Specifies a Boolean condition in a row. NULL is allowed and acts as TRUE. If you specify NULL or no audit condition, then any action on a table with that policy creates an audit record, whether or not rows are returned
·         audit_column: Specifies one or more columns to audit, including hidden columns. If set to NULL or omitted, all columns are audited. These can include Oracle Label Security hidden columns or object type columns. The default, NULL, causes audit if any column is accessed or affected.
·         handler_schema: If an alert is used to trigger a response when the policy is violated, specifies the name of the schema that contains the event handler.
·         handler_module: Specifies the name of the event handler. Include the package the event handler is in. This function is invoked only after the first row that matches the audit condition in the query is processed. If the procedure fails with an exception, then the user SQL statement fails as well.
·         enable: Enables or disables the policy using true or false. If omitted, the policy is enabled. The default is TRUE.
·         statement_types: Specifies the SQL statements to be audited: INSERT, UPDATE, DELETE, or SELECT only.
·         audit_trail: Specifies the destination (DB or XML) of fine-grained audit records. Also specifies whether to populate LSQLTEXT and LSQLBIND in FGA_LOG$.
·         audit_column_opts: If you specify more than one column in the audit_column parameter, then this parameter determines whether to audit all or specific columns.

You can audit all updates to the SALARY column:

DBMS_FGA.ADD_POLICY(

object_schema      => ‘MIKE’,

object_name        => ‘EMPLOYEE’,

policy_name        => ’salary_change’,

audit_column       => ‘SALARY’,

enable             =>  TRUE,

statement_types    => ‘UPDATE’,

audit_trail        =>  DBMS_FGA.DB + DBMS_FGA.EXTENDED);

end;

/

Or, you can get more specific by specifying the AUDIT_CONDITION.  This will create an FGA policy to record all updates to the SALARY column unless it is done by the owner of the table MIKE.  I am using the SYS_CONTEXT function to find information about the user and using that to determine if auditing should occur. 

begin

DBMS_FGA.ADD_POLICY(

object_schema      => ‘MIKE’,

object_name        => ‘EMPLOYEE’,

policy_name        => ’salary_change’,

audit_column       => ‘SALARY’,

audit_condition    => ‘SYS_CONTEXT(”USERENV”,”SESSION_USER”) <> ”MIKE” ‘,

enable             =>  TRUE,

statement_types    => ‘UPDATE’,

audit_trail        =>  DBMS_FGA.DB + DBMS_FGA.EXTENDED);

end;

/

Or, you may want to audit any changes that occur outside of your application (as set with the DBMS_APPLICATION_INFO package)

begin

DBMS_FGA.ADD_POLICY(

object_schema      => ‘MIKE’,

object_name        => ‘EMPLOYEE’,

policy_name        => ’salary_change’,

audit_column       => ‘SALARY’,

audit_condition    => ‘SYS_CONTEXT(”USERENV”,”MODULE”) <> ”MY_APP” ‘,

enable             =>  TRUE,

statement_types    => ‘UPDATE,INSERT,DELETE’,

audit_trail        =>  DBMS_FGA.DB + DBMS_FGA.EXTENDED);

end;

/

One advantage of FGA over Standard Auditing is that you don’t need to set the AUDIT_TRAIL parameter for it to work.  This means that you can enable auditing without restarting the database.  So with the combination of Mandatory, Standard and Fine Grained Auditing, along with some careful consideration of your own auditing requirements, you can record as much or as little information as necessary.