Oracle : Data Security - Hide data from unauthorized users using dbms_rls

Hide Data in Oracle using DBMS_RLS

The DBMS_RLS package is helpful in masking data in oracle.

Let’s see the below steps to test a simple data masking functionality using dbms_rls.

Setting up access policy

1) Create two users. The data_owner user is the owner of the table. The data_user is used for reading the tables from data_owner user.

conn / as sysdba

CREATE USER data_owner IDENTIFIED BY data_owner;

GRANT CONNECT,RESOURCE TO data_owner;

GRANT EXECUTE ON dbms_rls TO data_owner; 

CREATE USER data_user IDENTIFIED BY data_user;

GRANT CONNECT,RESOURCE TO data_user;

2) Create a table in the data owner.

CONN data_owner/data_owner@tdb

CREATE TABLE t_employee (empid NUMBER PRIMARY KEY, ename VARCHAR2(30),location VARCHAR2(30),salary NUMBER);

INSERT INTO t_employee VALUES (1001,'Alex','NY','2000');
INSERT INTO t_employee VALUES (1002,'Scott','NY','5000');
INSERT INTO t_employee VALUES (1003,'Tim','NJ','10000');
INSERT INTO t_employee VALUES (1004,'Ryan','NJ','2000');
INSERT INTO t_employee VALUES (1005,'Anderson','NJ','15000');

commit;

3) Grant select privilege to data_user

CONN data_owner/data_owner@tdb

GRANT SELECT ON t_employee to data_user;

4) Create a policy function for column level masking. The other users except data_owner shouldn't see the SALARY column.

CONN data_owner/data_owner@tdb

CREATE OR REPLACE FUNCTION fn_colmask(powner IN VARCHAR2, pjname IN VARCHAR2)
RETURN VARCHAR2
IS
   v_pred VARCHAR2(30);
BEGIN

IF USER<>'DATA_OWNER' THEN
   v_pred:= '1=2';
ELSE
   v_pred:= '1=1';
END IF;

RETURN v_pred;

END;
/

5) Add the policy for column masking

CONN data_owner/data_owner@tdb

BEGIN

DBMS_RLS.ADD_POLICY (
   object_schema=>'DATA_OWNER',
   object_name=>'T_EMPLOYEE',
   policy_name=>'T_EMPLOYEE_COL_POLICY',
   function_schema=>'DATA_OWNER',
   policy_function=>'FN_COLMASK',
   sec_relevant_cols=>'SALARY',
   sec_relevant_cols_opt=>dbms_rls.ALL_ROWS);

END;
/

6) The data_owner is able to see all the data

CONN data_owner/data_owner@tdb

select * from data_owner.t_employee;

     EMPID ENAME           LOCATION       SALARY
---------- --------------- ---------- ----------
      1001 Alex            NY               2000
      1002 Scott           NY               5000
      1003 Tim             NJ              10000
      1004 Ryan            NJ               2000
      1005 Anderson        NJ              15000

7) The salary column is not visible to data_user due to column masking on SALARY column.

conn data_user/data_user@tdb

select * from data_owner.t_employee;

     EMPID ENAME           LOCATION       SALARY
---------- --------------- ---------- ----------
      1001 Alex            NY              
      1002 Scott           NY              
      1003 Tim             NJ             
      1004 Ryan            NJ            
      1005 Anderson        NJ

8) Create a policy function for row masking as well.

The users except data_owner should see only rows of NY location. Not the rows of other locations.

CONN data_owner/data_owner@tdb

CREATE OR REPLACE FUNCTION fn_rowmask(powner IN VARCHAR2, pjname IN VARCHAR2)
RETURN VARCHAR2
IS
   v_pred VARCHAR2(30);
BEGIN

IF USER<>'DATA_OWNER' THEN
   v_pred:= 'LOCATION=''NY''';
ELSE
   v_pred:= '1=1';
END IF;

RETURN v_pred;

END;
/

9) Add the policy function for row level masking

CONN data_owner/data_owner@tdb

BEGIN

DBMS_RLS.ADD_POLICY (
   object_schema=>'DATA_OWNER',
   object_name=>'T_EMPLOYEE',
   policy_name=>'T_EMPLOYEE_ROW_POLICY',
   function_schema=>'DATA_OWNER',
   policy_function=>'FN_ROWMASK',
   sec_relevant_cols=>'SALARY');

END;
/

10) Now the policy has row level masking and column level masking

CONN data_owner/data_owner@tdb

select * from data_owner.t_employee;

     EMPID ENAME           LOCATION       SALARY
---------- --------------- ---------- ----------
      1001 Alex            NY               2000
      1002 Scott           NY               5000
      1003 Tim             NJ              10000
      1004 Ryan            NJ               2000
      1005 Anderson        NJ              15000

conn data_user/data_user@tdb

select * from data_owner.t_employee;

     EMPID ENAME           LOCATION       SALARY
---------- --------------- ---------- ----------
      1001 Alex            NY
      1002 Scott           NY

Exempt Access policy

SYS user is always exempted from access policy. Other users can be exempted from access policy by using exempt access policy privilege.

conn / as sysdba

grant exempt access policy to data_user;

CONN data_user/data_user@tdb

select * from data_owner.t_employee;

     EMPID ENAME           LOCATION       SALARY
---------- --------------- ---------- ----------
      1001 Alex            NY               2000
      1002 Scott           NY               5000
      1003 Tim             NJ              10000
      1004 Ryan            NJ               2000
      1005 Anderson        NJ              15000

User data_user is able to see all the data because of exemption of access policy

conn / as sysdba

revoke exempt access policy from data_user;

conn data_user/data_user@tdb

select * from data_owner.t_employee;

     EMPID ENAME           LOCATION       SALARY
---------- --------------- ---------- ----------
      1001 Alex            NY
      1002 Scott           NY

Viewing policy details

V$VPD_POLICY displays all the fine-grained security policies and predicates associated with the cursors currently in the library cache.

Dropping policy

The policy can be dropped using DBMS_RLS.DROP_POLICY procedure.

BEGIN

DBMS_RLS.DROP_POLICY (
   object_schema=>'DATA_OWNER',
   object_name=>'T_EMPLOYEE',
   policy_name=>'T_EMPLOYEE_COL_POLICY');

END;
/

DBMS_RLS.DROP_POLICY (
   object_schema=>'DATA_OWNER',
   object_name=>'T_EMPLOYEE',
   policy_name=>'T_EMPLOYEE_ROW_POLICY');

END;
/