Hands-On 21 (Auditing a database)

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 21 (Auditing a database)
-- Preparation
set echo on
connect system/manager as sysdba
SET linesize 1000 pagesize 55
SET SQLPROMPT 'SQL> '
COL name FORMAT a30
col username format a10
col password format a8
col account_status format a15
col default_tablespace format a20
col temporary_tablespace format a20
col profile format a20
pause

--Start

CLEAR SCR
-- 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.

pause


CONNECT system/manager AS SYSDBA

pause

CLEAR SCR
-- Truncate the database audit table.
-- The database audit table was truncated so
-- any old audited information will be deleted for
-- this exercise.

pause


TRUNCATE TABLE aud$
/

pause

CLEAR SCR
-- View the AUDIT_TRAIL parameter value.

pause

SHOW PARAMETER audit_trail

-- The NONE value indicates that the audit process 
-- was not activated.

pause

CLEAR SCR
-- Change the AUDIT_TRAIL parameter to db.

pause


ALTER SYSTEM SET audit_trail=db SCOPE=spfile
/

pause

CLEAR SCR
-- 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.

pause
SHUTDOWN IMMEDIATE
CONNECT system/manager AS SYSDBA
STARTUP
pause

CLEAR SCR
-- View the AUDIT_TRAIL value again.

pause


SHOW PARAMETER audit_trail

-- Notice that this time, the value was changed to db.

pause

CLEAR SCR
-- Query the AUD$ table.

pause


SELECT * FROM aud$
/

-- Notice that there are no records in the audit table.

pause

CLEAR SCR
-- 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. 

pause

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.

pause

CLEAR SCR
-- 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.

pause

AUDIT DELETE 
ON iself.emp
BY ACCESS
WHENEVER SUCCESSFUL
/

pause

CLEAR SCR
-- Connect as the ISELF user.

pause

CONNECT iself/schooling

pause

CLEAR SCR
-- Insert a record into the EMP table.

pause

INSERT INTO emp
VALUES (9999,'KAZ','RESEARCH',7788,'10-MAR-89',1000,null,30)
/

COMMIT
/

pause

CLEAR SCR
-- Now, delete the KAZ employee.

pause

DELETE FROM emp
WHERE empno = 9999
/

COMMIT
/

pause

CLEAR SCR
-- Connect again as the SYSTEM/MANAGER user.

pause

CONNECT system/manager AS SYSDBA

pause


CLEAR SCR
-- View the SYS.AUD$ table to display the date and time
-- who (username), and what (statement of an action) was
-- done.

pause


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#
/

pause

CLEAR SCR
-- Since auditing takes a lots of disk space be sure to
-- stop auditing when it is not needed.

-- Stop auditing.

pause

NOAUDIT ALL
/

pause

CLEAR SCR
-- 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 we can do this exercise over and over.

pause

ALTER SYSTEM SET audit_trail=NONE SCOPE=spfile
/
SHUTDOWN IMMEDIATE
CONNECT system/manager AS SYSDBA
STARTUP
pause

CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause