Vishal desai’s Oracle Blog

August 11, 2017

Roles and Views to restrict sensitive column data

Filed under: Oracle Database, Security — vishaldesai @ 3:07 pm

Application team wanted solution to mask sensitive column data but does not want to use VPD or change application code . Below is a quick demonstration using roles, views and synonym.

 

drop user user1 cascade;
drop user user2 cascade;
drop user nonvpd cascade;
drop role cansee;
drop role cannotsee;
drop function fn_role_enabled;

create user user1 identified by "Abc$$112" default tablespace users temporary tablespace temp;

grant connect, resource to user1;
grant create synonym to user1;

create user user2 identified by "Abc$$112" default tablespace users temporary tablespace temp;

grant connect, resource to user2;
grant create synonym to user2;

create user nonvpd identified by "Abc$$112" default tablespace users temporary tablespace temp;

grant connect, resource, dba to nonvpd;


drop table nonvpd.employees purge;

create table nonvpd.employees(  
  empno    number(4,0),  
  ename    varchar2(10),  
  job      varchar2(9),  
  mgr      number(4,0),  
  hiredate date,  
  sal      number(7,2),  
  comm     number(7,2),  
  deptno   number(2,0),  
  constraint pk_emp primary key (empno)  
);

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

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

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

commit;


create role cansee;
create role cannotsee;

create or replace function fn_role_enabled return number
is
cnumber number;
BEGIN
   IF DBMS_SESSION.IS_ROLE_ENABLED('CANSEE')
   THEN
      cnumber:=1;
   END IF;
   
      IF DBMS_SESSION.IS_ROLE_ENABLED('CANNOTSEE')
   THEN
      cnumber:=0;
   END IF;
   return cnumber;
END;
/

grant execute on fn_role_enabled to nonvpd  with grant option;

grant cansee to user2;
grant cannotsee to user1;

create view nonvpd.employees_view as
select EMPNo,
       ENAME,
       JOB,
       MGR,
       HIREDATE,
       decode(sys.fn_role_enabled,0,null,1,SAL) as SAL,
       decode(sys.fn_role_enabled,0,null,1,COMM) as COMM,
       DEPTNO
from nonvpd.employees;
;

grant select on nonvpd.employees_view to user1, user2;

conn user1/"Abc$$112"
create or replace synonym employees for nonvpd.employees_view;

conn user2/"Abc$$112"
create  or replace synonym employees for nonvpd.employees_view;

col sal format a10
col comm format a10

---connect user1
select * from 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

---connect user2
select * from 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

One using sys_context

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


create user user1 identified by "Abc$$112" default tablespace users temporary tablespace temp;

grant connect, resource to user1;
grant create synonym to user1;

create user user2 identified by "Abc$$112" default tablespace users temporary tablespace temp;

grant connect, resource to user2;
grant create synonym to user2;

create user nonvpd identified by "Abc$$112" default tablespace users temporary tablespace temp;

grant connect, resource, dba to nonvpd;


drop table nonvpd.employees purge;

create table nonvpd.employees(  
  empno    number(4,0),  
  ename    varchar2(10),  
  job      varchar2(9),  
  mgr      number(4,0),  
  hiredate date,  
  sal      number(7,2),  
  comm     number(7,2),  
  deptno   number(2,0),  
  constraint pk_emp primary key (empno)  
);

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

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

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

commit;


create role cansee;
create role cannotsee;



grant cansee to user2;
grant cannotsee to user1;

create view nonvpd.employees_view as
select EMPNo,
       ENAME,
       JOB,
       MGR,
       HIREDATE,
       decode(SYS_CONTEXT('SYS_SESSION_ROLES', 'CANSEE'),'FALSE',null,'TRUE',SAL) as SAL,
       decode(SYS_CONTEXT('SYS_SESSION_ROLES', 'CANSEE'),'FALSE',null,'TRUE',SAL) as COMM,
       DEPTNO
from nonvpd.employees;
;

grant select on nonvpd.employees_view to user1, user2;

conn user1/"Abc$$112"
create or replace synonym employees for nonvpd.employees_view;

conn user2/"Abc$$112"
create  or replace synonym employees for nonvpd.employees_view;

col sal format a10
col comm format a10

conn user1/"Abc$$112"
select * from 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

conn user2/"Abc$$112"
select * from 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

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
	   

Create a free website or blog at WordPress.com.