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

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: