Data Masking POC Overview

GenRocket employs a mechanism called synthetic data replacement for masking specific columns within a given database table.


This POC will show how to use the GenRocket platform to populate a database with synthetic data and how mask specific columns in a database. 


POC Goals

At a high level, you will complete the following in this POC: 

  • Import three tables into GenRocket Web via a data definition language (DDL) file  
  • Model your data requirements and output formats in GenRocket Web 
  • Generate test data that maintains referential integrity even though the tables do not have a direct foreign key relationship
  • Populate two MySQL databases from scratch via JDBC
  • Mask columns of sensitive data while maintaining referential integrity via JDBC


Prerequisites

Users should complete the following prerequisites before performing the steps in the POC:

  1. Have installed Java version 1.7.+ on their local computer.
  2. Have the MySQL database version 5.6.37 with command line Ver 14.+ or some other SQL database installed on their local computer.
  3. Download and place a MySQL Connector in your genrocket-3.5.6/lib folder
  4. Have attained a GenRocket user license and able to login to your GenRocket organization. 
  5. Completed viewing the Main Concept tutorial and training videos on the GenRocket Tutorial Dashboard.
  6. Completed viewing the training videos for Downloading and installing the GenRocket runtime.
  7. Completed viewing the training video for Downloading your Profile.
  8. Completed Downloading and installing the GenRocket runtime on their local computer.
  9. Completed Downloading you profile and saving it to your local .genrocket subdirectory.  


Entity Relationship (ER) Diagram

The below ER Diagram defines the three tables that are to be used in this POC.


Setup

  • There will be three database tables that are unrelated to each other by referential integrity constraints but share a common column although named differently in each table. The common column is the following:
    • user.external_id
    • grant_history.granter_id
    • notification_setting.user_external_id
  • Each table contains two or more columns of sensitive data that must be synthetically replaced.  
  • Where the data to be replaced is common amongst the tables (e.g. username, first_name, last_name, etc.), the data replacement must be the same across all three tables.


Table Sensitive Data

The sensitive data columns that must undergo  data masking in each table are described below:

  • User
    • external_id
    • first_name
    • last_name
    • username
    • ssn
    • password
  • grant_history
    • granter_id
    • first_name
    • last_name
    • username
    • credit_card
  • notification_setting
    • user_external_id
    • username

Databases

For this POC we want to populate data into two databases. The following information assumes you are using a MySQL database. 

  • alpha - will contain table user
  • beta - will contain tables grant_history and notification_history


Setting up Database alpha

Step 1: Login to your database

Open your command prompt and enter the following command: 

#Replace USERNAME with your database Username 
mysql -u USERNAME -p
#Enter your Password when prompted


Step 2: Create and use alpha database 

Enter the following command to create database alpha: 

CREATE DATABASE alpha;

Enter the following command to use database alpha:

USE alpha; 


Step 3: Create table statement - user 

Below is the basic create table SQL statement for the user table.  Simply copy and paste this statement into your command prompt and press enter to create the 'user' table. This SQL create table statement is best suited for a MySQL database.

drop table if exists user;
create table `user` (
  id              int(10) not null auto_increment, 
  external_id     varchar(50) not null unique, 
  first_name      varchar(25) not null, 
  last_name       varchar(25) not null, 
  middle_initial  char(1), 
  username        varchar(100) not null, 
  ssn             varchar(15) not null, 
  password        varchar(255) not null, 
  activation_date date, 
  primary key (id));


Setting up Database beta

You should still be logged in to your MySQL database. 

Step 1: Create and use beta database 

Enter the following command to create database beta: 

CREATE DATABASE beta;  

Enter the following command to use database beta:

USE beta; 


Step 2: Create table grant_history  - grant_history 

Below is the basic create table SQL statements for the grant_history table and the notification_setting table. Simply copy and paste this statement into your command prompt and press enter to create the 'grant_history' table. This SQL create table statements are best suited for a MySQL database.

drop table if exists grant_history;
create table grant_history (
  id           int(10) not null auto_increment, 
  granter_id   varchar(50) not null unique, 
  first_name   varchar(25) not null, 
  last_name    varchar(25) not null, 
  username     varchar(100) not null, 
  status       varchar(15) not null, 
  credit_card  varchar(25) not null, 
  card_type    varchar(25) not null, 
  grant_date   date not null, 
  grant_amount float not null, 
  primary key (id));


Step 3: Create table notification_setting

Below is the basic create table SQL statements for the notification_setting table. Simply copy and paste this statement into your command prompt and press enter to create the 'notification_setting' table.This SQL create table statements are best suited for a MySQL database.

drop table if exists notification_setting;
create table notification_setting (
  id               int(10) not null auto_increment, 
  user_external_id varchar(50) not null unique, 
  username         varchar(100) not null, 
  frequency_count  int(10) not null, 
  frequency_type   varchar(15) not null, 
  hit_rate         int(10) not null, 
  failure_rate     int(10) not null, 
  primary key (id));


Continue to the Next Lesson >