20 Oct 18 · victor ·       Add to Favorites   Report

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

select *
from sales_data
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';


victor

posted on 20 Oct 18

Read great educational content like this and a lot more !

Members get free exclusive access to content, new courses, and discounts. Signup for a free account to write a post / comment / upvote posts. Creating an account takes less than 5 seconds and you can start earning badges & points too

Copied