Oracle微粒审计详解，可以看到操作数据库每一语句，对大型项目很有用

Oracle使用大量不同的审计方法来监控使用何种权限，以及访问哪些对象。审计

不会防止使用这些权限，但可以提供有用的信息，用于揭示权限的滥用和误用。

下表中总结了Oracle数据库中不同类型的审计。


审计类型说明


1.语句审计

按照语句类型审计SQL语句，而不论访问何种特定的模式对象。也可以在数据库

中指定一个或多个用户，针对特定的语句审计这些用户



2.权限审计

审计系统权限，例如CREATE TABLE或ALTER INDEX。和语句审计一样，权限审计

可以指定一个或多个特定的用户作为审计的目标



3.模式对象审计

审计特定模式对象上运行的特定语句(例如，DEPARTMENTS表上的UPDATE语句)。

模式对象审计总是应用于数据库中的所有用户



4.细粒度的审计

根据访问对象的内容来审计表访问和权限。使用程序包DBMS_FGA来建立特定表上

的策略

.......详细概念见附件《Oracle审计详解.rar》





下面是我们项目中一个简单的FGA的使用列子：



第一步：开启Oracle的审计功能

exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_cdmx',policy_name =>'dangan_1',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_cdyw',policy_name =>'dangan_2',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_cdzm',policy_name =>'dangan_3',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_chrzb',policy_name =>'dangan_4',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_daczrz',policy_name =>'dangan_5',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_daddmx',policy_name =>'dangan_6',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_dafm',policy_name =>'dangan_7',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_daglxxb',policy_name =>'dangan_8',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_dajdmxjl',policy_name =>'dangan_9',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_dajdmxjl_gzk',policy_name =>'dangan_10',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_dajdyw',policy_name =>'dangan_11',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_dast',policy_name =>'dangan_12',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_dast_gzk',policy_name =>'dangan_13',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_dawzxx',policy_name =>'dangan_14',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_dawzxx_gzk',policy_name =>'dangan_15',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_daxdqd',policy_name =>'dangan_16',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_daxdqd_gzk',policy_name =>'dangan_17',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_daxdyw',policy_name =>'dangan_18',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_daxhmxjl',policy_name =>'dangan_19',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_daxhmxjl_gzk',policy_name =>'dangan_20',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_daxhyw',policy_name =>'dangan_21',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_dayd',policy_name =>'dangan_22',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_ddqd',policy_name =>'dangan_23',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_ddyw',policy_name =>'dangan_24',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_dzsmyw',policy_name =>'dangan_25',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_hdyw',policy_name =>'dangan_26',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_jdiys',policy_name =>'dangan_27',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_jnml',policy_name =>'dangan_28',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_jxry',policy_name =>'dangan_29',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_jxry_gzk',policy_name =>'dangan_30',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_jzyjgdyw',policy_name =>'dangan_31',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_jzyjlsh',policy_name =>'dangan_32',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_jzyjmx',policy_name =>'dangan_33',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_lyys',policy_name =>'dangan_34',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_xhys',policy_name =>'dangan_35',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_ywdagxb',policy_name =>'dangan_36',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_ywsl',policy_name =>'dangan_37',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpu_nrgh',policy_name =>'dangan_38',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpu_smyxwj',policy_name =>'dangan_39',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_dabmlsh',policy_name =>'dangan_40',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_jzgdmx',policy_name =>'dangan_41',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_bcdrxx',policy_name =>'dangan_42',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_jzthjl',policy_name =>'dangan_43',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'tpa_zzcdxx',policy_name =>'dangan_44',statement_types

=>'insert,update,delete');
exec dbms_fga.add_policy(object_schema =>'DANGAN',object_name

=>'aqua_explain_2246325',policy_name =>'dangan_45',statement_types

=>'insert,update,delete');





第二步：查询FGA的微粒审计（当然在这之前你操作了你监听已经开启FGA的表）

select session_id SessionID,timestamp 时间,object_schema

SCHECMA,sql_text 执行语句,sql_bind 绑定值,transactionid 事物ID
from dba_fga_audit_trail where  timestamp>to_timestamp('2011-7-19

15:29:18','yyyy-mm-dd hh24:mi:ss')
and object_schema='DANGAN';

查询出来的结果见附件《FGA查询结果.rar》



第三步：关闭FGA微粒审计功能（当然是自愿的）

exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_cdmx',policy_name =>'dangan_1')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_cdyw',policy_name =>'dangan_2')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_cdzm',policy_name =>'dangan_3')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_chrzb',policy_name =>'dangan_4')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_daczrz',policy_name =>'dangan_5')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_daddmx',policy_name =>'dangan_6')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_dafm',policy_name =>'dangan_7')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_daglxxb',policy_name =>'dangan_8')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_dajdmxjl',policy_name =>'dangan_9')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_dajdmxjl_gzk',policy_name =>'dangan_10')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_dajdyw',policy_name =>'dangan_11')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_dast',policy_name =>'dangan_12')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_dast_gzk',policy_name =>'dangan_13')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_dawzxx',policy_name =>'dangan_14')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_dawzxx_gzk',policy_name =>'dangan_15')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_daxdqd',policy_name =>'dangan_16')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_daxdqd_gzk',policy_name =>'dangan_17')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_daxdyw',policy_name =>'dangan_18')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_daxhmxjl',policy_name =>'dangan_19')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_daxhmxjl_gzk',policy_name =>'dangan_20')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_daxhyw',policy_name =>'dangan_21')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_dayd',policy_name =>'dangan_22')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_ddqd',policy_name =>'dangan_23')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_ddyw',policy_name =>'dangan_24')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_dzsmyw',policy_name =>'dangan_25')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_hdyw',policy_name =>'dangan_26')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_jdiys',policy_name =>'dangan_27')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_jnml',policy_name =>'dangan_28')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_jxry',policy_name =>'dangan_29')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_jxry_gzk',policy_name =>'dangan_30')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_jzyjgdyw',policy_name =>'dangan_31')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_jzyjlsh',policy_name =>'dangan_32')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_jzyjmx',policy_name =>'dangan_33')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_lyys',policy_name =>'dangan_34')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_xhys',policy_name =>'dangan_35')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_ywdagxb',policy_name =>'dangan_36')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_ywsl',policy_name =>'dangan_37')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpu_nrgh',policy_name =>'dangan_38')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpu_smyxwj',policy_name =>'dangan_39')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_dabmlsh',policy_name =>'dangan_40')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_jzgdmx',policy_name =>'dangan_41')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_bcdrxx',policy_name =>'dangan_42')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_jzthjl',policy_name =>'dangan_43')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'tpa_zzcdxx',policy_name =>'dangan_44')
exec dbms_fga.drop_policy(object_schema =>'DANGAN',object_name

=>'aqua_explain_2246325',policy_name =>'dangan_45')

好了，我也不多说了，用法用例附件中都有，自己需要就下载去吧