Blogs / Snowflake / Automating user access provisioning in Snowflake

Automating user access provisioning in Snowflake

May 24, 2021

SHARE

In the previous blog, we looked at restricting user’s access to a specific set of rows. The crux of row-level security is adding entries to the control table, and this also adds up the administrative burden to the DBA. This blog will focus on approaches for automating row-level security for the users by eliminating DBA from the access provisioning process.

Business Scenario 

Consider the same data set that was used in the row-level security blog. Assume that a new regional manager has been appointed and would like to access data for his region. A typical process for access request would be:

Automating user access provisioning in Snowflake
Figure 1: Process for user access provisioning

This way of access provisioning not only takes time but is also error-prone. 

Automation Approaches 

As it is evident from the above process flow, every request involves multiple stakeholders. The automation process must provide an interface for users to log access requests, validate the details entered, manages the approval process, and a mechanism to add entries to the authorization table and alters the secure view by running the procedure.  

There are two approaches that you can take: 

  1. Power Apps + Power Automate + Azure Function 
  2. Express.js App + Okta + Node.js Connector 

Let us take a closer look into these approaches. 

Power Apps + Power Automate + Azure Function 

In this approach, a typical process for access request would be: 

Automating user access provisioning in Snowflake
Figure 2: Process for user access provisioning using Power Apps, Power Automate and Azure Function

Power Apps – It’s a low code custom app builder that simplifies the custom business app building experience by enabling users to build feature-rich apps. An application is built using Power Apps, where the end-user raises an access request. The app takes care of the validation of data that does not require DB check. 

Power Automate – The Power Automate is used for each button click made in the app. It manages the entire flow end to end and replaces the DB administrator. It takes care of getting the request approved, calling the Azure Function to add entries to the control tables, notifying the user when access is provisioned/rejected/in case of errors.  

Azure Function – Azure Function is used to add entries to the table and execute the procedure to recreate the secure view. It’s also used to validate requests made by the user. The function has an HTTP trigger and is written in python. Uses the native Snowflake connector to connect with Snowflake. Azure Function can be replaced with AWS Lambda Function or Google Cloud Function if you have subscriptions to the respective cloud providers. 

The below gif elucidates the app in action. 

Automating user access provisioning in Snowflake

Express.js App + Okta + Node.js Connector 

In this approach, a typical process for access request would be: 

Automating user access provisioning in Snowflake
Figure 3: Process for user access provisioning using Express.js, Okta and Node.js

Express.js App – Express.js is a popular web framework. It’s incredibly simple and minimalistic. An application is built using Express.js, where the end-user raises an access request. The logic is written in JavaScript for getting the request approved, calling the database scripts to add entries to the control tables, notifying the user when access is provisioned/rejected/in case of errors. 

Okta or other Identity providers – Okta is an identity provider that helps you secure your web application. Okta will be able to provide SSO into the web application. 

Node.js Connector – This is the native connector provided by Snowflake that seamlessly connects Node.js application to Snowflake. It is used to connect to the Snowflake DB, to validates the entries in the request, make DB changes, and run the procedure. 

Below is a quick comparison that will be handy when you have to choose between the approaches 

 Power Automate Node.js Connector 
Recommendation The approach is best suited when you have Microsoft 365 licenses and a cloud provider subscription.  This approach is best suited when you want your application to be platform-independent and easily portable. 
Effort and skill Power Automate and Power Apps are low code applications. It’s easier to develop an application and does not require coding skills. However the Azure Function requires Python. You will need web designers, node.js, and express.js experts to design and build your application. 
Security Your application is secured using Azure AD credentials. Hence no additional effort is required to secure the application access 3rd party identity providers like Okta/ complex scripting has to be done to ensure authorized access. 
Deployment You can publish the app and share a link to anyone in the organization almost instantly Requires dedicated servers to run the Express.js application 
Customization Even though Power Apps provide the most-used components, it’s still limited to the components available in the application and bound to the limitations of the application You can develop the application the way you want it, with no restrictions whatsoever. 

Hope you would have got an idea of how you could automate user access provisioning. As we saw in both approaches, automating user access will take away DB Administrator from the process flow altogether.  It would take some time to build the application you need but will indeed turn worthy and provide a better ROI.  

We have also designed the app to view/edit/revoke access, which could help the DB administrator manage all from a single application. Interested in seeing a demo? Contact us here .

To learn more about Snowflake, check out these blogs. Reach out to us here today if you are interested in evaluating if Snowflake is suitable for you. 

Learn more about Visual BI’s Snowflake offerings here


Corporate HQ:
5920 Windhaven Pkwy, Plano, TX 75093

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com


Copyright © Visual BI Solutions Inc.

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

You have Successfully Subscribed!

Share This!

Share this with your friends and colleagues!