Oracle
Database Auditing allows to profile user database activities.
Auditing
is the monitoring, profiling and recording of selected user database actions.
The
relevant records can be stored in the db audit trail or in OS files.
Auditing
can be done on some individual actions, such as the type of SQL statement
executed, or on combinations of data that can include the user name,
application, time, and so on.
It's
possible to audit both successful and failed activities.
In
Oracle 11g auditing is turned on by default.
In
Oracle 10g you have to enable it, but nothing is audited by default once it is
active.
Oracle
audit important parameters:
[oracle@localhost
~]$ sqlplus / as sysdba
SQL*Plus:
Release 11.1.0.6.0 -
Copyright
(c) 1982, 2007, Oracle. All rights reserved.
Connected
to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
show parameter audit
NAME
TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest
string /u01/app/oracle/admin/MYDBINST/a
dump
audit_sys_operations
boolean FALSE
audit_syslog_level
string
audit_trail
string DB
SQL>
audit_file_dest
- specifies the OS directory where the audit trail is written, when the
AUDIT_TRAIL initialization parameter is set to os, xml, or xml,extended
audit_trail
- enables or disables database auditing.
Values
(for parameter audit_trail):
none
- database auditing will be disabled.
os
- will enable db auditing and directs all audit records to the operating
system's audit trail.
db
- enables database auditing and directs all audit records to the database audit
trail (the SYS.AUD$ table).
db,extended
- enables database auditing and directs all audit records to the database audit
trail (the SYS.AUD$ table). In addition, populates the SQLBIND and SQLTEXT CLOB
columns of the SYS.AUD$ table.
xml
- enables database auditing and writes all audit records to XML format OS
files.
xml,extended
- enables database auditing and prints all columns of the audit trail, including
SqlText and SqlBind values.
In
case audit_trail=db or audit_trail=db,extended is enabled, the audit trail is
stored in the SYS.AUD$ table.
You
can query it directly or via related dictionary views:
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS
Example:
--
The db,extend setting will populate the SYS.AUD$ and DBA_AUDIT_TRAIL sql_text
field.
-- Note, in Oracle 10g release 1, the db_extended was used in place of
db,extended.
SQL>
ALTER SYSTEM SET audit_trail=db,extended SCOPE=SPFILE;
System
altered.
SQL>
shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total
System Global Area 1887350784 bytes
Fixed
Size
2145504 bytes
Variable Size
1241514784 bytes
Database Buffers 419430400
bytes
Redo
Buffers
224260096 bytes
Database mounted.
Database opened.
SQL> AUDIT SELECT TABLE, UPDATE TABLE BY myuser;
Audit
succeeded.
SQL>
conn myuser/mypass
Connected.
SQL> select * from sometab;
AAA
----------
123
SQL>
select
* from dba_audit_trail where username = 'MYUSER';
select
*
from dba_audit_trail
where username = 'MYUSER'
and owner = 'MYUSER';
Audit
command format:
AUDIT
{statement_option|privilege_option} [by user] [by {session|access}] [ whenever
{successful|unsuccessful}];
The difference between BY SESSION and BY ACCESS is that when you specify BY
SESSION Oracle will try to merge multiple audit entries into one record when
the session and the action audited match. BY SESSION causes Oracle to write a
single record for all SQL statements of the same type issued in the same
session. BY ACCESS causes
Oracle
to write one record for each access.
Audit
Oracle commands examples:
AUDIT
SESSION; - will audit all successful and unsuccessful connections to and
disconnections from the database, regardless of user.
AUDIT
SESSION by myuser; - the same, like a previous option, but set selectively, for
individual user 'myuser'.
AUDIT
INSERT TABLE, UPDATE TABLE, DELETE TABLE by myuser by ACCESS; - will audit
'myuser' Oracle user all insert, update, delete activities.
AUDIT
SELECT TABLE BY myuser by ACCESS; - will audit selects issued by 'myuser'
Oracle user.
AUDIT
SELECT TABLE, INSERT TABLE, DELETE TABLE, UPDATE TABLE, EXECUTE PROCEDURE BY
ACCESS WHENEVER NOT SUCCESSFUL; - will audit all unsuccessful SELECT, INSERT,
DELETE
and UPDATE statements on all tables and unsuccessful uses of the EXECUTE
PROCEDURE system privilege, by all database users, and by individual audited
statement.
AUDIT
SELECT TABLE, INSERT TABLE, DELETE TABLE, UPDATE TABLE, EXECUTE PROCEDURE by
myuser BY ACCESS WHENEVER NOT SUCCESSFUL; - will audit all unsuccessful SELECT,
INSERT,
DELETE and UPDATE statements on all tables and unsuccessful uses of the EXECUTE
PROCEDURE system privilege, by <myuser> Oracle database user, and by
individual audited statement.
AUDIT
ALL BY myuser BY ACCESS; - will audit all activities of the 'myuser' Oracle
user. This could generate a lot of audit trace information and could cause
overhead.
NOAUDIT
ALL; - remove all auditing.
After
audit_trail functionality is enabled for some time, the audit_trail related
rows will grow quickly in the SYSTEM tablespace, causing performance and/or
lack of space issues. The DBA may want to delete audit trail records to free
space and to facilitate audit trail management.
There
are a few options to perform it:
DELETE
FROM SYS.AUD$;
DELETE FROM SYS.AUD$ WHERE obj$name='SOME_OBJECT';
TRUNCATE TABLE SYS.AUD$;
(SYS.AUD$
is the only SYS object that should ever be directly modified.)
Example:
[oracle@localhost
~]$ sqlplus / as sysdba
SQL*Plus:
Release 11.1.0.6.0
Copyright
(c) 1982, 2007, Oracle. All rights reserved.
Connected
to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
NOAUDIT ALL;
Noaudit
succeeded.
SQL>
TRUNCATE TABLE SYS.AUD$;
Table
truncated.
