Skip to main content

Required 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;
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.

GRANT ROLE ACCOUNTADMIN to DATAOPS_ADMIN;

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

GRANT CREATE DATABASE ON ACCOUNT TO ROLE DATAOPS_ADMIN;

Warehouse

GRANT CREATE WAREHOUSE ON ACCOUNT TO DATAOPS_ADMIN;

User

GRANT CREATE USER ON ACCOUNT TO ROLE DATAOPS_ADMIN;

Role

GRANT CREATE ROLE ON ACCOUNT TO DATAOPS_ADMIN;

Share

GRANT CREATE SHARE ON ACCOUNT TO ROLE DATAOPS_ADMIN;

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:

GRANT USAGE ON DATABASE <database-name> TO ROLE DATAOPS_ADMIN;

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:

GRANT USAGE ON DATABASE <database-name> TO ROLE DATAOPS_ADMIN;

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:

GRANT INSERT ON TABLE <database-name>.<schema-name>.<table-name> TO ROLE DATAOPS_ADMIN;

If using an external stage, and the stage wasn't created using the same role with which trying to create the pipe:

GRANT USAGE ON STAGE <database-name>.<schema-name>.<stage-name> TO ROLE DATAOPS_ADMIN;

If using an internal stage, and the stage wasn't created using the same role with which trying to create the pipe:

GRANT READ ON STAGE <database-name>.<schema-name>.<stage-name> TO ROLE DATAOPS_ADMIN;

View

If creating a view using a role that wasn't used to create the database:

GRANT USAGE ON DATABASE <database-name> TO ROLE DATAOPS_ADMIN;

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:

GRANT SELECT ON TABLE <database-name>.<schema-name>.<table-name> TO ROLE DATAOPS_ADMIN;

File Format

If creating file format using a role that wasn't used to create the database:

GRANT USAGE ON DATABASE <database-name> TO ROLE DATAOPS_ADMIN;

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:

GRANT CREATE SCHEMA ON database <database-name> TO DATAOPS_ADMIN;

Table

If creating a table using a role that wasn't used to create the database:

GRANT USAGE ON DATABASE <database-name> TO ROLE DATAOPS_ADMIN;

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:

GRANT USAGE ON DATABASE <database-name> TO ROLE DATAOPS_ADMIN;

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:

GRANT USAGE ON DATABASE <database-name> TO ROLE DATAOPS_ADMIN;

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:

GRANT USAGE ON DATABASE <database-name> TO ROLE DATAOPS_ADMIN;

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:

GRANT EXECUTE TASK ON ACCOUNT TO ROLE DATAOPS_ADMIN;

If creating a task using a role that wasn't used to create the database:

GRANT USAGE ON DATABASE <database-name> TO ROLE DATAOPS_ADMIN;

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:

GRANT USAGE ON WAREHOUSE <warehouse-name> TO ROLE DATAOPS_ADMIN;

Based on the value of the sql_statement parameter:

  • the USAGE privilege will be required on the database and the schema which we want to use.

  • if using a select statement on a table, the SELECT privilege will be required:

    GRANT SELECT ON TABLE <database-name>.<schema-name>.<table-name> TO ROLE DATAOPS_ADMIN;
  • if trying to insert into a table, INSERT privilege will be required:

    GRANT INSERT ON TABLE <database-name>.<schema-name>.<table-name> TO ROLE DATAOPS_ADMIN;
  • If using a stream in the task, and the stream isn't owned by the role:

    GRANT SELECT ON STREAM <database-name>.<schema-name>.<stream-name> TO ROLE DATAOPS_ADMIN;