Mixture of managed and non-managed users

[kokorin]

We configured SCIM and SSO for our SF account. So users are created automatically by a call from Azure AD (when a user is added to AD group).
We followed this guide for SCIM and created dedicated role for it:

create or replace security integration aad_provisioning
    type = scim
    scim_client = 'azure'
    run_as_role = 'AAD_PROVISIONER';

We also create service users (for CI) which are not in AD Group using SnowDDL.

The problem is that we can't manage users (i.e. can't grant business roles) created by AAD_PROVISIONER using SnowDDL.

I see the following possible options:

1. Run AAD provisioner as SNOWDDL_ADMIN role, but it gives more permissions then necessary to aad_provisioning
2. Split plan/apply into 2 steps: create users using AAD_PROVISIONER role, then create everything else using SNOWDDL_ADMIN role. Not too complex, but can turn into debugging headaches.
3. Grant AAD_PROVISIONER to SNOWDDL_ADMIN, but I'm not sure if it will work

Could you please advice on what option to use? Could you suggest any other solution?

[littleK0i]

As far as I know, SCIM integration in Snowflake has significant limitations. Especially around flexibility of queries used to CREATE and ALTER users. Setting custom user parameters might be very difficult, probably not possible.

With this in mind, we normally suggest the following options in order of priority:

1) Use programmatic config instead of SCIM
Build a tiny python script running SnowDDL. In this script load user data via API and build UserBlueprint(s) for SnowDDL. When user is added / updated / deleted, trigger this script. Make sure only one instance of script runs in parallel.

With this approach you get 100% flexibility and control over all aspects of user management. You may also create additional objects on "per-user" basis, like personal warehouses or schemas.

2) Let SnowDDL create business roles, but let SCIM integration to manage users
Define business roles and run SnowDDL at least once. Create SCIM integration with role having MANAGE GRANTS privilege. After that SCIM should be able to create users and assign SnowDDL business roles directly to them. You may add one more layer of roles if SCIM requires it: SnowDDL business role -> SCIM managed role -> SCIM managed user.

SnowDDL ignores users which are owned by anything except SnowDDL admin role. Direct business role grants will not be revoked.

[kokorin]

1. I will ask our AD admins if it's possible to do from security perspective
2. Configuring SCIM to manage business roles can be possible, but it would mean that we have to define roles first.
   I will discuss with our team, thank you!

[littleK0i]

If you have an exceptionally busy environment with users being created or dropped all the time, you may also extend the first approach.

Build UserBlueprint(s) dynamically during full SnowDDL runs. But instead of triggering full SnowDDL run every time, you may implement a tiny custom integration running the same commands on per-user basis.

It is essentially what Snowflake does, but there are two important benefits:

1. You fully control commands and may add any custom logic or additional parameters at any time.
2. You may cross-validate, repair or even drop & re-create an entire environment.

As far as I know, standard SCIM integration cannot do any of these. If somebody dropped a user accidentally, Snowflake SCIM cannot do full sync to recover it.

[kokorin]

We found compromise solution:

1. SCIM creates users [email protected]
2. SnowDDL creates similar users with disabled: true (so no one can log in): name_surname
3. We manually grant NAME_SURNAME__U_ROLE user role to SCIM-managed user [email protected]

[kokorin]

Interesting, let me check

[kokorin]

We ended up with programmatic config to manage AAD users and their roles. Programmatic config registers 2 resolvers very similar to UserResolver and UserRoleResolver.
User resolver:

1. throws exception if AAD user is absent
2. returns SKIP for any delete_object call
3. allows any symbols in AAD user name
4. replaces forbidden symbols with _ in user role names
5. uses custom user role name suffix

[littleK0i]

Does Azure enforce some lower-cased names? This would be terrible.

If so, maybe you could set this parameter to TRUE?
https://docs.snowflake.com/en/sql-reference/parameters#quoted-identifiers-ignore-case

Names would be upper-cased transperently.

[kokorin]

No, the problem was that AAD created users with uppercase emails as user names like [email protected].
Besides that we decided not to grant user-roles manually, instead programmatic config parses extra YAML file with a list of AAD users with default warehouse and business roles only.

[littleK0i]

Hmm, these names are quite problematic, since you'll have to use double-quotes when referring such names at all times. Also, some reporting tools and custom SQL clients might break. It is non-issue for Snowflake Web interface.

It might be worth exploring options to connect connect to AAD, get list of users and generate entire blueprints with more-readable names. Helps to sleep peacefully at night.