| Include Page | ||||
|---|---|---|---|---|
|
Configure a snowflake account with RSA Key pair ( Public & Private )
...
1. Create Role
Login to your Snowflake account and switch role to
ACCOUNTADMIN.
...
Click on
Accounttab.
Navigate
Go to
UsersRoles tab and click on Create icon.
...
Click on
Create...to create a user. In theGeneralsection provide Create a Role by entering a Name and selecting Parent Role, then clickFinish.
2. Create User
Go to Users tab and click on Create icon.
In
Generalsection enter User Name and Password details, then clickNext.
...
.
ZILLAREADERUSERis an example, you can choose any name you want. Note down the User Name for later steps. ClickNext.
In
Advancedsection fill in the details specific to your organization, these are examples only for Zilla. ClickNext.
...
, see Note below.
Note: Login
...
Name should be the
...
User Name provided
...
in the
Generalsection
...
, Email should be an email address from your organization,
zillareaderuser@example.comis just an example. ClickNext.In
Preferencessection, selectACCOUNTADMINthe role created in step 1 above as Default Role and clickFinish.
...
Before proceeding, login with the above user to verify the user has been created and has the default role ACCOUNTADMIN.
3. Create Key Pair Authentication for the
...
User
Step 1: Generate the Private Key
...
$ 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
...
Step 3: Store the Private and Public Keys Securely: These are Store these sensitive keys , store securely so that no one can access them.
Step 4: Assign the Public Key to the new created Snowflake user Snowflake User (e.g.,
ZILLAREADERUSER) created in step 2 above with the following SQL query:
| Code Block |
|---|
// Execute this query to enable admin privilege, so that we can modify user USE ROLE ACCOUNTADMIN; |
Then execute Execute the following query replacing {User Name} with the user name created abovesubstituting user created in step 2 above for
ZILLAREADERUSERand the public key, see Note below:
| Code Block |
|---|
// Make change to user to associate RSApublic key ALTER USER {UserZILLAREADERUSER Name} SET RSA_PUBLIC_KEY='MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKC........; |
Note: To print the contents of the public key type $ cat rsa_key.pub on the terminal. Note: The public key assigned is without the beginning and end prefix and post-fixpart of it, i.e., -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY----- respectively.
Now we have all the artifacts we need to configure the Snowflake integration.
Configure A Snowflake integration on Zilla Dashboard
Login to Zilla with your admin account.
Go to Applications tab and click
Add Application.
...
Type “Snowflake” in the search box. Click
Add to Applicationsbutton to the right of theSnowflakeentry.
...
Fill in the form details and click
Add to Applications.
...
The
Snowflakeapplication entry shall appear in theApplicationstab (if not visible, search for it). Click the app name link.
...
Click
Sync Nowin the upper right of the application details page. You will see the configuration dialog. Click the slider below API Configuration to enable it.
...
4. Give the Warehouse Grant Privileges to Role (ZILLAREADERROLE)
Navigate to the
Warehousestab 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
Worksheetstab. 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. SubstituteZILLAREADERUSERwith the User Name created in step 2 above.Code Block use role ACCOUNTADMIN; use warehouse ZILLA; create database ZILLA_SNOWFLAKE_USAGE; create schema ZILLA_SNOWFLAKE_USAGE.ACCOUNT_USAGE; create view ZILLA_SNOWFLAKE_USAGE.ACCOUNT_USAGE.USERS as select * from SNOWFLAKE.ACCOUNT_USAGE.USERS; create view ZILLA_SNOWFLAKE_USAGE.ACCOUNT_USAGE.GRANTS_TO_USERS as select * from SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS; grant usage on warehouse ZILLA to role ZILLAREADERROLE; grant usage on database ZILLA_SNOWFLAKE_USAGE to role ZILLAREADERROLE; grant usage on schema ZILLA_SNOWFLAKE_USAGE.ACCOUNT_USAGE to role ZILLAREADERROLE; grant select on view ZILLA_SNOWFLAKE_USAGE.ACCOUNT_USAGE.USERS to role ZILLAREADERROLE; grant select on view ZILLA_SNOWFLAKE_USAGE.ACCOUNT_USAGE.GRANTS_TO_USERS to role ZILLAREADERROLE; grant role ZILLAREADERROLE to user ZILLAREADERUSER;
6. Set up Snowflake Application Integration on Zilla:
Navigate to your Snowflake instance in your library. Click Sync now in the top right corner.This pop-up should appear, click on the slider below API Integration to enable it.
Here fill in the details as follows,
RSA Private Key: The PVT RSA key generated in the step 3 above step, . You can print the key on the terminal using
$cat rsa_key.p8command.
...
Note that
...
key
...
shall be copied
...
with
-----BEGIN PRIVATE KEY-----and-----END PRIVATE KEY
...
----
...
-
...
otherwise app
...
will throw an error. Paste the key in the input box.
RSA Private Key Passphrase: If you created an encrypted RSA PVT key then a passphrase to decrypt the key must shall be provided here. If you have not created an encrypted key using
-nocryptflag, leave this blank.Snowflake Account Id: If your Snowflake snowflake URL is
https://unaXXXXX.us-east-1.snowflakecomputing.com/thenunaXXXXX.us-east-1is your account id.Account Admin Username: The User Name created above, in our example it’s
ZillaIn our case, it’sZILLAREADERUSER. If you have chosen a custom name above, provide that name.Account User Role: In our case, it’s
ZILLAREADERROLE. If you have chosen any custom role name above, provide that name.Warehouse name ( Where SNOWFLAKE db & ACCOUNT_USAGE schema is located ): Name of the warehouse which has the SNOWFLAKE database & ACCOUNT_USAGE schema. Use
show warehouses;command to see available warehouses in your account.
After filling in the above details click on
Next.You will see a configuration stored success toast as well as a small dialog saying
In the next step, you will be asked to log in to Snowflake - 1, and then the data sync will start automatically.ClickNext.
...
.
| Include Page | ||||
|---|---|---|---|---|
|