|
"Logic is in the
eye of the logician." - Gloria Steinem |
Read
first then play the video:
DBA021(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
/
|
