Snowflake: Backup and recovery of users and permissions

user_9800032 0
Viewed 519

With snowflake (cloud data platform) one can use time travel and failsafe to protect against data corruption due to user error, data loss due to hardware failure, but does that cover all the users, roles, and grants? (Our security is pretty extensive with hundreds of users and roles often nested several levels deep.) If not, is there a simple way to back all those up? I've attempted to write a SQL script to generate all the create user/role and grant statements but its proving more difficult to extract that from the grants_to_roles view than anticipated because of all the permutations of grant statement syntax. Rather than continue I thought I'd ask here to see if maybe I'm missing something and its not necessary, or maybe someone else has already done this and I'm reinventing the wheel.

TIA

snowflake-cloud-data-platform
1 Answers

Check this post that creates 3 procedures to automate this:

Grant, roles and tables:

CREATE OR REPLACE TABLE DBUSERS (NAME VARCHAR,CREATED_ON TIMESTAMP_LTZ,LOGIN_NAME VARCHAR,DISPLAY_NAME VARCHAR,FIRST_NAME VARCHAR,LAST_NAME VARCHAR,EMAIL VARCHAR,MINS_TO_UNLOCK VARCHAR,DAYS_TO_EXPIRY VARCHAR,TCOMMENT VARCHAR,DISABLED VARCHAR,MUST_CHANGE_PASSWORD VARCHAR,SNOWFLAKE_LOCK VARCHAR,DEFAULT_WAREHOUSE VARCHAR,DEFAULT_NAMESPACE VARCHAR,DEFAULT_ROLE VARCHAR,EXT_AUTHN_DUO VARCHAR,EXT_AUTHN_UID VARCHAR,MINS_TO_BYPASS_MFA VARCHAR,OWNER VARCHAR,LAST_SUCCESS_LOGIN TIMESTAMP_LTZ,EXPIRES_AT_TIME TIMESTAMP_LTZ,LOCKED_UNTIL_TIME TIMESTAMP_LTZ,HAS_PASSWORD VARCHAR,HAS_RSA_PUBLIC_KEY VARCHAR,REFRESH_DATE TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP())COMMENT = ‘stores snapshot of current snowflake users’ ;

CREATE OR REPLACE TABLE DBROLES (CREATED_ON TIMESTAMP_LTZ,NAME VARCHAR,IS_DEFAULT VARCHAR,IS_CURRENT VARCHAR,IS_INHERITED VARCHAR,ASSIGNED_TO_USERS NUMBER,GRANTED_TO_ROLES NUMBER,GRANTED_ROLES NUMBER,OWNER VARCHAR,RCOMMENT VARCHAR,REFRESH_DATE TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP())COMMENT = ‘stores snapshot of current snowflake roles’ ;

CREATE OR REPLACE TABLE DBGRANTS (CREATED_ON TIMESTAMP_LTZ,PRIVILEGE VARCHAR,GRANTED_ON VARCHAR,NAME VARCHAR,GRANTED_TO VARCHAR,GRANTEE_NAME VARCHAR,GRANT_OPTION VARCHAR,GRANTED_BY VARCHAR,REFRESH_DATE TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP())COMMENT = ‘stores snapshot of current grants’ ;

A more sustainable approach would be to have your master copy of users, roles, and grants outside Snowflake -- in a version controlled environment. Then you can use tools like Terraform, SatoriCyber, or Permifrost to quickly create and recreate your desired environment.

See https://gitlab.com/gitlab-data/permifrost/.

user_1324380
Related