DBMS FGA

DBMS_FGA is a PL/SQL package used to define FGA (Fine Grain Auditing) on objects.

Examples

Define a simple policy called DUP_ACCESS:

BEGIN
  DBMS_FGA.ADD_POLICY(
         OBJECT_SCHEMA => 'SCOTT',
         OBJECT_NAME   => 'EMP',
         POLICY_NAME   => 'DUP_ACCESS');
END;
/

Another example:

Define policy named FGA_EMP to audit the DBA's (oracle user) attempts to access to salary (emp.sal column) information:

SQL> exec DBMS_FGA.ADD_POLICY( -
>         object_schema   => 'SCOTT', -
>         object_name     => 'EMP', -
>         policy_name     => 'FGA_EMP', -
>         audit_column    => 'SAL', -
>         audit_condition => 'SYS_CONTEXT(USERENV,OS_USER) = oracle ', -
>         statement_types => 'SELECT', -
>         audit_trail     => DBMS_FGA.DB);
PL/SQL procedure successfully completed.

Sample select to violate the policy:

SQL> SELECT sal FROM EMP WHERE rownum < 1;
no rows selected

Query audit trail:

SQL> SELECT policy_name, object_name, statement_type, os_user, db_user FROM dba_fga_audit_trail;
POLICY_NAM OBJECT_NAM STATEME OS_USER    DB_USER
---------- ---------- ------- ---------- ----------
FGA_EMP    EMP        SELECT  oracle     SCOTT

Remove FGA policy:

SQL> exec DBMS_FGA.DROP_POLICY('SCOTT', 'EMP', 'FGA_EMP');
PL/SQL procedure successfully completed.

Clear FGA audit trail:

SQL> TRUNCATE TABLE fga_log$;
Table truncated.