Free Template for Masking Data in Snowflake

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

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

‍

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.

‍

Start masking data in Snowflake now

‍

CASE STUDIES

Providing real solutions

View All

CLASSIFICATION

Zelis Leverages ALTR to Balance Data Democratization with Stringent Compliance Requirements

CLASSIFICATION

ALTR Helps Financial Institutions Pioneer New Era of Data Governance with Ease

CLASSIFICATION

Health and Well-Being Technology Redefines Data Governance at Scale with ALTR

CLASSIFICATION

ALTR Helps Fortune 10 Corporation Meet Stringent Compliance Requirements in Just Weeks

CLASSIFICATION

ALTR Helps Global Logistics Leader Scale Data Governance and Maximize Snowflake Data Cloud Investment

CLASSIFICATION

Tax and Compliance Software Vendor Bolsters Security and Simplifies Data Complexity with ALTR

CLASSIFICATION

Multinational Retailer Relies on ALTR to Secure Customer PII

CLASSIFICATION

TDECU Takes a Data-Driven Approach to Supporting Its Members’ Financial Journeys

CLASSIFICATION

Security Becomes a Business Enabler at this Leading Mortgage Firm

CLASSIFICATION

Redwood Logistics Accelerates Data-Driven Insight with ALTR and Aptitive

CLASSIFICATION

HumanN Utilizes Data Consumption Intelligence to Better Govern Customer Data

CLASSIFICATION

How ALTR helped Q2’s Biller Direct offering become Level 1 PCI DSS certified in 30 days

CLASSIFICATION

Exposure of Private Data

Back to Blog

Free Template for Masking Data in Snowflake

PUBLISHED:

No items found.

Step 1 - Create masking policy stored procedure

Run the following stored procedure in your Snowflake account, as ACCOUNTADMIN.

Copy Text

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

‍

Want to make masking data in Snowflake even easier?

Do it in ALTR!

‍

Start masking data in Snowflake now

‍

Free Template for Masking Data in Snowflake

Watch the Webinar

Step 1 - Create masking policy stored procedure

Step 2 - Mask data!

Want to make masking data in Snowflake even easier?

Start masking data in Snowflake now

Related Resources

The CISO's Dilemma: Securing Snowflake's ACCOUNTADMIN Role

ALTR Expands GTM Team with Powerhouse Hires to Lead the Charge in Data Security

Zelis Leverages ALTR to Balance Data Democratization with Stringent Compliance Requirements

Unleashing the Power of FPE: ALTR Key Sharing Meets Snowflake Data Sharing

Automated Data Access Governance and Security

Product

Pricing

Customers

Use Cases

Ecosystem

Resources

Company

industry

PLATFORM

use case

Free Template for Masking Data in Snowflake

Step 1 - Create masking policy stored procedure

Step 2 - Mask data!

Want to make masking data in Snowflake even easier?

Start masking data in Snowflake now

Heading 1

Heading 2

Heading 3

Heading 4

Heading 5

Heading 6

Providing real solutions

Free Template for Masking Data in Snowflake

Step 1 - Create masking policy stored procedure

Step 2 - Mask data!

Want to make masking data in Snowflake even easier?

Start masking data in Snowflake now

Free Template for Masking Data in Snowflake

Watch the Webinar

Step 1 - Create masking policy stored procedure

Step 2 - Mask data!

Want to make masking data in Snowflake even easier?

Start masking data in Snowflake now

Related Resources

The CISO's Dilemma: Securing Snowflake's ACCOUNTADMIN Role

ALTR Expands GTM Team with Powerhouse Hires to Lead the Charge in Data Security

Zelis Leverages ALTR to Balance Data Democratization with Stringent Compliance Requirements

Unleashing the Power of FPE: ALTR Key Sharing Meets Snowflake Data Sharing

Automated Data Access Governance and Security

Subscribe to ALTR's Newsletter

Product

Pricing

Customers

Use Cases

Ecosystem

Resources

Company

industry

PLATFORM

use case

Free Template for Masking Data in Snowflake

Step 1 - Create masking policy stored procedure

Step 2 - Mask data!

Want to make masking data in Snowflake even easier?

Start masking data in Snowflake now

Heading 1

Heading 2

Heading 3

Heading 4

Heading 5

Heading 6

Providing real solutions

Free Template for Masking Data in Snowflake

Step 1 - Create masking policy stored procedure

Step 2 - Mask data!

Want to make masking data in Snowflake even easier?

Start masking data in Snowflake now