Setup snowflake RSA security to bring your own snowflake account

What is RSA Security?

RSA, an acronym for Rivest-Shamir-Adleman, is a public-key cryptosystem used for secure data transmission and digital signatures. It relies on a pair of keys: a public key for encryption and a private key for decryption, making it an asymmetric encryption method.

How to Generate an RSA Key Pair for Snowflake Authentication:

To generate Snowflake-Compatible RSA Public/Private Key Pair, first have to install OpenSSL.

What is OpenSSL?

OpenSSL is an open-source software library that secures data transmission and storage. It's used to encrypt data, generate digital certificates, and more.

Install OpenSSL:

Linux: OpenSSL is typically pre-installed on Linux systems.
Windows: You can download OpenSSL from the link here. Download the full version compatible with your windows version.

Setup the OpenSSL setup bin path to system environment variable, which will be like "C:\Program Files\OpenSSL-Win64\bin" (may vary as per the installation setting)

Once OpenSSL is installed, you can test the installation using the below command in the command prompt (Windows) or terminal (Linux):

openssl -version

Generate keys:

Linux Instructions

Create Private Key

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out snowflake_key.p8 -nocrypt

Output file: snowflake_key.p8

Extract Public Key

openssl rsa -in snowflake_key.p8 -pubout -out snowflake_key.pub

Output file: snowflake_key.pub

Windows Instructions

Create Private Key

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out snowflake_key.p8 -nocrypt

Output file: snowflake_key.p8

Extract Public Key

openssl rsa -in snowflake_key.p8 -pubout -out snowflake_key.pub

Output file: snowflake_key.pub

Note: Files will be generated at the location in system where you run these commands.

Snowflake Setup for Public Key-Based Access

The snowflake account should have ACCOUNTADMIN or SECURITYADMIN privileges.

User configuration steps:

Create Service User

CREATE USER "SERVICE_USER_NAME" TYPE = SERVICE DISABLED = FALSE COMMENT = 'Service account for automated processes';

Assign Public Key
```
ALTER USER SERVICE_USER_NAME SET RSA_PUBLIC_KEY = 'MIIBIjANBgkqh...AQAB';
```
Key Formatting Requirements:
- Remove -----BEGIN PUBLIC KEY----- header
- Remove -----END PUBLIC KEY----- footer
- Remove newline characters (single line)
- Must start with MII
Verify Key Assignment
Snowflake Verification:
```
DESC USER SERVICE_USER_NAME;
```
Note the RSA_PUBLIC_KEY_FP value.

Local Verification with OpenSSL:
```
openssl rsa -pubin -in snowflake_key.pub -outform DER | openssl dgst -sha256 -binary | openssl enc -base64
```
Above, both outputs must match.

Privileges:
Grant this service user the same privileges as were required for username+password authenticated users.

Create a role:

CREATE ROLE {role_name};

Grant all privileges for the Warehouse to the above role:

GRANT ALL PRIVILAGE ON WAREHOUSE {warehouse_name} TO ROLE {role_name};

Grant Usage access to the database inside the above warehouse to the role:

GRANT USAGE ON DATABASE {database_name} TO ROLE {role_name};

Grant usage access to the schema for design, stage and live under the above database to the role:

GRANT USAGE ON SCHEMA {database_name}.{schema_name}_DESIGN TO ROLE {role_name};
GRANT USAGE ON SCHEMA {database_name}.{schema_name}_STAGE TO ROLE {role_name};
GRANT USAGE ON SCHEMA {database_name}.{schema_name} TO ROLE {role_name};

Grant select on all future tables and views of the above all schemas to the role:

GRANT SELECT ON FUTURE TABLE IN SCHEMA {database_name}.{schema_name}_DESIGN TO ROLE {role_name};
GRANT SELECT ON FUTURE VIEW IN SCHEMA {database_name}.{schema_name}_DESIGN TO ROLE {role_name};

GRANT SELECT ON FUTURE TABLE IN SCHEMA {database_name}.{schema_name}_STAGE TO ROLE {role_name};
GRANT SELECT ON FUTURE VIEW IN SCHEMA {database_name}.{schema_name}_STAGE TO ROLE {role_name};

GRANT SELECT ON FUTURE TABLE IN SCHEMA {database_name}.{schema_name} TO ROLE {role_name};
GRANT SELECT ON FUTURE VIEW IN SCHEMA {database_name}.{schema_name} TO ROLE {role_name};

Finally, assign the role to the service user:

GRANT ROLE {role_name} TO USER {SERVICE_USER_NAME};

Workspace Configuration

With a Standard or Enterprise License, the user adds a user license key to Activate license and set up the workspace:

You will be asked to input the ‘Account name’ which is a mandatory field, followed by ‘Account description’ and ‘Account logo’ which are non-mandatory fields.

Tip: Consider giving your account the same name as your organisation or company. This makes it easily distinguishable among other accounts for Verodat users, enhancing clarity and recognition.

You will proceed to the ‘Workspace Details’. You will be asked to input the 'Workspace name’ which is a mandatory field, followed by ‘Workspace description’, 'Workspace table prefix', and ‘Workspace logo’ which are non-mandatory fields.

Workspace table prefix: This is an optional prefix that will be applied to the tables that are generated in your chosen target database. i.e. if you enter "tbl" then a dataset named "Products" in Verodat will appear as "tbl_products" in the target database

After filling in the mandatory field ‘Workspace name’, the ‘Save and continue’ button will be activated. If you do not wish to complete the other fields you can always visit the Workspace Settings and add them later on.

Note: Standard and Enterprise licenses do not provide blob storage and Snowflake to the user. In the process of setting up an account with a standard/enterprise licence users need to provide their blob storage and snowflake details.

Verodat allows you to connect to your own storage account for blob and database storage if you are a Starndard or Enterprise customer while you are creating an account and workspace:

Blob storage is a type of cloud storage for unstructured data. A "blob," which is short for Binary Large Object, is a mass of data in binary form that does not necessarily conform to any file format. Blob storage keeps these masses of data in non-hierarchical storage areas called data lakes. You can use Azure and AWS blob storage.

Next, you can select “Connection Database” as Snowflake or SQL Database:

On Click of Snowflake, you need to provide:

URL: you can get snowflake url inside the response as host using the below SQL statement:
use role acountadmin;
select system$allowlist();
Username: The service user which we created in Snowflake above "SERVICE_USER_NAME"
RSA key: The private key generated above and stored in "snowflake_key.p8" file

Once it is provided you need to select the "Warehouse" and "Database":

Select Schema for Design, Staging and Live: