How to Create, Interact, & Manage Snowflake Secure Views:?

Securing sensitive information is essential for businesses today. And, in the category of sensitive information, comes the underlying tables and internal structural details that might get exposed in the user view.

Snowflake, being an active contributor in the data security realm, has certain protocols to authorize or unauthorize users’ access to information in the user view.

For example, consider a user with access to a subset of data, and all that data contains is red balls, but the user writes a query to find if there is any yellow ball in the mix:

select *
from ball_view
where 1/iff(color = 'Purple', 0, 1) = 1;

If ball_view is not in Snowflake secure views, the query will definitely generate an error to show no yellow ball exists (the user not having access to the information will not be a deciding factor here). It’s established that error generation only depends on whether the query optimizer evaluates the user access filter or not.

Enter secure views. If ball_view was in the secure view, the query optimizer would have evaluated the user’s authorization to the information and then returned the answer to the query. Snowflake secure views ensure that only authorized persons can access the underlying data. 

How and When to use Snowflake Secure Views?

1. Sometimes users that require to do internal optimizations access the underlying data in the base table. This exposes some sensitive information that is not meant for some viewers.
2. A user can access sensitive information through user code like user-defined functions and other new programmatic functions/methods already developed. And, secure views ensure that users don’t have access to the underlying data.
3. Secure views not only protect sensitive information from unauthorized access. It simply adds users with special privileges, also known as the view definition or text, to make information available only using special commands and interfaces.
4. On the other hand, it’s advised only to use secure views when securing information that is specially designated for extended data privacy.
5. Snowflake recommends not to use secure views to define query convenience. Query convenience is created to simplify access to querying data, so users don’t need to understand the underlying data representation.
6. The use of secure views is advised for certain circumstances only because while evaluating insecure views, the Snowflake query optimizer bypasses certain optimizations used for regular views, resulting in lower query performance speed.

How to Create Snowflake Secure Views?

SECURE keyword with the standard DDL is used to define Secure views. Let’s see how:

First, you must specify the SECURE keyword. Here we leverage CREATE VIEW or CREATE MATERIALIZED VIEW commands.

Second, to convert an existing view into a secure view and vice versa, you will need to set/unset the SECURE keyword in the ALTER VIEW, or ALTER MATERIALIZED VIEW command.

Ways to Interact With Snowflake Secure Views

There are three ways to interact with Snowflake Secure Views. Let’s discuss them in detail.

1. Viewing the Definition for Secure Views

• Only the authorized users can see the definition of a secure view; hence the view definition does not get exposed to unauthorized users: Some view definitions are mentioned below:
  • SHOW VIEWS and SHOW MATERIALIZED VIEWS commands.
  • GET_DDL utility function.
  • VIEWS Information Schema view.
  • VIEWS Account Usage view.

2. Determine if a view is secure

The IS_SECURE column provided in the Information Schema and Account Usage views defines if a view is secure, but only for non-materialized views. Let’s explain it with an example:

select table_catalog, table_schema, table_name, is_secure from mydb.information_schema.views where table_name = 'MYVIEW';

select table_catalog, table_schema, table_name, is_secure from snowflake.account_usage.views where table_name = 'MYVIEW';

In the code provided above, MYVIEW is a view name in the mydb database. Alternatively, the SHOW VIEWS command can be leveraged to view similar information.

show views like 'myview';

Use the SHOW MATERIALIZED VIEWS command to identify whether a view is secure.

show materialized views like 'my_mv';

3. View Secure View Details in Query Profile

• Query Profile does not show secure view’s internal information. And the same is applicable to the owner of the secure view because a non-owner might have access to the Query Profile.

Manage Snowflake Secure Views With Snowflake Access Control

Using the CURRENT_ROLE and CURRENT_USER context functions, view security can be integrated with Snowflake users — the example given below shows how to use roles to control access to the rows of a table. The example uses the data (widgets) and an access table (widget_access_rules) to track which roles have access to which rows and which data table.

desc table widgets;

------------+-------------------+--------+-------+---------+-------------+------------+--------+------------+---------+
    name    |       type        |  kind  | null? | default | primary key | unique key | check  | expression | comment |
------------+-------------------+--------+-------+---------+-------------+------------+--------+------------+---------+
 ID         | NUMBER(38,0)      | COLUMN | Y     | [NULL]  | N           | N          | [NULL] | [NULL]     | [NULL]  |
 NAME       | VARCHAR(16777216) | COLUMN | Y     | [NULL]  | N           | N          | [NULL] | [NULL]     | [NULL]  |
 COLOR      | VARCHAR(16777216) | COLUMN | Y     | [NULL]  | N           | N          | [NULL] | [NULL]     | [NULL]  |
 PRICE      | NUMBER(38,0)      | COLUMN | Y     | [NULL]  | N           | N          | [NULL] | [NULL]     | [NULL]  |
 CREATED_ON | TIMESTAMP_LTZ(9)  | COLUMN | Y     | [NULL]  | N           | N          | [NULL] | [NULL]     | [NULL]  |
------------+-------------------+--------+-------+---------+-------------+------------+--------+------------+---------+

desc table widget_access_rules;

-----------+-------------------+--------+-------+---------+-------------+------------+--------+------------+---------+
   name    |       type        |  kind  | null? | default | primary key | unique key | check  | expression | comment |
-----------+-------------------+--------+-------+---------+-------------+------------+--------+------------+---------+
 WIDGET_ID | NUMBER(38,0)      | COLUMN | Y     | [NULL]  | N           | N          | [NULL] | [NULL]     | [NULL]  |
 ROLE_NAME | VARCHAR(16777216) | COLUMN | Y     | [NULL]  | N           | N          | [NULL] | [NULL]     | [NULL]  |
-----------+-------------------+--------+-------+---------+-------------+------------+--------+------------+---------+

create or replace secure view widgets_view as
select w.*
from widgets w
where w.id in (select widget_id
               from widget_access_rules a
               where upper(role_name) = current_role());

Snowflake Endorsed Best Practices for Using Snowflake Secure Views

• To stop unauthorized access, prevent users from viewing rows/tables of data, secure views has certain sets of best practices.
• We will go through these best practices using examples for each possible scenario in this section. In the example provided below, we will use the sample widgets tables and the view is defined similarly to the example provided above. Let’s begin.

Sequence-generated columns

• The most popular practice to generate surrogate keys is to use a sequence or auto-increment column. Probability dictates that if the keys/data are subjected to unauthorized access from users not having access to all of the underlying data, it is still possible to guess the underlying data distribution details.
• For example, widgets_view actually exposes the ID column. In fact, a user of widgets_view can actually find out the total number of widgets created between the given time interval that the user has access to if the ID is generated from a sequence:

select * from widgets_view order by created_on;

------+-----------------------+-------+-------+-------------------------------+
  ID  |         NAME          | COLOR | PRICE |          CREATED_ON           |
------+-----------------------+-------+-------+-------------------------------+
...
 315  | Small round widget    | Red   | 1     | 2017-01-07 15:22:14.810 -0700 |
 1455 | Small cylinder widget | Blue  | 2     | 2017-01-15 03:00:12.106 -0700 |

The example mentioned earlier shows how a user can guess/suspect the creation of 1139 widgets between the time interval January 7 and January 15. The following alternatives can be used to halt the view:

• Not exposing the sequence-generated column as part of the view.
• Using randomized identifiers instead of using sequence-generated values.
• Programmatically blurring the identifiers.

Scanned Data Size

• In general, Snowflake doesn’t reveal the amount of total number of data scanned for queries containing secure views. This protects an organization’s data from unauthorized access or from users having access to only a subset of the data. 
• But, in fact, based on the performance characteristics of queries, users can still make observations. For example, a query that runs twice as long might process twice as much data. While any such observations can be termed as approximations at best, in some cases, it might lead to unwanted data theft or surveillance issues.
• In cases like these, it’s recommended that data is materialized as per user/role. In the case of the widgets table, a table would be created for each role that has access to widgets, which contains only the widgets accessible by that role, and a role would be granted access to its table.
• Not only is this process more unmanageable than using a single view for high-security cases, but this is also be warranted by Snowflake themselves.

Secure Views and Data Sharing

Use the CURRENT_ACCOUNT function to authorize users to access specific rows when using secure views with Sharing Data Securely in Snowflake.

Helpful Resources about Snowflake and Snowflake Secure Views

If you want to learn more about Snowflake and Snowflake Secure Views, we will recommend three articles that will definitely help you:

1. Understanding Snowflake UI: An Easy Guide
2. Working with Secure Views
3. A Comprehensive Guide on Snowflake Pricing

Conclusion

• Let’s conclude. In this tutorial article, we discussed Snowflake Secure Views. in detail.
• We provided an outline of why Snowflake Secure Views is needed and can prove vital.
• In short, Snowflake Secure Views is used to secure sensitive data which might get exposed in the user view. To mitigate this shortcoming, we also provided a set of best practices that will help you define your next move.

And, as your business grows, data is generated at an exponential rate across business functions. To meet today’s growing storage and computing needs,  you will require a portion of your engineering bandwidth to integrate data from all sources, Clean & Transform it, and finally load it to a cloud data warehouse such as Snowflake for further business analytics. All this can be efficiently handled by a Cloud-Based ETL tool such as Hevo Data.

Hevo Data is a no-code data pipeline platform that helps new-age businesses integrate their data from multiple sources systems to a data warehouse and plug this unified data into any BI tool or Business Application.

The platform provides 150+ ready-to-use integrations with a range of data sources and is trusted by hundreds of data-driven organizations from 30+ countries.