One of the requirements in modern organizations where data integrity and security is of utmost importance, is the use of Audit Trails. Oracle 10g provides very powerful auditing features which can be enabled in very simple steps without writing complex trigger mechanism etc. You have the choice to store the audit trails in the database or on operating system by .xml files.
1. Check if auditing is enabled by querying for the parameter audit_trail
SQL> SHOW PARAMETER AUDIT
NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string C:\ORACLE\PRODUCT\10.2.0\ADMIN
\DB10G\ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE
Auditing is disable by default. You can enable it by setting the static parameter audit_trail to the following:
AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }
* none or false – Auditing is disabled.
* db or true – Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
* db,extended – As db, but the SQL_BIND and SQL_TEXT columns are also populated.
* xml- Auditing is enabled, with all audit records stored as XML format OS files.
* xml,extended – As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
* os- Auditing is enabled, with all audit records directed to the operating system’s audit trail.
SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1248600 bytes
Variable Size 71303848 bytes
Database Buffers 213909504 bytes
Redo Buffers 2945024 bytes
Database mounted.
Database opened.
2. Now test this newly enabled auditing feature using a test user and a test table
CONNECT sys/password AS SYSDBA
CREATE USER audit_test IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;
GRANT connect TO audit_test;
GRANT create table, create procedure TO audit_test;
3. Enable Auditing on the user audit_test
CONNECT sys/password AS SYSDBA
AUDIT ALL BY audit_test BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY audit_test BY ACCESS;
AUDIT EXECUTE PROCEDURE BY audit_test BY ACCESS;
These options audit all DDL and DML, along with some system events.
* DDL (CREATE, ALTER & DROP of objects)
* DML (INSERT UPDATE, DELETE, SELECT, EXECUTE).
* SYSTEM EVENTS (LOGON, LOGOFF etc.)
4. Create a test table called QIIB
CREATE TABLE AUDIT_TEST.QIIB
(
ID NUMBER,
Name VARCHAR2(55 BYTE),
Dept VARCHAR2(55 BYTE),
Address VARCHAR2(255 BYTE),
Title VARCHAR2(255 BYTE)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
## Insert some data into this table. I generally use TOAD to perform these small inserts, its more convenient to use when you are just doing testing, rather than writing long insert statements. Watch out for my post coming up on very useful features of toad for Oracle DBA’s which can maximize your efficiency as a dba.
5. Now that we are done creating user and the table and doing inserts. It is time to find out what audit trails have been generated. Please note I have enable XML, extended auditing. This generates audit trails in your adump directory. Location of adump is usually $ cd $ORACLE_BASE/admin/adump
$ sqlplus “/as sysdba”
COLUMN db_user FORMAT A10
COLUMN object_schema FORMAT A10
COLUMN object_name FORMAT A10
COLUMN extended_timestamp FORMAT A35
SELECT db_user,
extended_timestamp,
object_schema,
object_name,
action,
sql_bind,
sql_text
FROM v$xml_audit_trail
WHERE object_schema = ‘AUDIT_TEST’
ORDER BY extended_timestamp
Ofcourse you query like:
Select * from v$xml_audit_trail;
This is one of the simplest method to enable audting. Ofcourse there are more complex scenarios where selective auditing or fine-grained auditing needs to be implemented. More on FGA later.