Netezza Performance Server

Netezza Performance Server

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

Dynamic data masking and Role based encryption

By SUNIL BHATNAGAR posted Mon April 26, 2021 02:25 PM

  
Written by: @SUNIL BHATNAGAR @Aniket Kulkarni @MARK Fraase

This topic provides instructions on how to configure and use Dynamic Data Masking in Netezza Performance Server. To illustrate this we will use the following schema as an example:

Tables description
Lets consider that customer_name and customer_address are the sensitive columns that need to be masked and encrypted. Out of all the users that can access (ie. SELECT privilege) these tables, lets say that only those with role ACCOUNTS have the ability to view the actual values of these two columns. Others would see masked (via encryption) values for these two columns.
The standard way to do this is to protect access to the underlying tables and then have views on top of the masking tables that change the values depending on the role the users are privileged to have. To do this, one would create a view of the same name as the table by first renaming the underlying table and then projecting encrypted values for sensitive columns based on the roles that have access.
Step 1: Setup the encryption key for this masking in a separate table that only administrators have privilege and access to. This is a one time step that needs...

-- as DBA
CREATE TABLE "$encryption_keys" (KEY VARCHAR(30));
INSERT INTO "$encryption_keys" VALUES ('my-super-@-secret-key');

Step 2: Prep the base table, by renaming it and then create a masking view on top of it
ALTER TABLE customers RENAME TO "$customers_base";

CREATE VIEW customers AS
SELECT customer_id,
CASE WHEN current_role = 'ACCOUNTS' THEN customer_name
ELSE encrypt(current_name, (SELECT "$encryption_keys".key))
 END AS customer_name,
 CASE WHEN current_role = 'ACCOUNTS' THEN customer_address
ELSE encrypt(current_address, (SELECT "$encryption_keys".key))
 END AS customer_address,
 customer_state,
 customer_zip
FROM "$customers_base";

GRANT SELECT ON customers TO public;

Step 3: Allow a subset of users to have to privilege to take on the ACCOUNTS role. For example, if there are users alice, bob, clark lets assume that only alice and bob have the privilege to the ACCOUNTS role. This is typically done as follows
CREATE ROLE accounts;
GRANT list ON accounts TO alice;
GRANT list ON accounts TO bob;
GRANT select ON customers, orders TO accounts;
At this point the dynamic masking is setup and ready to be used. Lets say Alice needs a report of details of all the customer orders in the past month
SELECT SUM(order_amount) AS total, customer_state
FROM customers INNER JOIN orders
USING (customer_id)
GROUP BY customer_state;


Results in

TOTAL CUSTOMER_STATE
10750.01 NY
2000.00
 TX

Alice can access customers (the masked view). The customer_id column is not encrypted, and is used to join against the ORDERS table.
The customer_state column is not encrypted, and is used to do the roll-up (by state). But customer_name and customer_address are sensitive. If she chooses to use/view them they are encrypted.
SELECT *
FROM customers
ORDER BY customer_id;

Results in

CUSTOMER_ID CUSTOMER_NAME CUSTOMER_ADDRESS CUSTOMER_STATE CUSTOMER_ZIP
10031 &J.&#+RXV /U[/%9$,1WZJ\*?<1L3- TX 90210
21451
INR0-F50_0
1U*"\(7H"V;3S%NH0]!= ['T
 NY 55555
43918 I_*$)VM0X0 0U+;!="XWV;#T&^Y#F#=8[ NY 98765
60844 M>6%(7Q0Y@ -T+#!9$@9T+#T6L(5L0 NY 55554
If Alice needs to access the unencrypted columns she has been granted the privilege to take on the accounts roles which allows her to see the unencrypted source data
SET ROLE accounts;
SELECT *
FROM customers
ORDER BY customer_id;

Results in
CUSTOMER_ID CUSTOMER_NAME CUSTOMER_ADDRESS CUSTOMER_STATE CUSTOMER_ZIP
10031 Mark F 234 Main Street TX 90210
21451
Clark K
 1 Metropolis Ave NY 55555
43918 Bruce W 1600 Gotham Lane NY 98765
60844 Peter P 500 Fifth Ave NY 55554

Note: Instead of masking the data using encryption, one could use the same procedure to instead simply mask or hide data entirely. For example -

CREATE VIEW customers AS
SELECT customer_id,
CASE WHEN current_role = 'ACCOUNTS' THEN customer_name
ELSE '*****' -- this could be NULL if the column was not (VAR)CHAR
 END AS customer_name,
 CASE WHEN current_role = 'ACCOUNTS' THEN customer_address
ELSE '*****'
 END AS customer_address,
 customer_state,
 customer_zip
FROM "$customers_base";

The advantage to masking using encryption is that, if needed, one can join this table's encrypted column to another table's encrypted column and still maintain the relationship between rows. Whereas this wouldn't be possible if the column is instead masked or treated as NULL
​​​
#NetezzaPerformanceServer
0 comments
85 views

Permalink

https://community.ibm.com/community/user/blogs/sunil-bhatnagar1/2021/04/26/dynamic-data-masking-and-role-based-encryption