Snowflake

Overview

Snowflake is a cloud-based data warehousing platform that enables organizations to store, manage, and analyze vast amounts of data with high scalability, concurrency, and performance, making it easier to derive valuable insights and support data-driven decision-making.

Entitle can manage the following resource types in Snowflake:

• Roles
• Databases
• Schemas
• Tables
• Warehouses

Role types

There are two different role types supported by Entitle when managing access to resources:

• Snowflake Verbs (default) - users can request a ‘Snowflake Verb’ as a ‘role’ on Entitle.
  • For instance - ‘usage’ on a warehouse, ‘select’ on a table, etc.
• Simplified Roles - Can be turned on via a flag in the integration configuration. If simplified roles are enabled, users will be able to choose between ‘read’, ‘write’, and ‘admin’ privileges to the selected resource on Entitle, and all the ‘Snowflake verbs’ related to the chosen role will be granted.

User generation

Entitle supports user provisioning in Snowflake. This feature can be activated by checking the Allow creating accounts checkbox on the integration setup page. When turned on, if an employee with no Snowflake account requests access to a Snowflake resource using Entitle, a new and temporary account with the requested permissions will be created, and sent to the employee.

General guidelines

To integrate your Snowflake in Entitle, you will need to create a service account and grant Entitle's service account with a Security Admin role, generate a set of public and private keys in Snowflake.

Prerequisites

• An accountadmin account in Snowflake.

Snowflake - setup for Entitle's access

Stage 1: Creating a Snowflake user for Entitle

To generate this user:

• Go to Worksheets.
• Click on '+ Worksheet' in the top right corner.
• Choose a name for the user Entitle is going to use to access Snowflake.
• In the worksheet, run the following command:

CREATE USER <ENTITLE_USERNAME>

• Execute the following command in the worksheet to grant the user with a securityadmin access:

  GRANT ROLE SECURITYADMIN TO <ENTITLE_USERNAME>

Stage 2: Generating a public and private key pair

1. To connect Entitle with the generated user, you will need to generate a private-public key pair and configure the public one in Snowflake and the private one in Entitle.
2. To proceed with the manual, you need to have a Linux/MacOS machine with OpenSSL installed
   You can either download and install it from the official source:
   https://www.openssl.org/source/, or using your favorite package manager (brew/apt/yum etc…)
3. Execute the following script:

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

sed  -e '2,$!d' -e '$d' rsa_key.pub > snowflake_key.pub
echo "" >> rsa_key.p8
sed  -e ':a' -e 'N' -e '$!ba' -e 's/\n/\\n/g' rsa_key.p8 > entitle_key.p8

rm rsa_key.pub
rm rsa_key.p8

echo "snowflake user key (can be found in ./snowflake_key.pub):\n\n"
cat snowflake_key.pub

echo "\n\nentitle configuration key (can be found in ./entitle_key.p8):\n\n"
cat entitle_key.p8

4. The output of the script will include two keys. The snowflake key will be a multi-line key, printed first, and the Entitle key will be a ‘single line’ (might be wrapped by shell), and will begin with ‘——BEGIN PRIVATE KEY——’.
5. Script output example:

6. From now on we’ll refer to the entitle key as <entitle_config_key> , and to the snowflake key as <snowflake_key>.
7. Next, configure the <snowflake_key> in Snowflake, go back to the worksheet, and run the following command:

alter user **<entitle_access_user_name>** set rsa_public_key='**<snowflake_key>**

📘

Note

The double asterisks wrapping the user are required for the setup process.

8. The <entitle_config_key> will be used in the last step when configuring the integration on Entitle.

Stage 3: Grant warehouse permissions to ‘securityadmin’ role

1. Next, you’ll need to grant the user access to a warehouse, on which all the queries run by Entitle will be executed. You can see a list of all warehouses by going from the main menu to ‘Admin → Warehouses’.
2. Choose a warehouse, from now on we’ll refer to its name as <entitle_warehouse_name>.
3. Click on the warehouse you want to grant access to.
4. Scroll down to the ‘Privileges’ section and click ‘+ Privilege’ on the top right part of the section.
5. Choose the SECURITYADMIN role, and the ‘USAGE’ privilege, and click ‘Grant Privileges.

Stage 4: Retrieving the Account ID

1. The last piece of information we need to configure the snowflake integration on Entitle is the <snowflake_account_id>, it can be retrieved via one of the following ways:
2. Option one - through the admin panel:
   1. Open your console.
   2. On the left side panel click Admin → Accounts → Under ‘Account’ column Hover on your desired account and then on the 📎 icon.
   3. Your account will appear in https://<snowflake_account_id>**.snowflakecomputing.com.

3. Option two - through the ‘classic console’:
   1. On the top right corner, click on the user-name section.
   2. Click on the ‘copy’ button to the right of ‘Account Information’.
   3. The output will be of the structure of XXXXXXX.YYYYYYY.
   4. replace the dot “.” with a hyphen “-” and get a value structured XXXXXXX-YYYYYYY that is your <snowflake_account_id>.

Creating the integration in Entitle

All that is left to do is create an integration on the Entitle application.

1. Log into Entitle and navigate to the Integrations page.

2. After clicking the Add Integration button, type Snowflake in the Application field.

3. Don’t forget to set Save on correctly on your agent or Entitle’s cloud.

4. Example Connection JSON:

{
    "user": "<ENTITLE_USERNAME>",
    "account": "<SNOWFLAKE_ACCOUNT_ID>",
		"role" : "SECURITYADMIN",
	  "warehouse": "<ENTITLE_WAREHOUSE_NAME>",
    "private_key": "<CONFIGURATION_PRIVATE_KEY>",
		"options": {
			"simplify_roles": true/false
		}
}

📘

Note

Field values are retrieved in the different stages of this manual.

The ‘simplify_roles’ option allows changing the available roles from the ‘standard’ snowflake actions (SELECT, DELETE, USAGE, etc…) to a simplified ‘read-write-admin’ model.

9. Click Save.
   The Entitle integration with Snowflake saves