Masking Sensitive Data

in
Oracle Database

Maja Veselica, Consultant

Contact

Maja Veselica,

CTO & Principal Consultant

Email: maja@evoxera.com

Twitter: orapassion

Website www.evoxera.com
 Agenda

➢ Introductory and Concepts Q&A

➢ Use-case-based Q&A

➢ Your questions
Introductory and Concepts
Q&A
 Masking Data

 What is sensitive data?

 What is considered as masking of data?

 Why should you mask data?

 When and Where?

 Which Oracle solutions exist?

 Oracle solutions
❖ Virtual Private Database (VPD) enables you to restrict access to
data at row-level or column-level.

❖ The main purpose of Data Redaction is to provide different

ways to mask sensitive data from end-users in production
environments.

❖ Transparent Sensitive Data Protection (TSDP) provides a way to

create classes of sensitive data and enables more centralized
control of how sensitive data is protected.

❖ Oracle Data Masking enables organizations to use production

data in development and test environments by changing
production data with realistic data (transformation is done by
using masking rules).
 Concepts

➢ Data Masking
➢ Format entry

➢ built-in masking primitives

➢ built-in masking routines

➢ user defined function

 Built-in masking primitives

Type Description
Array List Randomly chosen value from the list of values

Fixed Number Number that will be used for data masking

Fixed String String that will be used for data masking
Random Dates Date interval from which a value will be chosen
in a random way
Random Digits Digit interval from which a value will be chosen
in a random way
Random Numbers Number interval from which a value will be
chosen in a random way
Random Strings String interval from which a value will be chosen
in a random way
 Built-in masking routines

Type Description

Shuffle Changing the position of the original data

Substitute Uses a hash-based substitution (deterministic

masking)

Substring Specify start and length of substring

Table column Value is chosen from the specified table column

 Concepts

➢ Data Masking

➢ Masking format

➢ Masking definition

➢ Format Library
Data Masking
Format Library
EM 12c
EM 12c
EM 12c
Q: How to implement Data Masking?

Identify sensitive data

Define mask formats

Clone production DB to staging

Create masking definitions

Execute masking job

Clone staging DB to test

 Concepts

➢ VPD

➢ Policy

➢ Policy function

➢ Driving context
Q: How to implement VPD?
 Data Redaction

➢ Masks sensitive data just before results of SQL query

are returned to application that issued the query

➢ Different from Data Masking

➢ It’s designed to protect from end-users, not from

privileged users
 Available Redaction Types

Regular
None Full Partial Random
Expression

• Redaction is • Columns • User- • Pattern for • Preserves

NOT are specified matching data types
applied redacted to positions and
constant are replaced replacing is • Randomizes
values by a user- defined and output
depending specified used for
on column character redaction
data type
 Data Redaction - Full

CUSTOMERS
SQL> SELECT * FROM CUSTOMERS; NAME CREDIT_CARD

tom 3455647456589132
mike 3734982321225691
john 3472586894975806
NAME CREDIT_CARD

tom 0
mike 0
DBMS_REDACT.ADD_POLICY
john 0 (object_schema => ‘GLDB’,
object_name => ‘CUSTOMERS’,
policy_name => ‘CCN_POLICY',
column_name => ‘CREDIT_CARD',
function_type => DBMS_REDACT.FULL,
expression => ‘7=7');
 Data Redaction - Partial

CUSTOMERS
SQL> SELECT * FROM CUSTOMERS; NAME CREDIT_CARD

tom 3455647456589132
mike 3734982321225691
john 3472586894975806
NAME CREDIT_CARD

tom ####-####-####-9132
DBMS_REDACT.ADD_POLICY
mike ####-####-####-5691 (object_schema => ‘GLDB’,
object_name => ’CUSTOMERS’,
john ####-####-####-5806 policy_name => ’CCN_POLICY',
column_name => ’CREDIT_CARD',
function_type => DBMS_REDACT.PARTIAL,
function_parameters => 'VVVVFVVVVFVVVVFVVVV,
VVVV-VVVV-VVVV-VVVV, #,1,12’
expression => ‘7=7');
 Data Redaction - Exemptions

CUSTOMERS
RMAN> BACKUP TABLESPACE gltbs; NAME CREDIT_CARD

tom 3455647456589132
mike 3734982321225691
john 3472586894975806
NAME CREDIT_CARD

tom 3455647456589132
mike 3734982321225691 DBMS_REDACT.ADD_POLICY
(object_schema => ‘GLDB’,
john 3472586894975806 object_name => ’CUSTOMERS’,
policy_name => ’CCN_POLICY',
column_name => ’CREDIT_CARD',
function_type=> DBMS_REDACT.FULL,
expression => ‘7=7');
 Data Redaction - Exemptions

CUSTOMERS
SQL> SELECT * FROM CUSTOMERS; NAME CREDIT_CARD

tom 3455647456589132
User with EXEMPT REDACTION POLICY mike 3734982321225691
john 3472586894975806
NAME CREDIT_CARD

tom 3455647456589132
mike 3734982321225691 DBMS_REDACT.ADD_POLICY
(object_schema => ‘GLDB’,
john 3472586894975806 object_name => ’CUSTOMERS’,
policy_name => ’CCN_POLICY',
column_name => ’CREDIT_CARD',
function_type => DBMS_REDACT.FULL,
expression => ‘7=7');
 Data Redaction - Example
c##zoran@PDB1> BEGIN
2 DBMS_REDACT.ADD_POLICY (object_schema => ‘GLDB’,
3 object_name => ’CUSTOMERS’,
4 policy_name => ’CCN_POLICY',
5 column_name => ’CREDIT_CARD',
6 function_type => DBMS_REDACT.PARTIAL,
7 function_parameters => 'VVVVFVVVVFVVVVFVVVV, VVVV-VVVV-VVVV-VVVV,
#,1,12’
8 expression => ‘7=7');
9 END;
10 /
PL/SQL procedure successfully completed.

c##zoran@PDB1> select * from gldb.customers;

NAME CREDIT_CARD
---------------- --------------------
tom 3455-6474-5658-9132
mike 3734-9823-2122-5691
john 3472-5868-9497-5806

c##zoran@PDB1> grant select on gldb.customers to maja;

Grant succeeded.
 Data Redaction - Example

maja@PDB1> select * from gldb.customers;

NAME CREDIT_CARD
---------------- --------------------
tom ####-####-####-9132
mike ####-####-####-5691
john ####-####-####-5806

maja@PDB1> select * from gldb.customers where credit_card like ‘3472%’;

NAME CREDIT_CARD
---------------- --------------------
john ####-####-####-5806
 Data Redaction – NULLIFY Example (12.2)

Note: This example is created on Oracle Database Cloud Service

NAME TYPE VALUE

-------------------------------------------------- ----------- --------
compatible string 12.2.0
noncdb_compatible boolean FALSE

create table c##maja.test(id number, d date, email varchar2(30));

insert into c##maja.test values (1,sysdate,'maja.veselica@gmail.com');

insert into c##maja.test values (2,sysdate,'maja.veselica@sroug.rs');
insert into c##maja.test values (3,sysdate,'zoran@zoran-pavlovic.com');
insert into c##maja.test values (4,sysdate,'zoran.pavlovic@sroug.rs');
Data Redaction – NULLIFY Example (12.2)
Before redaction
 Data Redaction – NULLIFY Example (12.2)
Redaction policy

BEGIN
DBMS_REDACT.ADD_POLICY (
object_schema => 'C##MAJA',
object_name => 'TEST',
policy_name => 'EMAIL_POLICY',
column_name => 'EMAIL',
function_type => DBMS_REDACT.NULLIFY,
expression => '1=1');
END;
/
 Data Redaction – NULLIFY Example (12.2)
After redaction

SELECT * FROM C##MAJA.TEST;

User with EXEMPT REDACTION POLICY

SELECT * FROM C##MAJA.TEST;

Transparent Sensitive
Data Protection (TSDP)
 TSDP

➢ Provides a way to create classes of sensitive data

such as:
➢ Social security number
➢ Credit card number
➢ Enables more centralised control of how sensitive data
is protected
➢ Levereges security mechanisms:
➢ VPD
➢ Data Redaction
Q: How to implement TSDP?

Create a sensitive data type

Associate sensitive columns with that type

Create TSDP policy

Associate the policy with the type

Enable the policy

 TSDP Example - Create a sensitive data type

SQL> BEGIN
2 DBMS_TSDP_MANAGE.ADD_SENSITIVE_TYPE (
3 sensitive_type => ‘email_type’,
4 user_comment=> ’Type for email redaction’);
5 END;
6 /

SQL> select name from DBA_SENSITIVE_COLUMN_TYPES;

NAME
----------------
email_type
 TSDP Example – Associate sensitive columns
with that type
SQL> BEGIN
2 DBMS_TSDP_MANAGE.ADD_SENSITIVE_COLUMN(
3 schema_name => 'PARALLEL',
4 table_name => 'T1',
5 column_name => 'EMAIL_ADDRESS',
6 sensitive_type => 'email_type');
7 END;
8 /

SQL> BEGIN
2 DBMS_TSDP_MANAGE.ADD_SENSITIVE_COLUMN(
3 schema_name => ‘HR',
4 table_name => ‘EMPLOYEES',
5 column_name => 'EMAIL',
6 sensitive_type => 'email_type');
7 END;
8 /

Note: You can associate a column with only one type. If you try to associate it
with another type, you’ll receive ORA-45607.
 TSDP Example – Create TSDP policy
SQL> DECLARE
2 redact_feature_options DBMS_TSDP_PROTECT.FEATURE_OPTIONS;
3 policy_conditions DBMS_TSDP_PROTECT.POLICY_CONDITIONS;
4 BEGIN
5 redact_feature_options('expression') :='1=1';
6 redact_feature_options('function_type') :='DBMS_REDACT.REGEXP';
7 redact_feature_options('regexp_pattern'):='([A-Za-z0-9._%+-]+)@([A-Za-
z0-9.-]+\.[A-Za-z]{2,4})';
8 redact_feature_options('regexp_replace_string'):='\1@xxxx.com';
9 policy_conditions(DBMS_TSDP_PROTECT.DATATYPE) := 'VARCHAR2';
10 DBMS_TSDP_PROTECT.ADD_POLICY
('redact_regexp_email',DBMS_TSDP_PROTECT.REDACT,redact_feature_options,
policy_conditions);
11 END;
12 /
 TSDP Example – Associate the policy
with the type

SQL> BEGIN
2 DBMS_TSDP_PROTECT.ASSOCIATE_POLICY(
3 policy_name => 'redact_regexp_email',
4 sensitive_type => ‘email_type',
5 associate => true);
6 END;
7 /
 TSDP Example – Enable the policy
SQL> begin
2 DBMS_TSDP_PROTECT.ENABLE_PROTECTION_COLUMN(
3 schema_name =>'PARALLEL',
4 table_name =>'T1',
5 column_name =>'EMAIL_ADDRESS',
6 policy => 'redact_regexp_email');
7 end;
8 /

SQL> begin
2 DBMS_TSDP_PROTECT.ENABLE_PROTECTION_COLUMN(
3 schema_name =>'HR',
4 table_name =>'EMPLOYEES',
5 column_name =>'EMAIL',
6 policy => 'redact_regexp_email');
7 end;
8 /

SQL> BEGIN
2 DBMS_TSDP_PROTECT.ENABLE_PROTECTION_TYPE(
3 sensitive_type => 'email_type');
4 END;
5 /
 TSDP Example – Disable the policy
SQL> begin
2 DBMS_TSDP_PROTECT.DISABLE_PROTECTION_COLUMN(
3 schema_name =>'PARALLEL',
4 table_name =>'T1',
5 column_name =>'EMAIL_ADDRESS',
6 policy => 'redact_regexp_email');
7 end;
8 /

SQL> begin
2 DBMS_TSDP_PROTECT.DISABLE_PROTECTION_COLUMN(
3 schema_name =>'HR',
4 table_name =>'EMPLOYEES',
5 column_name =>'EMAIL',
6 policy => 'redact_regexp_email');
7 end;
8 /

SQL> BEGIN
2 DBMS_TSDP_PROTECT.DISABLE_PROTECTION_TYPE(
3 sensitive_type => 'email_type');
4 END;
5 /
 Q: How to alter the TSDP policy?

➢ Before altering the policy, you have to disable it for all

columns

➢ After altering the policy, it is not automatically enabled

(you have to enable it)
 TSDP Example – Alter the policy
SQL> DECLARE
2 redact_feature_options DBMS_TSDP_PROTECT.FEATURE_OPTIONS;
3 policy_conditions DBMS_TSDP_PROTECT.POLICY_CONDITIONS;
4 BEGIN
5 redact_feature_options ('expression') :='1=1';
6 redact_feature_options ('function_type') :='DBMS_REDACT.REGEXP';
7 redact_feature_options ('regexp_pattern'):='([A-Za-z0-9._%+-]+)@([A-
Za-z0-9.-]+\.[A-Za-z]{2,4})';
8 redact_feature_options ('regexp_replace_string'):='\1@mydomain.com';
9 redact_feature_options ('regexp_position'):='1';
10 redact_feature_options ('regexp_occurrence'):='DBMS_REDACT.RE_FIRST';
11 policy_conditions(DBMS_TSDP_PROTECT.DATATYPE) := 'VARCHAR2';
12 DBMS_TSDP_PROTECT.ALTER_POLICY
('redact_regexp_email',redact_feature_options, policy_conditions);
13 end;
14 /
 TSDP Example – Test it
SQL> connect hr@pdb1
hr@PDB1> select email from employees where first_name='Maja';

EMAIL
------------------------------
maja.veselica@mydomain.com

hr@PDB1> connect parallel@pdb1

parallel@PDB1> select email_address from t1;

EMAIL_ADDRESS
------------------------------
maja.veselica@mydomain.com
zoran.pavlovic@mydomain.com
TSDP – Predefined REDACT_AUDIT policy

➢ Purpose of this policy is to mask bind values of bind

variables

➢ You can neither alter nor drop it

➢ You can disable or enable it

Use-case-based Q&A
 Use Case 1

➢ Identify and mask sensitive data

➢ Data will be used on the test system

➢ App accesses 3 tables – Klijenti, Racuni and Kartice

➢ Referential integrity?
Original data in table Klijenti (Clients)
Original data in table Racuni (Accounts)
Original data in table Kartice (CreditCards)
Identified sensitive columns
Table Column
Klijenti Prezime
Klijenti Telefon
Klijenti Adresa
Klijenti JMBG
Racuni Stanje
Racuni Broj_racuna
Kartice Datum_izdavanja
Kartice Datum_isteka
Kartice Broj_kartice
Masking Definition
Masking Definition
Masked data
Masked data
Masked data
 Use Case 2

➢ Identify and mask sensitive data

➢ Data will be used on the production system

➢ 2 apps access the same table and they have different

security requirements
 Use Case 3

➢ “Regular” employee can access only his or her data in a

table and manager user can access his or her data in the table
and data for employees he or she directly manages.

➢ Data will be used on the production system

➢ Create app context hr_ctx, PL/SQL package and logon
trigger
➢ Create policy function emp_access and use it in VPD policy

SQL> CREATE OR REPLACE FUNCTION emp_access(

schema_var IN VARCHAR2,
table_var IN VARCHAR2)
RETURN VARCHAR2
IS
return_value VARCHAR2 (400);
BEGIN
return_value:= '(emp_id = SYS_CONTEXT(''hr_ctx'',''emp_id'')) OR (mgr_id =
SYS_CONTEXT(''hr_ctx'',''emp_id''))';
RETURN return_value;
END emp_access;
/
 Use Case 4

➢ You need to share your production data with 3rd party

company that will create reports. Afterwards, you need to
see original data.
 Use Case 4

➢ Use Encrypt Data Masking Format (EM 12c)

➢ Note: It is very important that you remember

Encryption Seed, because without it you won’t be able
to decrypt data
Thank you!