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;

Related Topics
Database Security
DBMS_CRYPTO
DBMS_RANDOM
Transparent Data Encryption
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx