Auditing in Oracle needs to know - part2

Apart from SYSDBA auditing, Oracle provides three auditing techniques:

1. standart auditing

Auditing based on statement, Privileges and object level. 

In standard auditing we won't get any information of the event like what actually happened on tables ,for example what got inserted or deleted etc . Value-based audit will give this feature .

2. Value-based auditing
Value-based auditing uses database triggers. Whenever a row is inserted, updated,
or deleted, a block of PL/SQL code will run that can (among other things)
record complete details of the event. 

3.Fine-grained auditing
Fine-grained auditing allows tracking access to tables according to which rows
(or which columns of the rows) were accessed. It is much more precise than
either database auditing or value-based auditing, and it can limit the number
of audit records generated to only those of interest.

The standard auditing having 3 levels of auditing:

Statement: Audit all action at any type of objects.

Privilege: audit action on system level privileges

Object_level: Specific audit action lie select, update, insert or delete.

There is no clear cut demarcation between these three auditing , all are some how related to each other.

For all the 3 level of auditing you can choose to audit by access (audit every time you access) or by session (audit only once per access during the session), you can also audit on if the access was successful (whenever successful) or not (whenever not successful)

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.

That is ,

audit by access- audit every time you access- will give accurate picture about what is going on .

audit by session - audit only once per access during the session - Here after session got created and if you are going to insert 100 times on a table ,then it will logged only for the first attempt .

 CREATE SESSION is a DDL statement Oracle audits this statement by access.

So for statement options and system privileges that audit SQL statements other than DDL, you can specify either BY SESSION or BY ACCESS. BY SESSION is the default.

Oracle database can write to an operating system audit file but cannot read it to detect whether an entry has already been written for a particular operation. Therefore, if you are using an operating system file for the audit trail (that is, the AUDIT_TRAIL initialization parameter is set to OS), then the database may write multiple records to the audit trail file even if you specify BY SESSION.

A. statemente level auditing

Statement auditing is the selective auditing of related groups of statements for a type of database structure or type of schema object for ddl (data definition language) or dml (data manipulation language) statements. For example when 'audit table' is audited create , drop and truncate table statements are audited. Statement level auditing is broad .

The statements that can be audited can be seen from stmt_audit_option_map.

SQL> select * from stmt_audit_option_map;

SQL> select * from stmt_audit_option_map where name like '%TABLE%'; etc,

How to use;

SQL> sho user;

USER is "SYS"

SQL> audit table by scott, system; -- ddl level

Audit succeeded.

SQL> conn scott/scott

Connected.

SQL> create table aud_test as select * from EMP;

Table created.

SQL> drop table aud_test;

Table dropped.

SQL>

SQL> alter session set nls_date_format = 'dd-MON-yyyy hh24:mi:ss';

Session altered.

SQL> conn sys as sysdba
Enter password:
Connected.

SQL>  select USERNAME,obj_name,ACTION_NAME,TIMESTAMP from DBA_AUDIT_TRAIL;

USERNAME     OBJ_NAME     ACTION_NAME         TIMESTAMP

------------ ------------ ------------------- --------------------

SCOTT        AUD_TEST     CREATE TABLE        15-FEB-2013 14:02:35

SCOTT        AUD_TEST     DROP TABLE          15-FEB-2013 14:22:32

Statement level auditing at DML level :-

Eg:-

SQL> audit insert table by scott whenever successful;

 Note:- In statement level auditing we are not specifying any object name to audit , that is statement level auditing broad in its audit action .

we can audit the session level event by using following statement audit ,

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> audit session by access;

Audit succeeded.

SQL> conn scott/scott
Connected.
SQL> conn hr/hr
Connected.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter session set nls_date_format = 'dd-MON-yyyy hh24:mi:ss';

Session altered.

SQL>  select username,timestamp,logoff_time,ACTION_NAME from dba_Audit_session;

USERNAME     TIMESTAMP            LOGOFF_TIME          ACTION_NAME

------------ -------------------- -------------------- -------------------

SYSMAN       15-FEB-2013 16:25:18 15-FEB-2013 16:31:29 LOGOFF

SCOTT        15-FEB-2013 16:25:31 15-FEB-2013 16:25:34 LOGOFF

HR           15-FEB-2013 16:25:35 15-FEB-2013 16:26:36 LOGOFF

SQL>

Audit that ebabled on statement level can be viewed by ,

 SQL> select * from DBA_STMT_AUDIT_OPTS;

Note the following views gives you which audits are enabled:

dba_obj_audit_opts
dba_priv_audit_opts
dba_stmt_audit_opts

Disabling Audit:-

The NOAUDIT statement turns off the various audit options of Oracle. Use it to reset statement, privilege and object audit options. A NOAUDIT statement that sets statement and privilege audit options can include the BY USER option to specify a list of users to limit the scope of the statement and privilege audit options.

noaudit table by scott;

noaudit insert table by scott;

noaudit session by access;