Restricting Data Access Using Virtual Private Database
Restricting Data Access Using Virtual Private Database
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.
Approximately 30 minutes
This tutorial covers the following topics:
Place the cursor over this icon to load and view all
the screenshots for this tutorial. (Caution: Because this action loads all screenshots
simultaneously, 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.
The Virtual Private Database (VPD) enables data access control
by user or by customer with the assurance of physical data separation. 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
Before starting this tutorial, you should have:
Back to Topic List
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. |
Open a terminal window. Change directory to /home/oracle/wkdir.
Start SQL*Plus with the /nolog option. In the SQL*Plus session, execute
the following script:
@create_vpd_users_and_roles.sql
set echo off pagesize 20000
connect system/<password>
set echo off
prompt *** Create some end users.
prompt
prompt ***** Create STEVEN.KING@OSRD.COM (President)
create user "STEVEN.KING@OSRD.COM" identified by welcome1;
grant create session to "STEVEN.KING@OSRD.COM";
prompt
prompt ***** Create LOUISE.DORAN@OSRD.COM (Sales Rep)
create user "LOUISE.DORAN@OSRD.COM" identified by welcome1;
grant create session to "LOUISE.DORAN@OSRD.COM";
prompt
prompt ***** Create NEENA.KOCHHAR@OSRD.COM (AD_VP)
create user "NEENA.KOCHHAR@OSRD.COM" identified by welcome1;
grant create session to "NEENA.KOCHHAR@OSRD.COM";
prompt
prompt ***** Create SUSAN.MAVRIS@OSRD.COM (HR_REP)
create user "SUSAN.MAVRIS@OSRD.COM" identified by welcome1;
grant create session to "SUSAN.MAVRIS@OSRD.COM";
grant update on hr.employees to "SUSAN.MAVRIS@OSRD.COM";
prompt
prompt ***** Create ELENI.ZLOTKEY@OSRD.COM (Sales Manager)
create user "ELENI.ZLOTKEY@OSRD.COM" identified by welcome1;
grant create session to "ELENI.ZLOTKEY@OSRD.COM";
prompt
prompt ***** Create a role to get access to CUSTOMERS
drop role ots_role;
create role ots_role;
grant ots_role to public;
prompt
prompt ***** Grant object privileges to role ots_role
connect oe/oe
grant select on customers to ots_role;
prompt
prompt **** Update the email addresses of the users we just created
connect hr/hr
update employees set email='STEVEN.KING@OSRD.COM' where email='SKING';
update employees set email='LOUISE.DORAN@OSRD.COM' where email='LDORAN';
update employees set email='NEENA.KOCHHAR@OSRD.COM' where email='NKOCHHAR';
update employees set email='SUSAN.MAVRIS@OSRD.COM' where email='SMAVRIS';
update employees set email='ELENI.ZLOTKEY@OSRD.COM' where email='EZLOTKEY';
|
| |
In this tutorial, you use the EMPLOYEES and ORDERS tables.
Describe what is in those tables. From a SQL*Plus session, execute the
following script:
@describe_tables.sql
set pagesize 20000
connect oe/oe
prompt *** The ORDERS table:
desc orders
connect hr/hr
prompt
prompt *** The EMPLOYEES table:
desc employees
|
Back to Topic List
Currently, Eleni, a user, can connect to the CUSTOMERS table
and see the credit_limit information for all customers (her customers and her
colleague's). Perform the following:
| 1. |
From a SQL*Plus session, execute the following script:
@select_customers_as_eleni.sql
set echo off pagesize 20000
prompt *** Connect as user ELENI.ZLOTKEY@OSRD.COM
prompt (Account Manager with Employee ID 149)
set echo on
connect "ELENI.ZLOTKEY@OSRD.COM"/welcome1
select cust_last_name, cust_first_name, credit_limit, account_mgr_id
from oe.customers
order by account_mgr_id;
set echo off
|
Back to Topic List
To restrict Eleni from viewing her colleague's customers credit_limit
information, you need to create a policy function with a relevant column. Perform
the following steps:
| 1. |
From a SQL*Plus session, execute the following script:
@enable_vpd_policy.sql
set echo off pagesize 20000
connect system/<password>
set echo off
prompt *** Drop Policy in case there is one.
prompt If there is none then an error is generated. Do not mind.
set echo on
begin
dbms_rls.drop_policy('oe','customers','accesscontrol_customers');
end;
/
set echo off
prompt
prompt *** Create policy function
set echo on
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;
v_user varchar2(100);
-- 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
out_string varchar2(400) default '1=2 ';
begin
-- get session user
v_user := lower(sys_context('userenv','session_user'));
-- if session user is not SECUSER,
-- then the connection is using a persionalized database user
-- if session user is SECUSER,
-- then the connection is using an application user
if v_user != 'secuser' then
null;
else
v_user := lower(sys_context('userenv','proxy_user'));
end if;
-- Now get the employee ID of the user
begin
select employee_id into v_employee_id
from hr.employees where lower(email)=lower(v_user);
exception
when no_data_found then
v_employee_id:=0;
end;
-- Now generate the WHERE clause
out_string:=out_string||' or account_mgr_id = '||v_employee_id;
return out_string;
end;
/
set echo off
prompt
prompt *** The policy function must be accessible for all users
set echo on
grant execute on f_policy_customers to public;
set echo off
prompt
prompt *** Turn policy on
set echo on
begin
dbms_rls.add_policy('oe','customers','accesscontrol_customers',
'system','f_policy_customers',sec_relevant_cols=>'CREDIT_LIMIT');
end;
/
set echo off
prompt
prompt *** Grant SELECT privilege on hr.employees to PUBLIC (all users).
prompt This is NOT RECOMMENDED to do in real world,
prompt but shows the power of Virtual Private Database
set echo on
grant select on oe.customers to public;
|
| 2. |
Now that the policy has been applied, execute the same
SELECT statement as Eleni to query customer credit limits. From a SQL*Plus
session, execute the following script:
@select_customers_as_eleni.sql
set echo off pagesize 20000
prompt *** Connect as user ELENI.ZLOTKEY@OSRD.COM
prompt (Account Manager with Employee ID 149)
set echo on
connect "ELENI.ZLOTKEY@OSRD.COM"/welcome1
select cust_last_name, cust_first_name, credit_limit, account_mgr_id
from oe.customers
order by account_mgr_id;
set echo off
Notice that you only see the Eleni's customers now.
|
| 3. |
If you perform another query without the credit_limit
column, you get different results. From a SQL*Plus session, execute the
following script:
@select_customers_as_eleni_wo_cl.sql
set echo off pagesize 20000
prompt *** Connect as user ELENI.ZLOTKEY@OSRD.COM
prompt (Account Manager with Employee ID 149)
set echo on
connect "ELENI.ZLOTKEY@OSRD.COM"/welcome1
select cust_last_name, cust_first_name, account_mgr_id
from oe.customers
order by account_mgr_id;
set echo off
|
Back to Topic List
When the policy is applied, a WHERE clause is added to the
v$vpd_policy table. To view the policy, perform the following steps:
| 1. |
From a SQL*Plus session, execute the following script:
@vpd_where_clause.sql
set echo off
connect system/<password>
set echo on
select policy, predicate as "predicate (WHERE clause)" from v$vpd_policy;
|
Back to Topic List
In this case, you apply a policy so that Eleni can see all
her customers and her colleague's customers. Only the credit limit for Eleni's colleague's
customers will be blank. Perform the following steps:
| 1. |
From SQL*Plus, execute the following script:
@enable_vpd_relevant_columns_filtering.sql
-- Function will return a string that is used as a WHERE clause
return varchar2
as
v_employee_id number;
v_user varchar2(100);
-- 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
out_string varchar2(400) default '1=2 ';
begin
-- get session user
v_user := lower(sys_context('userenv','session_user'));
-- if session user is not SECUSER, then the connection
-- is using a persionalized database user
-- if session user is SECUSER, then the connection is using
-- an application user
if v_user != 'secuser' then
null;
else
v_user := lower(sys_context('userenv','proxy_user'));
end if;
-- Now get the employee ID of the user
begin
select employee_id into v_employee_id
from hr.employees where lower(email)=lower(v_user);
exception
when no_data_found then
v_employee_id:=0;
end;
-- Now generate the WHERE clause
out_string:=out_string||' or account_mgr_id = '||v_employee_id;
return out_string;
end;
/
set echo off
prompt
prompt *** The policy function must be accessible for all users
set echo on
grant execute on f_policy_customers to public;
set echo off
prompt
prompt *** Turn policy on
set echo on
begin
dbms_rls.add_policy('oe','customers','accesscontrol_customers',
'system','f_policy_customers',sec_relevant_cols=>'CREDIT_LIMIT',
sec_relevant_cols_opt => dbms_rls.ALL_ROWS);
end;
/
set echo off
prompt
prompt *** Grant SELECT privilege on hr.employees to PUBLIC (all users).
prompt This is NOT RECOMMENDED to do in real world, but shows the power
prompt of Virtual Private Database
set echo on
grant select on oe.customers to public;
|
| 2. |
Now you can test the policy by executing the select_customers_as_eleni.sql
script again.
@select_customers_as_eleni.sql
set echo off pagesize 20000
prompt *** Connect as user ELENI.ZLOTKEY@OSRD.COM
prompt (Account Manager with Employee ID 149)
set echo on
connect "ELENI.ZLOTKEY@OSRD.COM"/welcome1
select cust_last_name, cust_first_name, credit_limit, account_mgr_id
from oe.customers
order by account_mgr_id;
set echo off
Scroll up to account manager ID 148.
Notice that you now see Eleni's customers with their
credit limits and all other customers with their credit limits blank.
|
Back to Topic List
In this tutorial, you've learned how to:
 |
Enable a VPD Policy with Relevant Columns |
|
View the Policy |
|
Apply a VPD Policy with relevant Columns
and Column Filtering |
Back to Topic List
|
To learn more about
Oracle Database 10g, refer to additional OBEs on the OTN
Web site. |
|
To ask a question about this OBE tutorial,
post a query on the OBE
Discussion Forum. |
Back to Topic List
|
Bookmark
