Find the cause of excessive records in AUDIT table Oracle

Find the cause of Excessive records in AUDIT Oracle

Find the way which statements is causing high audit records

set echo on
col username for a20
break on username
select USERNAME, action, action_name, count(*) from dba_audit_trail
group by USERNAME, action, action_name order by 1,2, 4 desc;

Note:
1. You will get the count which action is causing the high records in Audit table.
2. You can send disable that option if not needed with NOAUDIT command.

Find total no of rows in Audit table

select count(*) TOTAL from sys.aud$;

Check the AUDIT option enabled

SELECT * FROM DBA_STMT_AUDIT_OPTS order by user_name,audit_option;
SELECT * FROM DBA_PRIV_AUDIT_OPTS order by user_name,privilege;
SELECT * FROM DBA_OBJ_AUDIT_OPTS order by owner,object_name,object_type;
SELECT * FROM ALL_DEF_AUDIT_OPTS;

Generate the current auditing data with NOAUDIT command in Oracle
You can disable as you got information from above it will give you complete list of NOAUDIT commands.

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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.