If you need a quick way to create masking policies in Snowflake, this template allows you to save a stored procedure that you can call any time create new or append roles to an existing masking policy. Enjoy!
Step 1 - Create masking policy stored procedure Run the following stored procedure in your Snowflake account, as ACCOUNTADMIN.
Copy Text
View code snippet
Close code snippet
CREATE OR REPLACE PROCEDURE ADD_MASKING(DBNAME VARCHAR, SCHEMANAME VARCHAR, TABLENAME VARCHAR, COLUMNNAME VARCHAR, ROLENAME VARCHAR)
RETURNS STRING
LANGUAGE JAVASCRIPT
VOLATILE
EXECUTE AS CALLER
AS $$
function execQuery(queryString) {
return snowflake.execute({sqlText:queryString});
}
// This script must execute as ACCOUNTADMIN so that
// we can grant the target role permissions on objects
// that the target role may not even be able to see.
try {
execQuery("USE ROLE ACCOUNTADMIN");
} catch (error) {
return 'You must call this stored procedure with a user and role that can assume role ACCOUNTADMIN: ' + error;
}
//try to append to masking policy if it exists
try {
//get masking policies
var masks = execQuery(`SHOW MASKING POLICIES;`);
var name = execQuery(`select "name" as NAME from table(result_scan(last_query_id())) WHERE NAME='SP_FULL_MASK_${COLUMNNAME}'`);
if (name.getRowCount() != 0)
{
var result = execQuery(`DESCRIBE MASKING POLICY SP_FULL_MASK_${COLUMNNAME};`);
result.next();
var maskingBody = result.getColumnValueAsString('body');
//if not already in the masking policy
if (maskingBody.indexOf(ROLENAME) == -1)
{
//insert it in the masking Body
//find first when
var index = maskingBody.indexOf('when');
maskingBody = maskingBody.slice(0, index) + `when current_role() in ('${ROLENAME}') then '*********'\n\t\t` + maskingBody.slice(index);
//alter the masking policy
execQuery(`alter masking policy if exists SP_FULL_MASK_${COLUMNNAME} set body -> ${maskingBody}`);
return `Successfly added role ${ROLENAME} to mask`;
}
else {
return `Column ${COLUMNNAME} is already masked for ${ROLENAME}`;
}
}
} catch (error) {
return error;
}
//create masking policy if it doesn't exist
try {
execQuery(`
create masking policy if not exists SP_FULL_MASK_${COLUMNNAME} as (val string) returns string ->
case
when current_role() in ('${ROLENAME}') then '*********'
else val
end;`
);
//apply the policy
execQuery(`alter table if exists ${DBNAME}.${SCHEMANAME}.${TABLENAME} modify column ${COLUMNNAME} set masking policy SP_FULL_MASK_${COLUMNNAME};`);
return `Successfly masked column ${COLUMNNAME} for role ${ROLENAME}`;
} catch (error) {
return error;
}
return `Successfly masked column ${COLUMNNAME} for role ${ROLENAME}`;
$$;
Step 2 - Mask data!
Copy Text
View code snippet
Close code snippet
CALL ADD_MASKING([db_name], [schema_name], [table_name], [column_name], [role_name]);
Want to make masking data in Snowflake even easier? Do it in ALTR!
ALTR lets you mask data in Snowflake without writing any code, so creating and maintaining masking policies is easy and doesn’t take up your time. ALTR does a lot more, too, including automatically classifying data, controlling access to data, and reporting on data usage, all for free.
