Topics: Auditing a
database
|
More Resources by
Google: |
|
|
|
|
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
|