Monitoring SQL activities of your USERS (Auditing)

Added  April4, 2006  Ammar Sajdi

This technique can be applied for Oracle 9i and above.

The DBMS_FGA (Fine Grain Auditing), is a package that allows you to audit user or application activity. The Normal Oracle Auditing feature has been around from the early days of Oracle RDBMS. The DBMS_FGA, give more control and allow you to get more detailed and precise auditing information.

The following code, for example, allows you to audit the EMP table that belongs to SCOTT whenever, the query condition include Where DEPTNO=10. 

The example illustrates the idea

BEGIN

 DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA =>'SCOTT',

                   OBJECT_NAME => 'EMP',

                   POLICY_NAME => 'AMMAR_AUDIT',

                   AUDIT_CONDITION => 'DEPTNO = 10',

                   ENABLE =>TRUE);

END;

/

SQL> select * from emp where deptno =10;

 EMPNO         ENAME     JOB      MGR         HIREDATE     SAL     Deptno
---------- ---------- --------- ---------- ---------   ---------- ----------
7782         CLARK     MANAGER    7839        09-JUN-81     2450    10

7839         KING P    RESIDENT               17-NOV-81     5000    10

7934         MILLER    CLERK      7782        23-JAN-82     1300    10

Then connect to SYSTEM in order to investigate if any auditing information is recorded. This is done by checking DBA_FGA_AUDIT_TRAIL

SQL> select session_id, db_user, SQL_TEXT from DBA_FGA_AUDIT_TRAIL;

SESSION_ID     DB_USER     SQL_TEXT
----------         ------------   --------------- ------------------------------
14630             SCOTT         select * from emp where deptno=10

Trick Question:  Will SELECT * FROM EMP   be audited?

The answer is YES if there are records with depto=10. This is because while the query is running, there is going to be at least one match where deptno=10 and this record shall trigger the auditing event.

Note the second statement is select * from emp without any Predicate (Where condition) and it is still audited

SESSION_ID DB_USER                        SQL_TEXT

---------- ------------------------------ ------------------------------

     14630 SCOTT                          select * from emp where deptno

                                           =10

     14632 SCOTT                          select * from emp

The following is a summary of the DBMS_FGA.ADD_POLICY procedure

DBMS_FGA.ADD_POLICY (

  OBJECT_SCHEMA => 'SCOTT',

  OBJECT_NAME => 'EMP',

  POLICY_NAME => 'AMMAR_AUDIT2',

  AUDIT_CONDITION => JOB=’’MANAGER’’,

  AUDIT_COLUMN => 'SAL',

  HANDLE_SCHEMA=> 'PALCO',

  HANDLER_MODULE=> 'PALCO_TRAIL',

  STATEMENT_TYPES => 'INSERT, UPDATE');

Audit Condition: The condition you provide where shall be evaluated against all retrieved records. If one of the records matches the condition, the auditing event is triggered. NOTE: the evaluation takes at parse time and not when the rows are actually retrieved. This can lead to FALSE POSITIVE , but not false negative

Audit_Column: The audit even occurs only if this column is included in the SELECT statement.

Handler: An optional even handler, a PL/SQL procedure that defines and additional actions that should be taken during auditing. For example insert records to another table etc…

Status : if you no longer want the auditing event to occur, you can disable the policy.

Summary of all DBMS_FGA  procedures

PROCEDURE ADD_POLICY

 Argument Name                  Type                    In/Out Default?

 ------------------------------ ----------------------- ------ --------

 OBJECT_SCHEMA                  VARCHAR2                IN     DEFAULT

 OBJECT_NAME                    VARCHAR2                IN

 POLICY_NAME                    VARCHAR2                IN

 AUDIT_CONDITION                VARCHAR2                IN     DEFAULT

 AUDIT_COLUMN                   VARCHAR2                IN     DEFAULT

 HANDLER_SCHEMA                 VARCHAR2                IN     DEFAULT

 HANDLER_MODULE                 VARCHAR2                IN     DEFAULT

 ENABLE                         BOOLEAN                 IN     DEFAULT

 STATEMENT_TYPES                VARCHAR2                IN     DEFAULT

 AUDIT_TRAIL                    BINARY_INTEGER          IN     DEFAULT

 AUDIT_COLUMN_OPTS              BINARY_INTEGER          IN     DEFAULT

PROCEDURE DISABLE_POLICY

 Argument Name                  Type                    In/Out Default?

 ------------------------------ ----------------------- ------ --------

 OBJECT_SCHEMA                  VARCHAR2                IN     DEFAULT

 OBJECT_NAME                    VARCHAR2                IN

 POLICY_NAME                    VARCHAR2                IN

PROCEDURE DROP_POLICY

 Argument Name                  Type                    In/Out Default?

 ------------------------------ ----------------------- ------ --------

 OBJECT_SCHEMA                  VARCHAR2                IN     DEFAULT

 OBJECT_NAME                    VARCHAR2                IN

 POLICY_NAME                    VARCHAR2                IN

PROCEDURE ENABLE_POLICY

 Argument Name                  Type                    In/Out Default?

 ------------------------------ ----------------------- ------ --------

 OBJECT_SCHEMA                  VARCHAR2                IN     DEFAULT

 OBJECT_NAME                    VARCHAR2                IN

 POLICY_NAME                    VARCHAR2                IN

 ENABLE                         BOOLEAN                 IN     DEFAULT