Working with Secure Views

This topic covers concepts and syntax for defining views and materialized views as secure.

In this Topic:

Overview of Secure Views

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 might 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 various commands and interfaces. For details, see Interacting with Secure Views (in this topic).

For security or privacy reasons, you might 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 created 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 certain optimizations used for regular views. This might result in some impact on query performance for secure views.


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?

Using the following widgets example, consider a user who has access to only a small subset of widgets, and all of the widgets are red. Suppose 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 might 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:

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

For non-materialized views, the IS_SECURE column in the Information Schema and Account Usage views identifies whether a view is secure. For example, for a view named MYVIEW in the mydb database:

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';

Alternatively, you can use the SHOW VIEWS command to view similar information (note that the view name is case-insensitive):


For materialized views, use the SHOW MATERIALIZED VIEWS command to identify whether a view is secure. For example:


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, because non-owners might 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:


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 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 might inadvertently expose information about the underlying data if views are not constructed carefully. This section discusses some potential pitfalls to avoid.

To illustrate these pitfalls, this section uses 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 might 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 might 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 might process twice as much data. While any such observations are approximate at best, in some cases it might 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 might be warranted.

Secure Views and Data Sharing

When using secure views with Sharing Data Securely in Snowflake, use the CURRENT_ACCOUNT function to authorize users from a specific account to access rows in a base table.


Snowflake does not allow using CURRENT_ROLE or CURRENT_USER in secure views that will be shared to other accounts, because the owner of the data being shared does not typically control the roles and users in the account to which it is being shared.