Oracle Virtual Private Database

ORACLE VIRTUAL PRIVATE DATABASE

This is my scenario:

There are 3 managers and they have access to "HR.MYEMPLOYEES" table to see the details of employees which are working for their own department but we dont want them to see other departments' employees.

Create users for managers and give the required privileges:

create user Neena identified by Neena;
grant connect to Neena;
grant select on hr.myemployees to Neena;

create user Alexander identified by Alexander;
grant connect to Alexander;
grant select on hr.myemployees to Alexander;

create user Nancy identified by Nancy;
grant connect to Nancy;
grant select on hr.myemployees to Nancy;

Check "HR.MYEMPLOYEES" table:

select * from hr.myemployees;

Neena's employee_id........=101
Alexander's employee_id....=103
Nancy's employee_id........=108


We need the function which we'll use it for VPD policy:

CREATE OR REPLACE FUNCTION man_policy_func(user_name IN VARCHAR2,
                                      tab_name  in varchar2)
  RETURN varchar2 IS
  granted_manager NUMBER;
BEGIN
  CASE SYS_CONTEXT('USERENV', 'SESSION_USER')
    WHEN 'NEENA' THEN
      granted_manager := 101;
    WHEN 'ALEXANDER' THEN
      granted_manager := 103;
    WHEN 'NANCY' THEN
      granted_manager := 108;
    ELSE
      granted_manager := 0;
  END CASE;

  RETURN 'manager_id=' || granted_manager;

END;

With this function, Oracle will add the following clause on each query for our table:

where manager_id = &granted_manager;

Now, let's create the VPD policy:

BEGIN
      SYS.DBMS_RLS.ADD_POLICY(
        object_schema   => 'hr',
        object_name     => 'myemployees',
        policy_name     => 'manager_policy_vpd',
        function_schema => 'SYS',
        policy_function => 'man_policy_func',
        statement_types => 'select'
     );
  END;

Check if our VPD policy is working:

• As you see, Neena could only see the employees whose manager_id is 101

• Like Neena, Alexander can see only his employees.

Oracle Transparent Sensitive Data Protection

The aim of this post is creating a sensitive type, associate it to columns and combine it to "Oracle Sensitive Data Protection".

In our scenario, there is an application user called "APPUSER" and it has privileges to select and see the original data but we dont want any other users (even the owner of table) will see the sensitive data of the related tables.

Our sensitive column is TCKN and its datatype is "varchar2"

Create tables with sensitive column:

CREATE TABLE HR.employees (first_name varchar2(20), 
                           last_name varchar2(20), 
                           telephone_num varchar2(20), 
                           TCKN varchar2(11));

CREATE TABLE HR.accounts (TCKN varchar2(11), 
                          branch_id number(5), 
                          deposit number(10));

Insert data:

insert into HR.employees values ('Emre','Ozturk','222-333-444','11223344550');
insert into HR.employees values ('Ahmet','Yilmaz','444-555-666','22334455660');
insert into HR.employees values ('Okan','Demirel','666-777-888','33445566770');
insert into HR.employees values ('Alper','Akin','000-111-222','44556677880');
commit;

insert into HR.accounts values ('11223344550',120,2450);
insert into HR.accounts values ('22334455660',156,2450);
insert into HR.accounts values ('33445566770',134,2450);
insert into HR.accounts values ('44556677880',197,2450);
commit;

Create our sensitive type:

BEGIN
  DBMS_TSDP_MANAGE.ADD_SENSITIVE_TYPE(sensitive_type => 'TYPE_FOR_TCKN',
                                      user_comment   => 'Type for Identitiy Number columns using a varchar data type');
END;

We can check the created type:

select * from DBA_SENSITIVE_COLUMN_TYPES ;

Add our 1st sensitive column to associate the sensitive type:

BEGIN
  DBMS_TSDP_MANAGE.ADD_SENSITIVE_COLUMN(schema_name    => 'HR',
                                        table_name     => 'EMPLOYEES',
                                        column_name    => 'TCKN',
                                        sensitive_type => 'TYPE_FOR_TCKN',
                                        user_comment   => 'Associate EMPLOYEES table for sensitive column');
END;

Create the Transparent Sensitive Data Protection Policy:

For this policy, we will redact the TCKN column of first and last 3 digits. Only "appuser" will see the original data:

DECLARE
  redact_feature_options DBMS_TSDP_PROTECT.FEATURE_OPTIONS;
  policy_conditions      DBMS_TSDP_PROTECT.POLICY_CONDITIONS;
BEGIN
  redact_feature_options('expression') := 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') !=''APPUSER''';
  redact_feature_options('function_type') := 'DBMS_REDACT.PARTIAL';
  redact_feature_options('function_parameters') := 'FFVVVVVVFFF,**VVVVVV***,*,1,1';
  policy_conditions(DBMS_TSDP_PROTECT.DATATYPE) := 'VARCHAR2';
  DBMS_TSDP_PROTECT.ADD_POLICY('PARTIAL_TCKN_POL',
                               DBMS_TSDP_PROTECT.REDACT,
                               redact_feature_options,
                               policy_conditions);
END;

Associate the Policy with a Sensitive Type:

BEGIN
     DBMS_TSDP_PROTECT.ASSOCIATE_POLICY(
     policy_name        => 'PARTIAL_TCKN_POL',
     sensitive_type     => 'TYPE_FOR_TCKN',
     associate          => TRUE);
END;

Enable Protection:

BEGIN
  DBMS_TSDP_PROTECT.ENABLE_PROTECTION_TYPE(sensitive_type => 'TYPE_FOR_TCKN');
END;

Select table with appuser:

SQL> conn appuser/ora123;
Connected.

SQL> select * from hr.employees;

FIRST_NAME           LAST_NAME            TELEPHONE_NUM        TCKN
-------------------- -------------------- -------------------- -----------
Emre                 Ozturk               222-333-444          11223344550
Ahmet                Yilmaz               444-555-666          22334455660
Okan                 Demirel              666-777-888          33445566770
Alper                Akin                 000-111-222          44556677880

Select table with HR:

SQL> conn hr/hr
Connected.
 
SQL> select * from hr.employees;

FIRST_NAME           LAST_NAME            TELEPHONE_NUM        TCKN
-------------------- -------------------- -------------------- -----------
Emre                 Ozturk               222-333-444          ***23344***
Ahmet                Yilmaz               444-555-666          ***34455***
Okan                 Demirel              666-777-888          ***45566***
Alper                Akin                 000-111-222          ***56677***

Now associate this sensitive type to TCKN column of another table:

BEGIN
  DBMS_TSDP_MANAGE.ADD_SENSITIVE_COLUMN(schema_name    => 'HR',
                                        table_name     => 'ACCOUNTS',
                                        column_name    => 'TCKN',
                                        sensitive_type => 'TYPE_FOR_TCKN',
                                        user_comment   => 'Sensitive column addition of TCKN_type');
END;

Disable and Enable the protection:

BEGIN
  DBMS_TSDP_PROTECT.DISABLE_PROTECTION_TYPE(sensitive_type => 'TYPE_FOR_TCKN');
  DBMS_TSDP_PROTECT.ENABLE_PROTECTION_TYPE(sensitive_type => 'TYPE_FOR_TCKN');
END;

Now query the table with HR:

SQL> conn appuser/ora123
Connected.

SQL> select * from hr.accounts

TCKN         BRANCH_ID    DEPOSIT
----------- ---------- ----------
11223344550        120       2450
22334455660        156       3650
33445566770        134       1480
44556677880        197       8690

SQL> conn hr/hr
Connected.

SQL> select * from hr.accounts

TCKN         BRANCH_ID    DEPOSIT
----------- ---------- ----------
***23344***        120       2450
***34455***        156       3650
***45566***        134       1480
***56677***        197       8690