Auditing

Auditing

select * from DBA_PRIV_AUDIT_OPTS


Auditing session per user

audit create session by svadapalli;


See audit trail db parameter in db

To audit logins

Audit session;

To disable audit logins

Noaudit session;

To audit when login failed
AUDIT SESSION WHENEVER NOT SUCCESSFUL;

select * from dba_audit_trail where username !='HISNA_DBA' and TIMESTAMP>SYSDATE-1 order by timestamp desc;

select username,OS_username,userhost,terminal,timestamp,returncode from dba_audit_trail where action_name='LOGON' and returncode in (01004,
01005,
01017,
01045,
28000,
28001) and trunc(timestamp) >= trunc(sysdate-1);






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.

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;



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