9i的细粒度审计
表的查询语句用普通触发器是检测不到的,除非开启数据库审计,但是从ORACLE9i开始,提供了一个DBMS_FGA包,可以在线对单个的表进行审计并查询审计资料,这就是细粒度审计。但是这个包的审计过程要求数据库运行在CBO优化模式下,如果不是,可能会有意想不到的结果,这就要求对审计的表进行分析。
下面将就这个包的使用做简单的介绍,这个包共有四个过程,先介绍一下该包中的第一个过程:
PROCEDURE add_policy(object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
policy_name IN VARCHAR2,
audit_condition IN VARCHAR2 := '1=1',
audit_column IN VARCHAR2 := NULL,
handler_schema IN VARCHAR2 := NULL,
handler_module IN VARCHAR2 := NULL,
enable IN BOOLEAN := TRUE);
object_schema:要审计的用户的名称,默认是本用户
object_name:要审计的对象的名称
policy_name:这次审计的策略名称,每次审计都有一个名称,区别于别的审计策略
audit_condition:审计条件(谓语动词),默认全部,如规定a=1,那么当返回的结果集中显式包含(指定该字段)或隐式包含(不指定该字段)a=1的行的时候,该查询语句将被审计。
audit_column:表示审计那些列,默认全部,如果指定列,那么只有select或者是where指定该列的时候才被审计,如果是select * 也算是隐式的指定了该列,因为*本身是包含任意列。
注:如果同时指定了audit_column与audit_condition,那么必须都满足的情况下,才能被审计,也就是说,这两个条件是一个并的关系而不是或的关系。
handler_schema:我们还可以规定在策略执行的时候,执行一个指定用户的存储过程,这里是存储过程的所有者
handler_module:指定这个策略执行的时候,执行的存储过程的名称
enable:确定审计策略是否马上生效。
很明显,该包的作用就是增加一个审计策略,object_schema、object_name与policy_name唯一确定一个审计策略,光看policy_name不能唯一确定一个策略名,不同的对象,策略名可以一样。
我们再来看第二个过程
PROCEDURE drop_policy(object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
policy_name IN VARCHAR2);
object_schema、object_name与policy_name的含义和第一个包一样,只要指定这三个参数,就可以唯一的删除一个指定的审计策略。
第三、四个过程enable_policy、disable_policy第二个过程相似,就是根据object_schema、object_name与policy_name来禁止或者开启审计策略,这样对于需要多次使用同样的审计策略是很有用的,没有必要删除后重新创建,不用的时候,只需要disable就可以了,用的时候,只要enable就可以了
如果开启了handler_schema与handler_module,则需要一个handler_schema用户下的存储过程,假定名称是sp_chk_mytable,这个过程可以类似为:
CREATE PROCEDURE sp_chk_mytable (
p_object_schema VARCHAR2,
p_object_name VARCHAR2,
p_policy_name VARCHAR2) AS
BEGIN
INSERT INTO audit$proc (login_user,audit_time,ip_address,audsid,
object_schema, object_name, policy_name )
VALUES (ora_login_user,sysdate,sys_context('USERENV','IP_ADDRESS'),
userenv('SESSIONID'),p_object_schema,
p_object_name, p_policy_name );
EXCEPTION
WHEN OTHERS THEN
sp_write_log('Audit Exception:'||SQLERRM);
END sp_chk_mytable;
当然,在创建该过程之前还需要建立一个audit$proc的表,因为并不是我们讨论的重点,这个就不多说了,存储过程可以根据自己的要求来改写,以便获取信息。
以下我们将用一个例子来说明DBMS_FGA包的使用,在这个例子里面,并不包含执行指定的存储过程,也就是说,仅仅对表的审计做一个示例。
首先,我们假定我们存在表Test,包含记录为
SQL> select * from manager.test;
A B
-- --
1 2
2 3
3 4
4 5
5 6
6 7
6 rows selected
分析该表,让其使用CBO优化模式
SQL> analyze table test compute statistics;
Table analyzed
检查以前的审计策略与审计日志
SQL> select t.object_schema,t.object_name,t.policy_name from dba_audit_policies t;
OBJECT_SCHEMA OBJECT_NAME POLICY_NAME
------------------------------ ------------------------------ ------------------------------
SQL>select t.object_schema,t.object_name,t.policy_name,t.sql_text from Dba_Fga_Audit_Trail t;
OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SQL_TEXT
------------------------- --------------------- --------------------- --------------------------------------
dba_audit_policies用来存放审计策略,其基表是sys.fga$,而Dba_Fga_Audit_Trail则用来存放审计日志,即执行了那些SQL语句,其基表是sys. fga_log$,其日志可以手工删除。通过以上的查询,我们也可以看到,以前没有审计策略,也没有审计日志
现在,我们在manager.Test表增加一个审计策略
SQL> BEGIN
2 dbms_fga.add_policy( object_schema => 'MANAGER',
3 object_name => 'test',
4 policy_name => 'chk_test',
5 audit_condition => 'a = 1',
6 audit_column => 'b',
7 enable => TRUE );
8 END;
9 /
PL/SQL procedure successfully completed
我们再查询dba_audit_policies,就可以发现这条审计策略了。
SQL> select t.object_schema,t.object_name,t.policy_name,t.enabled from dba_audit_policies t;
OBJECT_SCHEMA OBJECT_NAME POLICY_NAME ENABLED
------------------------------ ------------------------------ ------------------------------ -------
MANAGER TEST CHK_TEST YES
那么就让我们看看该审计策略怎么生效,假定我们执行如下的一些语句,这些语句都有很普遍的代表性:
SQL> select count(*) from test where a=2;
COUNT(*)
------------
1
可以看到,该查询返回的结果集中显然没有a=1的行,所以,该查询不能被审计。
SQL> select count(*) from test where a=1;
COUNT(*)
----------
1
有人说这个语句包含了a=1,该被审计了吧,其实不然,我们的审计条件中还有一个是审计列必须要有b,count(*)不是说是包含了b,所以该语句还是没有被审计。
SQL> select count(*) from test where b=2;
COUNT(*)
----------
1
这个语句被审计了,因为已经包含了列b在where中,而b=2正好是a=1的行,所以a=1是被隐式包含的。
SQL> select * from test where a=1;
A B
-- --
1 2
该语句肯定被审计,所有列肯定包含审计列b,谓语条件正好是a=1。
SQL> select a from test where a=2;
A
--
2
这个语句肯定就不被审计了,典型的什么都不满足
SQL> select b from test where b=2;
B
--
2
这个语句也被审计,为什么呢?这个语句包含审计列,而且隐式包含了a=1(因为a=1与b=2是同一行)
SQL> select b from test where b=4;
B
--
4
这个语句没有被审计,因为虽然满足了审计列的条件,但是没有显式或隐式包含a=1,但是如果在您的环境中,这个语句如果被审计了,就请您确信是否分析过表,该查询是否是使用的CBO优化计划。
综合以上结果,以上被审计的语句应当是3个,检查一下了
SQL>select t.object_schema,t.object_name,t.policy_name,t.sql_text from Dba_Fga_Audit_Trail t;
OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SQL_TEXT
------------------------- --------------------- ------------------ -------------------------------------
MANAGER TEST CHK_TEST select * from test where a=1
MANAGER TEST CHK_TEST select b from test where b=2
MANAGER TEST CHK_TEST select count(*) from test where b=2
对于越来越多的审计记录,我们必须手工维护,删除没有参考价值的记录,我们可以运行如下查询来删除审计记录(需要delete any table的权限或在sys下执行):
delete from sys.fga_log$
or
delete from Dba_Fga_Audit_Trail
到这里,大家总该明白了怎么样审计表的Select语句,同普通审计与DML触发器一样,对过多的表进行审计,将会严重影响性能。但是,在特定的情况下,如果想跟踪一个表的Select语句已便于优化,也还是可以的。
10g的细粒度审计
在 Oracle 9i Database 下,这个策略只能审计 SELECT 语句。然而,在 Oracle Database 10g 中,您可以扩展它,使它包含 INSERT、UPDATE 和 DELETE。您可以通过指定一个新的参数来实现这个目的:
statement_types => 'INSERT, UPDATE, DELETE, SELECT'
这个参数将在所有包括的语句类型上启用审计。您甚至可能考虑为每种语句类型创建单独的策略,这将允许您随意地启用和禁用策略 — 尤其是,控制审计线索的创建,以管理它们占用的空间。
参考
http://otn.oracle.com/global/cn/oramag/webcolumns/2003/techarticles/nanda_fga.html
http://otn.oracle.com/global/cn/pub/articles/nanda_fga_pt2.html
http://otn.oracle.com/global/cn/pub/articles/nanda_fga_pt3.html