Auditing in Oracle Database
The Oracle Server provides several auditing options. For example, you can choose to audit certain types of SQL statements, such as INSERT or UPDATE statements. You can audit statements that use specific system privileges, such as CREATE TABLE or ALTER TABLE. You can also audit the unsuccessful attempts to perform some type of action in the database, such as failed login attempts. Auditing can be limited to a specific user or group of users, or can even be limited to actions performed on a specific schema object on a “by session” or “by access” basis.
The following three types of audits are provide by Oracle
1. Session audits (LOGON,LOGOFF etc) 2. Database action and object audits and 3. DDL(CREATE, ALTER & DROP of objects)
The three main views to see the AUDIT Information are:
DBA_AUDIT_TRAIL– Standard auditing only (fromAUD$).DBA_FGA_AUDIT_TRAIL– Fine-grained auditing only (fromFGA_LOG$) [For 10g].DBA_COMMON_AUDIT_TRAIL– Both standard and fine-grained auditing [For 10g].
To enable database auditing, you must provide a value for the AUDIT_TRAIL parameter.
The initialization parameters of audit facility of Oracle
SQL> SHOW PARAMETER AUDIT NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string C:\ORACLE\PRODUCT\10.2.0\ADMIN \ORCL\ADUMP audit_sys_operations boolean FALSE audit_trail string DB
Auditing is disabled by default, but can enabled by setting the AUDIT_TRAIL static parameter, which has the following allowed values.
AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }
DB Auditing is enabled. Audit records will be written to the
SYS.AUD$ table.
OS Auditing is enabled. Audit records will be written to an
audit trail in the operating system.
db,extended As db, but the SQL_BIND and SQL_TEXT columns are also populated.
NONE Auditing is disabled (default).
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.
TRUE This value is supported for backward-compatibility
with versions of Oracle;it is equivalent to the DB value.
FALSE This value is supported for backward-compatibility
with versions of Oracle;it is equivalent to the NONE value.
In Oracle 10g Release 1, db_extended was used in place of db,extended. The XML options are new to Oracle 10g Release 2.
The AUDIT_SYS_OPERATIONS static parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. All audit records are written to the OS audit trail.
The AUDIT_FILE_DEST parameter specifies the OS directory used for the audit trail when the os, xml and xml,extended options are used. It is also the location for all mandatory auditing specified by the AUDIT_SYS_OPERATIONS parameter.
Audit in SYS.AUD$ desertion Table
Set audit_trail to DB in pfile (audit_trail = DB) .
Enable auditing and direct audit records to the database audit trail
SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE; System altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 612368384 bytes Fixed Size 1250452 bytes Variable Size 230689644 bytes Database Buffers 377487360 bytes Redo Buffers 2940928 bytes Database mounted.
The command to begin auditing connects (login) attempts is:
AUDIT SESSION;
AUDIT SESSION WHENEVER SUCCESSFUL;
AUDIT SESSION WHENEVER NOT SUCCESSFUL;
To view the report of Audit session run the following query.
SQL Code:
SELECT os_username,
username,
terminal,
returncode,
TO_CHAR(timestamp, 'DD-MON-YYYY HH24:MI:SS') LOGON_TIME,
TO_CHAR(logoff_time, 'DD-MON-YYYY HH24:MI:SS') LOGOFF_TIME
FROM dba_audit_session;
Disable Session Audit
NOAUDIT SESSION;
Audit a User
AUDIT CREATE TABLE BY scott;
Disable User Audit
NOAUDIT CREATE TABLE BY scott;
Audit a User Table
It is also possible to audit SELECT, INSERT, UPDATE, and DELETE operations on specific database tables. Depending on the type of audit you wish to conduct, you can include either the BY SESSION or BY ACCESS clause in the AUDIT command. This clause is available for both object audits and system-level action audits.
It denotes whether an audit record will be written once for each session (BY SESSION) or once for each time a schema objects is accessed (BY ACCESS).
AUDIT INSERT ON scott.emp1 BY SESSION;
AUDIT INSERT ON scott.emp2 BY ACCESS;
Disable User Table Audit
NOAUDIT INSERT ON scott.emp1;
Set audit_trail to OS file destination, in pfile (audit_trail = OS)
audit_file_dest = /u01/oracle/product/10.2.0/admin/orcl/adump
Open your init.ora file and check the AUDIT_FILE_DEST parameter for the location of your operating system audit trail files.
Open the file in Unix system and If you are using Windows NT, then go to Start -> Programs -> Administrative Tools -> Event Viewer. In the menu bar of the Event Viewer, choose Log -> Application. Double click on the events listed.
To find out the name of the database action that corresponds to the numeric code of 100, query the AUDIT_ACTIONS data dictionary view.
SQL Code: SELECT action, name FROM audit_actions WHERE action = 100;
Similarly, to discover the name of the privilege used to allow you to perform this database action, query the STMT_AUDIT_OPTION_MAP data dictionary view. While still logged on as user
SQL Code: SELECT option#, name FROM stmt_audit_option_map WHERE option# = 5;
View Audit Trail
The audit trail is stored in the SYS.AUD$ table. Its contents can be viewed directly or via the following views:
SQL Code: SQL> SELECT view_name 2 FROM dba_views 3 WHERE view_name LIKE 'DBA%AUDIT%' 4 ORDER BY view_name; VIEW_NAME ------------------------------ 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
