Fine Grained Auditing in Oracle Database

 

Fine Grained Auditing in Oracle

Prerequesites:

To perform fine grained auditing

unfied auditing must be stoped

Check Unified Auditing

SQL> SELECT * FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

PARAMETER      VALUE      CON_ID

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

Unified Auditing     TRUE          0

Disable Unified Auditing

shu immediate;

lsnrctl stop

cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk uniaud_off ioracle

startup 

lsnrctl start

Check Audit Policies

SQL>SELECT object_schema,object_name,policy_owner,policy_name,policy_column,sel,ins,upd,del from dba_audit_policies;

SQL>no rows selected

Check Audit Parameter

show parameter audit;

NAME                                   TYPE         VALUE

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

audit_file_dest                          string        /u01/app/oracle/admin/orcl2/adump

audit_sys_operations               boolean    TRUE

audit_syslog_level                    string

audit_trail                                  string         DB, EXTENDED

unified_audit_sga_queue_size  integer      1048576

sql> conn scott/scott

SQL> select * from tab;

TNAME      TABTYPE  CLUSTERID

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

BONUS          TABLE

DEPT             TABLE

EMP               TABLE

SALGRADE   TABLE

SQL> select * from emp;

Create seprate user for audit record monitoring and policy creation

SQL> create user auditmntr identified by auditmntr account unlock;

Give Grants for fine grained auditing to user

SQL> grant create session to auditmntr;

SQL> grant AUDIT_ADMIN to auditmntr identified by auditmntr;

SQL> grant AUDIT_VIEWER to auditmntr identified by auditmntr; 

SQL> grant AUDIT_ADMIN,DBA to auditmntr identified by auditmntr;   --DBA grant is optional

SQL> grant select,update,insert,delete on scott.emp to auditmntr;

SQL> grant select,update,insert,delete on scott.BONUS to auditmntr;

SQL> grant select,update,insert,delete on scott.SALGRADE to auditmntr;

SQL> grant select,update,insert,delete on scott.DEPT to auditmntr;

Note: To create fine-grained audit policies, you must be granted the AUDIT_ADMIN role or the EXECUTE privilege on the DBMS_FGA package.

Note: To view and analyze fine-grained audit data, you must be granted the AUDIT_VIEWER role.

Syntax for Creating a Fine-Grained Audit Policy

The DBMS_FGA.ADD_POLICY procedure syntax is as follows:

DBMS_FGA.ADD_POLICY(

object_schema        IN  VARCHAR2 DEFAULT NULL 

object_name            IN  VARCHAR2,

policy_name            IN  VARCHAR2, 

audit_condition        IN  VARCHAR2 DEFAULT NULL, 

audit_column           IN  VARCHAR2 DEFAULT NULL 

handler_schema      IN  VARCHAR2 DEFAULT NULL, 

handler_module       IN  VARCHAR2 DEFAULT NULL, 

enable                      IN  BOOLEAN DEFAULT TRUE, 

statement_types      IN  VARCHAR2 DEFAULT SELECT,

audit_trail                 IN  BINARY_INTEGER DEFAULT NULL, 

audit_column_opts  IN  BINARY_INTEGER DEFAULT ANY_COLUMNS, 

policy_owner           IN  VARCHAR2 DEFAULT NULL);

object_schema

specifies the schema of the object to be audited.

object_name

 specifies the name of the object to be audited.

policy_name

specifies the name of the policy to be created. Ensure that this name is unique.

audit_condition

specifies a Boolean condition in a row. NULL is allowed and acts asTRUE. See Audits of Specific Columns and Rows for more information. If you specify NULL or no audit condition, then any action on a table with that policy creates an audit record, whether or not rows are returned. 

example ( audit_condition => 'EMPNO=0', audit_condition => 'DEPARTMENT_ID = 50')

audit_column

specifies one or more columns to audit, including hidden columns.If set to NULL or omitted, all columns are audited. 

These can include Oracle Label Security hidden columns or object type columns. The default, NULL, causes audit if any column is accessed or affected.

enable

enables or disables the policy using true or false. If omitted, the policy is enabled. The default is TRUE.

statement_types

specifies the SQL statements to be audited: INSERT, UPDATE, DELETE, or SELECT only. If you want to audit a MERGE operation, then set statement_types to 'INSERT,UPDATE'. The default is SELECT.

audit_trail

If you have migrated to unified auditing, then Oracle Database ignores this parameter and writes the audit records immediately to the unified audit trail. If you have migrated to unified auditing, then omit this parameter.

audit_column_opts   => DBMS_FGA.ANY_COLUMNS,

audit_column_opts   => DBMS_FGA.ALL_COLUMNS,

  

Note: If the audit_column lists more than one column, then you can use the audit_column_opts parameter to specify whether a statement is audited when the query references any column specified in the audit_column parameter or only when all columns are referenced.

Again Check Audit Policies

SQL>SELECT object_schema,object_name,policy_owner,policy_name,policy_column,sel,ins,upd,del from dba_audit_policies;

Create fine grained policy for select

Create audit policy from newly creates user

Connect to the user

conn auditmntr/auditmntr

 

BEGIN
DBMS_FGA.ADD_POLICY(
object_schema => 'SCOTT',
object_name => 'emp',
policy_name => 'SCOTT_FGA_slct_Plcy',
--audit_condition => 'EMPNO=0',
audit_column => 'ENAME,JOB',
enable => true,
statement_types => 'SELECT',
audit_trail => DBMS_FGA.DB_EXTENDED,
audit_column_opts => DBMS_FGA.ANY_COLUMNS);
END;
/

select * from scott.emp;

select userhost,object_name,db_user,policy_name,sql_text from DBA_FGA_AUDIT_TRAIL;

SELECT * FROM DBA_AUDIT_POLICY_COLUMNS;

Create fine grained policy for update

SQL>BEGIN

DBMS_FGA.ADD_POLICY (

object_schema => 'SCOTT',

object_name => 'emp',

policy_name => 'SCOTT_FGA_updt_Plcy',

--audit_condition => 'ID=1',

audit_column => 'ENAME,job',

enable => true,

statement_types => 'UPDATE',

--audit_trail => DBMS_FGA.DB_EXTENDED,

audit_column_opts => DBMS_FGA.ANY_COLUMNS);

END;

/

Create Policy for SELECT,UPDATE,DELETE,INSERT

SQL> BEGIN

dbms_fga.add_policy(

object_schema   => 'SCOTT',

object_name     => 'EMP',

policy_name     => 'SCOTT_FGA_ALL_AUDIT',

--audit_condition => 'SALARY > 2500',

audit_column    => 'ENAME,JOB,MGR',

enable          => TRUE,

statement_types  => 'SELECT,UPDATE,DELETE,INSERT');

END ;

/  

To Check Audit Record

DBA_COMMON_AUDIT_TRAIL

DBA_FGA_AUDIT_TRAIL

SYS.FGA_LOG$

Enable the Policy

DBMS_FGA.ENABLE_POLICY(

object_schema VARCHAR2,

object_name VARCHAR2,

policy_name VARCHAR2,

enable BOOLEAN);

Disable the Policy

DBMS_FGA.DISABLE_POLICY(

object_schema VARCHAR2,

object_name VARCHAR2,

policy_name VARCHAR2 );

Drop the policy

Note:To drop policies first we have to disable policy then drop it

DBMS_FGA.DROP_POLICY(

object_schema VARCHAR2,

object_name VARCHAR2,

policy_name VARCHAR2 );