Versions 8.1.7 , 9.0.1 , 9.2.0 , 10.1.0 , 10.2.0 , 11.1.0,11.2.0                   

You can run TFSAUDIT.SQL when you have the following privileges:

SELECT on DBA_OBJ_AUDIT_OPTS, DBA_STMT_AUDIT_OPTS,
DBA_AUDIT_TRAIL and DBA_PRIV_AUDIT_OPTS.

PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text 
editors, e-mail packages, and operating systems handle text formatting (spaces, 
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.

This script will show you what is being audited in the database and will 
provide the audit trail. 
 

Note.1020945.6 How to Setup Auditing Note.167293.1 Some examples about auditing and output of auditing Note.41800.1 Quick Reference to Auditing Information Note.222807.1 How To Audit GRANT ANY PRIVILEGE Or GRANT ANY ROLE

SET ECHO off 
REM NAME:   TFSAUDIT.SQL 
REM USAGE:"@path/tfsaudit" 
REM -------------------------------------------------------------------------- 
REM REQUIREMENTS: 
REM    SELECT ON DBA_OBJ_AUDIT_OPTS, DBA_STMT_AUDIT_OPTS, DBA_AUDIT_TRAIL 
REM    and DBA_PRIV_AUDIT_OPTS
REM -------------------------------------------------------------------------- 
REM AUTHOR:  
REM    Geert De Paep    -    Oracle Belgium       
REM -------------------------------------------------------------------------- 
REM PURPOSE: 
REM    see what is being audited in the database, and to see the audit_trail 
REM ------------------------------------------------------------------------- 
REM DISCLAIMER: 
REM    This script is provided for educational purposes only. It is NOT  
REM    supported by Oracle World Wide Technical Support. 
REM    The script has been tested and appears to work as intended. 
REM    You should always run new scripts on a test instance initially. 
REM -------------------------------------------------------------------------- 
REM Main text of script follows: 

col user_name for a12 heading "User name"
col proxy_name for a12 heading "Proxy name"
col privilege for a30 heading "Privilege"
col user_name for a12 heading "User name" 
col audit_option format a30 heading "Audit Option"
col timest format a13 
col userid format a8 trunc 
col obn format a10 trunc 
col name format a13 trunc 
col sessionid format 99999 
col entryid format 999 
col owner format a10 
col object_name format a10 
col object_type format a6 
col priv_used format a15 trunc 
break on user_name
set pages 1000

set pause 'Return...' 

pause Press return to see the audit related parameters...

col name for a20 
col display_value for a20

SELECT NAME ,DISPLAY_VALUE 
FROM V$PARAMETER 
WHERE UPPER(NAME) LIKE UPPER('%audit%') 
ORDER BY NAME,ROWNUM
/





prompt 
prompt System auditing options across the system and by user

select * from sys.dba_stmt_audit_opts
order by user_name, proxy_name, audit_option 
/

pause Press return to see auditing options on all objects...

select owner, object_name, object_type, 
       alt,aud,com,del,gra,ind,ins,loc,ren,sel,upd,ref,exe 
from sys.dba_obj_audit_opts 
where  
   alt !='-/-' or aud !='-/-' or com !='-/-' 
or del !='-/-' or gra !='-/-' or ind !='-/-' 
or ins !='-/-' or loc !='-/-' or ren !='-/-' 
or sel !='-/-' or upd !='-/-' or ref !='-/-' or exe !='-/-' 
/ 
 
pause Press return to see audit trail... Note that the query returns the audit data for the last day only
 
col acname format a12 heading "Action name" 
select username userid, to_char(timestamp,'dd-mon hh24:mi') timest , 
  action_name acname, priv_used, obj_name obn, ses_actions 
from sys.dba_audit_trail
where timestamp>sysdate-1
order by timestamp 
/ 

pause Press return to see system privileges audited across the system and by user...

select * from dba_priv_audit_opts
order by user_name, proxy_name, privilege
/

SQL> @tfsaudit

System auditing options across the system and by user

User name    Proxy name   Audit Option                   SUCCESS    FAILURE
------------ ------------ ------------------------------ ---------- ----------
SCOTT                     ALTER ANY CLUSTER              BY ACCESS  BY ACCESS
                          ALTER ANY DIMENSION            BY ACCESS  BY ACCESS
                          ALTER ANY INDEX                BY ACCESS  BY ACCESS
...

129 rows selected.

Press return to see auditing options on all objects...    

OWNER      OBJECT_NAM OBJECT ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE
---------- ---------- ------ --- --- --- --- --- --- --- --- --- --- --- --- ---
CTXSYS     SYS_IOT_OV TABLE   /   /   /   /   /   /   /   /   /   /   /   /   /
           ER_24695

CTXSYS     SYS_IOT_OV TABLE   /   /   /   /   /   /   /   /   /   /   /   /   /
           ER_24786        

Press return to see audit trail... 

USERID   TIMEST        Action name  PRIV_USED       OBN        SES_ACTIONS
-------- ------------- ------------ --------------- ---------- -------------------
SYSTEM   20-jun 11:07  SYSTEM GRANT GRANT ANY ROLE
SYSTEM   20-jun 11:07  SYSTEM GRANT
SYSTEM   20-jun 11:07  SYSTEM GRANT   
...

19 rows selected.

Press return to see system privileges audited across the system and by user...

User name    Proxy name   Privilege                      SUCCESS    FAILURE
------------ ------------ ------------------------------ ---------- ----------
SCOTT                     ADMINISTER DATABASE TRIGGER    BY ACCESS  BY ACCESS
                          ADMINISTER RESOURCE MANAGER    BY ACCESS  BY ACCESS
                          ALTER ANY CLUSTER              BY ACCESS  BY ACCESS
...

118 rows selected. 

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.

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.