Vishal desai’s Oracle Blog

August 11, 2017

VPD and roles to restrict sensitive column data

Filed under: Oracle Database, Security — vishaldesai @ 2:13 pm

Application team wanted to implement VPD using roles such that if a certain role is granted, user can see column data and if role is not granted user cannot see column data. Below is a quick demonstration.

PS I wrote this back in 2013 and little outdated compared to some 12c features.

drop user user1 cascade;
drop user user2 cascade;
drop user vpd cascade;
drop role cansee;
drop role cannotsee;

create user user1 identified by user1 default tablespace example temporary tablespace temp;

grant connect, resource to user1;

create user user2 identified by user2 default tablespace example temporary tablespace temp;

grant connect, resource to user2;


create user vpd identified by vpd default tablespace example temporary tablespace temp;

grant connect, resource, dba to vpd;


drop table vpd.employees purge;

create table vpd.employees as select * from scott.emp where 1=2;

insert into vpd.employees values (300,'USER1','USER1',1,sysdate,1000,500,10);

insert into vpd.employees values (400,'USER2','USER2',1,sysdate,2000,500,10);

insert into vpd.employees values (500,'USER3','USER3',1,sysdate,3000,600,20);

commit;


grant select on vpd.employees to user1;
grant select on vpd.employees to user2;

create or replace function fn_cannotsee(p_owner in varchar2, p_name in varchar2)
return varchar2
is
BEGIN
	IF DBMS_SESSION.IS_ROLE_ENABLED('CANNOTSEE')
	THEN
		return '1=2';
	ELSE
		return null;
	END IF;
END;
/

begin
dbms_rls.add_policy(object_schema=>'VPD',object_name=>'EMPLOYEES',
					policy_name=>'P_CANNOT_SEE',
					function_schema=>'VPD',
					policy_function=>'FN_CANNOTSEE',
					sec_relevant_cols=>'SAL,COMM',
					sec_relevant_cols_opt=>dbms_rls.ALL_ROWS);
end;
/




create role cansee;
create role cannotsee;

grant cansee to user2;
grant cannotsee to user1;


---user1
SQL> select *
from vpd.employees;  

     EMPNO ENAME      JOB              MGR HIREDATE  SAL                                      COMM                                         DEPTNO
---------- ---------- --------- ---------- --------- ---------------------------------------- ---------------------------------------- ----------
       300 USER1      USER1              1 19-MAY-13                                                                                           10
       400 USER2      USER2              1 19-MAY-13                                                                                           10
       500 USER3      USER3              1 19-MAY-13                                                                                           20

---user2
SQL> select *
from vpd.employees;  

     EMPNO ENAME      JOB              MGR HIREDATE  SAL                                      COMM                                         DEPTNO
---------- ---------- --------- ---------- --------- ---------------------------------------- ---------------------------------------- ----------
       300 USER1      USER1              1 19-MAY-13 1000                                     500                                              10
       400 USER2      USER2              1 19-MAY-13 2000                                     500                                              10
       500 USER3      USER3              1 19-MAY-13 3000                                     600                                              20
	   
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: