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
/