snowflake grants for create table

Question

I want to grant Create/Drop/Select/Insert/Delete/Truncate current & future table access to a role.

I did following still having problem.

grant usage on database TESTDB to role TEST_ROLE;
grant usage on schema TESTDB.TESTSCHEMA to role TEST_ROLE;
grant all on future tables in schema TESTDB.TESTSCHEMA to role TEST_ROLE;
grant all on all tables in schema TESTDB.TESTSCHEMA to role TEST_ROLE;

use role TEST_ROLE;
create table TESTDB.TESTSCHEMA.TESTTAB (name varchar(20);

SQL access control error: Insufficient privileges to operate on schema 'TESTSCHEMA'

Thanks

Answer 1

Creating a table is an action performed in the context of a schema. You need to use GRANT CREATE TABLE ON SCHEMA ...

Answer 2

I think you are looking to give all permissions of the new schema TESTSCHEMA (except ownership or giving grant to other roles) to the new role TEST_ROLE then use:

grant ALL PRIVILEGES on schema TESTDB.TESTSCHEMA to role TEST_ROLE;

you may verify the privileges giving by

show grants to role TEST_ROLE;

If you think that is too much, then make a list exactly what you want out of the SHOW command result and try to write the REVOKE/GRANT new command following doc of the privileges you wanna revoke/grant and we can assist further?

Answer 3

Below grants will provide CURD access to a role.

grant usage on database…
grant usage on schema…
grant create table on schema….
--above will give CURD
grant select, insert, delete, ... on all tables in schema
--above grant will take care of all exists table grants
grant select, insert, delete, ... on future tables in schema
--above grant will take care of all future tables