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
/