Oracle | Data Masking

Oracle Data Masking Version 11.2.0.4

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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

TBD

North American Phone Number mgmt_dm_gen_ph_na(...

TBD

North American Phone Number mgmt_dm_gen_ph_na_fh(...

TBD

State of California Phone Number mgmt_dm_gen_ph_usa_ca(...

TBD

State of California Phone Number mgmt_dm_gen_ph_usa_ca_fh(...

TBD

UK National Insurance Number mgmt_dm_gen_uk_nin_fh(...

TBD

UPC Number mgmt_dm_gen_upc(...

TBD

UPC Number mgmt_dm_gen_upc_fh(...

TBD

US Phone Number mgmt_dm_gen_ph_usa(...

TBD

US Phone Number mgmt_dm_gen_ph_usa_fh(...

TBD

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;

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_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;