| |
Creating a Virtual Private Database
Module Objectives
Purpose
In this module, you will learn how to create and
test a virtual private database.
Objectives
After completing this module, you should be able to:
Prerequisites
Before starting this module, you should have completed the following:
Reference Material
The following is a list of useful reference material if you want additional
information about the topics in this module:
 |
Documentation: Part Number A88876-02: Oracle9i
Application Developer's Guide - Fundamentals
|
Overview
Virtual Private Database in Oracle8i
and Oracle9i
Oracle8i set
a new standard in database security with the introduction of Virtual Private
Database (VPD): server-enforced, fine-grained access control, together
with secure application context, which enables multiple customers and
partners to have secure direct access to mission-critical data. Within
a single database, the Virtual Private Database 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. Web-hosting companies can
maintain data of multiple companies in the same Oracle9i
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. Virtual Private Database is
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.
The Virtual Private Database secures data in the database by providing
security at the row level, across all applications, by attaching a security
policy directly to a table or view. Oracle9i
expands the Virtual Private Database by adding several new enhancements:
|
Oracle Policy Manager, a tool to facilitate security
policy administration |
|
Partitioned fine-grained access control, to ease VPD
deployment in multi-application and hosted environments |
|
Global application context, to support application user
models |
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, 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.
A secure application context enables access conditions to be based on
virtually any attributes an application deems significant, such as organization,
cost center, account number, or position. For example, a Web order entry
system can enforce access based on customer number, and whether the user
is a customer or a sales representative. In this way, customers can view
their order status online (but only for their own orders), while sales
representatives can view multiple orders, but only for their own customers.
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 their only 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.
Setup
You must be able to identify the type of user who is connecting and to
set some attributes about the user that will be used in the security policies.
You do this with an application context. You will grant the OE and HR
users the privilege to create application contexts, add the user_id
column to the EMPLOYEES
and CUSTOMERS tables, create
the test users, and grant the HR user access to the CUSTOMERS
table in the OE schema. Perform the following:
| 1. |
From a SQL*Plus session connected to the your database, execute
the following:
@vpd_setup.sql
connect sys/oracle@orcl.world as SYSDBA;
Grant CREATE ANY CONTEXT to OE;
GRANT CREATE ANY CONTEXT to HR;
/***************************************************************/
/* Add USER_ID column to the EMPLOYEES table in the HR schema */
/***************************************************************/
connect hr/hr@orcl.world
Alter table employees add (user_id varchar (8));
update employees set user_id = 'OE1' where employee_id = 201;
commit;
/***************************************************************/
/* Add USER_ID column to the CUSTOMERS table in the OE schema */
/***************************************************************/
connect oe/oe@orcl.world
Alter table customers add (user_id varchar (8));
update customers set user_id = 'CU1' where customer_id = 125;
commit;
/**************************************************************/
/* Add test users and role */
/**************************************************************/
connect sys/oracle@orcl.world as sysdba;
create role order_entry_role;
grant order_entry_role to public;
grant connect, resource, select any sequence, insert any table,
update any table to order_entry_role;
create user oe1 identified by oe1;
alter user oe1 default tablespace example temporary tablespace temp
default role order_entry_role;
create user cu1 identified by cu1;
alter user cu1 default tablespace example temporary tablespace temp
default role order_entry_role;
/**************************************************************/
/* Grant HR access to OE tables */
/**************************************************************/
connect oe/oe@orcl.world
grant select on oe.customers to HR;
|
Create the OE and HR User's Application Context and Corresponding Packages
Create the user's application context and the corresponding package that
sets variables in this context. Perform the following:
| 1. |
You create the OE and HR context package by executing the following
script:
@vpd_create_context.sql
CONNECT hr/hr@orcl.world;
CREATE OR REPLACE CONTEXT HR USING HR.Context_Package;
CREATE OR REPLACE PACKAGE Context_Package AS
PROCEDURE Set_Context;
END;
/
SHOW ERRORS
|
| 2. |
Now create the package bodies for the Context_Package by executing
the following scripts:
@vpd_create_context_package.sql
CONNECT hr/hr@orcl.world ;
CREATE OR REPLACE PACKAGE BODY Context_Package IS
PROCEDURE Set_Context IS
This_PERSON_ID NUMBER;
BEGIN
/* Record the fact that we are setting up a users context */
/* We do this as we need to look up the user in the employee */
/* table when they log on, and we do not want the employee */
/* security policy to interfere with this lookup */
Dbms_Session.Set_Context('HR','SETUP','TRUE');
IF SUBSTR(SYS_CONTEXT('USERENV','SESSION_USER'),1,2)='OE'
THEN
BEGIN
/* User is not a customer, therefore they must be an employee*/
/* Set their Employee Id */
Dbms_Session.Set_Context('HR','ROLE', 'INTERNAL');
SELECT Employee_Id INTO This_PERSON_ID
FROM HR.Employees
WHERE User_Id = SYS_CONTEXT('USERENV','SESSION_USER');
Dbms_Session.Set_Context('HR','PERSON_ID', This_PERSON_ID);
EXCEPTION
WHEN NO_DATA_FOUND THEN
/* No Employee Id found, set a dummy Id */
Dbms_Session.Set_Context('HR','PERSON_ID', 0);
END;
END IF;
IF SUBSTR(SYS_CONTEXT('USERENV','SESSION_USER'),1,2)='CU'
THEN
/* If User is a customer, check to see if they have been */
/* given access */
BEGIN
Dbms_Session.Set_Context('HR','ROLE', 'EXTERNAL');
SELECT customer_id INTO This_PERSON_ID
FROM oe.customers
WHERE user_id = SYS_CONTEXT('USERENV','SESSION_USER');
Dbms_Session.Set_Context('HR','PERSON_ID', This_PERSON_ID);
EXCEPTION
WHEN NO_DATA_FOUND THEN
/* Customer has no access, so set a dummy id */
Dbms_Session.Set_Context('HR','PERSON_ID', 0);
END;
END IF;
Dbms_Session.Set_Context('HR','SETUP','FALSE');
END Set_Context;
END Context_Package;
/
SHOW ERRORS
|
| 3. |
Now make the packages public by executing the following scripts:
@vpd_grant_context_package_public.sql
Connect hr/hr@orcl.world;
GRANT EXECUTE ON hr.Context_Package to Public;
|
| 4. |
You now must create the triggers to invoke the Context_Package
when necessary by executing the following scripts:
@vpd_create_context_trigger.sql
CONNECT sys/oracle@orcl.world AS SYSDBA;
CREATE OR REPLACE TRIGGER HR.Set_Security_Context
AFTER LOGON ON DATABASE
BEGIN
HR.Context_Package.Set_Context;
END;
/
SHOW ERRORS
|
Test the Application Context
Before you implement the security policies, you will quickly test whether
the user application context is set correctly.
| 1. |
Run the following script to check which user is an employee:
@vpd_test_context_oe1.sql
CONNECT oe1/oe1@orcl.world
COLUMN Username FORMAT A8
COLUMN Role FORMAT A8
COLUMN Id FORMAT A4
SELECT SYS_CONTEXT('USERENV','SESSION_USER') Username,
SYS_CONTEXT('HR', 'ROLE') Role,
SYS_CONTEXT('HR', 'PERSON_ID') Id
FROM dual;
|
| 2. |
Run the following script to try a customer:
@vpd_test_context_cu1.sql
CONNECT cu1/cu1@orcl.world
COLUMN Username FORMAT A8
COLUMN Role FORMAT A8
COLUMN Id FORMAT A4
SELECT SYS_CONTEXT('USERENV','SESSION_USER') Username,
SYS_CONTEXT('HR', 'ROLE') Role,
SYS_CONTEXT('HR', 'PERSON_ID') Id
FROM dual;
|
| 3. |
Try other valid users who do not necessarily have order entry capability.
Run the following script:
@vpd_test_context_oe.sql
CONNECT oe/oe@orcl.world
COLUMN Username FORMAT A8
COLUMN Role FORMAT A8
COLUMN Id FORMAT A4
SELECT SYS_CONTEXT('USERENV','SESSION_USER') Username,
SYS_CONTEXT('HR', 'ROLE') Role,
SYS_CONTEXT('HR', 'PERSON_ID') Id
FROM dual;
|
Create the Security Policies and Functions
You can now create the PL/SQL functions to implement the security policies
that are required. A security policy function decides which SQL predicate
to return, based on the user's application context, to qualify that user's
access to data. You can also prevent users from inserting data that they
are not permitted to insert. You will use three security policies to do
the following:
- Prevent customers from creating anything other than online orders,
and internal Order Entry users from creating anything other than direct
orders. This prevents Order Entry users from claiming compensation for
an order they didn't actually process.
- Prevent customers from seeing orders for any customer other than
themselves
- Prevent employees from accessing employee data about anyone other
than themselves or their direct reports
| 1. |
Create the Security_Package
package specification by executing the following script:
@vpd_create_security_package.sql
CONNECT OE/OE@orcl.world;
CREATE OR REPLACE PACKAGE Security_Package AS
FUNCTION Sale_Orders_Insert_Security(Owner VARCHAR2, Objname VARCHAR2)
RETURN VARCHAR2;
FUNCTION Sale_Orders_Select_Security(Owner VARCHAR2, Objname VARCHAR2)
RETURN VARCHAR2;
FUNCTION Employees_Select_Security (Owner VARCHAR2, Objname VARCHAR2)
RETURN VARCHAR2;
END Security_Package;
/
SHOW ERRORS
|
| 2. |
Create the Security_Package
package body by executing the following script:
@vpd_create_security_package_body.sql
CONNECT Oe/Oe@orcl.world;
CREATE OR REPLACE PACKAGE BODY Security_Package IS
FUNCTION Employees_Select_Security(Owner VARCHAR2, Objname
VARCHAR2)
RETURN VARCHAR2 IS
Predicate VARCHAR2(2000);
BEGIN
/* If we are setting up a users context then we don't */
/* want queries against the employee table to be restricted */
/* so simply return a null predicate */
IF SYS_CONTEXT('OE','SETUP')='TRUE'
THEN
Predicate := null;
RETURN Predicate;
END IF;
/* If the user is a customer (i.e External), or if for some */
/* reason we could not find a valid employee id, return a */
/* predicate that prevents any access to the data. Otherwise*/
/* qualify the query so that users may only see their own */
/* or their sub-ordinates information */
IF (SYS_CONTEXT('HR','ROLE') = 'EXTERNAL')
OR (SYS_CONTEXT('HR', 'PERSON_ID') = 0)
THEN
Predicate := '1=2';
ELSE
Predicate := 'EMPLOYEE_ID = SYS_CONTEXT(''HR'',''PERSON_ID'')
OR MANAGER_ID = SYS_CONTEXT(''HR'',''PERSON_ID'')';
END IF;
RETURN predicate;
END Employees_Select_Security;
FUNCTION Sale_Orders_Select_Security(Owner VARCHAR2, Objname
VARCHAR2)
RETURN VARCHAR2 IS
Predicate VARCHAR2(2000);
BEGIN
Predicate := '1=2';
IF (SYS_CONTEXT('HR', 'ROLE') = 'INTERNAL')
THEN
/* If the user is an employee (i.e. Internal), then they */
/* can see all sale orders. Customers (i.e External) can */
/* only see the sale orders that are their own */
Predicate := NULL;
ELSE
Predicate := 'CUSTOMER_ID =
SYS_CONTEXT(''HR'',''PERSON_ID'')';
END IF;
RETURN Predicate;
END Sale_Orders_Select_Security;
FUNCTION Sale_Orders_Insert_Security(Owner VARCHAR2, Objname
VARCHAR2)
RETURN VARCHAR2 IS
Predicate VARCHAR2(2000);
BEGIN
Predicate := '1=2';
IF (SYS_CONTEXT('HR', 'ROLE') = 'INTERNAL')
THEN
/* Internal employees can only create direct orders */
Predicate := 'ORDER_MODE = ''direct'' ';
ELSE
/* External customers can only create online order */
Predicate := 'ORDER_MODE = ''online'' ';
END IF;
RETURN Predicate;
END Sale_Orders_Insert_Security;
END Security_Package;
/
SHOW ERRORS
|
| 3. |
Now make the security policy functions accessible to
the public. Execute the following script:
@vpd_grant_security_package_public.sql
CONNECT OE/OE@orcl.world;
GRANT EXECUTE ON Oe.Security_Package TO PUBLIC;
|
Test the Security Policies
You are now ready to test the security policies that you just created.
Perform the following:
| 1. |
You will now test the security policies, first as an Order Entry
user. Execute the following:
@vpd_test_security_package_oe1.sql
CONNECT oe1/oe1@orcl.world;
COLUMN Username FORMAT A8
COLUMN Role FORMAT A5
COLUMN Id FORMAT A2
COLUMN Predicate FORMAT A45
SELECT SYS_CONTEXT('USERENV','SESSION_USER') Username,
SYS_CONTEXT('OE', 'ROLE') Role,
SYS_CONTEXT('OE', 'PERSON_ID') Id,
'EMPLOYEES' Object,
'SELECT' DML,
OE.security_package.Employees_Select_Security('HR','EMPLOYEES')
Predicate
FROM dual
UNION ALL
SELECT SYS_CONTEXT('USERENV','SESSION_USER') Username,
SYS_CONTEXT('OE', 'ROLE') Role,
SYS_CONTEXT('OE', 'PERSON_ID') Id,
'ORDERS' Object,
'SELECT' DML,
OE.security_package.Sale_Orders_Select_Security('OE','ORDERS')
Predicate
FROM dual
UNION ALL
SELECT SYS_CONTEXT('USERENV','SESSION_USER') Username,
SYS_CONTEXT('OE', 'ROLE') Role,
SYS_CONTEXT('OE', 'PERSON_ID') Id,
'ORDERS' Object,
'INSERT' DML,
OE.security_package.Sale_Orders_Insert_Security('OE','ORDERS')
Predicate
FROM dual;
The Order Entry user OE1
is identified as an internal user, and his employee ID is set in
the his application context. For SELECT
statements on the EMPLOYEES
table, the user is able to see only his own details and the details
of any subordinates. The Order Entry has a null predicate on the
SALE ORDERS table,
so you can see all sale orders. The Order Entry user can insert
only direct sale orders.
|
| 2. |
Now try this function as a customer. Execute the following:
@vpd_test_security_package_cu1.sql
CONNECT cu1/cu1@orcl.world;
COLUMN Username FORMAT A8
COLUMN Role FORMAT A8
COLUMN Id FORMAT A3
COLUMN DML FORMAT A6
COLUMN Predicate FORMAT A37
SELECT SYS_CONTEXT('USERENV','SESSION_USER') Username,
SYS_CONTEXT('HR', 'ROLE') Role,
SYS_CONTEXT('HR', 'PERSON_ID') Id,
'EMPLOYEES' Object,
'SELECT' DML,
OE.security_package.Employees_Select_Security('HR','EMPLOYEES')
Predicate
FROM dual
UNION ALL
SELECT SYS_CONTEXT('USERENV','SESSION_USER') Username,
SYS_CONTEXT('HR', 'ROLE') Role,
SYS_CONTEXT('HR', 'PERSON_ID') Id,
'SALE_ORDERS' Object,
'SELECT' DML,
OE.security_package.Sale_Orders_Select_Security('OE','ORDERS')
Predicate
FROM dual
UNION ALL
SELECT SYS_CONTEXT('USERENV','SESSION_USER') Username,
SYS_CONTEXT('HR', 'ROLE') Role,
SYS_CONTEXT('HR', 'PERSON_ID') Id,
'SALE_ORDERS' Object,
'INSERT' DML,
OE.security_package.Sale_Orders_Insert_Security('OE','ORDERS')
Predicate
FROM dual;
The customer user CU1
is identified as an external user and has her customer ID set in
her application context. She will not be able to select any information
from the EMPLOYEES
table, even if given access. The customer can select only sale orders
that have been created for her and will be able to insert only online
sale orders.
|
Apply the Security Policies to the Required Tables
After testing the security policies, you now attach them to the tables
to which you want them applied. Note that a single security policy can
be applied to multiple tables, and it can also be applied to views. Perform
the following:
| 1. |
Add the policy to the table. Execute the following:
@vpd_attach_security_policies.sql
CONNECT system/oracle@orcl.world;
begin
dbms_rls.add_policy('OE',
'ORDERS',
'SALE_ORDERS_INSERT_POLICY',
'OE',
'SECURITY_PACKAGE.SALE_ORDERS_INSERT_SECURITY',
'INSERT',
TRUE);
dbms_rls.add_policy('OE',
'ORDERS',
'SALE_ORDERS_SELECT_POLICY',
'OE',
'SECURITY_PACKAGE.SALE_ORDERS_SELECT_SECURITY',
'SELECT');
dbms_rls.add_policy('HR',
'EMPLOYEES',
'EMPLOYEES_SELECT_POLICY',
'OE',
'SECURITY_PACKAGE.EMPLOYEES_SELECT_SECURITY',
'SELECT');
end;
/
SHOW ERRORS
|
| 2. |
Now check the policies to see whether they have been
set and are enabled. Execute the following:
@vpd_show_security_policies.sql
SELECT object_name,
policy_name,
sel,ins,upd,del,
chk_option,enable
FROM all_policies;
The EMPLOYEES table
is protected with EMPLOYEES_SELECT_POLICY
for SELECT operations.
The SALE_ORDERS table
is protected with SALE_ORDERS_INSERT_POLICY
for INSERT operations
and with SALE_ORDERS_SELECT_POLICY
for SELECT operations.
Other than for purposes of a naming standard, there is no need for
a correlation between the security policy and the tables or views
to which it is attached.
Note: Depending which lessons you have done in the Oracle9i
by Example Series, you may get additional objects.
|
Test the Virtual Private Database Implementation
You will now see the policies in action, first as a customer, then as
an Order Entry employee.
| 1. |
After logging in as a customer, you can see how many online orders
you have placed. Execute the following:
@vpd_test_select_sale_orders_cu1.sql
CONNECT Oe/OE@orcl.world ;
GRANT SELECT ON ORDERS TO PUBLIC;
CONNECT CU1/CU1@orcl.world ;
SELECT COUNT(*) FROM Oe.Orders;
|
| 2. |
Now, after logging in as an employee, you can see how many direct
orders have been placed. Execute the following:
@vpd_test_select_sale_orders_oe1.sql
CONNECT OE/OE@orcl.world ;
GRANT SELECT ON ORDERS TO PUBLIC;
CONNECT OE1/OE1@orcl.world ;
SELECT COUNT(*) FROM Oe.orders;
|
| 3. |
Now log in as user OE1
and try to insert 2 orders, a direct order and an online order.
Execute the following script:
@vpd_test_insert_sale_orders_oe1.sql
CONNECT OE1/OE1@orcl.world ;
INSERT INTO oe.orders VALUES (3000
,TO_TIMESTAMP('17-AUG-99 02.34.12.234359 PM'
,'DD-MON-YY HH.MI.SS.FF AM'
,'NLS_DATE_LANGUAGE=American')
,'direct'
,123
,0
,78279.6
,153
,NULL);
INSERT INTO oe.orders VALUES (3001
,TO_TIMESTAMP('17-AUG-99 02.34.12.234359 PM'
,'DD-MON-YY HH.MI.SS.FF AM'
,'NLS_DATE_LANGUAGE=American')
,'online'
,125
,0
,78279.6
,154
,NULL);
ROLLBACK;
An Order Entry user can insert a direct sales order, but cannot
insert an online sales order.
|
| 4. |
Try the same exercise as a customer. Execute the following script:
@vpd_test_insert_sale_orders_cu1.sql
CONNECT OE1/OE1@orcl.world ;
INSERT INTO oe.orders VALUES (3000
,TO_TIMESTAMP('17-AUG-99 02.34.12.234359 PM'
,'DD-MON-YY HH.MI.SS.FF AM'
,'NLS_DATE_LANGUAGE=American')
,'direct'
,123
,0
,78279.6
,153
,NULL);
INSERT INTO oe.orders VALUES (3001
,TO_TIMESTAMP('17-AUG-99 02.34.12.234359 PM'
,'DD-MON-YY HH.MI.SS.FF AM'
,'NLS_DATE_LANGUAGE=American')
,'online'
,125
,0
,78279.6
,154
,NULL);
ROLLBACK;
A customer cannot insert a direct sale order, but can insert
an online sale order.
|
Reset your Database
Now that you have examined the capabilities of the virtual private database,
you should refresh your database. To do this, perform the following:
| 1. |
From a SQL*Plus session, execute the following:
@vpd_cleanup.sql
spool vpd_cleanup.log
CONNECT system/oracle@orcl.world ;
DROP USER oe CASCADE;
DROP USER hr CASCADE;
DROP USER oe1 CASCADE;
DROP USER cu1 CASCADE;
@d:\oracle\ora90\demo\schema\human_resources\hr_main
@d:\oracle\ora90\demo\schema\order_entry\oe_main
spool off;
Note: You will be prompted for some variables when the
hr_main and oe_main
SQL scripts.
|
Module Summary
In this module, you should have learned how to:
Copyright © 2002 Oracle Corporation. All Rights Reserved.
Close Window
|
Bookmark
