Dynamic data masking using encryption

This tutorial provides instructions on how to configure and use dynamic data masking using encryption in the Netezza Performance Server.

Dynamic data masking helps prevent unauthorized access to sensitive data in database objects such as fields, or table columns, by masking it from non-privileged users. Masking rules are only applied in the query results and the data in the database is not affected, so it can be used by other applications that may need access to this data.

About this task

The following example schema is used in this tutorial:

The customer_name and customer_address are the sensitive columns that need to be masked and encrypted. Of all the users that can access these tables (that is, they have the SELECT privilege) only those having ACCOUNTS role are allowed to view the two sensitive columns. For other users these values are masked via encryption.

The standard way to do this is to restrict access to the underlying tables and layer views on top of the tables that change the values depending on the role of the user. To do this, first rename the underlying table and then create a view, using the original name of the table, that projects encrypted values for the sensitive columns based on the roles that have access.

Procedure

Define the encryption key:
This is a one time step.
Set up an encryption key that will be used for masking by creating a separate table that only administrators have privilege and access to. As database administrator:
```
create table encryption_keys (key varchar(30)); 
insert into encryption_keys values('<your key value goes here>');
```

Prepare the base table by renaming it, and then create a masking view on top of it.

As database administrator:

alter table customers rename to customers_table;
create or replace view customers as
select
customer_id,
case when current_role = 'ACCOUNTS' then customer_name
else encrypt(customer_name, (select encryption_keys.key)) end as customer_name,
case when current_role = 'ACCOUNTS' then customer_address
else encrypt(customer_address, (select encryption_keys.key)) end as customer_address,
customer_state,
customer_zip
from customers_table;

Tip: Instead of masking the data using encryption, you could use the same procedure for simple masking or hiding the data entirely:


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";

However, the advantage to masking using encryption is that, if needed, you can, for example, join this table's encrypted column to another table's encrypted column and still maintain the relationship between the rows. This would not be possible if the column was simply masked or treated as NULL.

Grant the ACCOUNTS role to the selected users.
In this step, you allow a subset of users the privilege to take on the ACCOUNTS role. For example, in a group of users alice, bob, and clark, only alice and bob need the privilege to the ACCOUNTS role.
```
create role accounts;
grant list on accounts to alice, bob;
```
Next, grant the privilege to run queries against customers (the masked view) and orders (the table).
```
grant select on  customers, orders  to  public, accounts;
```
At this point the dynamic masking is setup and ready to use.

Results

Lets assume the user alice needs a report of the total order_amount, broken down by the state code:

select
sum(order_amount) as total,
customer_state
from customers inner join orders using (customer_id)
group by customer_state;

TOTAL	CUSTOMER_STATE
10750.01	NY
2000.00	TX
99.99	XX

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 view them, they are encrypted:

select * from customers order by customer_id;

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
80008	H>F0*F]0Y@	1L>B4*7<#U:WN&Z,JISI7YS<	XX	0

If Alice needs to access the unencrypted columns, she has been granted the privilege to take on the ACCOUNTS role which allows her to see the unencrypted source data:

set role accounts;
select * from customers order by customer_id;

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
80008	Diana P	Themyscira Island	XX	0