Snowflake Secure vs Non-Secure Views in Data Sharing

In Snowflake AI Data Cloud, secure and non-secure views are two types of views with differences in how they manage data security, performance, and query visibility.

In this blog, we will discuss key differences between secure and non-secure views, their implementations for data sharing, and best practices for utilizing them effectively.

Overview of Views in Snowflake

In Snowflake, views are virtual tables that allow users to query data without physically storing it. They can simplify complex queries and provide a layer of abstraction over the underlying data. However, regarding data sharing, the choice between secure and non-secure views can significantly impact data governance and security.

Secure Views

Secure views in Snowflake are designed to enhance security and restrict access to the view’s definition and underlying data; secure views provide the following features:

Data Masking: To protect sensitive information, share data using data masking or redaction policies. Ensure that shared data complies with privacy regulations and internal data governance policies.
Role-based Access Control(RBAC): Implement strong RBAC policies to control who can share and access shared data. Define roles and permissions to ensure data security and compliance.
Auditability: Secure view maintains a clear audit trail, allowing organizations to track who accessed the data and when.
No Direct Access to Underlying Tables: Users cannot query the underlying tables directly if they only have access to the secure view, which helps protect sensitive data.

Practical Implementation Of Secure View Features: Data Masking

1. Create a Masking Policy

Define a masking policy to determine how sensitive data should be masked. In this example, the masking policy will show full data only to users with a specific role (e.g., HR_ROLE), while other users will see masked data.

				
					CREATE MASKING POLICY ssn_masking_policy AS (val STRING) 
RETURNS STRING ->
    CASE
        WHEN current_role() = 'HR_ROLE' THEN val
        ELSE 'XXX-XX-XXXX'
    END;

In this policy:

If the current role is HR_ROLE, the user can see the unmasked value.
For all other roles, the user sees a masked version of the data (XXX-XX-XXXX).

2. Apply the Masking Policy to a Column

Attach the masking policy to the column in the underlying table that needs masking. This masking policy will automatically apply to any views that reference this column.

				
					ALTER TABLE my_table MODIFY COLUMN ssn SET MASKING POLICY ssn_masking_policy;

In this example, the ssn column in my_table will be masked based on the abovementioned policy.

3. Create a Secure View

Now, create a secure view that references the masked column. The secure view will inherit the masking policy applied to the column, so users querying this view will see masked or unmasked data based on their role

				
					CREATE SECURE VIEW my_secure_view AS
SELECT name, ssn, salary
FROM my_table;

With the secure view in place:

Users with the HR_ROLE role will see the actual ssn values.
Other users will see the ssn values masked as XXX-XX-XXXX.

4. Test Column Masking in the Secure View

When users query my_secure_view, they will see either the original or the masked ssn values based on their role.

Query as a User with the HR_ROLE Role

				
					-- Assume the role has been set to HR_ROLE
SELECT * FROM my_secure_view;

Query as a User without the HR_ROLE Role

				
					-- Assume the role has been set to a non-HR role
SELECT * FROM my_secure_view;

Using masking policies in a secure view provides dynamic and granular control over data visibility, ensuring sensitive information is protected while still accessible to authorized users.

Access Control

Access control for secure views involves managing which roles and users have permission to access the view. Snowflake provides role-based access control (RBAC), where permissions are assigned to roles rather than directly to users.

1. Create Roles for Access

Define specific roles based on user responsibilities and access requirements. For example:

				
					CREATE ROLE hr_view_access_role;
CREATE ROLE finance_view_access_role;

2. Grant Role Permissions on the Secure View

Assign the necessary privileges from a secure view to the roles. Use SELECT permission to control read access.

				
					GRANT SELECT ON VIEW my_secure_view TO ROLE hr_view_access_role;

3. Assign Roles to Users

Grant the roles to specific users who need access to the view.

				
					GRANT ROLE hr_view_access_role TO USER john_doe; 
GRANT ROLE finance_view_access_role TO USER jane_smith;

4. Use Row Access Policies for Granular Control

Row access policies can apply additional filters based on the user’s role or attributes, further controlling which data each role can see in the view.

				
					CREATE ROW ACCESS POLICY hr_department_policy AS (user_name STRING) 
RETURNS BOOLEAN -> 
CASE 
WHEN user_name = 'john_doe' THEN TRUE 
ELSE FALSE 
END; 

ALTER VIEW my_secure_view SET ROW ACCESS POLICY hr_department_policy;

Auditability

The QUERY_HISTORY view in SNOWFLAKE.ACCOUNT_USAGE provides detailed information about each query executed, including the user, query text, and execution time. This allows you to identify who accessed the secure view and their actions.

1. Enable Query History Access

Ensure you have the appropriate permissions to access ACCOUNT_USAGE.QUERY_HISTORY

2. Log View Access with `QUERY_HISTORY`

To monitor access to a specific view, filter QUERY_HISTORY by the view name:

				
					SELECT query_start_time, user_name, role_name, query_text, database_name, schema_name, object_name AS view_name 
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY 
WHERE object_name = 'my_secure_view' 
ORDER BY query_start_time DESC;

3. Set up a task to log view access to a custom table

You can use a scheduled task to periodically insert data from QUERY_HISTORY:

				
					-- Creating a custom audit log table.
CREATE TABLE access_audit_log ( access_time TIMESTAMP, user_name STRING, role_name STRING, query TEXT );

-- Inserting logs into the custom audit table created above on a scheduled basis.
CREATE OR REPLACE TASK audit_secure_view_access 
WAREHOUSE = my_warehouse 
SCHEDULE = '1 DAY' AS INSERT INTO access_audit_log 
SELECT query_start_time, user_name, role_name, query_text 
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY 
WHERE object_name = 'my_secure_view';

Use Cases for Secure Views

Secure views are ideal for scenarios where data privacy is crucial.

When protecting sensitive or proprietary logins within the view,
Ensuring compliance with security policies that mandate query definition confidentiality.
Sharing datasets with external parties while hiding the data sources or logic.
Role-based access to data.
Sharing sensitive information with third parties while ensuring compliance with data protection regulations.

Non-Secure Views

A non-secure view is a regular view that does not have built-in security features. This means the underlying query definition is visible to all users with access to the view, potentially exposing sensitive information about the data structure and allowing unauthorized inferences about the data. In contrast, a secure view offers stricter access controls and data masking to protect sensitive information.

Full view definition visibility: Anyone accessing the view can see the exact SQL query used to create it, including column names, filtering, and data transformation logic.
No column-level access control: Unlike secure views, non-secure views cannot restrict access to specific columns within the data.
No data redaction or masking: Sensitive data within the view is not automatically masked or redacted for unauthorized users.
Potentially less secure for sensitive data: Because they lack security features, non-secure views should be used cautiously when dealing with sensitive data.
Use when security is not a concern: Non-secure views provide a lighter-weight view option. They are not as restrictive and are typically used when data security requirements are less strict.
Performance: Non-secure views may perform better than secure views; they lack additional security checks applied to secure views.

Use Cases for Non-Secure Views

Non-secure views are suitable for less sensitive data where security is not a primary concern, such as:

Internal reporting where data privacy is not an issue.
Sharing aggregated data with partners where individual data points do not need to be protected.
Queries where performance and optimization are priority.
Scenarios where exposing SQL logic and structure is not a concern.

Choosing Between Secure and Non-Secure Views

When deciding between secure and non-secure views, consider the following:

Data Sensitivity: Use secure views to protect sensitive data or enforce strict security policies.
Performance Needs: Non-secure views may be more performant, so they’re better for high-volume or non-sensitive data sharing.
Transparency Requirements: Use non-secure views when sharing logic. Transparency is needed or acceptable for end-users, and secure views when hiding the logic is crucial.

Conclusion

Secure views enhance data privacy by limiting access to underlying data and hiding the view definition from unauthorized users. A non-secure view exposes the full query details and allows the user to see how the view is constructed, potentially revealing sensitive information about the base tables. Due to additional security checks, secure views may execute slightly slower than non-secure views.

Are you looking to optimize your Snowflake data-sharing strategy? Contact our team today for expert guidance on secure and efficient solutions.