### Context Currently creating Snowflake account is manual work. We expect more demand now we are moving away from Sisense (which is used by quite some users as a query interface towards Snowflake) ### Resources/links 1. [An example issue](https://gitlab.com/gitlab-data/analytics/-/issues/19347) / [MR](https://gitlab.com/gitlab-data/analytics/-/merge_requests/9257/diffs) with the manual steps 1. Medium automate onboarding, [article](https://medium.com/super/automate-user-onboarding-and-permission-grants-to-snowflake-using-okta-and-terraform-a1991e6b5ed9) ### Overview of Current steps - The steps are listed in the [AR request template](https://gitlab.com/gitlab-data/analytics/-/blob/master/.gitlab/issue_templates/Platform:%20Snowflake%20Permissions%20Update%20Provisioning.md) - Run sql script, [user_provision.sql](https://gitlab.com/gitlab-data/analytics/-/blob/master/permissions/snowflake/user_provision.sql?ref_type=heads) - Create user using SECURITYADMIN role - Create user specific role - Assign user specific role - Add gcs group (possibly consider adding to snowflake group by default) - update [roles.yaml](https://gitlab.com/gitlab-data/analytics/-/blob/master/permissions/snowflake/roles.yml?ref_type=heads) file - update db section - update roles section - update user section - Priority - Currently, just need to do a simple one - Priority is c -> a -> b ### high level goals Main things to be accomplished: - create new user/role/user_db - assign the appropriate perms to created users ### Possible ways to create new user/role/user_db 1. Terraform, [Snowflake docs quickstart](https://quickstarts.snowflake.com/guide/terraforming_snowflake/index.html#8) - pros: users/roles can be IaC - cons: it requires quite a bit of effort to migrate existing Snowflake state to terraform. 3. [Okta SCIM](https://docs.snowflake.com/en/user-guide/scim-okta) - pro: auto-creates snowflake role - would require every user in Okta to also have Snowflake access 5. Run existing .sql code via the following 1. Snowflake UI - The sql script could be updated to allow for multiple users 3. CI job 1. run new emails based on passed in args 2. run new emails based on .yml file (any new changes to file) ### Possible ways to assign appropriate perms 1. Terraform 2. Permifrost ### Issues List 1. update_roles.py - python script that updates roles.yml, [issue](https://gitlab.com/gitlab-data/analytics/-/issues/19517) 2. user_provision.sql - update existing sql script too account for multiple users, [issue](https://gitlab.com/gitlab-data/analytics/-/issues/19518) 3. user_provision - CI job that runs the updates sql script, [issue](https://gitlab.com/gitlab-data/analytics/-/issues/19519) 4. automate google okta group user, [issue](https://gitlab.com/gitlab-data/analytics/-/issues/19520)