materialized view: grant all privs on schema

Question

GRANT ALL PRIVILEGES ON SCHEMA myDB.mySchema TO ROLE myRole;

For some reason, the above query does not cover Materialized Views:

grant create materialized view on schema myDB.mySchemato myRole;

The SF documentation does not include mViews in the definition:

schemaPrivileges ::= { MODIFY | MONITOR | USAGE | CREATE { TABLE | VIEW | FILE FORMAT | STAGE | PIPE | STREAM | TASK | SEQUENCE | FUNCTION | PROCEDURE } } [ , ... ]

Reference: https://docs.snowflake.net/manuals/sql-reference/sql/grant-privilege.html

In fact, I'm not sure I've found any documentation that shows the grant create materialized view on schema...".

Is this an omission in the implementation of the GRANT ALL PRIVs or intentional?

Answer 1

GRANT ALL PRIVILEGES ON SCHEMA, does include the privilege to create a new materialized view. Not sure what error you are receiving when you try this, but I tested it out on my Snowflake instance without an issue. You may want to make sure that the same role has permissions to the tables included in the view definition.

That said, we have made note of the documentation missing the materialized view details, and I'm sure somebody will get that updated, asap.