Difference between regular views vs secure views in Snowflake
Often, 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.
- Secure views: Secure views are defined using the SECURE keyword with the standard DDL for views. Unauthorized users will not be able to see the view definition using GET_DDL or DESC commands.
- Regular Views: Regular views allows anyone to see the view definition.
Secure Views use-cases?
For example, if an analyst is authorized only to view Texas state, He may reverse engineer the query to see if there are any sales in Ohio State. (If there are any sales in OH the below query might fail and the user knows there are sales happening in Ohio even though he's not authorized to view this info) (NOTE: Sometimes the optimizer might choose to execute the user's filter first followed by the view's filter). To overcome this we can use a Secure view, In Secure view, the query optimizer does not evaluate the user’s filter before the authorization predicate
where 1/iff(state = 'OH', 0, 1) = 1;
How to create a Secure view?
Secure views are defined using the SECURE keyword with the standard DDL for views:
create or replace secure view sales_data as
select from sales where state = 'TX';