Replicating SAML2, OIDC, OAuth and SCIM Integrations and Validating Users, Roles and Grants

1.Letting security integrations into the group

Snowflake can replicate SAML2 and OIDC (federated SSO), OAuth and SCIM security integrations, across regions and across cloud platforms. Adding INTEGRATIONS to OBJECT_TYPES does not replicate them on its own. A second setting, ALLOWED_INTEGRATION_TYPES, chooses which kinds of integration are included, and security integrations are included only if they are listed there. The configuration guide's example group lists ALLOWED_INTEGRATION_TYPES = API INTEGRATIONS. A group configured like that would replicate API integrations but leave your SSO and provisioning integrations behind.

CREATE FAILOVER GROUP FG OBJECT_TYPES = users, roles, warehouses, resource monitors, integrations ALLOWED_INTEGRATION_TYPES = security integrations ALLOWED_ACCOUNTS = example.northamericaeast REPLICATION_SCHEDULE = '10 MINUTE';

Sources1

2.SAML2: point the IdP at the connection URL

For each type of integration, the documented test follows the same four steps. First identify the source account, the target account and the connection URL. Then complete the steps in the source account, then the steps in the target account, and finally test failover and failback.

For SAML2, the important point is the connection URL. In the source account, set SAML2_SNOWFLAKE_ISSUER_URL and SAML2_SNOWFLAKE_ACS_URL to the connection URL rather than to the account URL, and configure the IdP application with that same URL. Users must also exist in the source account. If either of these updates is missing, Snowflake cannot verify users and they cannot sign in to the target account.

There is one limitation. Replicating a SAML2 integration that specifies the connection URL works only on the current primary connection. After a failover, SAML SSO works on the new primary connection. If you need SSO on both the primary and the secondary connection at the same time, create and manage a SAML2 integration separately in each account.

ALTER CONNECTION global PRIMARY;

Sources1

3.OIDC, SCIM and OAuth after failover

The other integration types each have their own details.

OIDC needs particular care. The integration's properties replicate with it, including OIDC_CLIENT_SECRET, which is encrypted at rest. For a custom provider, though, the redirect URIs registered with the IdP are tied to the account where the integration was created. If you fail over through Client Redirect, you must also register the secondary account's redirect URI with the IdP yourself. SAML2 avoids this problem because it advertises both the primary and secondary ACS URLs through SAML2_SNOWFLAKE_OTHER_ACS_URLS. OIDC has no equivalent setting yet.

Sources1

4.Validating users, roles and grants

Validation starts before the first refresh. Run SHOW USERS and SHOW INTEGRATIONS in the target account to get a baseline. After the refresh, the counts should rise by the number of new users and integrations. Run DESCRIBE INTEGRATION on each replicated integration to confirm its settings. For network policies, check that SHOW NETWORK POLICIES lists them and that DESCRIBE SECURITY INTEGRATION shows the replicated policy on the integration. If users or roles already exist in the target account but not in the source, read Initial replication of users and roles before you create the secondary group.

Role replication includes the privileges granted to roles and the role hierarchy. If users are replicated as well, the grants of roles to users replicate too. The SNOWFLAKE database itself is never listed in a group. Even so, when roles are replicated, the grants of its database roles and application roles to account roles, and IMPORTED PRIVILEGES on it, replicate as well.

Because of this, an audit of the target account has to check SNOWFLAKE database grants directly. A clean source account does not mean the target is clean. Also note that a refresh fails if the user who calls it in the target account has been dropped in the source account. Do not run refreshes as a personal user who might leave the organization.

Sources123

5.Checking refresh status

Comparing object counts tells you what arrived. The INFORMATION_SCHEMA table function REPLICATION_GROUP_REFRESH_HISTORY tells you how each refresh went: it reports a phase_name, start_time and job_uuid for every refresh of the group. A finished refresh shows the phase COMPLETED and a cancelled one shows CANCELED. A refresh in any other phase is still running. You cannot promote a group while a refresh is in progress, so this query is also the check to run before a failover.

SELECT phase_name, start_time, job_uuid FROM TABLE(INFORMATION_SCHEMA.REPLICATION_GROUP_REFRESH_HISTORY('myfg')) WHERE phase_name <> 'COMPLETED' and phase_name <> 'CANCELED';

Sources4

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Adding INTEGRATIONS to OBJECT_TYPES is enough to replicate SAML2, OAuth and SCIM integrations.Why is that wrong?

   Security integrations replicate only if ALLOWED_INTEGRATION_TYPES also lists security integrations.

   Covered in Letting security integrations into the group

2. 2.A replicated SAML2 integration that uses the connection URL gives SSO on the primary and secondary connections at the same time.Why is that wrong?

   It works only on the current primary connection. To have SSO on both, manage a separate SAML2 integration in each account.

   Covered in SAML2: point the IdP at the connection URL

3. 3.Revoking a SNOWFLAKE database role grant in the source account removes it from the target account at the next refresh.Why is that wrong?

   These grants replicate additively, so a revocation does not propagate and you have to revoke it in the target account as well.

   Covered in Validating users, roles and grants

Practise it for real

Set up a failover group with least-privilege roles, refresh it and verify the result

1. 1.In the source account, as ACCOUNTADMIN, run: USE ROLE ACCOUNTADMIN; CREATE ROLE myrole; GRANT CREATE FAILOVER GROUP ON ACCOUNT TO ROLE myrole;

   Why: From this point, group creation is done by a narrow role instead of ACCOUNTADMIN

   You should see: The statements succeed and myrole holds CREATE FAILOVER GROUP

2. 2.In the target account, run SHOW USERS and SHOW INTEGRATIONS and write down the counts

   Why: These counts are the baseline you will compare against after the refresh

   You should see: The current number of users and integrations in the target account

3. 3.As myrole in the source account, create a failover group with OBJECT_TYPES that include users, roles and integrations, ALLOWED_INTEGRATION_TYPES = security integrations, and ALLOWED_ACCOUNTS set to the target account only

   Why: These settings control which object types leave the source and which account may receive them

   You should see: SHOW FAILOVER GROUPS lists the new primary group

4. 4.As the role that owns the group, run GRANT REPLICATE ON FAILOVER GROUP myfg TO ROLE my_replication_role; in both the source and target accounts

   Why: REPLICATE is not replicated, so it must be granted in each account

   You should see: my_replication_role holds REPLICATE on the group in both accounts

5. 5.In the target account, create the replica with CREATE FAILOVER GROUP ... AS REPLICA OF, then run USE ROLE my_replication_role; ALTER FAILOVER GROUP myfg REFRESH;

   Why: Brings the target account up to date with the source

   You should see: The refresh completes without error

6. 6.Run SHOW USERS, SHOW INTEGRATIONS and DESCRIBE INTEGRATION in the target account, then run the REPLICATION_GROUP_REFRESH_HISTORY query

   Why: Confirms the objects arrived and that no refresh is still running

   You should see: Higher counts than the baseline and no rows from the history query