In the second part of this article, i will show how delphix masking can help ensure referential integrity consistency across different targets even when using different masking engines (5.2 an up).
In my demonstration case i will be using the (oracle) employees table and a copy of the same table on (mysql).
Scenario I : using the same engine
Check the content of both tables before masking.
[oracle@linuxsource ~]$ . oraenv
ORACLE_SID = [demovdb] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@linuxsource ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 29 08:35:11 2018
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning option
SQL> set long 9000
SQL> set lines 3000
SQL> select * from delphixdb.employees;
FIRST_NAME LAST_NAME DEPT_NAME CITY
----------- ------------ -------------------- -------
Kathryn Godolphin Validation
ARKEA ARKEA ARKEA ARKEA
Travis Skidmore Solution Architects
Nova Holcombe Solution Architects
Angele Callaghan Lyrical Rap Studies
Autumn Buckminster Sales
Tommie Paddock No Sleep Till
Louann Blair Superstar
Autumn Buckminster Sales
Vince Stratton Solution Architects
Awilda Emerson Imperial Army
FIRST_NAME LAST_NAME DEPT_NAME CITY
----------- ------------ -------------------- -------
MSA MSA MSA MSA
12 rows selected.
SQL>
mysql> select * from employees_mysql; +------------+-------------+---------------------+-------+ | first_name | last_name | dept_name | city | +------------+-------------+---------------------+-------+ | Kathryn | Godolphin | Validation | | | ARKEA | ARKEA | ARKEA | ARKEA | | Travis | Skidmore | Solution Architects | | | Nova | Holcombe | Solution Architects | | | Angele | Callaghan | Lyrical Rap Studies | | | Autumn | Buckminster | Sales | | | Tommie | Paddock | No Sleep Till | | | Louann | Blair | Superstar | | | Autumn | Buckminster | Sales | | | Vince | Stratton | Solution Architects | | | Awilda | Emerson | Imperial Army | | | MSA | MSA | MSA | MSA | +------------+-------------+---------------------+-------+ 12 rows in set (0.00 sec) mysql>
Let's mask both tables using the same masking algorithms with those rules:
- Firstname column using firstname sl algorithm
- Lastname column using lastname sl algorithm
- City column using city with null replacement algorithm
The result after masking both tables is as follows.
[oracle@linuxsource ~]$ . oraenv
ORACLE_SID = [demovdb] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@linuxsource ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 29 08:35:11 2018
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning option
SQL> set long 9000
SQL> set lines 3000
SQL> select * from delphixdb.employees;
FIRST_NAME LAST_NAME DEPT_NAME CITY
----------- ------------ -------------------- -------
Hilary Randal Validation
Joey Audley ARKEA
Jaleesa Gunn Solution Architects
Geraldo Baker Solution Architects
Augustina Golly Lyrical Rap Studies
Yung Garrah Sales
Delana Sigismund No Sleep Till
Hoa Colburn Superstar
Yung Garrah Sales
Riva Denton Solution Architects
Harriett Buckminster Imperial Army
FIRST_NAME LAST_NAME DEPT_NAME CITY
----------- ------------ -------------------- -------
Stacy Affleck MSA
12 rows selected.
SQL>
mysql> select * from employees_mysql;
+------------+-------------+---------------------+------+
| first_name | last_name | dept_name | city |
+------------+-------------+---------------------+------+
| Hilary | Randal | Validation | NULL |
| Joey | Audley | ARKEA | NULL |
| Jaleesa | Gunn | Solution Architects | NULL |
| Geraldo | Baker | Solution Architects | NULL |
| Augustina | Golly | Lyrical Rap Studies | NULL |
| Yung | Garrah | Sales | NULL |
| Delana | Sigismund | No Sleep Till | NULL |
| Hoa | Colburn | Superstar | NULL |
| Yung | Garrah | Sales | NULL |
| Riva | Denton | Solution Architects | NULL |
| Harriett | Buckminster | Imperial Army | NULL |
| Stacy | Affleck | MSA | NULL |
+------------+-------------+---------------------+------+
12 rows in set (0.00 sec)
mysql>
As you can see we ended up with the same masked values for both tables even the data is located in different databases
Scenario II : using different engines
To ensure the masking consistency in case of multi-engines deployment we have to use the same key.
Let's sync the masking key between our engines.
- exporting the key from the first engine
[root@linuxtarget masking_v2_api]# sh key_sync.sh
* logging in...
We've got the following token e9e0134d-8b03-47a5-92ed-840674ed16db
=======================================================================
=======================================================================
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
109 1468 104 1468 0 67 3321 151 --:--:-- --:--:-- --:--:-- 3191
We've exported this engine key {"exportResponseMetadata":{"exportHost":"192.168.247.132:8282","exportDate":"Mon Jan 29 11:34:20 UTC 2018","exportedObjectList":[{"objectIdentifier":{"keyId":"global"},"objectType":"KEY","revisionHash":"1268b1cc512be8e2bc15300fcdac243c781dcd1b"}]},"blob":"QgAAAAokMGIyMTFkZWItNGM2NC00NzlkLWFmN2YtZWYzMTc2ODU3ZDA4EhgyMDE4LTAxLTI5VDExOjM0OjIwLjI0M1ogAasBAAAKJDBiMjExZGViLTRjNjQtNDc5ZC1hZjdmLWVmMzE3Njg1N2QwOBKCAwo7CAkSNwordHlwZS5nb29nbGVhcGlzLmNvbS9FbmNyeXB0aW9uS2V5SWRlbnRpZmllchIICgZnbG9iYWwa/QEKJDAxYzcyZjJmLWM2OTItNGIyZC04ZDkxLTM3N2E1NDQ2YzQwNBIkODZkMjhhZmMtZjhjNy00ZTIzLWJkNjctN2ZiMDgxMWYyOWU4Gnd7CiAgIm9iamVjdFR5cGUiOiAiS0VZIiwKICAiaWQiOiB7CiAgICAiQHR5cGUiOiAidHlwZS5nb29nbGVhcGlzLmNvbS9FbmNyeXB0aW9uS2V5SWRlbnRpZmllciIsCiAgICAiaWQiOiAiZ2xvYmFsIgogIH0KfSIoYjJmNzk4MzgwYmY3MDVhZDU1YjdjMTk1MmQ4N2YzMWZlYWY0ZTJmZSjhm4ndhCww4ZuJ3YQsIkMKJXR5cGUuZ29vZ2xlYXBpcy5jb20vRW5jcnlwdGlvbktleURhdGESGgoYb3dtN1NiZGsyVnZSZG1id2NKd29nUT09VgAAAAokMGIyMTFkZWItNGM2NC00NzlkLWFmN2YtZWYzMTc2ODU3ZDA4Gi4wLAIUA0rFDP61wq14wVtdQXS94NZzXwYCFGxUI7qCegFTp+56uxkAF041OS/9","signature":"MCwCFANKxQz+tcKteMFbXUF0veDWc18GAhRsVCO6gnoBU6fuersZABdONTkv/Q==","publicKey":"MIHxMIGoBgcqhkjOOAQBMIGcAkEA/KaCzo4Syrom78z3EQ5SbbB4sF7ey80etKII864WF64B81uRpH5t9jQTxeEu0ImbzRMqzVDZkVG9xD7nN1kuFwIVAJYu3cw2nLqOuyYO5rahJtk0bjjFAkBnhHGyepz0TukaScUUfbGpqvJE8FpDTWSGkx0tFCcbnjUDC3H9c9oXkGmzLik1Yw4cIGI1TQ2iCmxBblC+eUykA0QAAkEAlovwga0S6qSb6Yu0Ul+Hn5tNsVlnixO0KKNNFekvXCkDeQHuEkBwrzVoND13yV8ujlBZCSKBHNB2bMHwuccleg=="}
=======================================================================
=======================================================================
[root@linuxtarget masking_v2_api]#
- Importing the key to the second engine
[root@linuxtarget masking_v2_api]# sh key_sync.sh
* logging in...
We've got the following token 0333d96d-52a8-4975-a8ce-3ab04d94cfd3
=======================================================================
=======================================================================
=======================================================================
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
103 1554 0 85 104 1469 60 1037 0:00:01 0:00:01 --:--:-- 1038
We've imported this key {"exportResponseMetadata":{"exportHost":"192.168.247.132:8282","exportDate":"Mon Jan 29 11:34:20 UTC 2018","exportedObjectList":[{"objectIdentifier":{"keyId":"global"},"objectType":"KEY","revisionHash":"1268b1cc512be8e2bc15300fcdac243c781dcd1b"}]},"blob":"QgAAAAokMGIyMTFkZWItNGM2NC00NzlkLWFmN2YtZWYzMTc2ODU3ZDA4EhgyMDE4LTAxLTI5VDExOjM0OjIwLjI0M1ogAasBAAAKJDBiMjExZGViLTRjNjQtNDc5ZC1hZjdmLWVmMzE3Njg1N2QwOBKCAwo7CAkSNwordHlwZS5nb29nbGVhcGlzLmNvbS9FbmNyeXB0aW9uS2V5SWRlbnRpZmllchIICgZnbG9iYWwa/QEKJDAxYzcyZjJmLWM2OTItNGIyZC04ZDkxLTM3N2E1NDQ2YzQwNBIkODZkMjhhZmMtZjhjNy00ZTIzLWJkNjctN2ZiMDgxMWYyOWU4Gnd7CiAgIm9iamVjdFR5cGUiOiAiS0VZIiwKICAiaWQiOiB7CiAgICAiQHR5cGUiOiAidHlwZS5nb29nbGVhcGlzLmNvbS9FbmNyeXB0aW9uS2V5SWRlbnRpZmllciIsCiAgICAiaWQiOiAiZ2xvYmFsIgogIH0KfSIoYjJmNzk4MzgwYmY3MDVhZDU1YjdjMTk1MmQ4N2YzMWZlYWY0ZTJmZSjhm4ndhCww4ZuJ3YQsIkMKJXR5cGUuZ29vZ2xlYXBpcy5jb20vRW5jcnlwdGlvbktleURhdGESGgoYb3dtN1NiZGsyVnZSZG1id2NKd29nUT09VgAAAAokMGIyMTFkZWItNGM2NC00NzlkLWFmN2YtZWYzMTc2ODU3ZDA4Gi4wLAIUA0rFDP61wq14wVtdQXS94NZzXwYCFGxUI7qCegFTp+56uxkAF041OS/9","signature":"MCwCFANKxQz+tcKteMFbXUF0veDWc18GAhRsVCO6gnoBU6fuersZABdONTkv/Q==","publicKey":"MIHxMIGoBgcqhkjOOAQBMIGcAkEA/KaCzo4Syrom78z3EQ5SbbB4sF7ey80etKII864WF64B81uRpH5t9jQTxeEu0ImbzRMqzVDZkVG9xD7nN1kuFwIVAJYu3cw2nLqOuyYO5rahJtk0bjjFAkBnhHGyepz0TukaScUUfbGpqvJE8FpDTWSGkx0tFCcbnjUDC3H9c9oXkGmzLik1Yw4cIGI1TQ2iCmxBblC+eUykA0QAAkEAlovwga0S6qSb6Yu0Ul+Hn5tNsVlnixO0KKNNFekvXCkDeQHuEkBwrzVoND13yV8ujlBZCSKBHNB2bMHwuccleg=="}
=======================================================================
[root@linuxtarget masking_v2_api]#
After syncing the engine masking key, mask both tables using different engine and the same algorithm for each.
[oracle@linuxsource ~]$ . oraenv
ORACLE_SID = [demovdb] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@linuxsource ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 29 08:35:11 2018
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning option
SQL> set long 9000
SQL> set lines 3000
SQL> select * from delphixdb.employees;
FIRST_NAME LAST_NAME DEPT_NAME CITY
----------- ------------ -------------------- -------
Hilary Randal Validation
Joey Audley ARKEA
Jaleesa Gunn Solution Architects
Geraldo Baker Solution Architects
Augustina Golly Lyrical Rap Studies
Yung Garrah Sales
Delana Sigismund No Sleep Till
Hoa Colburn Superstar
Yung Garrah Sales
Riva Denton Solution Architects
Harriett Buckminster Imperial Army
FIRST_NAME LAST_NAME DEPT_NAME CITY
----------- ------------ -------------------- -------
Stacy Affleck MSA
12 rows selected.
SQL>
mysql> select * from employees_mysql;
+------------+-------------+---------------------+------+
| first_name | last_name | dept_name | city |
+------------+-------------+---------------------+------+
| Hilary | Randal | Validation | NULL |
| Joey | Audley | ARKEA | NULL |
| Jaleesa | Gunn | Solution Architects | NULL |
| Geraldo | Baker | Solution Architects | NULL |
| Augustina | Golly | Lyrical Rap Studies | NULL |
| Yung | Garrah | Sales | NULL |
| Delana | Sigismund | No Sleep Till | NULL |
| Hoa | Colburn | Superstar | NULL |
| Yung | Garrah | Sales | NULL |
| Riva | Denton | Solution Architects | NULL |
| Harriett | Buckminster | Imperial Army | NULL |
| Stacy | Affleck | MSA | NULL |
+------------+-------------+---------------------+------+
12 rows in set (0.00 sec)
mysql>
We ended up with the same values even using different masking engines because of key sync and same algorithm usage.
Posts
No comments:
Post a Comment