Comment on page
Administration user
SnowDDL suggests two main options to set up the admin user, each having its own pros and cons.
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
PASSWORD = '<password>'
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 bySNOWDDL_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 withACCOUNTADMIN
role.
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
PASSWORD = '<password>'
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
orRESOURCE_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 grantACCOUNTADMIN
toSNOWDDL_ADMIN
and grantSNOWDDL_ADMIN
toACCOUNTADMIN
at the same time due to circular dependency.ACCOUNTADMN
will not be able to access objects created bySNOWDDL_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.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 ...
whenALTER TABLE
is not available.- Management of
MASKING POLICIES
andROW ACCESS POLICIES
, due to calls to table functionPOLICY_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.GRANT CREATE SHARE, IMPORT 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;
Last modified 1yr ago