CODEWITHSUNDEEP
oracle-databaseaudit-trail
Frank Conry
Frank Conry

Reputation: 2718

Oracle Fine Grained Auditing for entire schema

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.

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

Upvotes: 1

Views: 4563

Answers (1)

HiltoN
HiltoN

Reputation: 328

Disable auditing:

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;
/

Enable auditing for entire schema:

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;
/

Upvotes: 3

Related Questions