Snowflake Series : Secure views and Row Level Security

Introduction

In this introductionary blogpost, I'll tell you a story about Secure Views together with a howto set up Row Level Security. I've done this before with SSAS Multidimensional, SSAS Tabular model and even on the database with Azure SQL database and now with Snowflake.

What is the difference between a normal view and a secure view? 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).

Setup

So, what is the setup for the demo that is used as starting point for this blogpost? For this blogpost, I created a database, with two tables (data and a security table) and a couple of views. You can imagine that you want hide the tables for the users and use the views as a security layer (with Row Level Security).

Here is the script for the creation of the table. I used an example of Netflix series and movies for this purpose.

//=============================================================================
// Description : This is a Demo sql for explaining secure views
//============================================================================
//=============================================================================
// Set context
//=============================================================================
USE ROLE sysadmin;
USE WAREHOUSE Compute_WH;

//=============================================================================
// Create the Database and the Tables
//=============================================================================
CREATE OR REPLACE DATABASE SecureViews;
--DROP DATABASE IF EXISTS SecureViews;

USE DATABASE SecureViews;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE NETFLIX_SERIES (
    ID NUMBER,
    TITLE STRING,
    MOVIECATEGORY STRING
);

INSERT INTO NETFLIX_SERIES
VALUES 
  (0, 'Queens Gambit', 'Drama'), 
  (1, 'Ozark', 'Thriller'), 
  (2, 'The Crown', 'Drama'), 
  (3, 'Mindhunter', 'Don''t know'),  
  (4, 'The Haunting of Hill House', 'Horror'), 
  (5, 'Tiger King', 'Hmmm'), 
  (6, 'Stranger Things', 'Don''t know either');
  

Next, I created the User security table, with a Moviecategory and UserId as columns. For this demo I want to limit the access per user on certain movie categories. The user Hennie is allowed to see movies in the category "Drama" and Peter in the category "Thriller".

SELECT CURRENT_USER();

CREATE OR REPLACE TABLE USERSECURITY
(  
   MOVIECATEGORY STRING,
   USERID STRING
);

INSERT INTO USERSECURITY(MOVIECATEGORY, USERID) VALUES ('Drama', 'HENNIE'), ('Thriller', 'PETER');

SELECT * FROM USERSECURITY;

And here is the result of the SELECT:

Create a secure view

The next step is creating the secure view with the keyword SECURE in CREATE SECURE VIEW. Here is the DDL for the view.

CREATE OR REPLACE SECURE VIEW NETFLIX_SERIES_Secure_view_v2
AS
SELECT 
NS.ID,
NS.TITLE,
NS.MOVIECATEGORY,
US.USERID
FROM NETFLIX_SERIES NS
INNER JOIN USERSECURITY US ON NS.MOVIECATEGORY = US.MOVIECATEGORY
WHERE USERID = CURRENT_USER();

SELECT * FROM NETFLIX_SERIES_Secure_view_v2;

I executed this SELECT with user HENNIE and it is only showing the movies with category "Drama"

Next, I looked at SHOW VIEWS command

SHOW VIEWS;

And here you can see the definition of the view

Can I create this RLS on a normal View? Yes we can.. 

/=============================================================================
// Can I create the security without the SECURE key word?
//=============================================================================
CREATE OR REPLACE VIEW NETFLIX_SERIES_view
AS
SELECT 
NS.ID,
NS.TITLE,
NS.MOVIECATEGORY,
US.USERID
FROM NETFLIX_SERIES NS
INNER JOIN USERSECURITY US ON NS.MOVIECATEGORY = US.MOVIECATEGORY
WHERE USERID = CURRENT_USER();

SELECT * FROM NETFLIX_SERIES_view;

One thing I tried was creating a materialized Secure view. 

//=============================================================================
// Can I create a Secure materialized views?
//=============================================================================
CREATE OR REPLACE SECURE MATERIALIZED VIEW NETFLIX_SERIES_MATERIALIZED_SECURE_view
AS
SELECT 
NS.ID,
NS.TITLE,
NS.MOVIECATEGORY,
US.USERID
FROM NETFLIX_SERIES NS
INNER JOIN USERSECURITY US ON NS.MOVIECATEGORY = US.MOVIECATEGORY
WHERE USERID = CURRENT_USER();

Now creating a materialized view isn't possible because of the reason that it's not possible to create a materialized view based on two tables.

SQL compilation error: error line 0 at position -1 Invalid materialized view definition. More than one table referenced in the view definition

Check the RLS views with another User

Now create another user "PETER" and what are results that Peter can see of the Netflix series and movies. Here are some scripts for creating the user PETER.

USE ROLE ACCOUNTADMIN;

CREATE ROLE IF NOT EXISTS SecureViews;

--GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE SecureViews;

GRANT USAGE ON DATABASE SecureViews TO ROLE SecureViews;

GRANT USAGE ON SCHEMA SecureViews.PUBLIC TO ROLE SecureViews;

GRANT SELECT ON ALL TABLES IN SCHEMA SecureViews.PUBLIC TO ROLE SecureViews;
GRANT SELECT ON ALL VIEWS IN SCHEMA SecureViews.PUBLIC TO ROLE SecureViews;

--Grant role to USER PETER
GRANT ROLE SecureViews TO USER PETER;

Next step is logging as user PETER and query the normal view.

SELECT * FROM NETFLIX_SERIES_view;

And here is the result of the query. Peter sees the Thriller category, only.

Can peter see the same with the SECURE view? Yes he can...

SELECT * FROM NETFLIX_SERIES_Secure_view_v2

Now what is the the difference between a normal view and a SECURE view? As snowflake states : "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))."

SHOW VIEWS;

And the definitions are not present. So with Secure views you can't see the definition of the view.

There is a drawback when using the SECURE keyword. Some perfomance optimizations are bypassed and therefore some precautions are necessary when adopting this in queries.

Final Thoughts

This blogpost is about Secure views and row level security.

Hennie