DELPHIX MASKING 5.2 AND REFERENTIAL INTEGRITY - PART II



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.

No comments:

Post a Comment