Snowflake does not support a table-level privilege that would restrict users to creating transient or temporary tables only. A solution would be to create a transient database and assign the necessary permissions for users in a specific role to create tables within this database. By design, all tables created within a transient database are transient.
- Create the transient database writeDB.
CREATE TRANSIENT DATABASE writeDB;
- Grant usage on writeDB to role Analysts.
GRANT USAGE ON DATABASE writedb TO ROLE analyst;
- Grant the CREATE TABLE and USAGE privileges on all schemas in the writeDB database. Members of a role automatically have all privileges on tables created by another member.
GRANT USAGE ON SCHEMA public TO ROLE analyst;
GRANT CREATE TABLE ON SCHEMA public TO ROLE analyst;
- Optionally grant additional table-level privileges on specific existing tables in a schema.