Snowflake Secure Views vs. Views in Oracle

by Balachandra Sridhara

Snowflake Secure Views vs. Views in Oracle

What is a view in a Database?

A view is the result set of a SQL query built on top of one or more tables. Views do not store the data; however, during run time view definition (SQL) will be executed, and the result will be shown to the user. Please find the Wikipedia link for more information View (SQL).

Behavior and usage of the views are the same across all of the database technologies -

1. Normal RDBMS - Oracle, MS SQL Server
2. Cloud MPP Databases - AWS Redshift, Azure SQL DW (Gen1 or Gen2), Google BigQuery, Snowflake
3. Appliance DB's - Teradata, Greenplum, Vertica

Now I would like to talk about a special feature available in Snowflake, i.e. secure views - this is available for a normal view and materialized views.

What is a secure view?

Secure views are similar to normal views, but they will not expose underlying view definitions (tables used in SQL) for unauthorized Users and Roles. Implementing data level security is easy when creating secure views.

For more detailed information, please refer to the documentation - https://docs.snowflake.com/en/user-guide/views-secure.html#overview-of-secure-views

Best use case:

When exposing an object to a business analyst or a vendor team or a team of non-data owners, it is risky to expose underlying objects where sensitive and confidential data is stored. Ex: In a Sales channel of a company, for a business analyst ( vendor team) working for Texas BU, it is the responsibility of Data Steward to restrict the data for a business analyst to see only Texas BU and refrain him/her from checking underlying tables (how/source of the data).

Let me go through a few steps to demonstrate how a secure view can be used:

1. Create table - creating EMP table of SCOTT schema ( Oracle table for Demo purpose)
   
2. Create two views -

1. 1. Secured
      create secure view emp_mg_details_svw as select e.empno,e.ename,mgr.empno as mgr_empno,mgr.ename as mgr_name from emp e,emp mgr
      where e.mgr = mgr.empno
      alter view EMP_MG_DETAILS_SVW set secure;
      
   2. Non-Secured
      create view emp_mg_details_vw as select e.empno,e.ename,mgr.empno as mgr_empno,mgr.ename as mgr_name from emp e,emp mgr
      where e.mgr = mgr.empno
   1. Views are created under the DEMOADMIN role for a user. Now I'm provisioning the access to Developer role as below:
      grant select on emp_mg_details_svw to role DEMODEVELOPER;
      grant select on emp_mg_details_vw to role DEMODEVELOPER;
   2. Let's try to see the definitions from DEMODEVELOPER role:
      Normal view - select get_ddl('view','emp_mg_details_vw');
      
      Secure view - select get_ddl('view','emp_mg_details_svw');
      
   3. However, we can see the data of the secure view
      

In summary Secure views are good at controlling Data level and object level security where unauthorized user can not see what is happening behind the scene. In Secure view, the query optimizer does not evaluate the user's filter before the authorization predicate, hence it provides great flexibility to developer/admins to share the data in a secured way

Bala has spent over a decade working on various Big Data, Data Warehousing, Business Intelligence / Data Visualization projects in various companies. Some of the cloud technologies he loves to work on are AWS, Snowflake, and Azure. Basically he's into data integration, preparation and manipulation, it's the data analysis that really motivates him. He likes to understand and explore cutting edge technologies where many difficult data related problems can be solved.