Transcript DB Audit Expert

DB Audit Expert v1.1
for Oracle
This presentation is for DB Audit Expert for Oracle version 1.1 which is included with DB Tools for
Oracle version 4.0 and later.
To learn more about DB Audit for version 2 (for Oracle, Sybase, Microsoft SQL Server and DB2)
please visit DB Audit home page
Copyright © 1999-2002 SoftTree Technologies, Inc.
Mission Statement

Oracle Server supports basic functions for database operations auditing.
However, this auditing covers only system and user activity and does not
cover data change auditing. There are no GUI tools available from Oracle
to provide easy interface for setting audit options and reviewing results of
the audit.

DB Audit offers comprehensive Oracle database auditing:
- DB Audit provides methods and tools for data change auditing.
- DB Audit provides an easy-to-use GUI for system access and user
activity auditing.
Part I
System and user
activity auditing
Start DB Audit and connect to
database
System audit options
Note: To get brief description of every menu item simply highlight it
then look in the status bar at the bottom of DB Audit screen.
System Audit Options Dialog
Notes: This dialog has two tabs. One tab is used for setting system audit options for
SQL statements, and another tab is used for various schema objects. The dialog
provides complete instructions for setting system audit options. To enable system
auditing you must set audit_trail = true in the INIT.ORA file. To view the results of
auditing you can use reports from the Report menu.
Volume of audit trail data can grow
very fast. That’s why from time to time
you may want to purge the data and/or
archive it to some history table or file
before purging.
Archiving to a table
Notes: Type name of a new table to which audit trail will be
copied or select name of an existing table. If the chosen table
does not exists DB Audit will create it and copy audit trail
data, otherwise the audit data will be appended to the table.
Similarly you can export audit trail records to an external
tab-delimited file. Use Export to File menu for this
operation.
To check enabled system audit
options
Notes: See next slide for details
Enabled Statements Audit
(Example Report)
Let’s setup some system audit
options for user DEMO
(continued)
Let’s pretend we are user
DEMO performing some
operations in the database
Let’s see how the auditing
works. Let’s run Audit Report
by User Session.
(continued)
Now let’s run Auditing Report
by Object Access.
Reporting options
Notes: Volume of audit trail data can be very large, that’s why for every
report DB Audit provides simple filtering options that you can use to narrow
the report output. If you don’t specify any options the DB Audit will display
everything that is available in the system audit trail.
Part II
Data change auditing.
Q: How does DB Audit know when your
data has been changed?
A: DB Audit installs triggers on the tables
you select to audit. Triggers are events
that automatically execute every time a
row in a given table is inserted, updated,
or deleted.
DB Audit automatically builds necessary
triggers and “mirror” audit tables for
intelligent data auditing without requiring
from user to know what objects are used
internally to perform the auditing functions.
Let’s setup data auditing for
several DEMO tables
Generated Objects
(continued)
Let’s pretend we are user
DEMO making some data
changes in the database
Let’s run the Data Change
Audit report
(continued)
Notes: DB Audit shows the Select Table dialog that lists only tables being
audited. You can select the desired table and then click OK to proceed.
Here is the report
•For DELETE, type is always OLD
•For INSERT, type is always NEW
•For UPDATE, there are always two rows
one having type OLD and another having
type NEW
Let’s check another table
•For DELETE, type is always OLD
•For INSERT, type is always NEW
•For UPDATE, there are always two rows
one having type OLD and another having
type NEW
Notes: For DELETE operations DB Audit captures and saves complete
deleted rows, for INSERT it saves complete inserted rows, and for
UPDATE, it saves both old and new rows no matter how many columns
were affected by the UPDATE. A “mirror” table has the same set of columns
as the audited table plus some additional columns for storing information
about who and when made the change.
As you could see DB Audit makes data change
auditing even simpler than 1,2,3. It features only
2 steps:
1st step is to select the desired tables for auditing
2nd step is to review the data change report.
You don’t even need to know the SQL to setup
and use these powerful features.
You purge, export, and archive data audit trail
exactly as you do it for the system audit trail. The
only exception is that system audit trail is stored
in a single table and the data audit trail is stored
in as many tables as many tables were selected
for the auditing. Again, these operations are
optional and you usually perform them if you
want to free some space in the database.
For more details please see slides in the
beginning of this presentation.
Here are other options for viewing and manipulating
report data
For more details on the available
system audit options please see your
Oracle Administration Guide. For
more details on using DB Audit please
see the DB Audit help system.
The End