CODEWITHSUNDEEP
snowflake-cloud-data-platform
DaveTurpin
DaveTurpin

Reputation: 33

Distinction between CREATE TABLE and CREATE TEMPORARY TABLE

SNOWFLAKE: Is it possible to grant a ROLE CREATE TEMPORARY TABLES but not PERMANENT tables?

The following snippet would not allow a role to CREATE TABLE, but allow CREATE TEMP tables. Also, the third command does not exist/work.

GRANT ALL PRIVILEGES ON SCHEMA myDB.mySchema TO ROLE myRole;
REVOKE CREATE TABLE ON SCHEMA myDB.mySchema FROM ROLE myRole;
GRANT CREATE **TEMPORARY** TABLE ON SCHEMA myDB.mySchema FROM ROLE myRole;

I can see many use cases where we want to limit a class of users from creating perm tables, but allow them to use scratch data.

Upvotes: 0

Views: 912

Answers (1)

Rachel McGuigan
Rachel McGuigan

Reputation: 541

That feature you are asking for is SNOW-62117 for reference, please ask about that with Snowflake Support.

Another option to consider for users to use scratch data is for an admin to create Views for scratch data. https://docs.snowflake.net/manuals/sql-reference/sql/create-materialized-view.html

Or check out the data exchange to administer what types of scratch data you can use, depending on the use case. https://docs.snowflake.net/manuals/sql-reference/sql/create-materialized-view.html

Hope that helps!

Upvotes: 1

Related Questions