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:
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 |
|
|
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 |
|
|
|
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 |
|
|
|
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