Implement Fine-Grained Access Control
What Is Oracle Virtual Private Database?
Oracle Virtual Private Database (VPD) enables you to create security policies to control database access at the row and column level. Essentially, Oracle Virtual Private Database adds a dynamic WHERE clause to a SQL statement that is issued against the table, view, or synonym to which an Oracle Virtual Private Database security policy was applied.
This modification creates a WHEREcondition (called a predicate) returned by a function implementing the security policy. Oracle Database modifies the statement dynamically, transparently to the user, using any condition that can be expressed in or returned by a function. You can apply Oracle Virtual Private Database policies to SELECT, INSERT, UPDATE, INDEX, and DELETE statements.
For example, suppose a user performs the following query:
SELECT * FROM OE.ORDERS;
The Oracle Virtual Private Database policy dynamically appends the statement with a WHERE clause. For example:
SELECT * FROM OE.ORDERS
WHERE SALES_REP_ID = 159;
In this example, the user can only view orders by Sales Representative 159.
If you want to filter the user based on the session information of that user, such as the ID of the user, then you can create the WHERE clause to use an application context. For example:
SELECT * FROM OE.ORDERS
WHERE SALES_REP_ID = SYS_CONTEXT('USERENV','SESSION_USER');
· Create a Policy Function:
create function auth_orders(
schema_var varchar2,
table_var varchar2
)
return varchar2
is
return_value varchar2(100);
begin
return_value:='sales_rep_id=159';
return return_value;
end
· Create a VPD Policy
begin
dbms_rls.add_policy(
object_schema=>'OE',
object_name=>'orders',
policy_name=>'orders_policy',
function_schema=>'sys',
policy_function=>'auth_orders',
statement_types=>'select,insert,update,delete'
);
end;
/
· Test the policy
After you create the Oracle Virtual Private Database policy, it goes into effect immediately. The next time a user, including the owner of the schema, performs a
SELECT on OE.ORDERS, only the orders by Sales Representative 159 will be accessed.1. Log on as user
OE.2. CONNECT oe
3. Enter password: password
4. Enter the following
SELECT statement:5. SELECT COUNT(*) FROM ORDERS;
The following output should appear:
COUNT(*)--------- 7The policy is in effect for user
OE: As you can see, only 7 of the 105 rows in the orders table are returned.But users with administrative privileges still have access to all the rows in the table.
6. Log back on as user
SYS.7. CONNECT sys/as sysdba
8. Enter password: password
9. Enter the following
SELECT statement:10. SELECT COUNT(*) FROM OE.ORDERS;
The following output should appear:
COUNT(*)--------- 105SQL> select * from all_policies;
OBJECT_OWNER OBJECT_NAME POLICY_GROUP POLICY_NAME PF_OWNER PACKAGE FUNCTION SEL INS UPD DEL IDX CHK ENA STA POLICY_TYPE LON
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ --- --- --- --- --- --- --- --- ------------------------ ---
OE ORDERS SYS_DEFAULT ORDERS_POLICY SYS AUTH_ORDERS YES YES YES YES NO NO YES NO DYNAMIC NO
1* select object_owner,object_name,policy, predicate from v$vpd_policy
SQL> /
OBJECT_OWNER OBJECT_NAME POLICY PREDICATE
------------------------------ ------------------------------ ------------------------------ --------------------------------------------------
OE ORDERS ORDERS_POLICY
OE ORDERS ORDERS_POLICY sales_rep_id=159
Main data dictionary views:
Dba_policies
V$vpd_policy
Implementing a Policy with a Database Session-Based Application Context
In scott schema, we have 2 tables, customers and orders_tab
SQL> select * from scott.customers;
CUST_NO CUST_EMAIL CUST_NAME
---------- -------------------- --------------------
1234 TBROOKE Thadeus Brooke
5678 OWOODS Oberon Woods
2 rows selected.
SQL> select * from scott.orders_tab;
CUST_NO ORDER_NO
---------- ----------
1234 9876
5678 5432
5678 4592
There are 2 users tbrooke & owoods who have access to orders_tab table, now we need to create a context for cust_no which should be set based on the login name of the session:
1. Create a user sysadmin_vpd that will create the vpd policy
2. Create a context that will be based on a procedure:
Create context orders_ctx using set_custno;
3. Create the procedure which will set the context for customer number based on the login name:
create procedure set_custnum
as
custnum number;
begin
select cust_no into custnum from scott.customers where cust_email=SYS_CONTEXT('USERENV','SESSION_USER');
dbms_session.set_context('orders_ctx','cust_no',custnum);
exception
when no_data_found then null;
end set_custnum ;
/
4. Create a trigger which will be fired when any new session logs on
create trigger set_custno_ctx_trig after logon on database
begin
sysadmin_vpd.set_custnum;
end;
/
5. Create the VPD Policy function ( a function which returns where (cust_no = )
create or replace function return_custno(
schema_var varchar2,
table_var varchar2)
return varchar2
as
orders_string varchar2(100);
begin
orders_string := 'cust_no = SYS_CONTEXT(''orders_ctx'',''cust_no'')' ;
return orders_string;
end;
/
6. Create the VPD Policy
begin
dbms_rls.add_policy(
object_schema=>'SCOTT',
Object_name=>'ORDERS_TAB',
POLICY_NAME=>'vpd_policy',
function_schema=>'sysadmin_vpd',
policy_function=>'return_custno',
statement_types=>'select');
end;
/
7. Test by connecting to the tbrooke and then select * from scott.orders_tab;
