Administration user

Administration permissions in Snowflake is a complicated topic.

SnowDDL suggests two main options to set up the admin user, each having its own pros and cons.

1) Role with SYSADMIN + SECURITYADMIN

Create a dedicated USER and ROLE for SnowDDL. Grant roles SYSADMIN and SECURITYADMIN to it.

USE ROLE ACCOUNTADMIN;

CREATE ROLE SNOWDDL_ADMIN;

GRANT ROLE SYSADMIN TO ROLE SNOWDDL_ADMIN;
GRANT ROLE SECURITYADMIN TO ROLE SNOWDDL_ADMIN;

CREATE USER SNOWDDL
TYPE = SERVICE
RSA_PUBLIC_KEY = '<rsa_public_key>'
DEFAULT_ROLE = SNOWDDL_ADMIN;

GRANT ROLE SNOWDDL_ADMIN TO USER SNOWDDL;
GRANT ROLE SNOWDDL_ADMIN TO ROLE ACCOUNTADMIN;

Pros:

  • SnowDDL will only manage objects created by SNOWDDL_ADMIN role. You will be able to have other databases, warehouses, users, roles created manually or by other tools (e.g. Fivetran).

  • ACCOUNTADMIN will have full access to objects created by SNOWDDL_ADMIN.

  • SnowDDL will be able to create and alter most objects types.

Cons:

  • SnowDDL will not be able to apply changes for some object types, like ACCOUNT_PARAMETER, RESOURCE_MONITOR. But SnowDDL will be able to "suggest" changes, which can be reviewed and applied manually by user with ACCOUNTADMIN role.

2) Role with ACCOUNTADMIN

Create a dedicated USER and ROLE for SnowDDL. Grant role ACCOUNTADMIN to it.

USE ROLE ACCOUNTADMIN;

CREATE ROLE SNOWDDL_ADMIN;
GRANT ROLE ACCOUNTADMIN TO ROLE SNOWDDL_ADMIN;

CREATE USER SNOWDDL
TYPE = SERVICE
RSA_PUBLIC_KEY = '<rsa_public_key>'
DEFAULT_ROLE = SNOWDDL_ADMIN;

GRANT ROLE SNOWDDL_ADMIN TO USER SNOWDDL;

Pros:

  • SnowDDL will be able to apply changes to all object types.

  • It will be possible to manage Snowflake accounts in fully automated way, without any kind of "review" and manual application of DDL queries by humans.

Cons:

  • You may apply some unwanted changes to ACCOUNT_PARAMETER, NETWORK_POLICY or RESOURCE_MONITOR, which may cause security issues, damage your data or make you spend too much credits.

  • ACCOUNTADMIN role will no longer be an "ultimate admin" role. It is not possible to grant ACCOUNTADMIN to SNOWDDL_ADMIN and grant SNOWDDL_ADMIN to ACCOUNTADMIN at the same time due to circular dependency. ACCOUNTADMN will not be able to access objects created by SNOWDDL_ADMIN.

Do not grant ACCOUNTADMIN role directly to SNOWDDL user. It may cause SnowDDL to wipe all users and data on your account, if not configured properly. Always create a dedicated role for SnowDDL.

Administration WAREHOUSE

SnowDDL does not require an active warehouse for most operations. But a very few use cases still require an active warehouse, such as:

  • CREATE OR REPLACE TABLE AS SELECT ... when ALTER TABLE is not available.

  • Management of MASKING POLICIES and ROW ACCESS POLICIES, due to calls to table function POLICY_REFERENCES.

In order to cover these use cases, you should create a WAREHOUSE and assign it to SnowDDL user and role. Please adjust warehouse size accordingly.

USE ROLE ACCOUNTADMIN;

CREATE WAREHOUSE SNOWDDL_WH
WAREHOUSE_SIZE = 'XSMALL'
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE
INITIALLY_SUSPENDED = TRUE;

GRANT USAGE, OPERATE ON WAREHOUSE SNOWDDL_WH TO ROLE SNOWDDL_ADMIN;

ALTER USER SNOWDDL SET DEFAULT_WAREHOUSE = SNOWDDL_WH;

Do not create administration warehouse using SnowDDL config. Unlike warehouses created by ACCOUNTADMIN, warehouses from config can be created or dropped automatically at any time.

Shares

Additional privileges are required to process outbound shares:

GRANT CREATE SHARE ON ACCOUNT TO ROLE SNOWDDL_ADMIN;

It may be also required to grant OVERRIDE SHARE RESTRICTIONS if you want to share data from Business Critical edition account to lower edition account(s):

GRANT OVERRIDE SHARE RESTRICTIONS ON ACCOUNT TO ROLE SNOWDDL_ADMIN;
PreviousOther guidesNextIntegrations

Last updated