Support for Snowflake Secure Views

[Carolus-Holman]

Adding support for Secure View in Snowflake

When using the Materialize feature where setting the type of materialization, adding secure-view to the {{ config(materialized='secure-view') }} would be beneficial.

Current Work-around

Currently the solution for Snowflake secure views is running post-hook events to set the targeted views as secure, example: alter view sv_mySecureTest set secure;
This works, and each view that needs to be secured will need to be added to the post-hook event.

Affects only Snowflake

This feature is specific to the Snowflake Cloud Data warehouse.
https://docs.snowflake.net/manuals/user-guide/views-secure.html

This will help DBT Snowflake Developer / Non Developers

When creating a secure view in Snowflake, a developer can use 2 syntax commands

1. CREATE OR REPLACE SECURE VIEW...
2. Alter view <view_name> Set Secure

The first method will allow non-dbt user to render the DDL with the secure declaration as part of the DDL, the second statement is added to the end of the generated DDL however it may be ignored by developers unfamiliar with Snowflake Syntax, causing possible security issues, allowing unauthorized access to the View DDL by Read-Only roles in Snowflake.

[drewbanin]

Thanks @SolariPicasso! This would be a great feature to add to dbt.

You can check out the code that dbt uses to create views on Snowflake here.

I think one good approach for implementing this might be to add a config, like secure: true|false. We do something similar on Redshift to support "late bound views".

A config like secure would work like:

{{ config(materialized='view', secure=true) }}

We can also add secure as an AdapterSpecificConfig which will allow users to supply the secure: config in their dbt_project.yml file.

If this is something you're interested in contributing a PR for, I'd be happy to help!

[Carolus-Holman]

@drewbanin I am interested in contributing, however my Python knowledge is close to Zero, I have forked the branch, loaded the project, added some code to enable the secure view, however I can't seem to get the .15 build to work. I have read through the docs on the coding environment, and I am struggling to use the build correctly. Are there any pointers for getting DBT running using VSCODE and Windows. Thanks. Code Below:
Adapters.sql:
{% macro snowflake__create_view_as(relation, sql) -%} create or replace {% if secure -%} secure {%- endif %} view {{ relation }} as ( {{ sql }} ); {% endmacro %}
IMPL.py
AdapterSpecificConfigs = frozenset( {"transient", "cluster_by", "automatic_clustering","secure"} )