Snowflake Security Integration in Practice: SCIM

Wheat field alone Bruce Trail close to Mono Cliffs, ON, May 21, 2023

SCIM stands for System for Cross-domain Identity Management. It’s a protocol to manage user and role lifecycle via REST APIs with JSON payload.

Snowflake supports SCIM by hosting SCIM server in account when we create a SCIM security integration. Remote applications can then call SCIM APIs to create/modify/delete users and roles in Snowflake.

User/roles managed by SCIM API calls will be owned by a specific role who runs the SCIM security integration.

In following code, as accountadmin, we first create a dedicated SCIM integration role “generic_scim_role”, grant “create user/role” privileges to this role and then create a generic SCIM integration.

use role accountadmin;
create role if not exists generic_scim_role;
grant create user on account to role generic_scim_role;
grant create role on account to role generic_scim_role;
grant role generic_scim_role to role accountadmin;

create or replace security integration generic_scim_integration
    type=scim
    scim_client='generic'
    run_as_role='GENERIC_SCIM_ROLE';

-- use new created role to create a user 'scim_user' for next testings
use role generic_scim_role;
create or replace user scim_user;

SCIM REST API endpoint is like “https://<account URL>/scim/v2/”. Get your account URL from Snowsight UI -> Admin -> Accounts. In “Account” column at right accounts list mouse over the link icon and copy it.

For authentication, SCIM API asks for an access token which needs to be passed as bearer token in REST request header. Following command is to retrieve the SCIM access token of given SCIM integration.

select system$generate_scim_access_token('GENERIC_SCIM_INTEGRATION');
-- ver:2-hint:416041873413-did:1043-ETMsDgAAAYhQH...ZGluZwEAABAAEAOXt8i...

So now we can use a curl command to find an user “scim_user” owned by SCIM integration role in Snowflake like following:

 C:\Users\fengli>curl -H "Authorization: Bearer ver:2-hint:416041...
...7eCwM=" "https://zdwumob-yqb43232.snowflakecomputing.com/scim/v2/Users?filter=userName%20eq%20%22scim_user%22"
{
  "schemas" : [ "urn:ietf:params:scim:api:messages:2.0:ListResponse" ],
  "Resources" : [ {
    "schemas" : [ "urn:ietf:params:scim:schemas:core:2.0:User", "urn:ietf:params:scim:schemas:extension:2.0:User", "urn:ietf:params:scim:schemas:extension:enterprise:2.0:User" ],
    "id" : "9_1",
    "meta" : {
      "resourceType" : "User",
      "created" : "2023-05-24T23:33:55Z",
      "lastModified" : "2023-05-24T23:33:55Z"
    },
    "userName" : "SCIM_USER",
    "displayName" : "SCIM_USER",
    "active" : true,
    "groups" : [ ]
  } ],
  "totalResults" : 1,
  "startIndex" : 1,
  "itemsPerPage" : 1
}

Looks great! Then we can try to create a user using SCIM API following docs here …

C:\Users\fengli> curl --location -g "https://zdwumob-yqb43232.snowflakecomputing.com/scim/v2/Users" \
--header "Authorization: Bearer ver:2-hint:416041873413-did:10...bCZ7eCwM="  \
--header "Content-Type: application/scim+json" \
--header "Accept-Encoding: utf-8" \
--header "Accept-Charset: utf-8" \
--data-raw "{ \"schemas\": [ \"urn:ietf:params:scim:schemas:core:2.0:User\", \"urn:ietf:params:scim:schemas:extension:enterprise:2.0:User\" ], \"userName\": \"test_name\",  \"password\": \"test_password\",  \"name\": {\"givenName\": \"test_first_name\", \"familyName\": \"test_last_name\" }, \"emails\": [ {\"value\": \"test.user@snowflake.com\" } ], \"displayName\": \"test user\", \"active\": true }"

{
  "schemas" : [ "urn:ietf:params:scim:schemas:core:2.0:User", "urn:ietf:params:scim:schemas:extension:2.0:User", "urn:ietf:params:scim:schemas:extension:enterprise:2.0:User" ],
  "id" : "1043_1625163560_3",
  "meta" : {
    "resourceType" : "User",
    "created" : "2023-05-25T19:47:12Z",
    "lastModified" : "2023-05-25T19:47:12Z"
  },
  "userName" : "test_name",
  "displayName" : "test user",
  "name" : {
    "givenName" : "test_first_name",
    "familyName" : "test_last_name"
  },
  "emails" : [ {
    "primary" : true,
    "value" : "test.user@snowflake.com"
  } ],
  "active" : true,
  "groups" : [ ]
}

Now use “filter” to look for this new created user.

C:\Users\fengli>curl --location -g "https://zdwumob-yqb43232.snowflakecomputing.com/scim/v2/Users" 
--header "Authorization: Bearer ver:2-hint:416041873413-did:10...7eCwM=" 
--header "Content-Type: application/scim+json" 
--header "Accept-Encoding: utf-8" 
--header "Accept-Charset: utf-8"  
"https://zdwumob-yqb43232.snowflakecomputing.com/scim/v2/Users?filter=userName%20eq%20%22test_name%22"


{
  "schemas" : [ "urn:ietf:params:scim:api:messages:2.0:ListResponse" ],
  "Resources" : [ {
    "schemas" : [ "urn:ietf:params:scim:schemas:core:2.0:User", "urn:ietf:params:scim:schemas:extension:2.0:User", "urn:ietf:params:scim:schemas:extension:enterprise:2.0:User" ],
    "id" : "1043_1625163560_3",
    "meta" : {
      "resourceType" : "User",
      "created" : "2023-05-25T19:47:12Z",
      "lastModified" : "2023-05-25T19:47:12Z"
    },
    "userName" : "test_name",
    "displayName" : "test user",
    "name" : {
      "givenName" : "test_first_name",
      "familyName" : "test_last_name"
    },
    "emails" : [ {
      "primary" : true,
      "value" : "test.user@snowflake.com"
    } ],
    "active" : true,
    "groups" : [ ]
  } ],
  "totalResults" : 1,
  "startIndex" : 1,
  "itemsPerPage" : 1
}

Note, this “id” item in output is called user “id” in SCIM. This is a internal tag for an user which is used a lot in SCIM APIs. This is how you find user_id from a user name.

This user can be verified from Snowsight UI.

Now, let’s delete user scim_user using user id in SCIM API. User id is ‘9_1’ based on above output.

C:\Users\fengli> curl --location -g --request DELETE 
"https://zdwumob-yqb43232.snowflakecomputing.com/scim/v2/Users/9_1" 
--header "Authorization: Bearer ver:2-hint:416041...eCwM=" 
--header "Content-Type: application/scim+json" 
--header "Accept-Encoding: utf-8" 
--header "Accept-Charset: utf-8"

Verify from Snowsight UI seeing this user is gone.

It’s easy to get following format errors using curl command especially in different terminals. In my case with Git CMD, I’ll need to use double quotes for all parameters and back slash for each double quote in the data section.

<h1>Error 400 Bad Request</h1>
<p class="ErrorBody">Our apologies for the inconvenience. 
The requested you made couldn't be completed

curl: (3) URL using bad/illegal format or missing URL
curl: (6) Could not resolve host: Bearer
curl: (6) Could not resolve host: true

There are also “group” SCIM APIs … As Snowflake doesn’t use group-based membership, the “group” here is essentially “roles”. Groups from IdPs (like Okta, Azure AD) get pushed to Snowlfake via SCIM as roles.

So in addition to use curl commands managing Snowflake user/role, we can leverage existing tools like Okta, Azure. Once SCIM integration (scim_client = okta instead of generic) is created and token is retrieved, we can go to Okta to create SCIM application through which users/roles can be pushed to Snowflake (Snowflake doc for details). For those users keep in mind to manage them always via the tools. Modify them on Snowflake side will not be synced back to Okta for example resulting inconsistency.

Happy Reading!

Join Medium with my referral link - Feng Li