iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 |

 

Lesson 23

"Logic is in the eye of the logician." - Gloria Steinem

 

Read first then play the video:

   DBA-VIDEO -Auditing a database

   

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 iself.emp
              BY ACCESS
              WHENEVER SUCCESSFUL
/


Check auditing
Connect as the ISELF user.
SQL> CONNECT iself/schooling

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

 

"Everything has been figured out, except how to live." - Jean-Paul Sartre (1905-1980)

 

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 iself.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
/