整个模式的Oracle细粒度审核 [英] Oracle Fine Grained Auditing for entire schema

问题描述

我需要记录我拥有的Oracle数据库(非生产型，正在运行Oracle 11g企业版)中运行的所有sql查询.我要这样做的方法是使用Oracle的细粒度审核，因为它会记录确切的查询并在db中进行，这对我来说非常有用.但是，从文档(http://docs.oracle.com/cd/B19306_01/network.102/b14266/cfgaudit.htm#i1011783)看来，您需要指定表名，而没有默认名称.

I need to log all sql queries running in an Oracle database I have (non-production, running Oracle 11g enterprise). How I would like to do this, is to use Oracle's Fine Grained Auditing, since it logs the exact query and does so in the db, which is much more useful for me. However , it seems from the documentation (http://docs.oracle.com/cd/B19306_01/network.102/b14266/cfgaudit.htm#i1011783) that you are required to specify a table name, with no default.

是否可以使用Oracle细粒度审核来审核模式中的所有查询?

Is it possible to use Oracle fine grained auditing to audit all queries on a schema?

推荐答案

禁用审核:

begin
    for c1 in (select * from dba_audit_policies) loop
        dbms_fga.drop_policy (
            object_schema => c1.object_schema,
            object_name   => c1.object_name,
            policy_name   => c1.policy_name);
    end loop;
end;
/

为整个架构启用审核:

begin
    for c1 in (select * from all_tables where owner = '&schema') loop
        dbms_fga.add_policy (
            object_schema   => c1.owner,
            object_name     => c1.table_name,
            statement_types => 'SELECT,UPDATE,DELETE,INSERT',
            policy_name     => c1.table_name
        );
    end loop;
end;
/

这篇关于整个模式的Oracle细粒度审核的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！