SCRIPT: Generate AUDIT and NOAUDIT Statements for Current Audit Settings (Doc ID 287436.1)

 

APPLIES TO:

Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Schema Service - Version N/A and later
Information in this document applies to any platform.

GOAL

Provide a script which generates AUDIT commands for the current audit options as well as the NOAUDIT commands to negate them.  

SOLUTION

 

Abstract

This script generates AUDIT commands for the current audit options as well as the NOAUDIT commands to negate them.

Description

Since the NOAUDIT option only negates an AUDIT statement with the same syntax, it can be useful to know which AUDIT statements 
were issued in the past. We consider Statement and Privilege Auditing only, Object auditing is not administered in AUDIT$ but 
in the associated dictionary table, for example if you audit ON a table, the audit$ column of TAB$ is updated. Check DBA_OBJ_AUDIT_OPTS 
for additional Object Auditing options, for Statement and Privilege AUDIT this script generates two other scripts in turn: - audit_redo.sql 
This script contains the equivalents of previously issued AUDIT statements: they may not be the exact statements that were executed before, 
but still lead to the same state, for example: SQL> audit table by user1, user2; Audit succeeded. SQL> @getaud AUDIT TABLE BY user1 BY ACCESS ; 
AUDIT TABLE BY user2 BY ACCESS ; [...] Each BY <USER> clause generates a separate row in AUDIT$ table. - audit_undo.sql This script contains 
the NOAUDIT statements that can be used to negate the current audit options. SQL> audit grant any privilege by access whenever not successful; 
Audit succeeded. SQL> @getaud AUDIT GRANT ANY PRIVILEGE BY ACCESS WHENEVER NOT SUCCESSFUL ; NOAUDIT GRANT ANY PRIVILEGE WHENEVER NOT SUCCESSFUL ;

REFERENCES:

bug:6636804 WRONG OPTION# VALUE 229 IN C0801070.SQL FOR ON COMMIT REFRESH

bug 3883171, the query we use is partly derived from exu8aud, however
we join audit$ with stmt_audit_option_map and system_privilege_map,
exp / imp does not correctly transfer all audit options.

bug 3983762 was discovered while testing the script with various
AUDIT / NOAUDIT statements, got the '/* not possible */' (so it is a bug).

bug 3232631 the comment 'Apparently this syntax was never implemented' is not true

SCRIPT:

-- getaud.sql
--
-- Script to generate (equivalents *) of current AUDIT options
-- and the NOAUDIT statements to remove the current audit options.
--
-- It is intended for DBA's to get insight in current audit options,
-- backup / transfer purposes, can be used to manipulate audit needs
-- by selectively editing the resulting audit_redo.sql and audit_undo.sql
-- scripts and any other purpose I cannot immediately think of.
--
-- (C) Oracle Corporation 2004
-- Oracle Support Services, The Netherlands
--
-- *) Note for example that if you specify statement options or system
-- privileges that audit data definition language (DDL) statements, then Oracle
-- automatically audits by access regardless of whether you specify the
-- BY SESSION clause or BY ACCESS clause.
--
-- For statement options and system privileges that audit SQL statements other
-- than DDL, you can specify either BY SESSION or BY ACCESS. BY SESSION is
-- the default. The audit_redo.sql script will however include it for clarity.
-- Audit options are managed internally in AUDIT$ table only, both DBA views
-- DBA_STMT_AUDIT_OPTS and DBA_PRIV_AUDIT_OPTS select from this SYS table.
-- The success and failure columns have the following description from sql.bsq:
--
-- success : audit on success?
-- failure : audit on failure?
-- null = no audit, 1 = audit by session, 2 = audit by access
--
-- Only values 01,02,10,11,20,22 are valid.
-- Limitations:
--
-- * Only Statement and Privilege auditing options administered in AUDIT$ are
-- considered here.
set pages 1000
set echo off
set heading off
set feedback off
spool audit_redo.sql
select 'AUDIT '||m.name||decode(u.name,'PUBLIC',' ',' BY "'||u.name||'"')||
decode(nvl(a.success,0) + (10 * nvl(a.failure,0)),
1, ' BY SESSION WHENEVER SUCCESSFUL ',
2, ' BY ACCESS WHENEVER SUCCESSFUL ',
10,' BY SESSION WHENEVER NOT SUCCESSFUL ',
11,' BY SESSION ', -- default
20,' BY ACCESS WHENEVER NOT SUCCESSFUL ',
22,' BY ACCESS',' /* not possible */ ')||' ;'
"AUDIT STATEMENT"
FROM sys.audit$ a, sys.user$ u, sys.stmt_audit_option_map m
WHERE a.user# = u.user# AND a.option# = m.option#
and bitand(m.property, 1) != 1 and a.proxy# is null
and a.user# <> 0
UNION
select 'AUDIT '||m.name||decode(u1.name,'PUBLIC',' ',' BY "'||u1.name||'"')||
' ON BEHALF OF '|| decode(u2.name,'SYS','ANY',u2.name)||
decode(nvl(a.success,0) + (10 * nvl(a.failure,0)),
1,' WHENEVER SUCCESSFUL ',
2,' WHENEVER SUCCESSFUL ',
10,' WHENEVER NOT SUCCESSFUL ',
11,' ', -- default
20, ' WHENEVER NOT SUCCESSFUL ',
22, ' ',' /* not possible */ ')||';'
"AUDIT STATEMENT"
FROM sys.audit$ a, sys.user$ u1, sys.user$ u2, sys.stmt_audit_option_map m
WHERE a.user# = u2.user# AND a.option# = m.option# and a.proxy# = u1.user#
and bitand(m.property, 1) != 1 and a.proxy# is not null
UNION
select 'AUDIT '||p.name||decode(u.name,'PUBLIC',' ',' BY "'||u.name||'"')||
decode(nvl(a.success,0) + (10 * nvl(a.failure,0)),
1,' BY SESSION WHENEVER SUCCESSFUL ',
2,' BY ACCESS WHENEVER SUCCESSFUL ',
10,' BY SESSION WHENEVER NOT SUCCESSFUL ',
11,' BY SESSION ', -- default
20, ' BY ACCESS WHENEVER NOT SUCCESSFUL ',
22, ' BY ACCESS',' /* not possible */ ')||' ;'
"AUDIT STATEMENT"
FROM sys.audit$ a, sys.user$ u, sys.system_privilege_map p
WHERE a.user# = u.user# AND a.option# = -p.privilege
and bitand(p.property, 1) != 1 and a.proxy# is null
and a.user# <> 0
UNION
select 'AUDIT '||p.name||decode(u1.name,'PUBLIC',' ',' BY "'||u1.name||'"')||
' ON BEHALF OF '|| decode(u2.name,'SYS','ANY',u2.name)||
decode(nvl(a.success,0) + (10 * nvl(a.failure,0)),
1,' WHENEVER SUCCESSFUL ',
2,' WHENEVER SUCCESSFUL ',
10,' WHENEVER NOT SUCCESSFUL ',
11,' ', -- default
20, ' WHENEVER NOT SUCCESSFUL ',
22, ' ',' /* not possible */ ')||';'
"AUDIT STATEMENT"
FROM sys.audit$ a, sys.user$ u1, sys.user$ u2, sys.system_privilege_map p
WHERE a.user# = u2.user# AND a.option# = -p.privilege and a.proxy# = u1.user#
and bitand(p.property, 1) != 1 and a.proxy# is not null
;
spool off
spool audit_undo.sql
select 'NOAUDIT '||m.name||decode(u.name,'PUBLIC',' ',' BY "'||u.name||'"')||
decode(nvl(a.success,0) + (10 * nvl(a.failure,0)),
1,' WHENEVER SUCCESSFUL ',
2,' WHENEVER SUCCESSFUL ',
10,' WHENEVER NOT SUCCESSFUL ',
11,' ',
20, ' WHENEVER NOT SUCCESSFUL ',
22, ' ',' /* not possible */ ')||' ;'
"NOAUDIT STATEMENT"
FROM sys.audit$ a, sys.user$ u, sys.stmt_audit_option_map m
WHERE a.user# = u.user# AND a.option# = m.option#
and bitand(m.property, 1) != 1 and a.proxy# is null
and a.user# <> 0
UNION
select 'NOAUDIT '||m.name||decode(u1.name,'PUBLIC',' ',' BY "'||u1.name||'"')||
' ON BEHALF OF '|| decode(u2.name,'SYS','ANY',u2.name)||
decode(nvl(a.success,0) + (10 * nvl(a.failure,0)),
1,' WHENEVER SUCCESSFUL ',
2,' WHENEVER SUCCESSFUL ',
10,' WHENEVER NOT SUCCESSFUL ',
11,' ', -- default
20, ' WHENEVER NOT SUCCESSFUL ',
22, ' ',' /* not possible */ ')||';'
"AUDIT STATEMENT"
FROM sys.audit$ a, sys.user$ u1, sys.user$ u2, sys.stmt_audit_option_map m
WHERE a.user# = u2.user# AND a.option# = m.option# and a.proxy# = u1.user#
and bitand(m.property, 1) != 1 and a.proxy# is not null
UNION
select 'NOAUDIT '||p.name||decode(u.name,'PUBLIC',' ',' BY "'||u.name||'"')||
decode(nvl(a.success,0) + (10 * nvl(a.failure,0)),
1,' WHENEVER SUCCESSFUL ',
2,' WHENEVER SUCCESSFUL ',
10,' WHENEVER NOT SUCCESSFUL ',
11,' ', -- default
20, ' WHENEVER NOT SUCCESSFUL ',
22, ' ',' /* not possible */ ')||' ;'
"NOAUDIT STATEMENT"
FROM sys.audit$ a, sys.user$ u, sys.system_privilege_map p
WHERE a.user# = u.user# AND a.option# = -p.privilege
and bitand(p.property, 1) != 1 and a.proxy# is null
and a.user# <> 0
UNION
select 'NOAUDIT '||p.name||decode(u1.name,'PUBLIC',' ',' BY "'||u1.name||'"')||
' ON BEHALF OF '|| decode(u2.name,'SYS','ANY',u2.name)||
decode(nvl(a.success,0) + (10 * nvl(a.failure,0)),
1,' WHENEVER SUCCESSFUL ',
2,' WHENEVER SUCCESSFUL ',
10,' WHENEVER NOT SUCCESSFUL ',
11,' ', -- default
20, ' WHENEVER NOT SUCCESSFUL ',
22, ' ',' /* not possible */ ')||';'
"AUDIT STATEMENT"
FROM sys.audit$ a, sys.user$ u1, sys.user$ u2, sys.system_privilege_map p
WHERE a.user# = u2.user# AND a.option# = -p.privilege and a.proxy# = u1.user#
and bitand(p.property, 1) != 1 and a.proxy# is not null;
select unique
'-- Please correct the problem described in note 455565.1:'
||chr(13)||chr(10)||
'delete from sys.audit$ where user#=0 and proxy# is null;'
||chr(13)||chr(10)||'commit;'
from sys.audit$ where user#=0 and proxy# is null;
select '-- Please correct the problem described in bug 6636804:'
||chr(13)||chr(10)||
'update sys.STMT_AUDIT_OPTION_MAP set option#=234'
||chr(13)||chr(10)||' where name =''ON COMMIT REFRESH'';'
||chr(13)||chr(10)||'commit;'
from sys.STMT_AUDIT_OPTION_MAP where option#=229 and name ='ON COMMIT REFRESH';
select
'-- Please correct the problem described in bug 6124447:'
||chr(13)||chr(10)||
'noaudit truncate;'
from sys.audit$ where option#=155;
select unique '-- Please correct the problem described in note 1529792.1:'
||chr(13)||chr(10)||
'insert into javaobj$ select object_id,'
||chr(13)||chr(10)||'(select AUDIT$ from sys.javaobj$ where rownum=1)'
||chr(13)||chr(10)||' from dba_objects where object_type=''JAVA CLASS'''
||chr(13)||chr(10)||
' and status=''VALID'' and object_id not in (select obj# from sys.javaobj$);'
||chr(13)||chr(10)||'commit;'
from dba_objects
where object_type='JAVA CLASS'
and status='VALID'
and object_id not in (select obj# from sys.javaobj$);
select unique '-- Please correct the issue described in Note 2397585.1:'
||chr(13)||chr(10)||
'delete from SYS.AUDIT$ where OPTION# in (83,84);'
||chr(13)||chr(10)||'commit;'
from sys.audit$ where OPTION# in (83,84);
spool off
set heading on
set feedback on 

SAMPLE OUTPUT:

SQL> audit all;

Audit succeeded.

SQL> @getaud

AUDIT ALTER SYSTEM BY ACCESS ;
AUDIT SYSTEM AUDIT BY ACCESS ;
AUDIT CREATE SESSION BY ACCESS ;
AUDIT TABLE BY ACCESS ;
AUDIT CLUSTER BY ACCESS ;
AUDIT TABLESPACE BY ACCESS ;
AUDIT USER BY ACCESS ;
AUDIT ROLLBACK SEGMENT BY ACCESS ;
AUDIT TYPE BY ACCESS ;
AUDIT INDEX BY ACCESS ;
AUDIT SYNONYM BY ACCESS ;
AUDIT PUBLIC SYNONYM BY ACCESS ;
AUDIT VIEW BY ACCESS ;
AUDIT SEQUENCE BY ACCESS ;
AUDIT DATABASE LINK BY ACCESS ;
AUDIT PUBLIC DATABASE LINK BY ACCESS ;
AUDIT ROLE BY ACCESS ;
AUDIT DIMENSION BY ACCESS ;
AUDIT PROCEDURE BY ACCESS ;
AUDIT TRIGGER BY ACCESS ;
AUDIT PROFILE BY ACCESS ;
AUDIT DIRECTORY BY ACCESS ;
AUDIT MATERIALIZED VIEW BY ACCESS ;
AUDIT NOT EXISTS BY ACCESS ;
AUDIT SYSTEM GRANT BY ACCESS ;
AUDIT CONTEXT BY ACCESS ;

26 rows selected.

NOAUDIT ALTER SYSTEM ;
NOAUDIT SYSTEM AUDIT ;
NOAUDIT CREATE SESSION ;
NOAUDIT TABLE ;
NOAUDIT CLUSTER ;
NOAUDIT TABLESPACE ;
NOAUDIT USER ;
NOAUDIT ROLLBACK SEGMENT ;
NOAUDIT TYPE ;
NOAUDIT INDEX ;
NOAUDIT SYNONYM ;
NOAUDIT PUBLIC SYNONYM ;
NOAUDIT VIEW ;
NOAUDIT SEQUENCE ;
NOAUDIT DATABASE LINK ;
NOAUDIT PUBLIC DATABASE LINK ;
NOAUDIT ROLE ;
NOAUDIT DIMENSION ;
NOAUDIT PROCEDURE ;
NOAUDIT TRIGGER ;
NOAUDIT PROFILE ;
NOAUDIT DIRECTORY ;
NOAUDIT MATERIALIZED VIEW ;
NOAUDIT NOT EXISTS ;
NOAUDIT SYSTEM GRANT ;
NOAUDIT CONTEXT ;

26 rows selected.

SQL>

Disclaimer

EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE,
PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMS
ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS
THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR
RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR
EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR
OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE
NO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT
RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE. 

ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY
TIME WITHOUT NOTICE. 

Limitation of Liability

IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE,
DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN
CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE.
SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY.
ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU.

Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more