管理审计（三）--精确审计

精细审计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.