Distinction between CREATE TABLE and CREATE TEMPORARY TABLE

Question

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.

a Temporary table is "permanent" for the duration of the user session, so if they stay connected, they are in effect using up your disk space. But it does allow separation for sessions. Sounds like a good feature request. An alt solution might be to the role being allowed to create tables in a scratchpad schema work, and have a process/task that deletes table created over 24hrs ago, but that means sessions can see other sessions permanent tables. — Simeon Pilgrim
Simeon, I would prefer to stay away from the need to have to create a scrubber process to remove old scratch tables. I have that in MS SQL Server environments... it's easy but somewhat crude. An alternative to your alternative would be to have a global or session level setting that sets a time-to-live (TTL) on scratch tables. Per your suggestion, the very definition of a scratch table would be any table in a specially designated "scratch"/ephemeral schema. — DaveTurpin

Rachel McGuigan Rachel McGuigan · Accepted Answer · 2019-10-16T22:50:04

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!

Distinction between CREATE TABLE and CREATE TEMPORARY TABLE

1 Answers