Listing Active Statement/Privellege Audit Options in Oracle

Listing Active Statement Audit Options:


SELECT * FROM DBA_STMT_AUDIT_OPTS;

USER_NAME               AUDIT_OPTION         SUCCESS         FAILURE
--------------------    -------------------  ----------      ---------
JWARD                   SESSION              BY SESSION      BY SESSION
SWILLIAMS               SESSION              BY SESSION      BY SESSION
                        LOCK TABLE           BY ACCESS       NOT SET

Listing Active Privilege Audit Options:


SELECT * FROM DBA_PRIV_AUDIT_OPTS;

USER_NAME           PRIVILEGE            SUCCESS      FAILURE
------------------- -------------------- ---------   ----------
ALTER USER          BY SESSION           BY SESSION



Listing Active Object Audit Options for Specific Objects:


SELECT * FROM DBA_OBJ_AUDIT_OPTS
    WHERE OWNER = 'SCOTT' AND OBJECT_NAME LIKE 'EMP%';

OWNER OBJECT_NAME OBJECT_TY ALT AUD COM DEL GRA IND INS LOC ...
----- ----------- --------- --- --- --- --- --- --- --- --- ...
SCOTT EMP         TABLE     S/S -/- -/- A/- -/- S/S -/- -/- ...
SCOTT EMPLOYEE    VIEW      -/- -/- -/- A/- -/- S/S -/- -/- ...

Notice that the view returns information about all the audit options for the specified object. The information in the view is interpreted as follows:

The character “-” indicates that the audit option is not set.
The character “S” indicates that the audit option is set, BY SESSION.
The character “A” indicates that the audit option is set, BY ACCESS.
Each audit option has two possible settings, WHENEVER SUCCESSFUL and WHENEVER NOT SUCCESSFUL, separated by “/”. For example, the DELETE audit option for scott.emp is set BY ACCESS for successful delete statements and not set at all for unsuccessful delete statements.
Listing Default Object Audit Options:


SELECT * FROM ALL_DEF_AUDIT_OPTS;

ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE
--- --- --- --- --- --- --- --- --- --- --- --- ---
S/S -/- -/- -/- -/- S/S -/- -/- S/S -/- -/- -/- -/-


Listing Audit Records for the AUDIT SESSION Option:


SELECT USERNAME, LOGOFF_TIME, LOGOFF_LREAD, LOGOFF_PREAD,
    LOGOFF_LWRITE, LOGOFF_DLOCK
    FROM DBA_AUDIT_SESSION;

USERNAME   LOGOFF_TI LOGOFF_LRE LOGOFF_PRE LOGOFF_LWR LOGOFF_DLO
---------- --------- ---------- ---------- ---------- ----------
JWARD      02-AUG-91         53          2         24          0 
SWILLIAMS  02-AUG-91       3337        256        630          0 

Thanks
A Rahim Khan

Advertisements
  1. No trackbacks yet.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: