Restricting Data Access Using the Virtual Private Database

Purpose

The goal of this tutorial is to show the power of using the Virtual Private Database capability to restrict access to certain data to certain users.

Time to Complete

Approximately 30 minutes

Topics

This tutorial covers the following topics:

Overview
Scenario
Prerequisites

Setup
Enabling a VPD Policy on the ORDERS Table
Testing the VPD Policy on the ORDERS Table
Viewing the Policy on the ORDERS Table
Enabling a VPD Policy With Relevant Columns and Column Filtering on the CUSTOMERS Table
Testing the VPD Policy on the CUSTOMERS Table
Viewing the Policy on the CUSTOMERS Table
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

The Virtual Private Database (VPD) provides row-level access control beyond the capabilities of roles and views. For Internet access, the Virtual Private Database can ensure that online banking customers see only their own accounts. The Web-hosting companies can maintain data of multiple companies in the same Oracle database, while permitting each company to see only its own data.

Within the enterprise, the Virtual Private Database results in lower costs of ownership in deploying applications. Security can be built once, in the data server, rather than in each application that accesses data. Security is stronger, because it is enforced by the database, no matter how a user accesses data. Security is no longer bypassed by a user accessing an ad hoc query tool or new report writer. The Virtual Private Database is a key technology that enables organizations to build hosted, Web-based applications. Indeed, many Oracle applications themselves use VPD to enforce data separation for hosting, including Oracle SalesOnline.com and Oracle Portal.

How the Virtual Private Database Works

The Virtual Private Database is enabled by associating one or more security policies with tables or views. Direct or indirect access to a table with an attached security policy causes the database to consult a function that implements the policy. The policy function returns an access condition known as a predicate (a WHERE clause), which the database appends to the user's SQL statement, thus dynamically modifying the user's data access.

You can implement VPD by writing a stored procedure to append a SQL predicate to each SQL statement that controls row-level access for that statement. For example, if John Doe (who belongs to Department 10) inputs the SELECT * FROM emp statement, then you can use VPD to add the WHERE DEPT = 10 clause. In this way, you use query modification to restrict data access to certain rows.

The Virtual Private Database ensures that, no matter how a user gets to the data (through an application, a report writing tool, or SQL*Plus), the same strong access control policy is enforced. In this way, VPD can help banks ensure that customers see only their own accounts, that telecommunications firms can keep customer records safely segregated, and that human resources applications can support their complex rules of data access to employee records.

Back to Topic List

Scenario

In this tutorial, you create two different administrator users:

sec_admin

This user creates the policy function, applies the policy to the table, and creates an index on the table to improve performance.

 
hr_sec

This user creates the database users and roles after verifying they are contained in either the CUSTOMERS or EMPLOYEES tables.

 

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Perform the Installing Oracle Database 10g on Windows tutorial.

2.

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

Back to Topic List

Setup

In this tutorial, you create a set of users and roles to demonstrate how VPD works. To create users and roles, perform the following steps:

1.

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\create_admin_users_and_roles
set echo off
prompt *** Create admin users: sec_admin and hr_sec
prompt
connect / as sysdba
grant connect, create procedure to sec_admin identified by welcome1;
grant execute on sys.dbms_rls to sec_admin;
grant connect, create user, drop user, create role, drop any role
 to hr_sec identified by welcome1;

prompt
prompt ***** Create roles: employee_role and customer_role
connect hr_sec/welcome1;
create role employee_role;
create role customer_role;

prompt ***** Grant system and object privileges to roles and users
connect system/oracle;
grant connect to employee_role;
grant connect to customer_role;

connect oe/oe;
grant select on oe.orders to employee_role;
grant select on oe.orders to customer_role;
grant select on oe.customers to sec_admin;
grant select on oe.customers to employee_role;

connect hr/hr;
grant select on hr.employees to sec_admin;

connect hr_sec/welcome1;

prompt ***** Create Steven King (President)
create user SKING identified by welcome1;
grant employee_role to SKING;

prompt ***** Create Karen Partners (Sales Manager rep. to SKing)
create user KPARTNER identified by welcome1;
grant employee_role to KPARTNER;

prompt ***** Create Louise Doran (Sales Rep in Karen Partners team)
create user LDORAN identified by welcome1;
grant employee_role to LDORAN;

prompt ***** Create Eleni Zlotkey (another Sales Manager)
create user EZLOTKEY identified by welcome1;
grant employee_role to EZLOTKEY;

prompt ***** Create Matthias Hannah (Customer)
create user "MATTHIAS.HANNAH@GREBE.COM" identified by welcome1;
grant customer_role to "MATTHIAS.HANNAH@GREBE.COM";

 

Back to Topic List

Enabling a VPD Policy on the ORDERS Table

You apply a VPD policy to the ORDERS table, so that both internal (employees) and external users (customers) have access only to their information. Perform the following steps:

1.

From your SQL*Plus window, execute the following script: 

@c:\wkdir\enable_vpd_policy

The enable_vpd_policy.sql script contains the following: 

connect sec_admin/welcome1;
prompt *** Create policy function to be called when 'ORDERS' table is accessed
create or replace function f_policy_orders
-- 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_employee_id    number:=0;
 v_customer_id    number:=0;
 is_sales_rep     number:=0;
 is_sales_manager number:=0;
 is_president     number:=0;
 is_customer      number:=0;
 is_employee      number:=0;
 v_job_id        varchar2(20);
 v_user          varchar2(100);
 out_string      varchar2(400) default '1=2 ';
 -- out_string will be the return value. 
 -- It is initialized to '1=2' because 'WHERE 1=2' means 
 -- 'Nothing to access' and this can be combined with 
 -- other conditions by OR
begin
  -- get session user
  v_user := lower(sys_context('userenv','session_user'));
  -- Is the user a customer?
  begin
    select customer_id into v_customer_id from oe.customers 
    where lower(cust_email) = v_user;
    is_customer:=1;
  exception
   when no_data_found then 
     v_customer_id := 0;  
  end;  
  -- Is the user an employee?
  begin
    select employee_id,job_id into v_employee_id,v_job_id 
    from hr.employees where lower(email) = v_user;
    is_employee:=1;
  exception
   when no_data_found then 
    v_employee_id := 0;
  end;
  -- get role of employee if user is an employee
  if     v_employee_id != 0  and v_job_id='SA_REP' then
     -- User is Sales Rep
     is_sales_rep := 1;
  elsif v_employee_id != 0  and v_job_id='SA_MAN' then
     -- User is Sales Manager
     is_sales_manager := 1;
  elsif v_employee_id != 0  and v_job_id='AD_PRES' then
     -- User is President
     is_president := 1;
  end if;
 -- Now create the string to be used as the WHERE clause. If the user
    is e.g. sales rep and customer, both conditions are valid.
  if is_president = 1 or v_user='oe' then
  -- The president and the owner of the table (OE) are allowed to see
    all orders (WHERE 1=1 or anything) means all rows
  out_string := out_string||'or 1=1 ';
 end if;
 if is_customer = 1 then
  -- Customers are allowed to see their orders only
  out_string := out_string||'or customer_id = '||v_customer_id||' ';
 end if;
 if is_sales_rep = 1 then
  -- Sales Reps are allowed to see orders they have worked on
  out_string := out_string||'or sales_rep_id = '||v_employee_id||' ';
 end if;
 if is_sales_manager = 1 then
  -- Sales Managers are allowed to see orders of customers
    who belong to their Sales Reps; 
  -- In this case the WHERE clause needs a subquery in order
    to find if their sales reps have any customers in the orders table:
 out_string := out_string||'or sales_rep_id in 
   (select employee_id from hr.employees where manager_id = '||v_employee_id||')';
 end if;
 -- If the user is none of the above the WHERE clause will be
 (WHERE 1=2), the default and that means nothing to access
 return out_string;
end;
/

 
2.

From your SQL*Plus window, execute the following script: 

@c:\wkdir\apply_vpd_policy

The apply_vpd_policy.sql script contains the following: 

begin
dbms_rls.add_policy('oe','orders','accesscontrol_orders','sec_admin',
   'f_policy_orders',policy_type => dbms_rls.context_sensitive);
end;
/

 

Back to Topic List

Testing the VPD Policy on the ORDERS table

Now you can test the policy. Perform the following steps:

1.

The user Matthias has a CUSTOMER_ID of 106. The security policy verifies his login name in the Application Context against the CUSTOMERS table and then allows access only to his own orders in the ORDERS table. From your SQL*Plus window, execute the following script:

@c:\wkdir\select_orders_as_matthias

connect "MATTHIAS.HANNAH@GREBE.COM"/welcome1;
select ORDER_ID, ORDER_TOTAL, CUSTOMER_ID from oe.orders;

 
2.

Louise Doran is a Sales Rep with EMPLOYEE_ID=160. Run a query to show only the orders placed by her own customers. From your SQL*Plus window, execute the following script:

@c:\wkdir\select_orders_as_ldoran

connect LDORAN/welcome1;
select ORDER_ID, CUSTOMER_ID, ORDER_TOTAL, SALES_REP_ID from oe.orders;

 
3.

Karen Partner is a Sales Manager with EMPLOYEE_ID=146. Run a query to show only the orders placed by customers of her team of Sales Reps. From your SQL*Plus window, execute the following script:

@c:\wkdir\select_orders_as_kpartner

connect KPARTNER/welcome1;
select ORDER_ID, CUSTOMER_ID, ORDER_TOTAL, SALES_REP_ID
    from oe.orders order by sales_rep_id;

 
4.

Run a query to see how many orders are in the ORDERS table as the OE owner. From your SQL*Plus window, execute the following script:

@c:\wkdir\select_count_orders_as_oe

connect oe/oe;
select count(*) from oe.orders;

 
5.

Now run the same query as Steven King. Notice how the result is the same number and he can see all the orders. This is because he is the president and can see everything. From your SQL*Plus window, execute the following script:

@c:\wkdir\select_count_orders_as_sking

connect SKING/welcome1;
select count(*) from oe.orders;

 

Back to Topic List

Viewing the Policy on the ORDERS Table

The VPD policy appends a WHERE clause to all queries against the protected table. To verify the WHERE clauses are correct (your auditor may ask for this), perform the following steps:

1.

From your SQL*Plus window, execute the following script:

@c:\wkdir\vpd_where_clause_orders
connect system/oracle
select predicate as "predicate (WHERE clause)" from v$vpd_policy 
  where object_name='ORDERS';

Note: After the policy is applied to the table, all queries by authorized users are filtered according to the WHERE clauses defined in the policy function. In order to improve performance, you can apply an index to the protected table over the column used in the WHERE clause. In this tutorial, several indexes are already applied to the ORDERS table by default.

 

Back to Topic List

Enabling a VPD Policy with Relevant Columns and Column Filtering on the CUSTOMERS Table

In this section, you enable and apply a VPD policy with Relevant Columns and Column Filtering. This policy is applied to the CUSTOMERS table, so that only employees have access to information they really need. Customers are not granted any access rights. Access to confidential information (i.e. CREDIT_LIMIT) is regulated by the policy. Perform the following steps:

1.

From your SQL*Plus window, execute the following script:

@c:\wkdir\enable_vpd_policy2

connect sec_admin/welcome1;
create or replace function f_policy_customers
-- 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_employee_id    number:=0;
 is_sales_manager number:=0;
 is_sales_rep     number:=0;
 is_president     number:=0;
 is_employee      number:=0;
 v_job_id        varchar2(20);
 v_user          varchar2(100);
 out_string      varchar2(400) default '1=2 ';
 -- out_string will be the return value. 
 -- It is initialized to '1=2' because 'WHERE 1=2' means 
 -- 'Nothing to access' and this can be combined with 
 -- other conditions by OR
begin
  -- get session user
  v_user := lower(sys_context('userenv','session_user'));
  begin
    select employee_id,job_id into v_employee_id,v_job_id from hr.employees
    where lower(email) = v_user;
    is_employee:=1;
  exception
   when no_data_found then 
    v_employee_id := 0;
  end;
  -- get role of employee:
  if    v_employee_id != 0  and v_job_id='SA_MAN' then
     -- User is Sales Manager
     is_sales_manager := 1;
  elsif v_employee_id != 0  and v_job_id='AD_PRES' then
     -- User is President
     is_president := 1;
  end if;
 -- Now create the string to be used as the WHERE clause;  
  if is_president = 1 or v_user='oe' then
  -- The president and the owner of the table (OE) are allowed
    to see all orders (WHERE 1=1 or anything) means all rows
  out_string := out_string||'or 1=1 ';
 end if;
 if is_sales_manager = 1 then
  -- Sales Managers are allowed to see all information about their
    own customers; the Credit Limit of all other customers is hidden
    from them:
 out_string := out_string||'or ACCOUNT_MGR_ID = '||v_employee_id||'';
 end if;
 -- If the user is none of the above the WHERE clause will be (WHERE 1=2),
    the default and that means nothing to access
 return out_string;
end;
/

 
2.

Now you can apply the policy to the table. To increase performance, the parameter context_sensitive is added, so that the function is only executed when the content of the application context changes (i.e. when a new user logs in). From your SQL*Plus window, execute the following script: 

@c:\wkdir\apply_vpd_policy2
begin
dbms_rls.add_policy('oe','customers','accesscontrol_customers','sec_admin',
  'f_policy_customers', sec_relevant_cols=>'CREDIT_LIMIT', 
  sec_relevant_cols_opt => dbms_rls.ALL_ROWS, 
  policy_type => dbms_rls.context_sensitive);
end;
/

 

Back to Topic List

Testing the VPD Policy on the CUSTOMERS table

Now you can test the policy. Perform the following steps:

1.

Matthias Hannah is using a customer_role, which does not include access rights to the CUSTOMERS table at all. From your SQL*Plus window, execute the following script:

@c:\wkdir\select_customers_as_matthias

connect "MATTHIAS.HANNAH@GREBE.COM"/welcome1;
select * from oe.customers;

 
2.

Eleni Zlotkey is a Sales Manager with EMPLOYEE_ID=149. She is able to only see the Credit Limit for the customers she worked on. All others are hidden from her. From your SQL*Plus window, execute the following script:

@c:\wkdir\select_customers_as_ezlotkey

connect EZLOTKEY/welcome1;
select CUST_FIRST_NAME, CUST_LAST_NAME, CREDIT_LIMIT, ACCOUNT_MGR_ID 
  from oe.customers order by ACCOUNT_MGR_ID;

Scroll up a bit to see another ACCOUNT_MGR_ID. Notice that the credit limit is hidden.

 
3.

Run a query to see how the list of customers for Steven King. Since he is the President, he can see everything.. From your SQL*Plus window, execute the following script:

@c:\wkdir\select_customers_as_sking

connect SKING/welcome1;
select CUST_FIRST_NAME, CUST_LAST_NAME, CREDIT_LIMIT, ACCOUNT_MGR_ID 
   from oe.customers order by ACCOUNT_MGR_ID;

Scroll up a bit to see that Steven King can see all the credit limits for any ACCOUNT_MGR_ID.

 

Back to Topic List

Viewing the Policy on the CUSTOMERS Table

The VPD policy appends a WHERE clause to all queries against the protected table. To verify the WHERE clauses are correct (your auditor may ask for this), perform the following steps:

1.

From your SQL*Plus window, execute the following script:

@c:\wkdir\vpd_where_clause_customers
connect system/oracle
select predicate as "predicate (WHERE clause)" 
   from v$vpd_policy where object_name='CUSTOMERS';

Note: After the policy is applied to the table, all queries by authorized users are filtered according to the WHERE clauses defined in the policy function. In order to improve performance, you can apply an index to the protected table over the column used in the WHERE clause. In this tutorial, several indexes are already applied to the CUSTOMERS table by default.

 

Back to Topic List

Cleanup

Perform the following steps to cleanup your database:

1.

From your SQL*Plus window, execute the following script:

@c:\wkdir\vpd_cleanup
connect hr_sec/welcome1;
drop user SKING cascade;
drop user KPARTNER cascade;
drop user LDORAN cascade;
drop user EZLOTKEY cascade;
drop user "MATTHIAS.HANNAH@GREBE.COM" cascade;
drop role employee_role;
drop role customer_role;

connect sec_admin/welcome1;
execute dbms_rls.drop_policy('oe','orders','accesscontrol_orders');
execute dbms_rls.drop_policy('oe','customers','accesscontrol_customers');
drop function f_policy_orders;
drop function f_policy_customers;

connect / as sysdba;
drop user sec_admin cascade;
drop user HR_sec cascade;

 

Back to Topic List

Summary

In this tutorial, you learned how to:

Enable a VPD policy
View the policy
Apply a VPD policy with relevant columns and column filtering

Back to Topic List

Place your cursor on this icon to hide all screenshots.