全称是Fine-Grained Audit ，是Audit的一种特殊方式。使用FGA只要调用Oracle的包DBMS_FGA.ADD_POLICY创建一些policy(审计策略)就行，每个policy只能针一个表或视图。
在审计策略生效之前，必须对表进行分析，因为只有在CBO模式，DBMS_FGA才能正确的工作。
在 Oracle 9i Database 下，这个策略只能审计SELECT语句。在 Oracle Database 10g 中，select,insert,update,delete都可以记录到,当然也可以添加一些筛选条件只监测某些特殊的操作。
1、DBMS_FGA
SQL> desc dbms_fga
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
object_schema --schema的名字，表或视图的拥有者
object_name --对象名，表或视图的名字
policy_name --审计策略名字，它和数据库中其他对象一样，需要有一个不重复，唯一的名字
audit_condition --筛选条件，比如可以选择哪些符合条件的操作被记录
audit_column --表中的某一列，可以只记录对表中某一列的操作，如果不指定表示审计所有的列
handler_schema --是下面的handler_module的拥有者，其实也只能是创建policy的用户，而上面的object_schema可以是任意用户
handler_module --可以是一个存储过程或函数，但监测到任何一条符合条件的操作时执行它
enable --true 或false表示policy是开启或关闭状态，如果是false表示不进行审计
statement_types --表示哪些操作将被审计，可以填上select，insert，update，delete中的一个或几个，默认为select
audit_trail --DBMS_FGA.DB表示记录将被保存到数据库中，DBMS_FGA.EXTENDED表示如果sql语句中带有绑定变量也会被记录下来；SYS.DBMS_FGA.XML+SYS.DBMS_FGA.EXTENDED表示记录保存成xml文件
audit_column_opts --这个选项其实只有在audt_column中指定了某列时才起作用，它有any_columns，all_columns两个选项假如表中有eno，ename两列，并在audit_column中指定了这两列，那么选any_columns表示只要操作其中的任意一列都将被记录，而这里指定all_columns的话是说只有一个sql语句同时操作了这两列才被记录
2、示例1：
创建一个审计策略
SQL> begin
dbms_fga.add_policy (
object_schema=>'scott',
object_name=>'emp',
policy_name=>'fga_emp',
audit_column =>'empno',
audit_condition =>'empno >= 7800'
);
end; 2 3 4 5 6 7 8 9
10 /
PL/SQL procedure successfully completed.
测试验证：
[oracle@odd adump]$ sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 11 12:01:13 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from emp where empno>7800;
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
SAL COMM DEPTNO
---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81
5000 10
7844 TURNER SALESMAN 7698 08-SEP-81
1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87
1100 20
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
SAL COMM DEPTNO
---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81
950 30
7902 FORD ANALYST 7566 03-DEC-81
3000 20
7934 MILLER CLERK 7782 23-JAN-82
1300 10
6 rows selected.
SQL> insert into emp values('8100','SUBO','CLERK','7900',to_Date('20111201','yyyymmdd'),'1300','','10');
1 row created.
SQL> commit;
Commit complete.
SQL> delete from emp where empno='8100';
1 row deleted.
SQL> rollback;
Rollback complete.
SQL> delete from emp where empno='8100';
1 row deleted.
SQL> rollback;
Rollback complete.
SQL> update emp set sal=8100 where empno=8100;
1 row updated.
SQL> rollback;
Rollback complete.
SQL> exit
查看审计结果
[oracle@odd adump]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 11 13:07:46 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from dba_fga_audit_trail;
SESSION_ID TIMESTAMP
---------- ------------
DB_USER
------------------------------------------------------------
OS_USER
--------------------------------------------------------------------------------
USERHOST
--------------------------------------------------------------------------------
CLIENT_ID
--------------------------------------------------------------------------------
ECONTEXT_ID
--------------------------------------------------------------------------------
EXT_NAME
--------------------------------------------------------------------------------
OBJECT_SCHEMA
------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
POLICY_NAME SCN
------------------------------------------------------------ ----------
SQL_TEXT
--------------------------------------------------------------------------------
SQL_BIND
--------------------------------------------------------------------------------
COMMENT$TEXT
--------------------------------------------------------------------------------
STATEMENT_TYPE
--------------
EXTENDED_TIMESTAMP
---------------------------------------------------------------------------
PROXY_SESSIONID GLOBAL_UID
--------------- ----------------------------------------------------------------
INSTANCE_NUMBER OS_PROCESS TRANSACTIONID STATEMENTID
--------------- -------------------------------- ---------------- -----------
ENTRYID
----------
1421 11-NOV-12
SCOTT
oracle
odd
oracle
SCOTT
EMP
FGA_EMP 387507
select * from emp where empno>7800
SELECT
11-NOV-12 12.01.29.074305 PM +08:00
0 3298 7
1
从审计结果来看，只有select被审计到，说明默认只审计select语句。
3、示例2
将上面的审计disable掉
SQL> begin
dbms_fga.disable_policy (
object_schema=>'scott',
object_name=>'emp',
policy_name=>'fga_emp'
);
end; 2 3 4 5 6 7
8 /
PL/SQL procedure successfully completed.
创建一个新策略
SQL> begin
dbms_fga.add_policy (
object_schema=>'scott',
object_name=>'emp',
policy_name=>'fga_emp2',
audit_column =>'empno',
audit_condition =>'empno >= 7800',
STATEMENT_TYPES=>'INSERT,SELECT,UPDATE,DELETE'
);
end; 2 3 4 5 6 7 8 9 10
11 /
PL/SQL procedure successfully completed.
测试
[oracle@odd adump]$ sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 11 13:17:17 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> insert into emp values('8200','SUBO','CLERK','7900',to_Date('20111201','yyyymmdd'),'1300','','10');
1 row created.
SQL> commit;
Commit complete.
SQL> delete from emp where empno='8100';
1 row deleted.
SQL> rollback;
Rollback complete.
SQL> update emp set sal=8100 where empno=8100;
1 row updated.
SQL> rollback;
Rollback complete.
查看审计结果
SQL> select POLICY_NAME,sql_text from dba_fga_audit_trail;
POLICY_NAME
------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
FGA_EMP
select * from emp where empno>7800
FGA_EMP2
insert into emp values('8200','SUBO','CLERK','7900',to_Date('20111201','yyyymmdd
'),'1300','','10')
FGA_EMP2
delete from emp where empno='8100'
FGA_EMP2
update emp set sal=8100 where empno=8100
由此可知审计策略已生效
删除fga_emp2策略
SQL> begin
dbms_fga.drop_policy (
object_schema=>'scott',
object_name=>'emp',
policy_name=>'fga_emp2'
);
end; 2 3 4 5 6 7
8 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM DBA_AUDIT_POLICIES;
OBJECT_SCHEMA
------------------------------------------------------------
OBJECT_NAME
------------------------------------------------------------
POLICY_NAME
------------------------------------------------------------
POLICY_TEXT
--------------------------------------------------------------------------------
POLICY_COLUMN
------------------------------------------------------------
PF_SCHEMA
------------------------------------------------------------
PF_PACKAGE
------------------------------------------------------------
PF_FUNCTION ENABLE SEL
------------------------------------------------------------ ------ ------
INS UPD DEL AUDIT_TRAIL POLICY_COLUMN_OPTIONS
------ ------ ------ ------------------------ ----------------------
SCOTT
EMP
FGA_EMP
empno >= 7800
EMPNO
NO YES
NO NO NO DB+EXTENDED ANY_COLUMNS
策略fga_emp2已被删除。
4、示例3
创建一个策略
SQL> begin
dbms_fga.add_policy (
object_schema=>'scott',
object_name=>'emp',
policy_name=>'fga_emp2',
audit_column=>'deptno',
audit_condition=>'deptno = 10',
AUDIT_TRAIL=>SYS.DBMS_FGA.XML+SYS.DBMS_FGA.EXTENDED,
audit_column_opts=>SYS.DBMS_FGA.ANY_COLUMNS
);
end;
/
PL/SQL procedure successfully completed.
测试
[oracle@databak ~]$ sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 19 15:57:18 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from emp where deptno=10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ------------ ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7839 KING PRESIDENT 17-NOV-81 5000
10
7934 MILLER CLERK 7782 23-JAN-82 1300
10
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ------------ ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7839 KING PRESIDENT 17-NOV-81 5000
10
7934 MILLER CLERK 7782 23-JAN-82 1300
10
查看审计结果
SQL> show parameter audit_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /data/oracle10g/admin/ccod/adump
[oracle@databak adump]$ ll -lth
总计 40K
-rw-r--r-- 1 oracle oinstall 1.5K 11-19 16:00 ora_3213452996.xml
-rw-r----- 1 oracle oinstall 636 11-19 15:57 ora_15736.aud
[oracle@databak adump]$ cat ora_3213452996.xml
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-10_2.xsd">
select * from emp where deptno=10
select * from emp where deptno=10
验证结果ok！
祝好运！
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/20553601/viewspace-749430/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
【statspack级别设置】
请登录后发表评论
登录
全部评论
北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区
广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员
违法和不良信息举报电话:18101376593