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;

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;

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;

/

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;

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

lets try the same query again.

 

Clean up the environment

drop user VPD1 cascade;

drop user VPD2 cascade;

drop PUBLIC SYNONYM COLUMN_SEC_PACKAGE;