Integrating SailPoint with Snowflake

The SailPoint Snowflake integration is capable of end-to-end user administration with the provisioning and password management capabilities of Snowflake, helping provide rich governance and control experience to your enterprise deployment.

This document is designed to give specific information about the requirements and field definitions needed to get a working instance of a Snowflake source.

Supported Features

The SailPoint Snowflake connector supports the following features.

Account Management

• Manages Snowflake Users
• Aggregate and Refresh Accounts
• Create and Update Accounts
• Enable, Disable, Unlock, and Change Password
• Add or Remove Entitlements as Roles and Database Roles

Entitlement Management

• Manage Snowflake Entitlements as Roles and Database Roles
• Refresh and Aggregate Entitlements

For more information on features, refer to Source Features.

Prerequisites

• Configure at least one virtual appliance cluster and successfully test the connection. For more information, refer to the Virtual Appliance Reference Guide.

• To connect to Snowflake, you must have a public and private key or you must generate them. The keys are generated in the PEM format. For more information, refer to Generating Keys.

  Note

  • SailPoint only supports the encrypted private key for connecting to Snowflake to ensure maximum security.
  • By default, Windows systems do not support OpenSSL. You may need to download and install the OpenSSL libraries to generate your keys. Apple systems support OpenSSL by default. Go to OpenSSL to download OpenSSL for Windows.

Generating Keys

To connect to Snowflake, you must have a public and private key or you must generate them. The keys are generated in the PEM format.

To generate the private and public keys, complete the following:

1. On your system, open a command prompt, terminal, or emulator and use the following command to generate an encrypted version of the private key: openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8

   The command to generate an encrypted key prompts you to enter a passphrase to regulate access to the key. SailPoint recommends storing the passphrase in a secure location.

2. Use the following command to generate an encrypted version of the public key: openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

3. Copy the public and private key files to a local directory for storage. Record the path to the files.

   Note

   The private key is stored using the PKCS#8 (Public Key Cryptography Standards) format and is encrypted using the passphrase you specified in the first step. However, the file should still be protected from unauthorized access using the file permission mechanism provided by your operating system. It is your responsibility to secure the file when it is not being used.

Required Permissions

Configure the permissions required to grant necessary access rights to resources or data within the system to perform connector operations.

Administrative Account Permissions

Complete the following to set up a Snowflake administrative account with the minimum required permissions for the listed operation:

1. Log in to Snowflake with the ACCOUNTADMIN role and execute the following command:

   CREATE USER "UserName" type = SERVICE;
   

   Note

   SailPoint recommends setting the user to the Service type.

2. Generate the public key. The public key is in the PEM format. For more information on generating the public key, refer to Generating Keys. The following is an example of the public key in PEM format:

   -----BEGIN PUBLIC KEY-----
   MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAy+Fw2qv4Roud3l6tjPH4
   zxybHjmZ5rhtCz9jppCV8UTWvEXxa88IGRIHbJ/PwKW/mR8LXdfI7l/9vCMXX4mk
   ...
   -----END PUBLIC KEY-----
   

3. In the following command, replace PublicKey with the key you generated (do not include the BEGIN PUBLIC KEY and END PUBLIC KEY lines):

   ALTER USER "UserName" SET RSA_PUBLIC_KEY='PublicKey';
   

   For example:

   ALTER USER MYUSER SET RSA_PUBLIC_KEY='MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA1hhZwJvU4+MiD92bLLmf
   zvdieU6TvuaSrjKJGtEndSWRR3p2pMFIzDWbbX1PHPqtt43C+meMtKtwMVl8JWEk
   IawC7ZnfjHROufWVhpb+8DwhHuH/r7GWXCNCyjJTH/Z+htdIYFM/pbSKW1Qdt5X0
   Bf5TGINAe9XxL2Zp5kqo8pYMiPGudgUdYQlMGZ6y1AH0Rcb76KUkoHNrJQA/xRI8
   LSDMNJQSJo6rPGARD1Rn9ns0Z3M1qnoH6LOOX0GX3T4GU+ERwPaMVcMjkweSA3a1
   sqLhq+9hpC8piW+LaEv2clj1Sp73m70qh/0l8Cb2O4sq7Iov8G8Iahe0LGLVQX3+
   uQIDAQAB';
   

4. Use the following command to grant the ACCOUNTADMIN role to a user:

   GRANT ROLE ACCOUNTADMIN TO USER "UserName";
   

5. Use the following command to verify the user's public key fingerprint:

   DESCRIBE USER "UserName";
   

SYSADMIN Role

Snowflake recommends creating a hierarchy of custom roles, with the top-most custom role assigned to the system role SYSADMIN. For more information, refer to the Snowflake documentation.

1. Use the following command to create a role:

   CREATE ROLE "Rolename";
   

2. Use the following command to assign the SYSADMIN role to the new role:

   GRANT ROLE "Rolename" TO ROLE SYSADMIN;
   

Role Permissions

Use the following command to grant a role to a user:

GRANT ROLE "Rolename" TO USER "UserName";

Use the following command to set a user's default role:

ALTER USER "UserName" SET DEFAULT_ROLE = "Rolename";

Test Connection Permissions

By default, when you create a user, it is assigned a bare minimum role (PUBLIC). Only a bare minimum role is required for to test your connection.

Account Aggregation Permissions

This command can only be executed by users with a role that has the MANAGE GRANTS global privilege.

GRANT MANAGE GRANTS ON ACCOUNT TO ROLE "Rolename";

Create a new warehouse if one doesn't already exist.

CREATE WAREHOUSE "WAREHOUSE_NAME" ;

Use the following command to set default_warehouse to a user.

ALTER USER "UserName" SET DEFAULT_WAREHOUSE = "WAREHOUSE_NAME";

Use the following command to provide warehouse usage on the role.

GRANT USAGE ON WAREHOUSE "WAREHOUSE_NAME" TO ROLE "RoleName";

Group Aggregation Permissions

By default, when you create a user, it is assigned a bare minimum role (PUBLIC). Only a bare minimum role is required to perform a group aggregation.

Create Account Permissions

The CREATE command can only be executed by users with a role that has the CREATE USER global privilege.

GRANT CREATE USER ON ACCOUNT TO ROLE "Rolename";

Modify, Enable, Disable, Unlock, or Change Password Permissions

Note the following with the OWNERSHIP privilege:

• The OWNERSHIP privilege grants the ability to delete, alter, rename, and grant or revoke access to an object.
• OWNERSHIP is a special privilege on an object that is automatically granted to the role that created the object, but can also be transferred to a different role by the owning role (or any role with the MANAGE GRANTS privilege) using the GRANT OWNERSHIP command.
• The OWNERSHIP privilege grants full control over an object. Only a single role can hold this privilege on a specific object at a time. For more information on the GRANT OWNERSHIP command, refer to the Snowflake documentation.

Only the role with the OWNERSHIP privilege on the user, or a higher role, can execute this command to modify most user properties.

To grant ownership permissions, use the following command:

GRANT OWNERSHIP ON USER "UserName" TO ROLE "Rolename";

Where UserName refers to the name of the user that you are modifying.

Documentation Feedback

Feedback is provided as an informational resource only and does not form part of SailPoint’s official product documentation. SailPoint does not warrant or make any guarantees about the feedback (including without limitation as to its accuracy, relevance, or reliability). All feedback is subject to the terms set forth at https://developer.sailpoint.com/discuss/tos.