Working with Secure Views

This topic covers concepts and syntax for using secure views.

In this Topic:

Overview

What is a View?

A view allows the result of a query to be accessed as if it were a table. Any query expression that returns a valid result can be used to create a view, such as:

  • Selecting some (or all) columns in a table.
  • Selecting a specific range of data in table columns.
  • Joining data from two or more tables.

Views can be used to simplify queries and/or hide data. In addition, views can have query performance benefits. When a view is referenced in a query, the Snowflake query optimizer uses internal optimizations to process the view and the query together, enabling the view to be evaluated more efficiently than if it were evaluated separately.

Why Should I Use Secure Views?

Some of the internal optimizations for views require access to the underlying data in the base tables for the view. This access may allow data that is hidden from users of the view to be exposed through user code, such as user-defined functions, or other programmatic methods. Secure views do not utilize these optimizations, ensuring that users have no access to the underlying data.

In addition, by default, the query expression used to create a standard view, also known as the view definition or text, is visible to users in the following commands and interfaces:

For security or privacy reasons, you may not wish to expose the underlying tables or internal structural details for a view. With secure views, the view definition and details are only visible to authorized users, i.e. users who are granted the role that owns the view.

When Should I Use a Secure View?

Views should be defined as secure when they are specifically designated for data privacy, i.e. to limit access to sensitive data that should not be exposed to all users of the underlying table(s).

Secure views should not be used for views that are defined for query convenience, such as views create for simplifying querying data for which users do not need to understand the underlying data representation. This is because the Snowflake query optimizer, when evaluating secure views, bypasses the optimizations used for regular views. This may result in some impact on query performance for secure views.

Tip

When deciding whether to use a secure view, you should consider the purpose of the view and weigh the trade-off between data privacy/security and query performance.

How Might Data be Exposed by a Non-secure View?

Many users wonder exactly how a user could circumvent the security of a regular view. Using the following widgets example, consider a user who has access to only a small subset of widgets, and all of these widgets are red. The user wonders if any purple widgets exist and issues the following query:

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

If widgets_view is not a secure view, then this query may generate an error if a purple widget exists, even though the current user does not have access to that widget. Note that error generation depends on whether the query optimizer decides to evaluate the user’s filter before or after the authorization predicate (in the IN subquery). If widgets_view is a secure view, the query optimizer does not evaluate the user’s filter before the authorization predicate.

Creating Secure Views

Secure views are defined using the SECURE keyword with the standard DDL for views:

  • To create a secure view, specify the SECURE keyword in the CREATE VIEW command.
  • To convert an existing view to a secure view and back to a regular view, set/unset the SECURE keyword in the ALTER VIEW command.

Interacting with Secure Views

Viewing the Definition for Secure Views

The definition of a secure view is only exposed to authorized users, i.e. users who have been granted the role that owns the view. If an unauthorized user uses any of the following commands or interfaces, the view definition is not displayed:

Determining if a View is Secure

The IS_SECURE column in the VIEWS Information Schema view identifies whether a view is secure. For example:

select is_secure from information_schema.views where view_name = 'MYVIEW';

Viewing Secure View Details in Query Profile

The internals of a secure view are not exposed in Query Profile (in the web interface). This is the case even for the owner of the secure view, since non-owners may have access to an owner’s Query Profile.

Using Secure Views with Snowflake Access Control

View security can be integrated with Snowflake users and roles using the CURRENT_ROLE and CURRENT_USER context functions. The following example illustrates using roles to control access to the rows of a table. In addition to the table that contains the data (widgets), the example uses an access table (widget_access_rules) to track which roles have access to which rows in the data table:

Note

This example assumes the widgets and widget_access_rules tables already exist; it does not provide the syntax for creating the tables or populating them with data.

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());

Best Practices for Using Secure Views

Secure views prevent users from possibly being exposed to data from rows of tables that are filtered by the view. However, there are still ways that a data owner may inadvertently expose information about the underlying data if views are not constructed carefully. In this section, we discuss some potential pitfalls to avoid.

To illustrate these pitfalls, we will use the sample widgets tables and view defined in the earlier examples in this topic.

Sequence-generated Columns

A common practice for generating surrogate keys is to use a sequence or auto-increment column. If these keys are exposed to users who do not have access to all of the underlying data, then a user may be able to guess details of the underlying data distribution. For example, widgets_view exposes the ID column. If ID is generated from a sequence, then a user of widgets_view could deduce the total number of widgets created between the creation timestamps of two widgets that the user has access to. Consider the following query and result:

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 |
...

Based on the result, the user might suspect that 1139 widgets (1455 - 315) were created between January 7 and January 15. If this information is too sensitive to expose to users of a view, you can use any of the following alternatives:

  • Do not expose the sequence-generated column as part of the view.
  • Use randomized identifiers (e.g. generated by UUID_STRING) instead of sequence-generated values.
  • Programmatically obfuscate the identifiers.

Scanned Data Size

For queries containing secure views, Snowflake does not expose the amount of data scanned (either in terms of bytes or micro-partitions) or the total amount of data. This is to protect the information from users who only have access to a subset of the data. However, users may still be able to make observations about the quantity of underlying data based on performance characteristics of queries. For example, a query that runs twice as long may process twice as much data. While any such observations are approximate at best, in some cases it may be undesirable for even this level of information to be exposed.

In such cases, it is best to materialize data per user/role instead of exposing views on the base data to users. 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. This is much more cumbersome than using a single view, but for extremely high-security situations, this may be warranted.

Secure Views and Data Sharing

When using secure views with Snowflake Data Sharing, the CURRENT_ACCOUNT function can be used to authorize users from a specific account to access rows in a base table. Note that Snowflake does not recommend using CURRENT_ROLE or CURRENT_USER in secure views that will be shared to other accounts, since the owner of the data being shared does not typically control the roles and users in the account to which it is being shared.