everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

DBA Fundamentals

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
<< Previous

Chapter # 23

Next >>


 

Auditing a database

 

Introduction

As a DBA, you are responsible for auditing the database due to a suspicious transaction on certain table. An unknown user is deleting records and you have been assigned the task to investigate and find out who that person is. Your job�s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

 

Starting auditing

Stopping auditing

Reading from the AUDIT TRAIL table

Truncating the AUD$ table

Using the AUDIT_TRAIL view

Listing the AUDIT_TRAIL parameter

Setting the AUDIT_TRAIL parameter

Shutting down and startup a database using SPFILE

Using the AUD$ table

Auditing an auditor

Auditing who deletes a record

Viewing the AUD$ table

Stopping all auditing trail

Commands:

TRUNCATE TABLE aud$

SHOW PARAMETER

ALTER SYSTEM SET audit_trail=db SCOPE=spfile

SHUTDOWN IMMEDIATE

CONNECT system/manager AS SYSDBA

STARTUP

AUDIT delete ON sys.aud$

AUDIT DELETE ON BY ACCESS WHENEVER SUCCESSFUL

NOAUDIT ALL

 

Hands-on

In this exercise you will learn how to start and stop auditing, how to read from the audit trail table and more...

Now, connect as the SYSTEM/MANAGER user.
SQL> CONNECT system/manager AS SYSDBA
 

Clean audit table
Truncate the database audit table. The database audit table was truncated so any old audited information will be deleted for this exercise.
SQL> TRUNCATE TABLE aud$
/

Audit parameter

View the AUDIT_TRAIL parameter value.
SQL> SHOW PARAMETER audit_trail
The NONE value indicates that the audit process was not activated.

Change the AUDIT_TRAIL parameter to db in the Server Parameter File.
SQL> ALTER SYSTEM SET audit_trail=db SCOPE=spfile
/
 

Activate auditing
Shutdown and startup the database. Use the default Server Parameter File (SPFILE). Notice that when the AUDIT_TRAIL option is set to db, your audit trail information is stored in the AUD$ table owned by sys. And also, when AUDIT_TRAIL is set to Operating System, your audit trail information will be stored in the directory named by the AUDIT_FILE_DEST parameter. In this example, we are storing data into the AUD$ table in the Oracle database.
SQL> SHUTDOWN IMMEDIATE
SQL> CONNECT system/manager AS SYSDBA
SQL> STARTUP

View the AUDIT_TRAIL value again.
SQL> SHOW PARAMETER audit_trail
Notice that this time, the value was changed to db.

Query the AUD$ table.
SQL> SELECT * FROM aud$
/
Notice that there are no records in the audit table.
 

Check what to audit
First, let's audit who ever deleted any records from the aud$ table. That should be our first target since an auditor may cover his/her own actions by deleting records from the AUD$ table.
SQL> AUDIT delete ON sys.aud$
/
Notice that this is very important. You should be sure that no user can remove records from the audit logs undetected.

Audit who ever deleted a record from the EMP table. Since the 'audit trail process' generates a lot of records you should be very specific in regards to your auditing.
SQL> AUDIT DELETE
ON oracle.emp
BY ACCESS
WHENEVER SUCCESSFUL
/
 

Check auditing
Connect as the ORACLE user.
SQL> CONNECT oracle/learning

Insert a record into the EMP table.
SQL> INSERT INTO emp
VALUES (9999,'KAZ','RESEARCH',7788,'10-MAR-89',1000,null,30)
/
SQL> COMMIT
/

Now, delete the KAZ employee.
SQL> DELETE FROM emp
WHERE empno = 9999
/
SQL> COMMIT
/

Connect again as the SYSTEM/MANAGER user.
SQL> CONNECT system/manager AS SYSDBA

View the SYS.AUD$ table to display the date and time who (username), and what (statement of an action) was done.
SQL> SELECT
TO_CHAR(timestamp#,'DD-MON-YYYY HH24:MI:SS')

as "Date and Time", userid, name "Action by user"
FROM sys.aud$ JOIN sys.audit_actions
ON action = action#
/
 

Stop auditing
Since auditing takes a lots of disk space be sure to stop auditing when it is not needed. Try, stop auditing.
SQL> NOAUDIT ALL
/
 

Reset audit parameter
Now, reset the AUDIT_TRAIL parameter value to NONE and then shutdown and startup the database. Shutdown and startup the database by using the defualt Server Parameter File (SPFILE). We took the AUDIT_TRAIL parameter back to it's original value so you can do this exercise over if you want.
SQL> ALTER SYSTEM SET audit_trail=NONE SCOPE=spfile
/
SQL> SHUTDOWN IMMEDIATE
SQL> CONNECT system/manager AS SYSDBA
SQL> STARTUP
 

Questions:

Q: How do you activate auditing a database?

Q: How do you start auditing?

Q: How do you stop auditing?

Q: How do you read from the AUDIT_TRAIL table?

Q: When and why do you truncate the AUD$ table?

Q: How do you view the AUDIT_TRAIL parameter value?

Q: How do you set the AUDIT_TRAIL parameter value?

Q: How do you auditing an auditor?

Q: Audit all the users who delete a record or records from the EMP table?

Q: What do the following SQL statements do?

SQL> TRUNCATE TABLE aud$
/


SQL> ALTER SYSTEM SET audit_trail=db SCOPE=spfile
/


SQL> AUDIT delete ON sys.aud$
/


SQL> AUDIT DELETE
ON oracle.emp
BY ACCESS
WHENEVER SUCCESSFUL
/


SQL> SELECT
TO_CHAR(timestamp#,'DD-MON-YYYY HH24:MI:SS')

as "Date and Time", userid, name "Action by user"
FROM sys.aud$ JOIN sys.audit_actions
ON action = action#
/


SQL> NOAUDIT ALL
/
 

 

     Reviews and Templates for FrontPage
     

Copyright � everythingOracle.bizhat.com 2006 All Rights Reserved.