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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

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.

SELECT * from AUDIT_ACTIONS;

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:

/u01/app/oracle/admin/orcl/adump.

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
USER is "SYS"
SQL> ALTER SYSTEM SET audit_trail=DB_EXTENDED SCOPE=SPFILE;
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 10.2.0.3.0 - 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

Connected.

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$;

SQLTEXT
--------------------------------------------------------------------------------
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.

http://portal.acm.org/citation.cfm?id=1142487&dl=ACM&coll=GUIDE&CFID=15151515
&CFTOKEN=6184618

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