Understanding on Fine Grained Auditing (FGA)

 Follow the steps for understanding on FGA.

LOGIN FORM SYS or SYSTEM USER to sqlplus

Creating Fine Grained Auditing Policy on Scott emp table

begin
dbms_fga.add_policy(
object_schema=>'SCOTT',
object_name=>'EMP',
policy_name=>'EMP_SAL_CHK'
 ,audit_condition=>'deptno=10'
 ,audit_column => 'sal'
 ,enable=>TRUE
 ,Statement_types=>'SELECT,INSERT,UPDATE,DELETE'
);
end;

RUN BELOW QUERIES ONE BY ONE TO GENERATE AUDIT

run queries from scott user

QUERY NO 1
SQL> select * from emp where deptno=10;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450
      7839 KING       PRESIDENT            17-NOV-81       5000
      7934 MILLER     CLERK           7782 23-JAN-82       1300

QUERY NO 2
SQL> select * from emp where sal>6000;

no rows selected

QUERY NO 3
SQL> select * from emp where sal=950;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- --------------------
      7900 JAMES      CLERK           7698 03-DEC-81        950                30

QUERY NO 4
SQL> select ename , job from emp where empno=7839;

ENAME      JOB
---------- ---------
KING       PRESIDENT

QUERY NO 5
SQL>  select ename , job, sal from emp where empno=7839;

ENAME      JOB              SAL
---------- --------- ----------
KING       PRESIDENT       5000

QUERY NO 6
SQL> select ename , job, deptno from emp where empno=7839;

ENAME      JOB           DEPTNO
---------- --------- ----------
KING       PRESIDENT         10

QUERY NO 7
SQL> select ename , job, deptno from emp where deptno=10;

ENAME      JOB           DEPTNO
---------- --------- ----------
CLARK      MANAGER           10
KING       PRESIDENT         10
MILLER     CLERK             10

QUERY NO 8
SQL> select ename , job, sal from  emp where deptno=20;

ENAME      JOB              SAL
---------- --------- ----------
SMITH      CLERK            800
JONES      MANAGER         2975
SCOTT      ANALYST         3000
ADAMS      CLERK           1100
FORD       ANALYST         3000

QUERY NO 9
SQL>  select ename , job, sal from  emp where deptno=10;

ENAME      JOB              SAL
---------- --------- ----------
CLARK      MANAGER         2450
KING       PRESIDENT       5000
MILLER     CLERK           1300


FROM SYS USER CHECK AUDIT QUERIES

SQL> select sql_text from dba_fga_audit_trail;
or

SQL> select lsqltext from fga_log$;

LSQLTEXT
---------------------------------
select * from emp where deptno=10
select ename , job, sal from emp where empno=7839
select ename , job, sal from  emp where deptno=10


REASONyou can see that ONLY QUERY no  1,5,9  were Audited.

WHY

The above 1,5,9 queries only audited because we selected sal column and
result of rows belongs to deptno 10.

additionally, if you avoid sal column in select list  & select any column of emp table and records still belongs to deptno 10 than audit will not be genrated because your audit column is SAL in policy. So both condition must be matched.

One more understanding

audit_condition=>'deptno=10'

above parameter in audit policy doesn't mean that this value present in where clause. it means that audit generate when SAL column selected its records belongs to department no 10.



Dropping  Policy

from sys or system user

begin
dbms_fga.drop_policy(
object_schema=>'SCOTT',
object_name=>'EMP',
policy_name=>'EMP_SAL_CHK'
);
end;