Before we drill down to the “why” and “how” of limiting client tool access in Snowflake using Satori, here’s a short demo video of how this works in reality:
Here’s what we’ll discuss in this blogpost -
- Preventing Specific Tool Access Using Satori
- Partial Lockdown (Limited Only to Specific Data)
- Let’s Configure These Limitations in Satori
- Alternative Filtering Using Snowflake Secure Views
It may seem like as long as a user is authenticated and authorized to a specific data within a given data source, the user should have access to the data. However, from a broader and more complete perspective, you may wish to place additional limitations on data access.
One of these limitations, which we will discuss today, is based on the tool, or client, which the user is utilizing to access the data. Put simply, in many cases the tool by which a user accesses data is critical. After granting a consumer access, it makes a large difference whether a consumer uses a script or a BI tool to access the data.
In some cases, this distinction may be important for operational reasons, such as the concern that users who pull data programmatically may create logic that unnecessarily processes a large amount of data. In other cases, we may desire this limitation due to security or governance reasons. For example, a limitation can be used to enforce security policies that prevent human analysts from using application credentials.
Yet another complication arises because, sometimes, you want to apply such filtering only on certain parts of your Snowflake account, but not others. In other words, you may want to limit access based on which client application is used to access the data only on a certain part of your data.
Though these requirements may sound complex, all of the aforementioned limitations can be achieved by applying filtering on client tools using Satori. With that, let’s see how it is done.
Using Satori to Prevent Data Access Based on Tools
The limitations I mentioned are all built into Satori’s security policy engine and are easy to achieve.
As a recap, let’s examine how Satori controls access to your data. In the following diagram, we can see that the user is implementing a client tool to connect to Snowflake using Satori. The user can be a human or an application, and the client tool can be any client such as a BI tool (e.g. Looker, Tableau, Power BI, etc), DB client, or an application (e.g. Python, Go, NodeJS, etc). By defining a security policy on Satori, access can be granted or blocked based on the specific client tool used.
Now, let’s see some of the use-cases for this capability, as exemplified by some of our customers, and examine how to configure these definitions in Satori.
Use-Case 1: Using Snowflake Web UI as a Data Portal
In this case, a company wants to allow certain consumers to access their data. However, to limit risks, such as users iterating through the data using a script or downloading the data, companies want to allow access to their data only through a web UI. The goal is to limit such users, so they will not be able to access the Snowflake account using scripts or automated tools, but only through the web UI. The reasoning behind this limitation can vary from allowing the company to conform to a security policy to reducing operational risks.
This function is performed by employing Satori to block queries sent using any tool other than Snowflake Web UI, as per the following diagram. In many cases, this function is also achieved by using the IdP to block access based on specific identity groups:
Use-Case 2: Locking Down Users to BI Tools
Another use-case that I have observed, which is quite similar to the previous one, is when companies that have analysts or other data consumers who are meant to use Snowflake only through a certain BI tool, and the company does not wish the employees to connect through other client tools for various reasons. In some cases, this limitation is executed because the company is using the BI tool’s limitations to restrict their users to certain dashboards or capabilities.
The solution, in this case, is using Satori to lock down all SQL queries and commands sent using any other tool, except for the BI tool (in this case, Tableau):
Use-Case 3: Locking Down Applications to a Script
Another use-case I have encountered occurs when some of the data or some of the users in an organization should only be accessed by a certain application. For example, data that is written to a certain database should only be written by a Python or NodeJS connector as a precaution to prevent human users from writing or updating data in that location. This is done by configuring Satori to accept the allowed client tools while disallowing the others, in much the same way as the previous use cases.
In the following example, an application is enabled to connect to the Snowflake data cloud-only with its NodeJS connector but not through any other application:
Partial Lockdown (Limited Only to Specific Data)
In several cases, limitations on use to a specific client tool are only set for certain datasets. Such datasets can be configured in Satori and may contain one or more tables, schemas, or databases from the same Snowflake account, as well as from other Snowflake accounts, or even a combination of Snowflake data objects and other data objects.
In this way, a certain dataset can be configured to allow or disallow specific tools, regardless of the data infrastructure.
Let’s Configure These Limitations in Satori
In Satori, you can configure the limitations I described by using custom policies, which you can apply to specific datasets. The datasets you define can be as specific or as generic as necessary. Below is an example of the configuration in Satori which allows the user benherzberg to only access the dataset from Snowflake’s native tools (Snowflake UI & Snowsight). You may customize this policy to include identity groups or Satori data users directory groups instead of a single user:
Once you save this custom policy, it is automatically enforced in Satori:
For example, if we run a query in Snowflake, we will get the data as requested:
However, if we send the query through any other tool, we will not be authorized to retrieve the data:
Alternative Filtering Using Snowflake Secure Views
For very specific purposes, in the case where you have certain tables to which you want to control access based on a tool, you can also achieve this goal by creating a secure view filter in Snowflake. Note that clients detected in Satori are more granular. For example, in many cases, they would be a specific tool using JDBC instead of classifying as “JDBC.” Views are also only relevant to reading data, not to data modification.
For the most part, this method is not a good fit for the use-cases described above, as it is more difficult to maintain and needs to be incorporated into views for each table. Nevertheless, it may be useful in certain cases, so I thought it would be helpful to include a short description of it. This capability is achieved by using the CURRENT_CLIENT() context function.
The CURRENT_CLIENT() function returns the client that Snowflake detects. In the following example, we will limit access to the v_research_data view only to Snowflake UI:
CREATE SECURE VIEW v_research_data AS
Limiting access by client tools is only a small fraction of the power Satori brings to data engineers and owners when protecting data access and ensuring data democratization. If you would like to learn more, contact us to set up a demo: