0

I created a masking policy for PII data. I then applied it to a table like so:

CREATE TABLE EXAMPLE.EXAMPLE_TABLE 
(ID INT,
LAST_NAME STRING,
PHONE_NUMBER INT);

ALTER TABLE EXAMPLE.EXAMPLE_TABLE MODIFY COLUMN LAST_NAME SET MASKING POLICY PUBLIC.PII_MASK_STRING;
ALTER TABLE EXAMPLE.EXAMPLE_TABLE MODIFY COLUMN PHONE_NUMBER SET MASKING POLICY PUBLIC.PII_MASK_NUMERIC;

Now I want to be able to reverse engineer a DDL script like this with the ALTER TABLE... SET MASKING POLICY included.

Is there a way to query for the list of columns that have masking policies applied to them (and which mask it uses)?

EDIT: For this case, the user has ownership of the table but not the masking policy. what permissions are required to query this information?

David Garrison
  • 2,546
  • 15
  • 25

2 Answers2

1

The information schema table-function POLICY_REFERENCES has some interesting information in this case, more here: https://docs.snowflake.com/en/sql-reference/functions/policy_references.html

If this is not enough, you can query all policies with all tables and all columns when you combine the query above with your SHOW MASKING POLICIES; and the RESULT_SCAN()-function. RESULT_SCAN() allows you to query the results of SHOW MASKING POLICIES; (https://docs.snowflake.com/en/sql-reference/functions/result_scan.html)

Consequence: You get all names of policies and for each of them you can call POLICY_REFERENCES().

Marcel
  • 2,454
  • 1
  • 5
  • 13
  • turns out, due to my misunderstanding of how masking policies get inherited from cloning, I DO NOT have ownership of the masking policy. Both of those queries are showing up as empty. What grants would be needed for this? – David Garrison Oct 22 '20 at 19:51
  • Only those objects for which the current role has been granted access privileges are returned: https://docs.snowflake.com/en/sql-reference/info-schema.html and you Need to use a full qualified name or USE-statements – Marcel Oct 22 '20 at 20:15
  • I don't see "access privileges" in the list of grants here: https://docs.snowflake.com/en/sql-reference/sql/grant-privilege.html The only thing for masking policies is APPLY, but I don't want to be able to move masks around (my DBAs wouldn't want that either). I just want to be able to view the list. – David Garrison Oct 22 '20 at 20:26
  • What I meant: You just need access (select) to your Table :) – Marcel Oct 22 '20 at 20:33
  • I have select on the table. but no access to the masking policy. With my testing, it's looking like APPLY on the masking policy can work. It makes it so those queries from your answer give me results, and without access to alter the tables I still can't mess with them, but I've already gotten DBA pushback as I expected. – David Garrison Oct 22 '20 at 21:12
-1

A vendor option is to use a Snowflake partner solution to automate masking policies on PII for dynamic data masking using Immuta. There is a demo video here if you find it helpful.

Full disclosure: I am employed my Immuta and my team works on content for data engineers.

Sumit Sarkar
  • 312
  • 1
  • 5