The Oracle Server provides several auditing options.
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 (from AUD$).
·
DBA_FGA_AUDIT_TRAIL – Fine-grained
auditing only (from FGA_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.
Note - Auditing is
disabled by default, but can enabled by setting the AUDIT_TRAIL static
parameter, which has the following allowed values.
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
The initialization parameters of audit
facility of Oracle
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
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
SQL>
NOAUDIT;
SQL> NOAUDIT session;
SQL> NOAUDIT session BY scott, hr;
SQL> NOAUDIT DELETE ON emp;
SQL> NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;
SQL> NOAUDIT ALL;
SQL> NOAUDIT ALL PRIVILEGES;
SQL> NOAUDIT ALL ON DEFAULT;
SQL> NOAUDIT session;
SQL> NOAUDIT session BY scott, hr;
SQL> NOAUDIT DELETE ON emp;
SQL> NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;
SQL> NOAUDIT ALL;
SQL> NOAUDIT ALL PRIVILEGES;
SQL> NOAUDIT ALL ON DEFAULT;
