Free Template to Quickly Create Views in Snowflake

If you need a quick way to create views in Snowflake, this template allows you to save a stored procedure that you can call any time to create views and permission roles for them. Enjoy!

Step 1 - Create View Template 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 - Create views!

Call the stored procedure, substituting your own database, schema, table, and column names, as well as the Role that should have access to the view. Once run, the view will be created and role will have access to the view automatically!

Copy Text

Want to make access control in Snowflake even easier?

Do it in ALTR!

ALTR lets you control access to data in Snowflake without writing code or getting stuck with view bloat. ALTR does a lot more, too, including automatically classifying data, masking sensitive data, and reporting on data usage, all for free.

‍

Start controlling access 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 to Quickly Create Views in Snowflake

PUBLISHED:

No items found.

If you need a quick way to create views in Snowflake, this template allows you to save a stored procedure that you can call any time to create views and permission roles for them. Enjoy!

Step 1 - Create View Template 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 - Create views!

Copy Text

Want to make access control in Snowflake even easier?

Do it in ALTR!

‍

Start controlling access in Snowflake now!

‍

Free Template to Quickly Create Views in Snowflake

Watch the Webinar

Step 1 - Create View Template stored procedure

Step 2 - Create views!

Want to make access control in Snowflake even easier?

Start controlling access 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 to Quickly Create Views in Snowflake

Step 1 - Create View Template stored procedure

Step 2 - Create views!

Want to make access control in Snowflake even easier?

Start controlling access in Snowflake now!

Heading 1

Heading 2

Heading 3

Heading 4

Heading 5

Heading 6

Providing real solutions

Free Template to Quickly Create Views in Snowflake

Step 1 - Create View Template stored procedure

Step 2 - Create views!

Want to make access control in Snowflake even easier?

Start controlling access in Snowflake now!

Free Template to Quickly Create Views in Snowflake

Watch the Webinar

Step 1 - Create View Template stored procedure

Step 2 - Create views!

Want to make access control in Snowflake even easier?

Start controlling access 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 to Quickly Create Views in Snowflake

Step 1 - Create View Template stored procedure

Step 2 - Create views!

Want to make access control in Snowflake even easier?

Start controlling access in Snowflake now!

Heading 1

Heading 2

Heading 3

Heading 4

Heading 5

Heading 6

Providing real solutions

Free Template to Quickly Create Views in Snowflake

Step 1 - Create View Template stored procedure

Step 2 - Create views!

Want to make access control in Snowflake even easier?

Start controlling access in Snowflake now!