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
