This user will create the policy function, apply the policy to the table and create an index on the table to improve performance.

Open a terminal window and execute the following commands:

cd /home/oracle/wkdir
sqlplus /nolog
@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";
       

From your terminal window, execute the following script:

@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;
/

From your terminal window, execute the following script:

@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;
/

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 terminal window, execute the following script:

@select_orders_as_matthias

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

Louise Doran is a Sales Rep with EMPLOYEE_ID=160. You will run a query to show only the orders placed by her own customers. From your terminal window, execute the following script:

@select_orders_as_ldoran

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

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

@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;

You will run a query to see how many orders are in the ORDERS table as the OE owner. From your terminal window, execute the following script:

@select_count_orders_as_oe

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

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 terminal window, execute the following script:

@select_count_orders_as_sking

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

From your terminal window, execute the following script:

@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.

From your terminal window, execute the following script:

@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;
/

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 terminal window, execute the following script:

@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;
/

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

@select_customers_as_matthias

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

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

@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.

You will run a query to see how the list of customers for Steven King. Since he is the President, he can see everything.. From your terminal window, execute the following script:

@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.

From your terminal window, execute the following script:

@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.

From your terminal window, execute the following script:

@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;