|
Category :
Database
Resources -> Database Administration -> Troubleshooting & Solutions
DB Version
:
Oracle 8i /
Oracle 9i
OS Details :
Sun Solaris9
Some of the most common
requirements include: How do I keep track of packages or functions or
stored procedures that were executed between two timestamps? or find
out as to what tables had users select or insert or update or delete
data from it?
One of the approaches would be to
use auditing. With auditing you can very easily answer all of the above
questions. Give below are a few examples on using the audit feature to
keep track of object access.
(1) Edit the init file and
Set AUDIT_TRAIL=DB for your instance. This will require a database
restart.
(2) Make sure the auditing
objects are installed. Check for objects like audit_actions,
audit_option_map and dba_audit_trail exists. If not use cataudit.sql
file to set it up. On unix, this file can be found under $ORACLE_HOME/rdbms/admin
directory.
(3) Enable auditing on
executions by doing this:
-- This example audits DML and
execute statements by user test
SQL> audit select table, insert table, update table, delete table,
execute procedure by test;
-- The following example enables
auditing at the object level
SQL> audit select table,
insert table, update table, delete table, execute procedure by test;
(4) Once enabled, you can
query the dba_audit_object view joined to the dba_objects view or the
dba_audit_trail view to get the details on who accessed what object at
what time.
(5) Auditing can be
disabled by using the noaudit command:
-- This example disables auditing
on DML and execute statements by user test
SQL> noaudit select table, insert table, update table, delete
table, execute procedure by test;
-- The following example disables
auditing at the object level
SQL> audit select table,
insert table, update table, delete table, execute procedure by test;
Purging Audit Records
from the Audit Trail
After auditing is
enabled for some time, you may want to delete records from the
database audit trail both to free audit trail space and to facilitate
audit trail management.
For example, to
delete all audit records from the audit trail,
enter the following statement:
DELETE FROM SYS.AUD$;
Alternatively, to delete all audit records from the audit trail generated as a result of auditing the
table emp, enter the following statement:
DELETE FROM SYS.AUD$
WHERE obj$name='EMP';
If audit
trail information must be archived for historical purposes, you can
copy the relevant records to a normal database table (for example,
using INSERT INTO
table
SELECT ... FROM SYS.AUD$ ...)
or export the audit trail table to an operating system file.
Only the user
SYS,
a user who has the DELETE ANY TABLE
privilege, or a user to whom SYS
has granted DELETE
privilege on SYS.AUD$
can delete records from the database audit trail.
If you
disable auditing and no longer need the audit trail views, delete them
by connecting to the database as SYS
and running the script file
CATNOAUD.SQL. On UNIX, this
file can be found under $ORACLE_HOME/rdbms/admin directory. |
