DBA FUS‎ > ‎

Auditing of Commands

posted Sep 13, 2010, 6:30 AM by Sachchida Ojha
SELECT name
   FROM   stmt_audit_option_map
   WHERE  name not in (select audit_option from dba_stmt_audit_opts)
   AND    name not like 'SELECT%'
   AND    name not like 'INSERT%'
   AND    name not like 'UPDATE%'
   AND    name not like 'DELETE%'
   AND    name not like 'EXECUTE%'
   AND    name not like 'LOCK%'
   AND    name not in
               ('CREATE DIRECTORY','DROP DIRECTORY','GRANT LIBRARY',
                'WRITEDOWN DBLOW','READUP DBHIGH','WRITEUP DBHIGH',
                'WRITEDOWN','READUP','WRITEUP','EXISTS','SUMMARY',
                'NETWORK','DEBUG CONNECT USER','DEBUG CONNECT ANY',
                'CREATE CLUSTER','CREATE DATABASE LINK','CREATE DIMENSION',
                'CREATE LIBRARY','CREATE PROCEDURE','CREATE ROLE',
                'CREATE SEQUENCE','CREATE TABLE','CREATE TRIGGER',
                'CREATE TYPE','CREATE VIEW')
   AND    decode(name, 'TABLESPACE', 3,
                       'PUBLIC DATABASE LINK', 2,
                       'PUBLIC SYNONYM', 2,
                       'ROLLBACK SEGMENT', 3,
                       'TABLESPACE', 3,
                       'USER', 3, 100) >
                   (SELECT COUNT(*)
                    FROM   dba_stmt_audit_opts
                    WHERE  audit_option LIKE '%'||name||'%')
  UNION ALL
    select name from system_privilege_map
                where name not in
                  (select privilege from dba_priv_audit_opts)
               and name not in (select name from stmt_audit_option_map)
                and name not in ('EXTENDS TYPE','ALTER OPERATOR')
                and name not like '%ROLE%'
                and name not like '%SECURITY%'
                and name not like '%EXECUTE%'
                and name not like '%RULE%';
Comments