This page outlines the process of configuring and using the Snowflake API through Zilla to sync groups, permissions, and users.
Configure a snowflake account with RSA Key pair ( Public & Private )
...
| Include Page | ||||
|---|---|---|---|---|
|
1. Create Role
Login to your snowflake Snowflake account and switch role to
ACCOUNTADMIN.
...
Click on
Accounttab.
Go to
Userstab,
...
Click on Create user, In
Generalsection give name & password details, Then clickNext
...
In
AdvancedSection 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
Generalsection enter User Name and Password details.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, 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. Click
Next.In next
PreferencesselectACCOUNTADMINas default role, Click finish.
...
Note down the name
Zillayou can choose any different name but make a note of it, It will be used later.Before proceeding login with the above user to see if the user has been created & has the default role as ACCOUNTADMIN
Now let us Configuring Key Pair Authentication for this user “Zilla”
Step 1: section, select the role created in “1. Create Role” section above as Default Role and click
Finish.
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
...
:
| Code Block |
|---|
// Execute this query to enable admin previlige,privilege SOso that we can modify user Zilla USE ROLE ACCOUNTADMIN; |
Then execute Execute the following query substituting user created in “2. Create User” section above for
ZILLAREADERUSERand the public key, see Note below:
| Code Block |
|---|
// Make change to user Zilla to associate RSApublic key ALTER USER ZillaZILLAREADERUSER 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 we assigned is without the beginning & and end part 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
Applicationstab click on `Add Application
...
Type
Snowflakein the search box, A suggestedSnowflakelibrary entry shall appear clickAdd Applicationsbutton right next to it,
...
Fill in the
Add Applicationform in details & clickAdd to Applications
...
The
Snowflakeapplication entry shall appear in theApplicationstab click on the app name,
...
Next on the App details page to the right side, you will see
Update nowbutton click it, You will see configuration box appeared,
...
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 “2. Create User” section.
| 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 USER_ID,NAME,CREATED_ON,DELETED_ON,LOGIN_NAME,DISPLAY_NAME,FIRST_NAME,LAST_NAME,EMAIL,MUST_CHANGE_PASSWORD,HAS_PASSWORD,COMMENT,DISABLED,SNOWFLAKE_LOCK,DEFAULT_WAREHOUSE,DEFAULT_NAMESPACE,DEFAULT_ROLE,EXT_AUTHN_DUO,EXT_AUTHN_UID,BYPASS_MFA_UNTIL,LAST_SUCCESS_LOGIN,EXPIRES_AT,LOCKED_UNTIL_TIME,HAS_RSA_PUBLIC_KEY,PASSWORD_LAST_SET_TIME,OWNER,DEFAULT_SECONDARY_ROLE from SNOWFLAKE.ACCOUNT_USAGE.USERS;
create view ZILLA_SNOWFLAKE_USAGE.ACCOUNT_USAGE.GRANTS_TO_USERS
as select CREATED_ON,DELETED_ON,"ROLE",GRANTED_TO,GRANTEE_NAME,GRANTED_BY 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; |
...
Set up Snowflake Application Integration on Zilla:
Visit the Zilla application and login using your admin credentials and then click
Add Applicationin the top right.
...
A window with a search bar appears, type in
snowflakein the search bar. Snowflake application entry will appear at the top of the list, clickAdd to Applicationsbutton to the right.
...
Fill in the form with appropriate details and then click
Add to Applicationsbutton.
...
A detailed view of Snowflake application appears. Click
Sync nowor thegear iconin top right corner to configure your application.
...
Enable API Integration option from the dialog.
...
Add the configuration details, check the information below this screenshot.
Here fill in the details as follows,
RSA Private Key: The PVT RSA key generated in section “3. Create Key Pair Authentication for the User” 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 shall be provided here, . If you have not created an encrypted key using
-nocryptflag, leave this blank, ( In the above step we have not created an encrypted key by providing flag-nocrypt).Snowflake Account Id: If your snowflake URL is
https://unaXXXXX.us-east-1.snowflakecomputing.com/thenunaXXXXX.us-east-1is your account id.Account Admin Username: In our case, it’s
ZillaZILLAREADERUSER. If you have chosen any a custom name above, provide that nameWarehouse name ( Where SNOWFLAKE db & ACCOUNT_USAGE schema is located ).Warehouse name: Name of the warehouse which has the SNOWFLAKE database & ACCOUNT_USAGE schema. Use
show warehouses;command to see available warehoused warehouses in your account,.
After Click
Next/Sync Nowafter filling in the above details click onNextYou will see 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
...
config details
...
Click
Nexton the below screen
...
Click
Done.
...
Check sync summary and click
Close.
...
| Include Page | ||||
|---|---|---|---|---|
|