Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Oracle Basic Audit

Oracle Forensics tips by Paul Wright

How to see the database audit.

SELECT * FROM dba_audit_trail;

As a view this could be rootkitted therefore better to get the data from the underlying base table which is  SYS.AUD$

SELECT userid, action#, STATEMENT, OBJ$NAME, To_Char (timestamp#, 'mm/dd/yyyy hh24:mi:ss')
FROM sys.aud$ ORDER BY timestamp# asc;

Need to find out the actions and statement numbers from a separate table to make sense of the output.


Oracle logging is done to the Database SYS.AUD$ though by default auditing is switched off except for mandatory auditing which is the shutdown, startup and SYS logons which are logged to the OS in this directory by default:


Basic database auditing using the DB_EXTENDED setting can be quite useful as it allows the capture of SQL commands issued by users of the database. This is better than redo which only captures the changes to the data not the actual SQL entered. This is how to capture the actual SQL ran by users.

SQL> show user
System altered.
SQL> show parameter audit_trail;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
audit_trail                          string      NONE

Need to restart!
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

C:\Documents and Settings\Paul>sqlplus sys/password@orcl as sysdba
SQL*Plus: Release - Production on Sun Jan 7 22:03:01 2007
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
SQL> show parameter audit_trail;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB_EXTENDED

SQL> audit select on dba_users by access whenever not successful;
Audit succeeded.

SQL> select * from sys.aud$;
no rows selected

SQL> conn scott/tiger@orcl


SQL> select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
SCOTT                          CONNECT                        NO  YES NO
SCOTT                          PUBLIC                         NO  YES NO
SCOTT                          RESOURCE                       NO  YES NO 

SQL> select username, password from dba_users;

select username, password from dba_users

ERROR at line 1:

ORA-00942: table or view does not exist

conn sys/password@orcl as sysdb

SQL> desc sys.aud$;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SESSIONID                                 NOT NULL NUMBER
 ENTRYID                                   NOT NULL NUMBER
 STATEMENT                                 NOT NULL NUMBER
 TIMESTAMP#                                         DATE
 USERID                                             VARCHAR2(30)
 USERHOST                                           VARCHAR2(128)
 TERMINAL                                           VARCHAR2(255)
 ACTION#                                   NOT NULL NUMBER
 RETURNCODE                                NOT NULL NUMBER
 OBJ$CREATOR                                        VARCHAR2(30)
 OBJ$NAME                                           VARCHAR2(128)
 AUTH$PRIVILEGES                                    VARCHAR2(16)
 AUTH$GRANTEE                                       VARCHAR2(30)
 NEW$OWNER                                          VARCHAR2(30)
 NEW$NAME                                           VARCHAR2(128)
 SES$ACTIONS                                        VARCHAR2(19)
 SES$TID                                            NUMBER
 LOGOFF$LREAD                                       NUMBER
 LOGOFF$PREAD                                       NUMBER
 LOGOFF$LWRITE                                      NUMBER
 LOGOFF$DEAD                                        NUMBER
 LOGOFF$TIME                                        DATE
 COMMENT$TEXT                                       VARCHAR2(4000)
 CLIENTID                                           VARCHAR2(64)
 SPARE1                                             VARCHAR2(255)
 SPARE2                                             NUMBER
 OBJ$LABEL                                          RAW(255)
 SES$LABEL                                          RAW(255)
 PRIV$USED                                          NUMBER
 SESSIONCPU                                         NUMBER
 NTIMESTAMP#                                        TIMESTAMP(6)
 PROXY$SID                                          NUMBER
 USER$GUID                                          VARCHAR2(32)
 INSTANCE#                                          NUMBER
 PROCESS#                                           VARCHAR2(16)
 XID                                                RAW(8)
 AUDITID                                            VARCHAR2(64)
 SCN                                                NUMBER
 DBID                                               NUMBER
 SQLBIND                                            CLOB
 SQLTEXT                                            CLOB

Now the auditor can select the actual SQL ran by the user.

SQL> select sqltext from sys.aud$;

select username, password from dba_users

The extra audit information recorded using Extended database audit would be very useful to an Oracle forensics incident handler trying to deal with a hacked server. However Extend audit is quite a performance intensive way to audit. In fact many DBA?s will not use audit at all due to the performance hit. This is why basic audit is currently disabled by default, by Oracle in 10g. 11g is planned to have audit switched on by default and the performance disadvantage has been greatly reduced. This means that Extended audit could be recorded which would be very useful especially if it was archived and then referred back to in the case of either a suspected incident or the disclosure of a new vulnerability so that access to this vulnerability could be backtracked. One problem is that database audit is insecure as it is easy to delete by a user with DBA privileges given that the audit trail is simply a table in that database. This is why many DBA?s log to the OS as it is more difficult to get to from the DB. Oracle will always Audit privileged connections and startup/shutdowns to the OS which is often called Mandatory Audit. However the attacker who has gained DBA could still use UTL_FILE to delete the OS based logs as described in the previous sections.

It would be preferable to be able to send audit to a separate log host that could NOT be accessed using the Oracle DBA credentials which may have been gained by the attacker. The need for a separate party to validate data in the DB is echoed by this paper describing a digital notarization service and the concerns over timestamp integrity.

This paper is very interesting. A step in this direction would be Oracle audit logged to a separate log host where it can be correlated with all the other logs. This is the subject of the section 6.6 and is at the heart of a secure architecture. The last of the four core technical tasks during a forensic investigation is.


This is an excerpt from the book "Oracle Forensics: Oracle Security Best Practices", by Paul M. Wright, the father of Oracle Forensics.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational