//
you're reading...
Administration, Security

Securing Oracle Database : Fine-Grained Auditing

In the previous posts [P1 & P2], we talk about Standard Auditing. In this post we talk about Fine-Grained Auditing (FGA).

With standard auditing, you can easily captured all the objects and tables that were accessed and by whom, whether it was SELECT statement or any other DML statements such as DELETE, INSERT or UPDATE. But it would be cumbersome if you are looking for some significant details like what rows has been accessed or if certain columns of particular rows have been accessed.

With Fine-Grained Auditing (FGA) you can solve this problem. FGA policies allow you to monitor specific operations e.g. what rows or columns are accessed etc. on individual tables. It also reduces the significant amount of audit trail that are being written to the database.

FGA was introduced in Oracle 9i as an audit option apart from Standard Auditing. Initially FGA only support auditing for SELECT statements. DML statements were added in the Oracle 10g release.

The PL/SQL package DBMS_FGA is used to configure Fine-Grained Auditing. To use FGA you need EXEXUTE privilege on DBMS_FGA. Apart from this you don’t have to restart or set any kind of system wide parameter change to enable or disable FGA.

There are four procedures with DBMS_FGA package:

ADD_POLICY         : Create and add an audit policy
DROP_POLICY        : Drops an audit policy
DISABLE_POLICY     : Disable the audit policy without dropping it
ENABLE_POLICY      : Enable the audit policy

ADD_POLICY Procedure

The ADD_POLICY procedure has the following structure:

DBMS_FGA.ADD_POLICY
(
 object_schema     VARCHAR2,               
 object_name       VARCHAR2,                
 policy_name       VARCHAR2,               
 audit_condition   VARCHAR2,             
 audit_column      VARCHAR2,               
 handler_schema    VARCHAR2,              
 handler_module    VARCHAR2,              
 enable            BOOLEAN,                     
 statement_types   VARCHAR2,             
 audit_trail       BINARY_INTEGER IN DEFAULT,   
 audit_column_opts BINARY_INTEGER IN DEFAULT
);   

You can find description of all parameter of ADD_POLICY procedure here.

Let’s create a policy on hr.employees table which used to audit SELECT statement that query salary column of any employee that belongs to department 90.

BEGIN
  DBMS_FGA.ADD_POLICY(
   object_schema      => 'HR',
   object_name        => 'EMPLOYEES',
   policy_name        => 'audit_sal_employees',
   audit_condition    => 'department_id = 90',
   audit_column       => 'salary',
   enable             =>  FALSE);
END;
/
scott@O11R2>SELECT employee_id, last_name||' '||first_name NAME, salary 
            FROM hr.employees
            WHERE department_id=90;

EMPLOYEE_ID NAME                                               SALARY
----------- ---------------------------------------------- ----------
        100 King Steven                                         24000
        101 Kochhar Neena                                       17000
        102 De Haan Lex                                         17000

Now if we can query DBA_FGA_AUDIT_TRAIL view we see nothing because we just create the policy but do not enable it.

dbadmin@O11R2>SELECT db_user, object_schema, object_name, policy_name, sql_text, statement_type, 
                     extended_timestamp 
              FROM dba_fga_audit_trail;

No row selected.

Now enable the policy.

dbadmin@O11R2>BEGIN
                 DBMS_FGA.ENABLE_POLICY(
                  object_schema      => 'HR',
                  object_name        => 'EMPLOYEES',
                  policy_name        => 'audit_sal_employees',
                  enable             =>  TRUE);
              END;
/

FGA audit records are stored in the SYS.FGA_LOG$ table. To see the audit records have been generated for the audit policies that are in effect, you can query the DBA_FGA_AUDIT_TRAIL view. Apart from this you can also use DBA_COMMON_AUDIT_TRAIL view the audit records. This view combines audit records from both standard auditing and Fine-Grained Auditing.

Now suppose user SCOTT has issued the following SELECT statement on hr.employees table:

scott@O11R2>SELECT employee_id, last_name||' '||first_name NAME, salary, email 
            FROM hr.employees
            WHERE department_id=90;

scott@O11R2>SELECT last_name||' '||first_name NAME, email, salary FROM hr.employees;

scott@O11R2>SELECT last_name||' '||first_name NAME, salary FROM hr.employees
            WHERE department_id IN (10, 30);

Now query the DBA_FGA_AUDIT_TRAIL table to view audit records.

dbadmin@O11R2>SELECT db_user, object_schema, object_name, policy_name, 
              statement_type, to_char(extended_timestamp, DD-MON-YYYY HH24:MI:SS TZR) extended_timestamp
              FROM DBA_FGA_AUDIT_TRAIL;

DB_USER   OBJECT_SCHEMA OBJECT_NAME POLICY_NAME          STATEMENT_TYPE EXTENDED_TIMESTAMP
-------   ------------- ----------- -------------------- -------------- ------------------
SCOTT     HR            EMPLOYEES   AUDIT_SAL_EMPLOYEES  SELECT         31-AUG-12 23:05:52 
SCOTT     HR            EMPLOYEES   AUDIT_SAL_EMPLOYEES  SELECT         31-AUG-12 23:06:24 


dbadmin@O11R2>SELECT sql_text FROM dba_fga_audit_trail;

SQL_TEXT
--------------------------------------------------
select employee_id, last_name||' '||first_name NAME, salary, email from hr.employees
where department_id=90

select last_name||' '||first_name NAME, email, salary from hr.employees

If the SELECT statement is used to access the view based on the hr.employees table and query the column in question, it will also audit those SELECT statements.

scott@O11R2>SELECT last_name||' '||first_name NAME, salary, city FROM hr.emp_details_view
            WHERE department_id in (10,30,90);

dbadmin@O11R2>SELECT db_user, object_schema, object_name, policy_name, statement_type,  
              to_char(extended_timestamp, DD-MON-YYYY HH24:MI:SS TZR)
              FROM dba_fga_audit_trail;

DB_USER   OBJECT_SCHEMA OBJECT_NAME POLICY_NAME          STATEMENT_TYPE EXTENDED_TIMESTAMP
-------   ------------- ----------- -------------------- -------------- ------------------
SCOTT     HR            EMPLOYEES   AUDIT_SAL_EMPLOYEES  SELECT         31-AUG-12 23:05:52 
SCOTT     HR            EMPLOYEES   AUDIT_SAL_EMPLOYEES  SELECT         31-AUG-12 23:06:24 
SCOTT     HR            EMPLOYEES   AUDIT_SAL_EMPLOYEES  SELECT         31-AUG-12 23:09:32

dbadmin@O11R2>select sql_text from dba_fga_audit_trail;

SQL_TEXT
--------------------------------------------------
select employee_id, last_name||' '||first_name NAME, salary, email from hr.employees
where department_id=90

select last_name||' '||first_name NAME, email, salary from hr.employees

select last_name||' '||first_name NAME, salary, city from hr.emp_details_view
where department_id in (10,30,90)

If you wish the audit records to be written to XML files with SQL text and bind values, you can use AUDIT_TRAIL parameter in ADD_POLICY procedure.

BEGIN
  DBMS_FGA.ADD_POLICY(
   object_schema      => 'HR',
   object_name        => 'EMPLOYEES',
   policy_name        => 'audit_sal_employees',
   audit_condition    => 'department_id = 90',
   audit_column       => 'salary',
   audit_trail        => DBMS_FGA.XML + DBMS_FGA.EXTENDED,
   enable             =>  TRUE);
END;
/

scott@O11R2>SELECT last_name, salary, city from hr.emp_details_view;

LAST_NAME                     SALARY CITY
------------------------- ---------- ------------------------------
OConnell                        2600 South San Francisco
Grant                           2600 South San Francisco
Whalen                          4400 Seattle
Hartstein                      13000 Toronto
Fay                             6000 Toronto
Mavris                          6500 London
Baer                           10000 Munich
Higgins                        12008 Seattle
Gietz                           8300 Seattle
King                           24000 Seattle
Kochhar                        17000 Seattle
De Haan                        17000 Seattle
. . . . . .

106 rows selected.
C:\>type E:\app\Madhurendra\admin\O11R2\adump\o11r2_ora_2928_1.xml
<?xml version="1.0" encoding="UTF-8"?>
  <Audit xmlns="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-11_2.xsd"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-11_2.xsd">
   <Version>11.2</Version>
         <AuditRecord>
		     <Audit_Type>2</Audit_Type>
			 <Session_Id>63184</Session_Id>
			 <StatementId>14</StatementId>
			 <EntryId>1</EntryId>
			 <Extended_Timestamp>2012-08-31T18:17:59.265000Z</Extended_Timestamp>
			 <DB_User>SCOTT</DB_User>
			 <Ext_Name>INTEGER\Madhurendra</Ext_Name>
			 <OS_User>INTEGER\Madhurendra</OS_User>
			 <Userhost>WORKGROUP\INTEGER</Userhost>
			 <OS_Process>3748:2928</OS_Process>
			 <Instance_Number>0</Instance_Number>
			 <Object_Schema>HR</Object_Schema>
			 <Object_Name>EMPLOYEES</Object_Name>
			 <Policy_Name>AUDIT_SAL_EMPLOYEES</Policy_Name>
			 <Stmt_Type>1</Stmt_Type>
			 <Scn>1230124</Scn>
			 <DBID>3533758454</DBID>
             <Sql_Text>select last_name, salary, city from hr.emp_details_view</Sql_Text>
         </AuditRecord>
</Audit>

You can drop the policy using DROP_POLICY procedure.

begin DBMS_FGA.DROP_POLICY(
  object_schema      => 'HR',
  object_name        => 'EMPLOYEES',
  policy_name        => 'audit_sal_employees');
end;
Advertisements

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: