Fine-Grained Auditing

As I discussed in “Oracle Database Auditing” post oracle auditing is a very powerful utility. Using this option you are able to identify users’ activities, object access and so on..

As we know there exist four types of auditing: statement, object, privilege and fine-grained auditing. I will discuss fine-grained auditing, because previous three options are clarified in “Oracle Database Auditing” post. FGA can be defined only for table and optionally on column.

Fine-grained auditing uses PL/SQL package DBMS_FGA, which has the following methods: creating , dropping, enabling and disabling policies. We will identify which policies are created in the database and also see the audit logs.

Creating FGA Policy

DBMS_FGA.ADD_POLICY method is used for creating FGA policy. Which has the following parameters:

[object_schema] -Username, which owns the object that should be audited.Default is NULL, means current schema.

object_name -Name of the object that should be audited.

policy_name -Unique name for the policy.

[audit_condition] -If the condition, indicated here, evaluates to true or null(both of them are default) audit entry will be created. Note that this condition cannot directly use the following functions: USERENV, USER, SYSDATE, UID. It cannot use sequences or subqueries. Also it can not reference LEVEL, PRIOR, or ROWNUM pseudocolumns.

[audit_column] -List of columns, delimited by comma, on which audit option will be identified. The default value is NULL, means that any column.

[handler_schema] -Username, which owns event handler procedure. The default value is NULL, means current schema.

[handler_module] -The procedure name, which handles the event. The default is NULL, means not to use event handler procedure.If the procedure is in package, then you should indicate the whole name, for example, DBMSOBJG.GET_TAB_SPACE.

[enable] -Values are true or false. Indicates if this policy should be enabled or disabled. The default is TRUE.

[statement_types]-Values are SELECT, INSERT, UPDATE, and DELETE(comma delimited list). Indicates which DML statement should be audited.The default is SELECT.

[audit_trail] -Value DBMS_FGA.DB_EXTENDED(default) indicates that  database should record sql text and bind variables also. Other available value is and DBMS_FGA.DB indicates that db should not save sql text and bind variables.

[audit_column_ops]– Value DBMS_FGA.ALL_COLUMNS indicates that all columns listed in AUDIT_COLUMN parameter must be referenced in order to create audit record. Other value is DBMS_FGA.ANY_COLUMNS(default) means that if any column will be referenced audit record will be created.

Note that parameters that are enclosed by ‘[‘ and ‘]’ are optional, others are mandatory.

For example:

–Assume we are connected as sys user.

BEGIN
    DBMS_FGA.ADD_POLICY(
        object_schema=>'HR'
        ,object_name=>'EMPLOYEES'
        ,policy_name=>'EMPTABLE_AUD'
        ,audit_column=>'SALARY, COMMISSION_PCT'
        ,enable=>FALSE
        ,statement_types=>'SELECT,DELETE,INSERT');
END;

Enabling FGA Policy

If the policy is already enabled, enabling it once more will not give you an error. DBMS_FGA.ENABLE_POLICY is a method which enables it.

For example:

BEGIN
   DBMS_FGA.ENABLE_POLICY(
        object_schema=>'HR'
        ,object_name=>'EMPLOYEES'
        ,policy_name=>'EMPTABLE_AUD');
END;

Disabling FGA Policy

DBMS_FGA.DISABLE_POLICY is a method for disabling policy.

For example:

BEGIN
   DBMS_FGA.DISABLE_POLICY(
        object_schema=>'HR'
        ,object_name=>'EMPLOYEES'
        ,policy_name=>'EMPTABLE_AUD');
END;

Dropping FGA Policy

DBMS_FGA.DROP_POLICY is a method for dropping policy.

For example:

BEGIN
   DBMS_FGA.DROP_POLICY(
        object_schema=>'HR'
        ,object_name=>'EMPLOYEES'
        ,policy_name=>'EMPTABLE_AUD');
END;

Identifying FGA Policies in the Database

DBA_AUDIT_POLICIES it a view which shows all FGA policies enabled in your database.

–Query this

SELECT object_schema||'.'||object_name as Audited_Object
       ,policy_column
       ,policy_name
       ,enabled
       ,audit_trail
FROM dba_audit_policies;

–Result

AUDITED_OBJECT | POLICY_COLUMN | ENABLED | AUDIT_TRAIL
-------------------------------------------------------
HR.EMPLOYEES   | SALARY        | YES     |DB+EXTENDED

Viewing FGA logs

DBA_FGA_AUDIT_TRAIL is a view which shows audited logs, that have been written to the database.

–Query this

SELECT db_user
       ,timestamp
       ,userhost
       ,sql_text
FROM dba_fga_audit_trail
WHERE policy_name='EMPTABLE_AUD'

–Result

DB_USER|TIMESTAMP            |USERHOST  |SQL_TEXT
-------------------------------------------------------------
SCOTT  |2/16/2011 10:05:16 AM|ADA0\SCOTT|select * from employees