0) { obj.className = "imgborder_on"; } } } function hideImage(obj) { if (obj.className.substr(0, 10) == "imgborder_") { obj.src = eyeglass.src; obj.className = "imgborder_off"; } } function showAllImages() { imgs = document.images; for (i=0; i < imgs.length; i++) { showImage(imgs[i]); } } function hideAllImages() { imgs = document.images; for (i=0; i < imgs.length; i++) { hideImage(imgs[i]); } } //-->

OBE Home > 10gR2 Single > Security

Using Fine-Grained Auditing

Purpose

The goal of this tutorial is to show how fine-grained auditing can be used to detect access to a "honey token" in a table .

Time to Complete

Approximately 30 minutes

Topics

This tutorial covers the following topics:

Overview
Prerequisites

Setup

Creating and Applying a VPD Policy
Testing the Policy Implementation
Checking the Audit Violations
Cleanup
Summary

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.

Overview

Policies you establish with fine-grained auditing can monitor data access based on content. Using policies, you can specify the columns and conditions that you want audit records for. Conditions can include limiting the audit to specific types of DML statements used in connection with the columns that you specify. You can also provide the name of the routine you want called when an audit event occurs. This routine can notify or alert administrators or handle errors and anomalies.

Scenario

This tutorial shows how fine-grained auditing is used to detect access to a "honey token" in a table. In this tutorial, a VPD policy is created that limits access to rows by credit card vendor. The honey token is a credit card number that is never selected. It fails the usual verifications, and the number cannot be related to any vendor, so authorized access to this row is not possible.

The VPD policy excludes OE, the owner of this table. OE is granted full access, so when OE (or a hacker who obtained this identity) executes

select * from oe.cust_payment_info

the honey token is selected and the FGA policy records this event. In addition, you could invoke an event handler (not included in this tutorial), to send an email to audit_admin about the access to this special row.

This tutorial separates duties between the following users:

HR_sec Creates database users (from a list of employees in the hr.employees table) and database roles.
sec_admin

Creates and applies the VPD and FGA policies according to the company's access control and auditing policies.

audit_admin Queries the audit record in the database ( dba_fga_audit_trail).

Back to Topic List

Prerequisites

Befor you perform this tutorial, you should:

1.

Perform the Installing Oracle Database 10g on Windows tutorial.

2.

Download and unzip the fga.zip file into your working directory (c:\wkdir).

Back to Topic List

Setup

In this section, you update your sqlnet.ora, create an encrypted wallet ( ewallet.p12), open the wallet, and create the master key for TDE. Perform the following steps:

1.

(OPTIONAL: This topic should be performed only if you have not performed this section in a previous lesson) You need to update your sqlnet.ora file to include an entry for ENCRYPTED_WALLET_LOCATION. Open the c:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora file using either Notepad or Wordpad. Add the following entry to the end of the file: 

                               
ENCRYPTION_WALLET_LOCATION=
                                


   (SOURCE=(METHOD=FILE)(METHOD_DATA=
                                


   (DIRECTORY=c:\oracle\product\10.2.0\db_1\)))

Save your changes and close the file.

Note: Any directory can be picked for the encrypted wallet, but the path should not point to the standard obfuscated wallet ( cwallet.sso) created during DB installation.

 
2.

(OPTIONAL: This topic should be performed only if you have not performed this section in a previous lesson) Next, you need to open the wallet and create the master encryption key. Select Start > Programs > Oracle - OraDb10g_home1 > Application Development > SQL Plus. Enter /nolog for the User Name and click OK. Then enter the following command: 

                               
                                 
                                   
@c:\wkdir\fga00_dbsetup

connect / as sysdba
alter system set key identified by "welcome1";

The above alter command does the following:

If no encrypted wallet is present in the directory specified, the encrypted wallet is created ( ewallet.p12), the wallet is opened, and the master key for created/recreated.
If the encrypted wallet is present in the directory specified, the walled is opened, and the master key is created/recreated.

Note: only users with the ' alter system' privilege can create a master key or open the wallet.

The master key should only be created once, unless you want to re-encrypt your data with a new encryption key !!!

For later sessions, you do not want to use the command given above; you need the wallet to be open (it has been closed when you shut down your database), but you don't want to create a new master key. Then the command is: 

alter system set wallet open identified by "welcome1";

The master encryption key is necessary because each table has its own encryption key. These column keys are stored in the database. Since the wallet can only store a limited number of keys and is not very scalable, the column keys are encrypted with the master key. This way, you can have as many column keys as needed, with only a small number of master keys stored in the wallet (including retired keys, that you may need one day to decrypt data from an old backup-tape). By default, the command above generates a key using the Advanced Encryption Standard with 192 bits (AES192). 3DES could also be used, or a smaller or bigger number of bits for the AES encryption.

 
3.

In this tutorial, you create a set of users and roles to demonstrate how FGA works. You create a customer payment information table and populate it with credit card numbers. The credit card number associated with "Honey Token" is impossible, so this row is never selected by authorized users. It is only selected by administrative users who are exempt from access policies or intruders who became administrative 'insiders'. This allows for highly focused auditing on this table. From your SQL*Plus window, execute the following script:

                               
@c:\wkdir\01_fga_poptabl
                            
conn oe/oe
                              


create table cust_payment_info
                              


  (first_name varchar2(11),
                              


  last_name varchar2(10),
                              


  order_number number(5),
                              


  credit_card_number varchar2(16) ENCRYPT);
                              


insert into cust_payment_info values
                              


  ('Jon', 'Oldfield', 10001, '5446959708812985');
                              


insert into cust_payment_info values
                              


  ('Chris', 'White', 10002, '5122358046082560');
                              


insert into cust_payment_info values
                              


  ('Alan', 'Squire', 10003, '5595968943757920');
                              


insert into cust_payment_info values
                              


  ('Mike', 'Anderson', 10004, '4929889576357400');
                              


insert into cust_payment_info values
                              


  ('Annie', 'Schmidt', 10005, '4556988708236902');
                              


insert into cust_payment_info values
                              


  ('Elliott', 'Meyer', 10006, '374366599711820');
                              


insert into cust_payment_info values
                              


  ('Celine', 'Smith', 10007, '4716898533036');
                              


insert into cust_payment_info values
                              


  ('Steve', 'Haslam', 10008, '340975900376858');
                              


insert into cust_payment_info values
                              


  ('Albert', 'Einstein', 10009, '310654305412389');

 

4 .

SYSTEM creates three admin users: HR_sec, sec_admin and audit_admin. HR_sec creates the role ' emp_role' and then grants system and object privileges to the emp_role. From your SQL*Plus window, run the following script: 

                               
@c:\wkdir\02_fga_cradminusers

connect system/oracle;
                              


create user sec_admin identified by welcome1;
                              


create user audit_admin identified by welcome1;
                              


create user HR_sec identified by welcome1;
                              


grant connect, create user, drop user, create role, drop any role to HR_sec;
                              


connect HR_sec/welcome1;
                              


create role emp_role;
                              


connect / as sysdba;
                              


grant execute on dbms_rls to sec_admin;
                              


grant execute on dbms_fga to sec_admin;
                              


grant select on dba_fga_audit_trail to audit_admin;
                              


connect system/welcome1;
                              


grant connect to emp_role;
                              


grant create procedure to sec_admin;
                              


connect OE/oe;
                              


grant select on oe.cust_payment_info to emp_role;

 

5 .

HR_sec creates the database users and grants the emp_role to them. From your SQL*Plus window, run the following script: 

                               
@c:\wkdir\03_fga_crdbusers

connect HR_sec/welcome1;
                              


Prompt create user Janette King (JKING) (access to Card_A)
                              


grant emp_role to JKING identified by welcome1;
                              


Prompt create user Lindsay Smith (LSMITH) (access to Card_V)
                              


grant emp_role to LSMITH identified by welcome1;
                              


Prompt create user Louise Doran (LDORAN) (access to Card_M)
                              


grant emp_role to LDORAN identified by welcome1;
                              


Prompt grant emp_role to sec_admin:
                              


grant emp_role to sec_admin;
                              


Prompt grant emp_role to audit_admin:
                              


grant emp_role to audit_admin;
                               


connect hr/hr;
                              


grant select on hr.employees to sec_admin;

 

Back to Topic List

Creating and Applying a VPD Policy

In this section, you define and limit access to the rows with a VPD policy. You check if the person who logs into the database is an employee, and then you limit access to the cust_payment_info table by credit card number.

Card_A begins with '34' or '37':
Card_V begins with '4'
Card_M begins with '5

Perform the following steps:

1.

You first need to create a policy function to create the where-clause. From your SQL*Plus window, execute the following script:

                               
@c:\wkdir\04_fga_crpolicy_function

conn sec_admin/welcome1;
                              


create or replace function f_policy_oe_cust_payment_info
                              


-- Function must have the following parameters
                              


   (schema in varchar2, tab in varchar2)
                              


-- Function will return a string that is used as a WHERE clause
                              


 return varchar2
                              


as
                              


 v_manager_id     number:=0;
                              


 is_employee      number:=0;
                              


 v_user           varchar2(20);
                              


 out_string       varchar2(70) default '1=2 ';
                              


begin
                              


  -- get session user
                              


  v_user := lower(sys_context('userenv','session_user'));
                              


  -- Is the user an employee?
                              


  begin
                              


    select manager_id into v_manager_id
                              


    from hr.employees
                              


    where lower(email) = v_user;
                              


    is_employee:=1;
                              


  exception
                              


   when no_data_found then
                              


    is_employee:=2;
                              


  end;
                              


  -- create where clause when user is authorized to see parts of the table
                              


  if     is_employee=1 and lower(v_user)='jking' and v_manager_id=146 then
                              


  out_string := out_string ||'or CREDIT_CARD_NUMBER like ''34%'' or 
  CREDIT_CARD_NUMBER like ''37%''';
                              


  elsif  is_employee=1 and lower(v_user)='lsmith' and v_manager_id=146 then
                              


  out_string := out_string ||'or CREDIT_CARD_NUMBER like ''4%''';
                              


  elsif  is_employee=1 and lower(v_user)='ldoran' and v_manager_id=146 then
                              


  out_string := out_string ||'or CREDIT_CARD_NUMBER like ''5%''';
                              


  elsif  is_employee=2 and lower(v_user)='oe' then
                              


  out_string := '1=1';
                              


  end if;
                              


 return out_string;
                              


end;
                              


/

 
2.

You now add the policy to the oe.cust_payment_info table. From your SQL*Plus window, execute the following script: 

                               
@c:\wkdir\05_fga_applypolicy

begin
                              


dbms_rls.add_policy('oe','cust_payment_info','ac_cust_payment_info','sec_admin',
 'f_policy_oe_cust_payment_info',policy_type => dbms_rls.context_sensitive);
                              


end;
                              


/

 
3.

The sec_admin user adds an FGA policy to the sensitive table that captures access to the "Albert Einstein". From your SQL*Plus window, execute the following script: 

                               
                                 
@c:\wkdir\05_fga_applypolicy_waudit
                              
                            
begin
                              


DBMS_FGA.ADD_POLICY (
                              


  object_schema => 'OE',
                              


  object_name => 'cust_payment_info',
                              


  policy_name => 'fga_cust_payment_info',
                              


  audit_condition => 'credit_card_number = 310654305412389',
                              


  audit_column => NULL,
                              


  handler_schema => NULL,
                              


  handler_module => NULL,
                              


  enable => TRUE,
                              


  statement_types => 'UPDATE, DELETE, SELECT',
                              


  audit_trail => DBMS_FGA.DB + DBMS_FGA.EXTENDED);
                              


end;
                              


/

 

Back to Topic List

Testing the Policy Implementation

After establishing policies to tables, you can now test them by performing the following steps:

1.

To test the access from the JKING user, execute the following script from your SQL*Plus window. 

                               
@c:\wkdir\06_fga_test_policy_king

conn JKING/welcome1;
                              


col CREDIT_CARD_NUMBER heading Card_A format a17;
                              


select * from oe.CUST_PAYMENT_INFO order by CREDIT_CARD_NUMBER;

 
2.

Now you can test the policy for the LDORAN user by executing the following script: 

                               
@c:\wkdir\06_fga_test_policy_doran

conn LDORAN/welcome1;
                              


col CREDIT_CARD_NUMBER heading Card_M format a17;
                              


select * from oe.CUST_PAYMENT_INFO order by CREDIT_CARD_NUMBER;

 
3.

Now you can test LSMITH access by executing the following script: 

                               
@c:\wkdir\06_fga_test_policy_smith

conn LSMITH/welcome1;
                              


col CREDIT_CARD_NUMBER heading Card_V format a17;
                              


select * from oe.CUST_PAYMENT_INFO order by CREDIT_CARD_NUMBER;

 
4.

Now you can test SYS access by executing the following script: 

                               
@c:\wkdir\06_fga_test_policy_sys

conn / as sysdba;
                              


col CREDIT_CARD_NUMBER heading 'All Cards' format a17;
                              


select * from oe.CUST_PAYMENT_INFO order by CREDIT_CARD_NUMBER;

 
5.

And finally, you can test the OE user access by executing the following script: 

                               
@c:\wkdir\06_fga_test_policy_oe

conn OE/oe;
                              


col CREDIT_CARD_NUMBER heading 'All Cards' format a17;
                              


select * from oe.CUST_PAYMENT_INFO order by CREDIT_CARD_NUMBER;

 

Back to Topic List

Checking the Audit Violations

The audit_admin user can check for any access violations by performing the following steps:

1.

Execute the following script to check the dba_fga_audit_trail for the CUST_PAYMENT_INFO table. 

                               
@c:\wkdir\07_fga_chk_audit_violations

conn audit_admin/welcome1;
                              


col DB_USER format a10;
                              


col extended_timestamp heading 'ext. Time' format a35;
                              


select DB_USER, extended_timestamp, SQL_TEXT from dba_fga_audit_trail 
  where object_name='CUST_PAYMENT_INFO' order by extended_timestamp;

 

Back to Topic List

Cleanup

Now that you have tested your policies, you can drop the users and the policies by performing the following steps:

1.

Execute the following script:

                               
@c:\wkdir\08_fga_cleanup

conn sec_admin/welcome1;
                              


begin
                              


DBMS_FGA.DROP_POLICY (
                              


  object_schema => 'OE',
                              


  object_name => 'cust_payment_info',
                              


  policy_name => 'fga_cust_payment_info');
                              


end;
                              


/
                              


drop function f_policy_oe_cust_payment_info;
                              


conn oe/oe
                              


drop table cust_payment_info;
                              


conn HR_sec/welcome1;
                              


drop role emp_role;
                              


drop user JKING cascade;
                              


drop user LSMITH cascade;
                              


drop user LDORAN cascade;
                              


conn system/welcome1
                              


drop user sec_admin cascade;
                              


drop user audit_admin cascade;
                              


drop user HR_sec cascade;

 

Back to Topic List

Summary

In this tutorial, you learned how to:

Create and apply a VPD policy

Test the policy
Check for audit violations

Back to Topic List

Place your cursor on this icon to hide all screenshots.