Is it possible to protect columns of a view in snowflake?

Question

I'm working at a company where one team manages the databases and others use tables / views from there. From time to time, views get refactored which might break things for other teams.

Is it possible to protect some columns so that those cannot simply be renamed / dropped? Or at least have a log message telling the person who wants to do it that another team depends on it?

Answer 1

In Snowflake, only users with roles who have privileges to update a view by changing its definition are able to make changes on the specified view. If a specific role has privileges to replace view definition, there is no mechanism to stop them from renaming or dropping columns.

You can see the logs in QUERY_HISTORY function in Snowflake Information_schema. The functions gives extensive information on which user ran the query and the time it ran. A query like below would bring the appropriate information:

select user_name, role_name, query_text, start_time, end_time from table(information_schema.query_history())

where query_text ilike '%replace view %' order by start_time desc;

Answer 2

The privilege to alter a view is all or nothing. It does not restrict which columns the role can or cannot alter, remove, add, etc. However, since Snowflake allows using views as part of another view, this can form part of your organization's overall approach to do what you're seeking.

For example, create a base view that has all the protected columns. Tightly control access to which roles can alter the base view. From the base view, create views on top that less privileged roles can alter.