Secure Test Data Management with ORACLE Data Masking

(1)

Baden Basel · Bern· Brugg · Lausanne · Zürich · Düsseldorf Frankfurt/M. · Freiburg i. Br. · Hamburg · München Stuttgart · Wien

Secure Test Data Management with

ORACLE Data Masking

Michael Schellin Consultant, OCM

DOAG Regio München, Dec 2009

Agenda

Data are always part of the game.



Introduction



Requirements and Expectations



Oracle’s Approach



Challenges and Solutions

(2)

3

Agenda



Introduction



Requirements and Expectations



Oracle’s Approach



Challenges and Solutions



Summary

Data maskingis the

process of obscuring

(masking)

specific data elements

within data stores.

It ensures that

sensitive data is replaced with realistic but

not real data

.

The goal is that

sensitive customer information is not

available

outside of the authorized environment.

Data masking is typically done while provisioning non-production environments so that copies created to support test and development processesare

not exposing

Definition

(3)

5



Production Database are usually well secured

 Think of ASO, DB Vault, reliable passwords, proxy authentication



Non-Production is not. Reasons:

 Licence cost savings

 Personnel savings

 developer = dba  Ease of administration

 username = password



Regulations:

 SOX, Basel II, EU Data Protection Directive, PCI-DSS

Why mask?

Agenda



Introduction



Requirements and Expectations



Oracle’s Approach



Challenges and Solutions

(4)

7



Fundamental Requirements



Data Format



Data Distribution



Amount of Data



Repeatable Process



Extensibility

Requirements and Expectations



Fundamental Requirements



Irreversibility

no possibility of getting back to original data from masked data



Complete masking

apparently not relevant data needs to be masked if it could lead to sensitive data



Referential integrity

relations between data sets needs to be maintained

(5)

9



Data Format

 Your application might expects a defined format

 Check constraints



Data Distribution

 Among others, the CBO bases it’s decisions on that attribute



Amount of Data

 Must be able to mask large data sets

 Again, CBO

Expectations



Repeatable Process

 We do not want to reinvent the wheel with every iteration



Extensibility

 Applications change, schema design changes

  You need to change the masking definition according to these changes

 You want to do that incrementally

(6)

11

Agenda



Introduction



Requirements

and Expectations



Oracle’s Approach



Challenges and Solutions



Summary

(7)

13



Available as an Enterprise Manager Pack



Grid Control

 10.2.0.4  10.2.0.5



Database Control

 11.2.0.1



Database Version must be >= 9.2.0.x



No Installation. Out-of-the-box usable



Part of ORACLE’s Maximum Security Architecture

ORACLE Data Masking Pack



Format Library

 Repository for named data format definitions

 “create once, use many”

 ORACLE delivers predefined formats

 Credit card numbers (VISA, AMEX, …)  ISBN’s

 UPC (EAN)

 …



Masking Definitions

(8)

15



Suggested Workflow

ORACLE Data Masking Pack



Data Formats 1/2

ORACLE Data Masking Pack – Masking Process

Type Varchar2 Number Date Example

Fixed Number X X 100

Fixed String X Mueller

Substring X ueller

Random Number X X 4711

Random Digit X 0047

Random String X lurelm

Random Date X 02.10.1977

(9)

17



Data Formats 2/2

 User defined function

 Post-processing fuction

 Truncate

 NULL Value

 Delete

 Preserve original data



Condition based masking

 Available since 10.2.0.5

 Allows different masking options for logical data partitions

 Based on different WHERE-conditions

ORACLE Data Masking Pack – Masking Process



The Maskingprocess is always a Reorganization

DDL, (almost) no DML



Pure SQL is used as much as possible



Control of options relevant for performance

 Logging / Nologging

 Parallel Degree

 Statistic Generation

(10)

19

ORACLE Data Masking Pack - Live Demo

Agenda



Introduction



Requirements

and Expectations



Oracle’s Approach



Challenges and Solutions

(11)

21



Data Distribution



Orphan Keys



Recursive Select’s

Challenges and Solutions



Data Distribution

 Histograms are needed if data is not uniformly distributed

 Non-numeric data types needs special attention since only the leading 6 bytes are used

 Avoid leading constants

(12)

23



Orphan Keys

 Childrecords without parent

 Result of:

 Incomplete data models  “Online” Reorganizations  Tuning by removing FK’s

 Know your data

 Procedures to ensure data cleanliness

 Foreign keys are your friend

Challenges and Solutions



Orphan Keys – How does ORACLE Data Masking treat them?

 It depends on the version

 10.2.0.4 Grid Control Automatic data cleansing   10.2.0.5 Grid Control

Keeps orphaned values – sets the child key to NULL  11.2.0.1 Database Control

Let you choose

(13)

25



Recursive Select’s

 How can Data Masking know about your data structure?

 Check constraints  Uniqueness  Relationships

 If a table contains orphan keys?

 How is sample data generated?

some of them can cause Data Masking GUI to hang up…

Challenges and Solutions

Agenda



Introduction



Requirements



Oracle’s Way



Challenges and Solutions

(14)

28

Summary

Fundamental Requirements Data Format Data Distribution Amount of Data Repeatable Process Extensibility

Core Messages



Powerful SQL Generator



Out-of-the-box masking possible



Almost unlimited extensible



More advanced control features

would help

(15)

30

mehr zu 11g?

TechnoCircle

München,

20.01.2010

?

www.trivadis.com

  

_{Thank you!}