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;
Nice article but i got error at ERROR at line 1:
ReplyDeleteORA-28110: policy function or package VPD1.COLUMN_SEC_PACKAGE has error
https://www.facebook.com/bantalsilikongrosir
ReplyDeletehttp://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