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.
In this article, we will go through Snowfkale secure views in detail, talk about when and how to use it, and mention best practices, too. Let’s begin.
Table of Contents
- How and When to use Snowflake Secure Views?
- How to Create Snowflake Secure Views?
- Ways to Interact With Snowflake Secure Views
- Manage Snowflake Secure Views With Snowflake Access Control
- Best Practices for Using Snowflake Secure Views
How and When to use Snowflake Secure Views?
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.
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. 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.
On the other hand, it’s advised only to use secure views when securing information that is specially designated for extended data privacy.
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. 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:
Simplify Data Analysis With Hevo’s No-code Data Pipeline!
Hevo Data is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready format without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secured, consistent manner with zero data loss.Get Started with Hevo for Free
Check out why Hevo Data is the Best:
- Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
- Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
- Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
- Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
- Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
- Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
Ways to Interact With Snowflake Secure Views
There are three ways to interact with Snowflake Secure Views. Let’s discuss them in detail.
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.
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';
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.
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.
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.
If you want to learn more about Snowflake and Snowflake Secure Views, we will recommend three articles that will definitely help you:
- Understanding Snowflake UI: An Easy Guide
- Working with Secure Views
- Snowflake Pricing: A Comprehensive Guide for 2022
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 100+ ready-to-use integrations with a range of data sources and is trusted by hundreds of data-driven organizations from 30+ countries.Visit our Website to Explore Hevo
Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.
Don’t forget to comment below on your experience of reading today’s article.