Find every column of every table that has a masking policy assigned

Question

Is there a query that can list every column of every table that has a masking policy assigned?

Answer 1

You can't get this with just one SQL query, but this is a stored procedure developed by one of my teammates:

/* This table will hold a list of all masking policies defined */
CREATE OR REPLACE TABLE SNAPSHOT_MASKING_POLICIES (
SNAPSHOT_DATE TIMESTAMP,
CREATED_ON TIMESTAMP,
POLICY_NAME VARCHAR,
POLICY_DB VARCHAR,
POLICY_SCHEMA VARCHAR,
POLICY_KIND VARCHAR,
OWNER VARCHAR,
COMMENT VARCHAR);
/* This table will hold a list of all columns that are masked by one of the policies */
create or replace table SNAPSHOT_MASKING_POLICY_REFERENCES (
SNAPSHOT_DATE TIMESTAMP,
CREATED_ON TIMESTAMP,
POLICY_DB VARCHAR,
POLICY_SCHEMA VARCHAR,
POLICY_NAME VARCHAR,
POLICY_KIND VARCHAR,
REF_DATABASE_NAME VARCHAR,
REF_SCHEMA_NAME VARCHAR,
REF_ENTITY_NAME VARCHAR,
REF_ENTITY_DOMAIN VARCHAR,
REF_COLUMN_NAME VARCHAR
);

/* Procedure will loop through each policy and get the policy_references */  
CREATE OR REPLACE PROCEDURE UDP_GET_MASKING_POLICY_REFERENCES(V_POLICY_NAME STRING)
RETURNS STRING  
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
    var return_val = "";
    var query_id = "";
    var sql_command = "SHOW MASKING POLICIES LIKE '" + V_POLICY_NAME + "'"
    try {
        var result_set = snowflake.execute ({sqlText: sql_command});
        }
    catch (err)  {
        return "Failed: "+ sql_command + ": " + err;   // Return a success/error indicator.
        }
    var sql_command = "SELECT LAST_QUERY_ID()"
    try {
        var result_set = snowflake.execute ({sqlText: sql_command});
        while (result_set.next()) {
            query_id = result_set.getColumnValue(1);
            }
        }
    catch (err)  {
        return "Failed: "+ sql_command + ": " + err;   // Return a success/error indicator.
        }
    var sql_command = "DELETE FROM SNAPSHOT_MASKING_POLICIES WHERE POLICY_NAME LIKE ?"
    try {
        var result_set = snowflake.execute ({sqlText: sql_command,binds:[V_POLICY_NAME]});
        }
    catch (err)  {
        return "Failed: "+ sql_command + ": " + err;   // Return a success/error indicator.
        }
    var sql_command = "insert into SNAPSHOT_MASKING_POLICIES \
                       SELECT CURRENT_TIMESTAMP() AS SNAPSHOT_DATE,* \
                         FROM TABLE(RESULT_SCAN('" + query_id + "'));"
    try {
        var result_set = snowflake.execute ({sqlText: sql_command});
        }
    catch (err)  {
        return "Failed: "+ sql_command + ": " + err;   // Return a success/error indicator.
        }
    var sql_command = "DELETE FROM SNAPSHOT_MASKING_POLICY_REFERENCES WHERE POLICY_NAME LIKE ?"; 
    try {
        var result_set = snowflake.execute ({sqlText: sql_command,binds:[V_POLICY_NAME]});
        }
    catch (err)  {
        return "Failed: "+ sql_command + ": " + err;   // Return a success/error indicator.
        }
    var sql_command = "select POLICY_DB||'.'||POLICY_SCHEMA||'.'||POLICY_NAME FROM SNAPSHOT_MASKING_POLICIES WHERE POLICY_NAME LIKE ?"; 
    try {
        var result_set = snowflake.execute ({sqlText: sql_command,binds:[V_POLICY_NAME]});
        while (result_set.next())  {
        policy_name = result_set.getColumnValue(1);
        if (return_val == "") {
            return_val = policy_name;
        } else {
            return_val = return_val + ", " + policy_name;
        }
        var sql_command2 = "INSERT INTO SNAPSHOT_MASKING_POLICY_REFERENCES SELECT CURRENT_TIMESTAMP() AS SNAPSHOT_DATE, * FROM table(information_schema.policy_references(policy_name => '" + policy_name + "'))"
        var create_insert_stmt = snowflake.createStatement({ sqlText: sql_command2});
        try {
            var result_set2 = create_insert_stmt.execute ();
            }
        catch (err) {
        return "Failed: "+ sql_command2 + ": " + err;   // Return a success/error indicator.
            }
        }
        return "Complete: " + return_val;   // Return a success/error indicator.
        }
    catch (err)  {
        return "Failed: "+ sql_command + ": " + err;   // Return a success/error indicator.
        }
$$;
/* Call Procedure */
CALL UDP_GET_MASKING_POLICY_REFERENCES('%');
/* Display Results */
select * from SNAPSHOT_MASKING_POLICY_REFERENCES;

(thanks Bob Morrison!)