Privileges for a Fresh Environment
Below is the list of privileges required to manage objects using the Snowflake Object Lifecycle Engine (SOLE).
The role DATAOPS_ADMIN (default name) would be used to execute queries generated by SOLE in Snowflake.
Queries¶
Run all the queries mentioned below:
GRANT CREATE DATABASE ON ACCOUNT TO ROLE DATAOPS_ADMIN;
GRANT CREATE WAREHOUSE ON ACCOUNT TO DATAOPS_ADMIN;
GRANT CREATE USER ON ACCOUNT TO ROLE DATAOPS_ADMIN;
GRANT CREATE ROLE ON ACCOUNT TO DATAOPS_ADMIN;
GRANT CREATE SHARE ON ACCOUNT TO ROLE DATAOPS_ADMIN;
GRANT CREATE INTEGRATION ON ACCOUNT TO DATAOPS_ADMIN;
GRANT EXECUTE TASK ON ACCOUNT TO ROLE DATAOPS_ADMIN;
GRANT APPLY TAG ON ACCOUNT TO ROLE DATAOPS_ADMIN;
Info
Resource Monitor can only be created by roles with ACCOUNTADMIN access. If the resource monitor is to be managed by SOLE as well, run the following additional query.
Advanced query¶
This section lists out queries to be run if only a specific type of object is to be managed by SOLE or in case the parent of Database-Level objects already exists.
Database¶
Warehouse¶
User¶
Role¶
Share¶
Account (Grants)¶
No privilege is required to manage account grants.
Masking policy¶
If creating a masking policy using a role that wasn't used to create the database:
If creating a masking policy using a role that wasn't used to create the schema:
GRANT USAGE ON SCHEMA <database-name>.<schema-name> TO ROLE DATAOPS_ADMIN;
GRANT CREATE MASKING POLICY ON SCHEMA <database-name>.<schema-name> TO ROLE DATAOPS_ADMIN;
Pipe¶
If creating a pipe using a role that wasn't used to create the database:
If creating a pipe using a role that wasn't used to create the schema:
GRANT USAGE ON SCHEMA <database-name>.<schema-name> TO ROLE DATAOPS_ADMIN;
GRANT CREATE PIPE ON SCHEMA <database-name>.<schema-name> TO ROLE DATAOPS_ADMIN;
If creating a pipe using a role that wasn't used to create the table and using the table in a copy_statement parameter:
If using an external stage, and the stage wasn't created using the same role with which trying to create the pipe:
If using an internal stage, and the stage wasn't created using the same role with which trying to create the pipe:
View¶
If creating a view using a role that wasn't used to create the database:
If creating a view using a role that wasn't used to create the schema:
GRANT USAGE ON SCHEMA <database-name>.<schema-name> TO ROLE DATAOPS_ADMIN;
GRANT CREATE VIEW ON SCHEMA <database-name>.<schema-name> TO ROLE DATAOPS_ADMIN;
If using a table in a statement clause and the table wasn't created using the same role with which trying to create the view:
File format¶
If creating file format using a role that wasn't used to create the database:
If creating file format using a role that wasn't used to create the schema:
GRANT USAGE ON SCHEMA <database-name>.<schema-name> TO ROLE DATAOPS_ADMIN;
GRANT CREATE FILE FORMAT ON SCHEMA <database-name>.<schema-name> TO ROLE DATAOPS_ADMIN;
Schema¶
If creating schema using a role that wasn't used to create the database:
Table¶
If creating a table using a role that wasn't used to create the database:
If creating a table using a role that wasn't used to create the schema:
GRANT USAGE ON SCHEMA <database-name>.<schema-name> TO ROLE DATAOPS_ADMIN;
GRANT CREATE TABLE ON SCHEMA <database-name>.<schema-name> TO ROLE DATAOPS_ADMIN;
Stage¶
If creating a stage using a role that wasn't used to create the database:
If creating a stage using a role that wasn't used to create the schema:
GRANT USAGE ON SCHEMA <database-name>.<schema-name> TO ROLE DATAOPS_ADMIN;
GRANT CREATE STAGE ON SCHEMA <database-name>.<schema-name> TO ROLE DATAOPS_ADMIN;
Stream¶
If creating a stream using a role that wasn't used to create the database:
If creating a stream using a role that wasn't used to create the schema:
GRANT USAGE ON SCHEMA <database-name>.<schema-name> TO ROLE DATAOPS_ADMIN;
GRANT CREATE STREAM ON SCHEMA <database-name>.<schema-name> TO ROLE DATAOPS_ADMIN;
External table¶
If creating an external table using a role that wasn't used to create the database:
If creating an external table using a role that wasn't used to create the schema:
GRANT USAGE ON SCHEMA <database-name>.<schema-name> TO ROLE DATAOPS_ADMIN;
GRANT CREATE EXTERNAL TABLE ON SCHEMA <database-name>.<schema-name> TO ROLE DATAOPS_ADMIN;
Task¶
Require EXECUTE to grant a role the ability to execute a task after its creation:
If creating a task using a role that wasn't used to create the database:
If creating a task using a role that wasn't used to create the schema:
GRANT USAGE ON SCHEMA <database-name>.<schema-name> TO ROLE DATAOPS_ADMIN;
GRANT CREATE TASK ON SCHEMA <database-name>.<schema-name> TO ROLE DATAOPS_ADMIN;
If creating a task using a role that wasn't used to create the warehouse:
Based on the value of the sql_statement parameter:
- the
USAGEprivilege will be required on the database and the schema which we want to use. - if using a select statement on a table, the
SELECTprivilege will be required:
- if trying to insert into a table,
INSERTprivilege will be required:
- If using a stream in the task, and the stream isn't owned by the role:
Tag¶
If creating a tag using a role that wasn't used to create the database:
If creating a tag using a role that wasn't used to create the schema:
GRANT USAGE ON SCHEMA <database-name>.<schema-name> TO ROLE DATAOPS_ADMIN;
GRANT CREATE TAG ON SCHEMA <database-name>.<schema-name> TO ROLE DATAOPS_ADMIN;
If applying a tag using a role that has the OWNERSHIP privilege on the object being tagged:
If applying a tag using a role that doesn't have the OWNERSHIP privilege on the object being tagged: