Saturday, March 2, 2013

2 Virtual Private Database (VPD) 3: Limit the data at column level

From 10g, Oracle VPD can limit the data at column level. Once it is setup. All rows are accessible to the user as long as user does not access restricted column . If user does try to access restricted column, 2 scenario would happen depends on the setting.

  • The row contains the restricted column would be eliminate. Essentially all the row would be eliminate.
  • Return NULL for the restricted column

Setup the test environment

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; 
GRANT  create public synonym TO VPD1;
-- connect as 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,UPDATE,DELETE on vpd1.test_employees to VPD2;
image

create security policy/function

CREATE OR REPLACE PACKAGE COLUMN_SEC_PACKAGE AS
    FUNCTION TEST_SEC (OWNER VARCHAR2, OBJNAME VARCHAR2) RETURN VARCHAR2;
END;
/
 
CREATE OR REPLACE
PACKAGE BODY COLUMN_SEC_PACKAGE
IS
FUNCTION TEST_SEC(
    OWNER   VARCHAR2,
    OBJNAME VARCHAR2)
  RETURN VARCHAR2
IS
  v_SQL VARCHAR2(2000):='1=0';
BEGIN
  IF (SYS_CONTEXT('USERENV','SESSION_USER') ='VPD1') THEN
    v_SQL                                  :=NULL;
  END IF;
  RETURN V_SQL;
END;
END;
/
 SHOW ERRORS
  
GRANT EXECUTE ON VPD1.COLUMN_SEC_PACKAGE TO PUBLIC;
CREATE OR REPLACE PUBLIC SYNONYM COLUMN_SEC_PACKAGE FOR VPD1.COLUMN_SEC_PACKAGE;

image

Apply the policy function to the table.

BEGIN
  DBMS_RLS.ADD_POLICY (object_schema     => 'VPD1',
                       object_name       => 'test_employees',
                       policy_name       => 'VPD1_POLICY_COLUMN1',
                       function_schema   => 'VPD1',
                       policy_function   => 'COLUMN_SEC_PACKAGE.TEST_SEC',
                       sec_relevant_cols => 'salary,hire_date');
END;
/

image

VPD2 can see all the columns and query the data except salary and hire_date. As long as VPD2 touch either of the restricted column ( salary/hire_date), the system would return 0 rows.

desc vpd1.test_employees;
select employee_id,first_name from vpd1.test_employees where EMPLOYEE_ID=204;
select employee_id,first_name,salary from vpd1.test_employees where EMPLOYEE_ID=204;
select employee_id,first_name,hire_date from vpd1.test_employees where EMPLOYEE_ID=204;
select count(*) from vpd1.test_employees;

image

In my PHMO,return 0 rows is confusing , lets make the salary/hire_date as NULL by setting sec_relevant_cols_opt  to DBMS_RLS.ALL_ROWS.

Drop the policy and recreate it.

exec DBMS_RLS.DROP_POLICY(OBJECT_SCHEMA=> 'VPD1',OBJECT_NAME=>'test_employees',POLICY_NAME=>'VPD1_POLICY_COLUMN1');

BEGIN
  DBMS_RLS.ADD_POLICY (object_schema     => 'VPD1',
                       object_name       => 'test_employees',
                       policy_name       => 'VPD1_POLICY_COLUMN1',
                       function_schema   => 'VPD1',
                       policy_function   => 'COLUMN_SEC_PACKAGE.TEST_SEC',
                       sec_relevant_cols => 'salary,hire_date',
                       sec_relevant_cols_opt =>DBMS_RLS.ALL_ROWS);
END;
/

image

lets try the same query again.

image

 

Clean up the environment

drop user VPD1 cascade;
drop user VPD2 cascade;
drop PUBLIC SYNONYM COLUMN_SEC_PACKAGE;

2 comments:

  1. Nice article but i got error at ERROR at line 1:
    ORA-28110: policy function or package VPD1.COLUMN_SEC_PACKAGE has error

    ReplyDelete
  2. https://www.facebook.com/bantalsilikongrosir
    http://bantalsilikon01.blogspot.com/2014/12/a.html
    http://jualsangkarpleci.blogspot.com/2014/12/a.html
    http://kopiluwakliar01.blogspot.com/2014/12/a.html
    http://vvty.in/uncategorized/marinir-seo/

    http://marinirseo.blogspot.com/2014/12/a.html
    http://marinir-seo.blogspot.com/2014/12/a.html
    http://bumbu-pecel-malang.blogspot.com/2014/12/a.html
    http://distro-seo.blogspot.com/2014/12/a.html
    http://restoran-seo.blogspot.com/2014/12/a.html
    http://bantalmalangmurah.blogspot.com/2014/12/a.html
    http://distrobantal.blogspot.com/2014/12/a.html
    http://kesethandukmalang.blogspot.com/2014/12/a.html


    http://tasya.marinirseo.web.id/?p=4http://jeannet.marinirseo.web.id/?p=4http://anne.marinirseo.web.id/?p=4http://jelita.marinirseo.web.id/?p=4http://ruth.marinirseo.web.id/?p=4http://caca.marinirseo.web.id/?p=4http://brenda.marinirseo.web.id/?p=1 || http://tasya1.marinirseo.web.id/?p=4http://jeannet1.marinirseo.web.id/?p=4http://anne1.marinirseo.web.id/?p=4http://jelita1.marinirseo.web.id/?p=4http://ruth1.marinirseo.web.id/?p=4http://caca1.marinirseo.web.id/?p=5http://brenda1.marinirseo.web.id/?p=4 || http://tasya2.marinirseo.web.id/?p=5http://jeannet2.marinirseo.web.id/?p=4http://anne2.marinirseo.web.id/?p=4http://jelita2.marinirseo.web.id/?p=4http://ruth2.marinirseo.web.id/?p=4http://caca2.marinirseo.web.id/?p=4http://brenda2.marinirseo.web.id/?p=4http://brenda3.marinirseo.web.id/?p=4http://tasya3.marinirseo.web.id/dua/http://jeannet3.marinirseo.web.id/?p=4http://anne3.marinirseo.web.id/?p=4http://jelita3.marinirseo.web.id/?p=4http://ruth3.marinirseo.web.id/?p=4http://caca3.marinirseo.web.id/?p=4 || nhttp://brenda4.marinirseo.web.id/?p=4http://tasya4.marinirseo.web.id/?p=4http://jeannet4.marinirseo.web.id/?p=4http://anne4.marinirseo.web.id/?p=4http://jelita4.marinirseo.web.id/?p=5http://ruth4.marinirseo.web.id/?p=4http://caca4.marinirseo.web.id/?p=4 || http://tasya5.marinirseo.web.id/?p=4http://anne5.marinirseo.web.id/?p=4http://jelita5.marinirseo.web.id/?p=4http://ruth5.marinirseo.web.id/?p=4http://caca5.marinirseo.web.id/?p=4http://synganne.marinirseo.web.id/?p=4
    http://tasya.marinirseo.web.id/?p=4http://jeannet.marinirseo.web.id/?p=4http://anne.marinirseo.web.id/?p=4http://jelita.marinirseo.web.id/?p=4http://ruth.marinirseo.web.id/?p=4http://caca.marinirseo.web.id/?p=4http://brenda.marinirseo.web.id/?p=1 || http://tasya1.marinirseo.web.id/?p=4http://jeannet1.marinirseo.web.id/?p=4http://anne1.marinirseo.web.id/?p=4http://jelita1.marinirseo.web.id/?p=4http://ruth1.marinirseo.web.id/?p=4http://caca1.marinirseo.web.id/?p=5http://brenda1.marinirseo.web.id/?p=4 || http://tasya2.marinirseo.web.id/?p=5http://jeannet2.marinirseo.web.id/?p=4http://anne2.marinirseo.web.id/?p=4http://jelita2.marinirseo.web.id/?p=4http://ruth2.marinirseo.web.id/?p=4http://caca2.marinirseo.web.id/?p=4http://brenda2.marinirseo.web.id/?p=4http://brenda3.marinirseo.web.id/?p=4http://tasya3.marinirseo.web.id/dua/http://jeannet3.marinirseo.web.id/?p=4http://anne3.marinirseo.web.id/?p=4http://jelita3.marinirseo.web.id/?p=4http://ruth3.marinirseo.web.id/?p=4http://caca3.marinirseo.web.id/?p=4 || nhttp://brenda4.marinirseo.web.id/?p=4http://tasya4.marinirseo.web.id/?p=4http://jeannet4.marinirseo.web.id/?p=4http://anne4.marinirseo.web.id/?p=4http://jelita4.marinirseo.web.id/?p=5http://ruth4.marinirseo.web.id/?p=4http://caca4.marinirseo.web.id/?p=4 || http://tasya5.marinirseo.web.id/?p=4http://anne5.marinirseo.web.id/?p=4http://jelita5.marinirseo.web.id/?p=4http://ruth5.marinirseo.web.id/?p=4http://caca5.marinirseo.web.id/?p=4http://synganne.marinirseo.web.id/?p=4

    ReplyDelete

 

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