Snowflake
1. Create Role
Login to your Snowflake account and switch role to
ACCOUNTADMIN
.
Click on
Account
tab.Go to Roles tab and click on Create icon.
Create a Role by entering a Name and selecting Parent Role, then click
Finish
.
2. Create User
Go to Users tab and click on Create icon.
In
General
section enter User Name and Password details.ZILLAREADERUSER
is an example, you can choose any name you want. Note down the User Name for later steps. ClickNext
.In
Advanced
section fill in the details, see Note below.
Note: Login Name should be the User Name provided in theGeneral
section, Email should be an email address from your organization,zillareaderuser@example.com
is just an example. ClickNext
.In
Preferences
section, select the role created in “1. Create Role” section above as Default Role and clickFinish
.
3. Create Key Pair Authentication for the User
a. Generate the Private Key
To generate an unencrypted version, use the following command:
$ openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
To generate an encrypted version, use the following command (which omits “-nocrypt”):
$ openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8
b. Generate a Public Key
$ openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
c. Store the Private and Public Keys Securely: Store these sensitive keys securely so that no one can access them.
d. Assign the Public Key to the Snowflake User (e.g., ZILLAREADERUSER
) created in “2. Create User” section above, with the following SQL query:
// Execute this query to enable admin privilege so that we can modify user
USE ROLE ACCOUNTADMIN;
Execute the following query substituting user created in “2. Create User” section above for
ZILLAREADERUSER
and the public key, see Note below:
// Make change to user to associate RSApublic key
ALTER USER ZILLAREADERUSER SET RSA_PUBLIC_KEY='MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKC........;
Note: To print the contents of the public key type $ cat rsa_key.pub
on the terminal. The public key assigned is without the beginning and end part of it, i.e., -----BEGIN PUBLIC KEY-----
and -----END PUBLIC KEY-----
respectively.
4. Give the Warehouse Grant Privileges to Role (ZILLAREADERROLE
)
Navigate to the
Warehouses
tab and Select your Warehouse. Click on Grant Privileges.
Enter the Privileges to grant as USAGE and Grant privileges to role (
ZILLAREADERROLE
). ClickGrant
.
5. Give the DATABASE Grant Privileges to Role (ZILLAREADERROLE
)
Navigate to the
Worksheets
tab. Write the following query on a worksheet. To summarize, this SQL statement creates a new database that contains views that pull from the primary Snowflake Database. The role and user are only granted access to these views in the new database. SubstituteZILLAREADERUSER
with the User Name created in