Why can’t we grant privileges to a Snowflake object AND its children? In the first week of experimenting with Snowflake we came across many easy functionalities. Unfortunately, I also encountered my first counterintuitive feature concerning granting privileges.

GRANT ALL ON ALL?

When constructing a role and defining the privileges it is easy to lose track of the number of commands that you need to construct to make sure that a role has access to a whole database. Look at all these lines that I would need to make sure that a role has all privileges to all objects in a database.

GRANT ALL ON ALL TABLES IN SCHEMA EX_DATABASE.EX_SCHEMA TO ROLE ex_role;
GRANT ALL ON ALL VIEWS IN SCHEMA EX_DATABASE.EX_SCHEMA TO ROLE ex_role;
GRANT ALL ON ALL MATERIALIZED VIEWS IN SCHEMA EX_DATABASE.EX_SCHEMA TO ROLE ex_role;
GRANT ALL ON ALL STAGES IN SCHEMA EX_DATABASE.EX_SCHEMA TO ROLE ex_role;
GRANT ALL ON ALL TASKS IN SCHEMA EX_DATABASE.EX_SCHEMA TO ROLE ex_role;
GRANT ALL ON ALL STREAMS IN SCHEMA EX_DATABASE.EX_SCHEMA TO ROLE ex_role;
GRANT ALL ON ALL PROCEDURES IN SCHEMA EX_DATABASE.EX_SCHEMA TO ROLE ex_role;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA EX_DATABASE.EX_SCHEMA TO ROLE ex_role;

Ideally, I would be hoping on the following code to work:

GRANT ALL ON ALL IN SCHEMA EX_DATABASE.EX_SCHEMA TO ROLE ex_role;

If you came to this blog with the same expectation, I have to disappoint you. This is not a functionality. I have found a work-around, but you’ll have to get down and dirty with SQL. Scroll down if you are curious about this solution!

Snowflake best practice

In the Snowflake documentation they mention that the option to grant privileges on all objects of the same type within a container is solely a convenience option for the user. Internally, Snowflake still processes the query the same as you would write individual commands. Since it is possible to look through all the objects of a certain type within a container, I must wonder; why wouldn’t it be possible to look through all objects within this container and do the same?

Snowflakes answer to this is that they recommend setting up your system in a different way. They would like that there is a shared role granted to multiple users. When the users create objects with this role, it would accessible to all users when they assume that role. However, giving a role the privilege to create an object seems to be a role that you want to grant to few users. Whereas the privilege to query a table or view should be a pretty general privilege for more users.

Work-around

Luckily, there is a way to look for all existing object types in a specific schema. Then use this information to make Snowflake dynamically write queries for you.

CREATE TEMPORARY TABLE temp1 AS SELECT DISTINCT object_type FROM ex_database.information_schema.object_privileges WHERE object_schema = 'EX_SCHEMA';

SELECT 'GRANT ALL ON ALL ' || object_type  ||'S IN SCHEMA EX_DATABASE.EX_SCHEMA TO ROLE ex_role' || ';' FROM temp1;

In this code we make use of the object_privileges view in the information schema. This view holds information about all the privileges of the objects, but it also has a column named object_type which will specify the type of the object. By querying this column with the key word distinct it will return all the types present in the ex_schema. The last line concatenates the query together. All that is left is a simple copy from the result and paste it in your worksheet.

At this point my experience with Snowflake is still limited to practice. I am wondering how roles and privileges will work in real life cases. Maybe I will be using this little trick quite often, or maybe it is indeed more convenient to make a shared role.