CODEWITHSUNDEEP
snowflake-cloud-data-platform
Eric Mamet
Eric Mamet

Reputation: 3671

What is the complete list of privileges a role needs in order to create a table in a schema?

I have granted USAGE on the schemas and database. I have granted select on all tables.

Using that role, I can read data from all tables within any schema.

I then grant the permission to create tables in all schemas within that database

GRANT CREATE TABLE ON ALL SCHEMAS IN DATABASE TEST1_CONTROL TO DEVELOPERS;

Yet, when I issue this command (while using DEVELOPERS role), I get an error

CREATE TABLE PDS.ERIC_TEST_TABLE(COUCOU STRING NULL);

enter image description here

What am I missing?

Upvotes: 1

Views: 388

Answers (1)

Suzy Lockwood
Suzy Lockwood

Reputation: 1180

Works fine for me (script below). Going to go with what Lukasz commented and that your schema was created later.

use role accountadmin;
create database TEST1_CONTROL;
create schema PDS;
create role DEVELOPERS;
grant role DEVELOPERS to user <your_username>;
GRANT USAGE ON DATABASE TEST1_CONTROL TO DEVELOPERS;
GRANT USAGE ON ALL SCHEMAS IN DATABASE TEST1_CONTROL TO DEVELOPERS;
GRANT CREATE TABLE ON ALL SCHEMAS IN DATABASE TEST1_CONTROL TO DEVELOPERS;
use role DEVELOPERS;
CREATE TABLE PDS.ERIC_TEST_TABLE(COUCOU STRING NULL);

Snowflake does offer future grants if you want a role to have access to any new schemas that would be created in the future.

Upvotes: 4

Related Questions