DBA Fundamentals

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:

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')

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> NOAUDIT ALL
/