Skip to main content

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.

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;

Tag​

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

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

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:

GRANT APPLY ON TAG <tag-identifier> TO ROLE DATAOPS_ADMIN;

If applying a tag using a role that doesn't have the OWNERSHIP privilege on the object being tagged:

GRANT APPLY TAG ON ACCOUNT TO ROLE DATAOPS_ADMIN;
Last updated on