# Build an app accessing Snowflake with end-user roles

> How do I build a Windmill app that accesses Snowflake data with end-user OAuth roles?

:::info Legacy

This guide uses the legacy low-code app editor. For new apps, we recommend [full-code apps](../../../full_code_apps/index.mdx) with React or Svelte.

:::

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](https://docs.snowflake.com/en/user-guide/security-row-intro) 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](../../../advanced/27_setup_oauth/index.mdx#oauth) such as GitHub, Slack, or Google Workspace.

---

## Video tutorial

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

- 00:00 [Create a New Snowflake OAuth User Resource](https://www.youtube.com/watch?v=9r17_ABP4Xk&t=0s)
- 00:37 [Background Runnable to query Available Tables](https://www.youtube.com/watch?v=9r17_ABP4Xk&t=21s)
- 02:04 [Display Table Content](https://www.youtube.com/watch?v=9r17_ABP4Xk&t=83s)
- 02:47 [Test the App](https://www.youtube.com/watch?v=9r17_ABP4Xk&t=147s)

## Prerequisites

1. **Set up Snowflake OAuth**: Follow the [Snowflake OAuth guide](../../../advanced/27_setup_oauth/index.mdx#snowflake).
2. **Configure User Roles in Snowflake**: In the [Snowflake console](https://app.snowflake.com/), 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.
   ![Settings for User Resource Input](./user_resource_input.png)

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."
   ![Settings for Background Runnable](./background_runnable_1.png)

2. Enter a Snowflake query to list available tables:

   ```sql
   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.

   ![Settings for Background Runnable](./background_runnable_2.png)

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:

   ```js
   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:
   ```sql
   -- ? table_name (varchar) = default arg
   select * from TABLE(?)
   ```
2. Connect the **Rich result**’s "database" field to the **User Resource Input** component from Step 1.
3. Connect the **Rich result**’s "table_name" field to the **Select** component created in Step 2.
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.

   ![Settings for Rich result](./rich_result.png)

4. 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.

   ![View as HR user](./hr_view.png)

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.

   ![View as Support user](./support_view.png)