Saturday, March 2, 2013

0 Virtual Private Database (VPD) 1: Limit the data at row level base on condition

Virtual Private Database (VPD)  also known as Oracle Row-Level Security (RLS) or Fine Grained Access Control (FGAC).

  • DBMS_RLS.ADD_POLICY  -> Add the policy to the table .
  • DBMS_RLS.DROP_POLICY –> Drop the policy from table.
  • DBMS_RLS.ENABLE_POLICY –> Enable/Disable the policy.

Here are some example:

exec DBMS_RLS.ADD_POLICY(OBJECT_SCHEMA=> 'VPD1',OBJECT_NAME=>'test_employees',POLICY_NAME=>'RLS_TEST1',FUNCTION_SCHEMA=>'VPD1',POLICY_FUNCTION=>'FUN_VPD_EMP');
exec DBMS_RLS.DROP_POLICY(OBJECT_SCHEMA=> 'VPD1',OBJECT_NAME=>'test_employees',POLICY_NAME=>'RLS_TEST1');
 
exec DBMS_RLS.ENABLE_POLICY(OBJECT_SCHEMA=> 'VPD1',OBJECT_NAME=>'test_employees',POLICY_NAME=>'RLS_TEST1',ENABLE=>TRUE);
exec DBMS_RLS.ENABLE_POLICY(OBJECT_SCHEMA=> 'VPD1',OBJECT_NAME=>'test_employees',POLICY_NAME=>'RLS_TEST1',ENABLE=>FALSE);

 

Limit the data base on condition at row level

Setup the test environment

  • Create 2 users, VPD1 and VPD2.
  • Create VPD1.test_employees table base on hr.employees table.
  • Grant VPD2 permission on the VPD1.test_employee table.
CREATE USER VPD1 IDENTIFIED BY VPD1 DEFAULT TABLESPACE users;
CREATE USER VPD2 IDENTIFIED BY VPD2 DEFAULT TABLESPACE users;
ALTER USER VPD1 QUOTA UNLIMITED ON users;
ALTER USER VPD2 QUOTA UNLIMITED ON users;
 
GRANT CONNECT,resource TO VPD1,VPD2;
GRANT select on hr.employees to VPD1;
GRANT execute on DBMS_RLS to VPD1; 
 
CREATE TABLE test_employees as select * from hr.employees;
select count(*) from VPD1.test_employees;  -- 107 rows
desc VPD1.test_employees;
grant select, insert,delete, update on VPD1.test_employees to VPD2;

image

Create an function where limit the data only show the salary less than 10000.

CREATE OR REPLACE
  FUNCTION FUN_VPD_EMP(
      p_schema IN VARCHAR2,
      p_table  IN VARCHAR2)
    RETURN VARCHAR2
  AS
  BEGIN
    RETURN 'salary < 10000';
  END;

image

Attached the function to the table

exec DBMS_RLS.ADD_POLICY(OBJECT_SCHEMA=> 'VPD1',OBJECT_NAME=>'test_employees',POLICY_NAME=>'RLS_TEST1',FUNCTION_SCHEMA=>'VPD1',POLICY_FUNCTION=>'FUN_VPD_EMP');

image

From now on, when user query the test_employees table, Oracle will append “salary < 10000” in the where clause for all users, even for the table owner VPD1. ONLY SYS can get the whole data.

USE CASE1: VPD1 and VPD2 query the table.

They both would get the same number of the rows.

image

image

USE CASE2: VPD2 insert the data outside the select range. ( Salary > 10000)

INSERT INTO VPD1.test_employees VALUES
  (
    '201',
    'SUMMER',
    'CHEN',
    'summer@gmail.com',
    '515.123.5555',
    '17-FEB-04',
    'MK_MAN',
    13000,
    null,
    100,
    20
  );
COMMIT;

Data can be insert, however, it is only visible to the SYS.

image

image

USE CASE3: VPD2 delete/update the row outside the select range ( salary > 1000)

Both delete/update offrow does not work. This actually make sense because oracle needs to find the data first before delete/update. Since the data is not selectable, it does not make sense if we can update it.

  -- delete does not works on VPD2
  delete VPD1.test_employees where first_name = 'SUMMER';
  -- update offrow does notwork
  update VPD1.test_employees set salary= 99999 where first_name = 'SUMMER'

image

Clean up the environment:

This would drop the related function/policy as well.

DROP user VPD1 cascade;
DROP USER VPD2 cascade;

 

Reference

http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_rls.htm

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates