Basic audit on user level activities in Oracle

Configuring and Administering Auditing : Using Oracle 11g
============================================
Auditing is always about accountability, and is frequently done to protect and preserve privacy for the information stored in databases. Concern about privacy policies and practices has been rising steadily with the ubiquitous use of databases in businesses and on the Internet. Oracle Database provides a depth of
auditing that readily enables system administrators to implement enhanced protections, early detection of suspicious activities, and finely-tuned security responses.


Pre-requisites :
===========
ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
restart the database

Enabling and Disabling Auditing :
=========================
Any authorized database user can set statement, privilege, and object auditing options at any time, but Oracle does not generate audit information for the database audit trail unless database auditing is enabled. The security administrator is normally responsible for controlling auditing. This section discusses the initialization parameters that enable and disable auditing.

Note: 
All of the initialization parameters affecting auditing are static. This means that is you change the values for the AUDIT_SYS_OPERATIONS, AUDIT_TRAIL, and AUDIT_FILE_DEST initialization parameters, you must shut down and restart your database for the new values to take effect.

Caution : 
Enabling audit affects application performance. If you have good volume of CPU head-room, then you thing about audit.


############################################################
#### DEMO ######### DEMO ######### DEMO ######### DEMO #####
############################################################

-- create user / owner

create user SAMPLE
identified by SAMPLE
  default tablespace USERS
  temporary tablespace TEMP01
  profile DEFAULT;
grant CONNECT to SAMPLE;
grant RESOURCE to SAMPLE;
grant UNLIMITED TABLESPACE to SAMPLE;

--AUDIT ALL BY l2_user BY ACCESS;
AUDIT UPDATE TABLE, DELETE TABLE BY l2_user BY ACCESS;
AUDIT alter any PROCEDURE BY l2_user BY ACCESS;
AUDIT drop any PROCEDURE BY l2_user BY ACCESS;

NOAUDIT NETWORK;
NOAUDIT SELECT TABLE BY SAMPLE;
NOAUDIT EXECUTE PROCEDURE BY SAMPLE;

-- Enable audit on different user who is accessing SAMPLE's data.

AUDIT UPDATE TABLE, DELETE TABLE BY L2_USER;
AUDIT TRUNCATE BY L2_USER;
AUDIT alter any PROCEDURE BY L2_USER;
AUDIT drop any PROCEDURE BY L2_USER;

NOAUDIT NETWORK;
NOAUDIT SELECT TABLE BY L2_USER;
NOAUDIT EXECUTE PROCEDURE BY L2_USER;

-- View to verify

SELECT *
FROM   dba_audit_trail
WHERE
/*action_name='UPDATE' */ -- if to get only who updated
/* and username='L2_MASTER'*/ -- if to get who did required action
trunc(extended_timestamp)=trunc(sysdate) -- date frame to find data
/*and object_name='EMPLOYEE'*/ -- which object you want to search for as part of audit
and action_name not like 'LOG%' -- avoid LOGON and LOGOFF

OR

select * from DBA_COMMON_AUDIT_TRAIL
where trunc(extended_timestamp)=trunc(sysdate)
and statement_type not like 'LOG%';
/* add more filter conditions as per your requirement */

-- Find all action

SELECT * FROM DBA_AUDIT_TRAIL
SELECT * FROM  DBA_AUDIT_SESSION
select * from dba_audit_object


Views associated with audit:
====================

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

-- main views

DBA_AUDIT_TRAIL - Standard auditing only (from AUD$).
DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$).
DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.


Purging Audit Records from the Audit Trail:
================================

After auditing is enabled for some time, the security administrator may want to delete records from the database audit trail both to free audit trail space and to facilitate audit trail management.

For example, to delete all audit records from the audit trail, enter the following statement:

SQL> DELETE FROM SYS.AUD$;

Alternatively, to delete all audit records from the audit trail generated as a result of auditing the table emp, enter the following statement:

SQL> DELETE FROM SYS.AUD$ WHERE obj$name='EMP';

Archiving Audit Trail Information:
=======================

If audit trail information must be archived for historical purposes, then the security administrator can copy the relevant records to a normal database table (for example, using INSERT INTO table SELECT ... FROM SYS.AUD$ ...) or export the audit trail table to an operating system file.


Listing Active Statement Audit Options:
=============================

The following query returns all the statement audit options that are set:

SELECT * FROM DBA_STMT_AUDIT_OPTS;

SELECT * FROM DBA_PRIV_AUDIT_OPTS;

FAQ:
1) what does 'SESSION REC' mean in the action_name column of dba_audit_trail?
Ans:
SESSION REC is an audit record inserted in AUD$ table each time a DDL or DML operation is audited BY SESSION.

An AUDIT can be activated with 2 possible options to indicate how audit records should be generated if the audited statement is issued multiple time within a single user session:

1. By session
2. By access

2) When i have enabled the audting by the AUDIT Statements......How/Where should I check the Audited information....?

Ans:

to review the contents of the audit trail:

DBA_AUDIT_EXISTS    - lists audit trail entries produced by AUDIT NOT EXISTS.

DBA_AUDIT_OBJECT    - contains audit trail records for all objects in the system.

DBA_AUDIT_SESSION   - lists all audit trail records concerning CONNECT and

                                            DISCONNECT.

DBA_AUDIT_STATEMENT - lists audit trail records concerning GRANT, REVOKE,

                    AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the 

                    database.

DBA_AUDIT_TRAIL     - lists all audit trail entries.

DBA_OBJ_AUDIT_OPTS   - describes auditing options on all objects.

DBA_PRIV_AUDIT_OPTS  - describes current system privileges being audited across

                       the system and by user.

DBA_STMT_AUDIT_OPTS  - describes current system auditing options across the

                       system and by user.

3) How to audit log on and log off time :

Ans :

Use audit statement like :

AUDIT ALL BY scott BY ACCESS;

Click here to view all audit events from Oracle Documents

In case of doubt, please feel free to write a mail.