/

/

/

Free Template to Quickly Create Views in Snowflake

Create Views in Snowflake

Free Template to Quickly Create Views in Snowflake

Written by:

No items found.

Published on

Free Template to Quickly Create Views in Snowflake

Watch the Webinar

Get started for Free

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!

‍

‍

Cloud Data Warehouse

No items found.

No items found.

Related Resources

ALTR Featured as a Partner of the Snowflake Horizon Ecosystem

Q4 2023 ALTR Product: Helping Data Teams Ensure Data Security Earlier in the Data Lifecycle

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

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