S7. What's under the Snowflake(s)?

Do you want to keep secure your data? Then, let’s dedicate this new post of What’s under the Snowflake(s)? about secuity! We focus on the challenge of Week 39 – Basic from Frosty Friday.

To quickly start: copy and paste the block of code below. This will be the basis for your table:

CREATE OR REPLACE TABLE customer_deets ( id int, name string, email string ); 

INSERT INTO customer_deets VALUES
(1, 'Jeff Jeffy', 'jeff.jeffy121@gmail.com'), 
(2, 'Kyle Knight', 'kyleisdabest@hotmail.com'), 
(3, 'Spring Hall', 'hall.yay@gmail.com'), 
(4, 'Dr Holly Ray', 'drdr@yahoo.com');

Little tip: we can create and use designated databases and schemas to carry out our exercise. Once we have defined our environment, we just have to compile the code in the snippet. The result that appears on Showsignt will be the following:

At this point, we have to apply the column level security to the table customer_deets. The goal is to mask the email address, as sensitive data of customers.

How to proceed? A possible strategy!

What is a Column Level Security?

Column-level Security allows the application of a masking policy to a column within a table or view. Snowflake has two kinds of such column-level security: Dynamic Data Masking and External Tokenization. The former uses masking policies to selectively mask plain-text data in table and view columns at query time. This is that one we are going to use for the challegence.

Whilst, the latter enables grounds on tokenization that consists in a process of removing sensitive data by replacing it with an undecipherable token. External Tokenization makes use of masking policies with external functions.

How to create a masking policy?

customer_emails is name of our masking policy
input_string is the argument name to mask, whilst STRING is the argumnet type to mask.
RETURNS STRING: it return the data type that must match the input data type of the first column that is specified as an input column.
CASE WHEN … THEN: in normal cases of masking policy, it is a reasonable to use a combination of Conditional Expression Functions and Context Functions. Setting of the condition to trigger the masking policy will be easier by means of a conditional expression ‘If x, then y else z’.
CURRENT_ROLE() = ‘sysadmin’. The use of the CURRENT_ROLE context function is common in these cases. Why sysadmin? It is a good practice to use that role to set all the security stuff since it can globally MANAGE GRANTS privilege to grant or revoke privileges on objects in the account.
The regexp_replace function returns the subject with the specified pattern (or all occurrences of the pattern)(.+\\@) either removed or replaced by a replacement string (i.e. ‘snowflakeprotectsyoursensitivedata@’).

How to assign a security policy to a column of a table?

Let us test if our policy works. In the email column, it should return something like: «snowflakeprotectsyousensitivedata@…..com«. Hence, run the initial select statement. At this point, the wanting result should appear on your worksheet!