Build an app accessing Snowflake with end-user roles

This guide walks you through building an application that accesses Snowflake data based on the end-user’s role, using OAuth in Windmill. By leveraging dynamic role-based credentials from Snowflake’s OAuth integration, we avoid static credentials and enable secure data access customized for each user. This can be particularly useful for organizations with strict data access policies and multiple user roles where row access policies are set up.

The tutorial includes steps to set up Snowflake OAuth, configure user roles, and create UI components in Windmill for a seamless, role-specific data experience.

Note that the mechanism of using the end-user's role demonstated here with Snowflake can be used for any OAuth-supported resource in Windmill such as GitHub, Slack, or Google Workspace.

Resources and resource types

Resources are structured configurations and connections to third-party systems, with Resource types defining the schema for each Resource.

Snowflake OAuth

Setup a Snowflake OAuth connection

---

Video tutorial

For a visual walkthrough of building this app, watch the tutorial below:

• 00:00 Create a New Snowflake OAuth User Resource
• 00:37 Background Runnable to query Available Tables
• 02:04 Display Table Content
• 02:47 Test the App

Prerequisites

1. Set up Snowflake OAuth: Follow the Snowflake OAuth guide.
2. Configure User Roles in Snowflake: In the Snowflake console, ensure that user roles connected through OAuth have access to the relevant tables.

Sample app setup

For this example, we created a new Snowflake organization with a WINDMILL database, a PUBLIC schema, and two user roles:

• hr_user with the role PRIVILEGED
• support_user with the role RESTRICTED

The database contains the following tables:

• SALARIES – accessible only to the PRIVILEGED role
• LIMITED_SALARIES – accessible to both roles

The goal is to use OAuth to dynamically retrieve the credentials for the end-user connecting to the app rather than using static credentials.

Step 1: Create a new Snowflake OAuth user resource

1. After creating a new app in your Windmill workspace, add a User Resource Input component.

2. For "Resource Type," enter snowflake_oauth.

3. Enable Express OAuth Setup by toggling the option.

4. In the UI editor, click the plus icon (+) to authenticate with your Snowflake account and test the connection.

This component allows the app to use end-user credentials via an interactive OAuth connection rather than relying on static resources defined in the workspace.

Step 2: Background runnable to query available tables

Next, create a Background Runnable to retrieve the available tables based on the user’s Snowflake role.

1. Create a new Background Runnable of type "Snowflake."

2. Enter a Snowflake query to list available tables:

   SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC';

3. Connect the Background Runnable to the User Resource Input component from Step 1 by clicking the connect icon and selecting the result field of the User Resource Input.

4. Enable the toggle resource from users allowed to grant access to user-linked resources. Note that this resource is passed as a reference and won’t be accessible to the app publisher.

   

5. Click the Run button to test the query and view the results.

6. Create a Select component and connect it to the Background Runnable output to populate the dropdown menu. Map the output to label and value fields as follows:

   bg_0.result.map(_ => ({ value: _.TABLE_NAME, label: _.TABLE_NAME }))

Step 3: Display table content

Now, add a Rich result component to show the table content based on the selected table.

1. Create a Rich result component.

2. In the "Data Source" setting, Select "Create Inline Script" and select "Snowflake". In the code editor enter your snowflake query such as:

   -- ? table_name (varchar) = default arg
   select * from TABLE(?)

3. Connect the Rich result’s "database" field to the User Resource Input component from Step 1.

4. Connect the Rich result’s "table_name" field to the Select component created in Step 2.

5. Enable the toggle resource from users allowed to grant access to user-linked resources. Note that this resource is passed as a reference and won’t be accessible to the app publisher.

   

6. The component will automatically populate with data from the selected table.

Step 4: Test the app

Now, we’ll see how the displayed data changes based on the logged-in user’s role.

1. Click the Preview button to switch to the end-user preview mode.

2. Use the plus icon (+) to log in as a privileged user (e.g., hr_user) and view the content of the SALARIES table.

   

3. Log out by clicking the logout button next to the plus icon, then log in as the restricted user (e.g., support_user). You should now see only the LIMITED_SALARIES table.