Oracle Data Masking

Oracle Data Masking Version 11.2.0.3

General Information

Files Directory Path

{$ORACLE_HOME}/sysman/admin/emdrep/sql/db/latest/masking

Build Files

dm_fmtlib_pkgbody.sql	dm_pkgdef.sql	dm_tables.sql
dm_fmtlib_pkgdef.sql	dm_post_creation.sql	dm_types.sql
dm_pkgbody.sql

Data Dictionary Objects
(owned by SYSMAN)

DM_COLUMN_GROUP_SEQ	MGMT_DM_ALITEMS	MGMT_DM_RULEENTRY
DM_MAPTBL_SEQ	MGMT_DM_ARRAY_LIST	MGMT_DM_RULE_ENTRY
DM_RULE_NAME_SEQ	MGMT_DM_COLUMN_RULES	MGMT_DM_RULE_SETS
MGMT_DBNET_TNS_ADMINS	MGMT_DM_INFCONS_COLUMNS	MGMT_DM_RULETEMPLATES
MGMT_DM	MGMT_DM_JOB_EXECUTIONS	MGMT_DM_SCOPESPECS
MGMT_DM_JOB_EXECUTIONS	MGMT_DM_SS_COLUMNS

Data Dictionary Objects
(owned by DBSNMP)

DM_FMTLIB

Data Masking Demo

One thing I want to make very clear before I start work on this demo ... the point of data masking is to have an easy to utilize tool that performs a complex task without manual intervention.

That said: The point of being an Oracle ACE Director is to become knowledgeable so that one can speak with some level of authority about Oracle products. Thus this demo is my self-education exercise and, perhaps, will be yours too but it is not a substitute for running data masking in production from the OEM Grid/Cloud interface.

Create demo table and load data for masking

We will start with a realistic table with lots of personal information (PII) and opportunities to violate SarbOx, HIPAA, and PCI DSS.

CREATE TABLE rx_customers (
cust_id    INTEGER,
first_name VARCHAR2(20) NOT NULL,
last_name VARCHAR2(30) NOT NULL,
cust_addr VARCHAR2(30),
cust_city VARCHAR2(20),
cust_state VARCHAR2(2),
cust_zipcd VARCHAR2(10),
cust_phone VARCHAR2(12),
cust_dob   DATE         NOT NULL,
cust_ssn   VARCHAR2(11),
cust_pan   VARCHAR2(20),
fee_paid   NUMBER(6,2),
rx_prod    VARCHAR2(25),
cust_notes VARCHAR2(200));

ALTER TABLE rx_customers
ADD CONSTRAINT pk_rx_customers
PRIMARY KEY(cust_id);

Create sample data

INSERT INTO rx_customers
VALUES
(1, 'Dan', 'Morgan', '1 Oracle Pkwy', 'Redwood Shores', 'CA', '94065', '650-506-7000', TO_DATE('15-MAR-1950'), '544-49-9143', '4125-0010-7633-5125', 100, 'Thorazine', 'Fixated on Oracle');

DBSNMP.DM_FMTLIB

A substantial number of additional data masking functions are in this package and handle the many other possible credit card types, American Express, Diners, Discover, JBC, Mastercard, etc. as well as specific functions for each US state and Canadian province.

Canada Phone Number

p_buff must be a string of numbers 7 to 10 digits long and beginning with a value between 1 and 25

mgmt_dm_gen_ph_canada(
rid IN VARCHAR2,
column_name IN VARCHAR2,
p_buff IN VARCHAR2)
RETURN VARCHAR2;

conn uwclass/uwclass

SELECT dbsnmp.dm_fmtlib.mgmt_dm_gen_ph_canada('A', 'A', NULL)
FROM dual;

SELECT dbsnmp.dm_fmtlib.mgmt_dm_gen_ph_canada('A', 'A', '0011234567')
FROM dual;

SELECT dbsnmp.dm_fmtlib.mgmt_dm_gen_ph_canada('A', 'A', '251234567')
FROM dual;

Canada Phone Number

mgmt_dm_gen_ph_canada_fh(
rid IN VARCHAR2,
column_name IN VARCHAR2,
p_buff IN VARCHAR2)
RETURN VARCHAR2;

conn uwclass/uwclass

SELECT dbsnmp.dm_fmtlib.mgmt_dm_gen_ph_canada_fh('A', 'A', NULL)
FROM dual;

SELECT dbsnmp.dm_fmtlib.mgmt_dm_gen_ph_canada_fh('A', 'A', '0011234567')
FROM dual;

SELECT dbsnmp.dm_fmtlib.mgmt_dm_gen_ph_canada_fh('A', 'A', '251234567')
FROM dual;

Canada Social Insurance Number

The valueof p_buff is the leading value in the SIN

mgmt_dm_gen_cn_sin(
rid IN VARCHAR2,
column_name IN VARCHAR2,
p_buff IN VARCHAR2)
RETURN VARCHAR2;

SELECT dbsnmp.dm_fmtlib.mgmt_dm_gen_cn_sin('A', 'A', NULL)
FROM dual;

SELECT dbsnmp.dm_fmtlib.mgmt_dm_gen_cn_sin('A', 'A', '42')
FROM dual;

Canada Social Insurance Number

mgmt_dm_gen_cn_sin_fh(
rid IN VARCHAR2,
column_name IN VARCHAR2,
p_buff IN VARCHAR2)
RETURN VARCHAR2;

SELECT dbsnmp.dm_fmtlib.mgmt_dm_gen_cn_sin_fh('A', 'A', NULL)
FROM dual;

SELECT dbsnmp.dm_fmtlib.mgmt_dm_gen_cn_sin_fh('A', 'A', '42')
FROM dual;

Canada Social Insurance Number

mgmt_dm_gen_cn_sin_fs(
rid IN IN VARCHAR2,
column_name IN IN VARCHAR2,
p_buff IN IN VARCHAR2)
RETURN VARCHAR2;

SELECT dbsnmp.dm_fmtlib.mgmt_dm_gen_cn_sin_fs('A', 'A', NULL)
FROM dual;

SELECT dbsnmp.dm_fmtlib.mgmt_dm_gen_cn_sin_fs('A', 'A', '42')
FROM dual;

ISBN Numbers

MGMT_DM_GEN_ISBN10
MGMT_DM_GEN_ISBN10_FH
MGMT_DM_GEN_ISBN13
MGMT_DM_GEN_ISBN13_FH

North American Phone Number

mgmt_dm_gen_ph_na(

North American Phone Number

mgmt_dm_gen_ph_na_fh(

State of California Phone Number

mgmt_dm_gen_ph_usa_ca(

State of California Phone Number

mgmt_dm_gen_ph_usa_ca_fh(

UK National Insurance Number

mgmt_dm_gen_uk_nin_fh

UPC Number

mgmt_dm_gen_upc

UPC Number

mgmt_dm_gen_upc_fh

US Phone Number

mgmt_dm_gen_ph_usa(

US Phone Number

mgmt_dm_gen_ph_usa_fh(

US Social Security Number

Generate without hyphens

Reading the source code what I see is that the first two parameters are irrelevant and the value of the third becomes the leading values of the returned SSN

mgmt_dm_gen_ssn(
rid IN VARCHAR2,
column_name IN VARCHAR2,
p_buff IN VARCHAR2)
RETURN VARCHAR2;

SELECT dbsnmp.dm_fmtlib.mgmt_dm_gen_ssn('A', 'A', '52')
FROM dual;

US Social Security Number

Generate with hyphens

Reading the source code what I see is that the first two parameters are irrelevant and the value of the third becomes the leading values of the returned SSN

mgmt_dm_gen_ssn_fh(
rid IN VARCHAR2,
column_name IN VARCHAR2,
p_buff IN VARCHAR2)
RETURN VARCHAR2;

SELECT dbsnmp.dm_fmtlib.mgmt_dm_gen_ssn_fh('A', 'A', '6275')
FROM dual;

Visa Credit Card

Generate without hyphens or spaces

Reading the source code what I see is that the first two parameters are irrelevant and the value of the third becomes a portion of the new number

mgmt_dm_gen_vc(
rid IN VARCHAR2,
column_name IN VARCHAR2,
p_buff IN VARCHAR2)
RETURN VARCHAR2;

SELECT dbsnmp.dm_fmtlib.mgmt_dm_gen_vc('A', 'A', NULL)
FROM dual;

SELECT dbsnmp.dm_fmtlib.mgmt_dm_gen_vc('A', 'A', '4')
FROM dual;

SELECT dbsnmp.dm_fmtlib.mgmt_dm_gen_vc('A', 'A', '42')
FROM dual;

Visa Credit Card

Generate with hyphens between quartets

Reading the source code what I see is that the first two parameters are irrelevant and the value of the third becomes a portion of the new number

mgmt_dm_gen_vc_fh(
rid IN VARCHAR2,
column_name IN VARCHAR2,
p_buff IN VARCHAR2)
RETURN VARCHAR2;

SELECT dbsnmp.dm_fmtlib.mgmt_dm_gen_vc_fh('A', 'A', NULL)
FROM dual;

SELECT dbsnmp.dm_fmtlib.mgmt_dm_gen_vc_fh('A', 'A', '4')
FROM dual;

SELECT dbsnmp.dm_fmtlib.mgmt_dm_gen_vc_fh('A', 'A', '42')
FROM dual;

mgmt_dm_gen_vc_fs(
rid IN VARCHAR2,
column_name IN VARCHAR2,
p_buff IN VARCHAR2)
RETURN VARCHAR2;

SELECT dbsnmp.dm_fmtlib.mgmt_dm_gen_vc_fs('A', 'A', NULL)
FROM dual;

SELECT dbsnmp.dm_fmtlib.mgmt_dm_gen_vc_fs('A', 'A', '4')
FROM dual;

SELECT dbsnmp.dm_fmtlib.mgmt_dm_gen_vc_fs('A', 'A', '42')
FROM dual;