Prerequisites
Important
Snowflake needs to be added as an Identity Security Cloud source.
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.
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.
- The generated private key is required as an input on the Connection Settings page.
To generate the private and public keys, complete the following:
-
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.
-
Use the following command to generate an encrypted version of the public key:
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
-
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. The file should still be protected from unauthorized access using the file permission mechanism provided by your operating system, however. It is your responsibility to secure the file when it is not being used.
Required Permissions
Complete the following to set up a Snowflake administrative account with the minimum required permissions for the listed operation:
-
Login to snowflake with ACCOUNTADMIN role and execute the following command:
CREATE USER "UserName";
-
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-----
-
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';
-
Use the following command to verify the user's public key fingerprint:
DESCRIBE USER "UserName";
-
Use the following command to create a role:
CREATE ROLE "Rolename";
-
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. Use the following command to assign the SYSADMIN role:
GRANT ROLE "Rolename" TO ROLE SYSADMIN;
-
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";
-
Create a new warehouse if no existing warehouse is present.
CREATE WAREHOUSE WAREHOUSENAME ;
-
Use the following command to set default_warehouse to a user:
ALTER USER username SET DEFAULT_WAREHOUSE=WAREHOUSENAME
-
Use the following command to provide warehouse usage on the role:
GRANT USAGE ON WAREHOUSE WAREHOUSENAME TO ROLE "RoleName";
-
Ensure the below is already set.
ALTER USER username SET default_role="RoleName"
-
Use the following commands to grant a database role to a role:
GRANT DATABASE ROLE SNOWFLAKE.OBJECT_VIEWER TO ROLE "Rolename";
GRANT DATABASE ROLE SNOWFLAKE.SECURITY_VIEWER TO ROLE "Rolename";
-
Use the following commands to have permission privileges:
GRANT USAGE ON DATABASE "DatabaseName" TO ROLE "Rolename";
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE "DatabaseName" TO ROLE "Rolename";
GRANT USAGE ON ALL SCHEMAS IN DATABASE "DatabaseName" TO ROLE "Rolename";
GRANT REFERENCES ON FUTURE TABLES IN DATABASE "DatabaseName" TO ROLE "Rolename";
GRANT REFERENCES ON ALL TABLES IN DATABASE "DatabaseName" TO ROLE "Rolename";
GRANT REFERENCES ON FUTURE VIEWS IN DATABASE "DatabaseName" TO ROLE "Rolename";
GRANT REFERENCES ON ALL VIEWS IN DATABASE "DatabaseName" TO ROLE "Rolename";
GRANT USAGE ON FUTURE FUNCTIONS IN DATABASE "DatabaseName" TO ROLE "Rolename";
GRANT USAGE ON ALL FUNCTIONS IN DATABASE "DatabaseName" TO ROLE "Rolename";
GRANT USAGE ON FUTURE PROCEDURES IN DATABASE "DatabaseName" TO ROLE "Rolename";
GRANT USAGE ON ALL PROCEDURES IN DATABASE "DatabaseName" TO ROLE "Rolename";
GRANT REFERENCES ON ALL MATERIALIZED VIEWS IN DATABASE "DatabaseName" TO ROLE "RoleName";
GRANT REFERENCES ON FUTURE MATERIALIZED VIEWS IN DATABASE "DatabaseName" TO ROLE "RoleName";
Important
The whole script needs to be repeated per database.
-
Use the following commands to have data classification privileges:
GRANT DATABASE ROLE SNOWFLAKE.GOVERNANCE_VIEWER TO ROLE "Rolename";
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.