精细审计Fine Grained Auditing (FGA)
用于审计用户在特定数据行或列上的SQL操作
精细审计是通过DBMS_FGA包实现。
(1)建立FGA策略
04:01:14 SQL> exec dbms_fga.add_policy(object_schema=>'scott',-
04:01:15 > object_name=>'emp',policy_name=>'chk_emp',-
04:01:49 > audit_condition =>'deptno=20',audit_column =>'sal',-
04:02:40 > statement_types =>'update,select');
PL/SQL procedure successfully completed.
(2)执行与FGA策略相关的sql操作
04:04:18 SQL> select sal from scott.emp where deptno=20;
SAL
----------
1000
3175
2550
1300
3200
04:04:21 SQL> select ename,sal from scott.emp where deptno=20;
ENAME             SAL
---------- ----------
SMITH            1000
JONES            3175
CLARK            2550
ADAMS            1300
FORD             3200
04:04:37 SQL> select ename,sal from scott.emp where deptno=10;
ENAME             SAL
---------- ----------
SCOTT            2000
KING             5100
04:04:40 SQL> update scott.emp set sal=sal*2 where deptno=20;
5 rows updated.
04:04:59 SQL> conn scott/tiger
Connected.
04:05:15 SQL> select ename,sal from emp where deptno=20;
ENAME             SAL
---------- ----------
SMITH            2000
JONES            6350
CLARK            5100
ADAMS            2600
FORD             6400
(3)查看FGA审计结果
04:06:29 SQL> col db_user for a10
04:06:36 SQL> col sql_text for a50
04:06:46 SQL> select db_user ,sql_text from dba_fga_audit_trail;
DB_USER    SQL_TEXT
---------- --------------------------------------------------
SYSTEM     select sal from scott.emp where deptno=20
SYSTEM     select ename,sal from scott.emp where deptno=20
SYSTEM     update scott.emp set sal=sal*2 where deptno=20
SCOTT      select ename,sal from emp where deptno=20
(4)禁止精细审计
04:08:08 SQL> exec dbms_fga.disable_policy(-
04:08:21 > object_schema=>'scott',object_name=>'emp',-
04:08:49 > policy_name=>'chk_emp');
PL/SQL procedure successfully completed.
(5)激活精细审计
04:10:33 SQL> exec dbms_fga.enable_policy(-
04:10:40 > object_schema=>'scott',object_name=>'emp',-
04:10:51 >  policy_name=>'chk_emp');
PL/SQL procedure successfully completed.
(6)删除FGA策略
04:11:52 SQL> exec dbms_fga.drop_policy(-
04:11:54 > object_schema=>'scott',object_name=>'emp',-
04:11:59 >  policy_name=>'chk_emp');
PL/SQL procedure successfully completed.
(7)删除精细审计的结果
04:12:43 SQL> delete from sys.fga_log$;
4 rows deleted.

更多oracle视频教程:http://crm2.qq.com/page/portalpage/wpa.php?uin=800060152&f=1&ty=1&aty=0&a=&from=6