Menu Close Get Metabase

Advanced data sandboxing: limiting access to columns

Nov 18, 2020 by The Metabase Team

In a previous article, we covered the basics of sandboxing with Metabase Enterprise Edition. We defined sandboxing as a way to specify what data people can access based on who they are, and showed you how to limit access to table rows. As an example, we created a user, Ms. Brown, and gave her access to rows in the People and Orders tables that matched her user_id attribute.

In this article, we’ll walk through how to sandbox the Products table to limit the rows and columns Ms. Brown can view. In this case, we want Ms. Brown to:

  • Only see products she’s placed an order for in the Products table.
  • Only see the Title, Category, and Price columns.

The plan

We’re going to:

  1. Create a collection that only admins can access.

  2. Create a new SQL query. The Products table doesn’t include information about users. So to limit Ms. Brown’s access to the Products table, we’ll need to find out which products Ms. Brown ordered. We’ll write a SQL query that combines data from the Products table with data from the Orders table. In combining these tables, we’ll create a new tabular result that only includes the columns we want.

  3. Sandbox the Product table by displaying our query’s results instead of the original table for Ms. Brown.

  4. Confirm our sandbox by verifying what data Ms. Brown can see.

Create a collection accessible only by admins

We’ll want to create a collection to store the SQL query that we’ll use to sandbox this table. Let’s call it Sandbox Questions, and set permissions on this collection so that only admins can view its questions. This way non-admins won’t be able to alter the question and change the “dimensions” of the sandbox, for example by including columns Ms. Brown shouldn’t be able to see.

See Collection permissions to learn more about setting up permissions.

Create a SQL query

From the top blue nav bar, on the right, select the code icon to Write SQL. Select the Sample Dataset included with Metabase.

<em>Fig. 1.</em> Click on the code icon to <strong>Write SQL</strong>, and select the <strong> Sample Dataset</strong> included with Metabase.
Fig. 1. Click on the code icon to Write SQL, and select the Sample Dataset included with Metabase.

Here’s the query we’ll paste into the editor:

SELECT DISTINCT PRODUCTS.TITLE,
                PRODUCTS.CATEGORY,
                PRODUCTS.PRICE
FROM PRODUCTS
LEFT JOIN ORDERS ON ORDERS.PRODUCT_ID=PRODUCTS.ID
[[WHERE ORDERS.USER_ID IN ({{sandbox}})]]

And here’s what the query does:

  • Returns a result with columns from the Products table: Title,Category, and Price.
  • Checks that the products are distinct, i.e., only one row for each product.
  • Optionally filters this list to only show products that were ordered by the sandboxed user.

The double square brackets, [[ ]], around the WHERE clause make the clause optional. The double curly braces define the variable, {{sandbox}}. We’ll use this {{sandbox}} variable when sandboxing this question.

Let’s run the query, which gives us this result:

<em>Fig. 2.</em> SQL query to create new table.
Fig. 2. SQL query to create new table.

Now let’s save this question as “Products from Orders”, store it in the Sandbox Questions collection we created, and opt out of adding the question to a dashboard.

One point to reiterate here: we only selected columns from the Products table, as our query should only return columns from the table we want to sandbox.

Sandboxing the Products table using our saved question

Now that we’ve created our “Products from Orders” question, it’s time to sandbox the Products table. We’ll set up the sandbox so that Metabase inserts the user_id attribute we gave Ms. Brown in the previous walkthrough into the {{sandbox}} variable in our saved SQL question, “Products from Orders”.

We’ll click on the gears icon, select Admin, and click on the Permissions tab. On the left, under Sample Dataset, we’ll click on View tables. Since Ms. Brown is a member of the Customers group, and Metabase grants data permissions to groups, not users, we’ll grant Customers sandboxed access to the Products table.

<em>Fig 3.</em> Granting the <strong>Customer</strong> group sandboxed access to the <strong>Products</strong> table.
Fig 3. Granting the Customer group sandboxed access to the Products table.

When Metabase pops up the sandboxing modal, in the How do you want to filter this table for uses in this group? section, we’ll select the second filter option: “Use a saved question to create a custom view for this table.” We’ll navigate to our admin-only Sandbox Questions collection and select our question, “Products from Orders”.

For PARAMETER OR VARIABLE, we’ll select the variable we included in our SQL query, {{sandbox}}. For the USER ATTRIBUTE, we’ll select user_id.

<em>Fig. 4.</em> <strong>Sandbox modal</strong> summarizes the effects of our selection.
Fig. 4. Sandbox modal summarizes the effects of our selection.

Our summary now says:

  • Users in Customers
  • Can view rows in the Products from Orders question
  • Where sandbox variable equals user_id

Let’s click Save in the modal, then confirm our changes by clicking on Save Changes in the purple notice bar.

Check settings as the sandboxed user

Let’s see what our sandboxed Products table looks like from Ms. Brown’s perspective.

We’ll open a private browser window, navigate to our Metabase instance, and sign in as Ms. Brown.

When we open up the Products table using the data browser, we’ll confirm that Ms. Brown can only see a list of the products she’s ordered, and only the three columns we included in our saved “Products from Orders” question: Title, Category, and Price.

<em>Fig. 5.</em> The <strong>Products</strong> table from the Metabase home page only showing products that Ms. Brown has ordered.
Fig. 5. The Products table from the Metabase home page only showing products that Ms. Brown has ordered.

If Ms. Brown asks a question that queries the Products table, she’ll still only see results based on the products she’s ordered. If she has access to a question that includes columns outside of her sandbox, she’ll see an error.

Prefer a SQL question when sandboxing a table

While we can use a custom question to sandbox a table, we recommend using SQL questions for sandboxing.

Behind the scenes, custom questions create SQL queries based off the filters, summaries, and joins in our custom question. When we sandbox based on a custom question, we may not realize the full extent of the information we’re giving people access to.

Alternatively, we can use the notebook editor to create a custom question, and then take a look under the hood to see the SQL code Metabase will run. In the notebook editor, we’ll click on the View the SQL button in the upper right corner (figure 6) and confirm that Metabase is including the correct tables and columns - and nothing else.

<em>Fig. 6.</em> You can view the SQL generated by the query builder by clicking on the code icon in the upper right of the notebook editor.
Fig. 6. You can view the SQL generated by the query builder by clicking on the code icon in the upper right of the notebook editor.

Recap

When sandboxing with questions:

  • Prefer SQL queries.
  • Make sure your saved question only returns columns from the table you intend to sandbox.
  • Save sandboxing questions in a collection that’s inaccessible to non-admins, preferably a collection dedicated to questions used for sandboxing.

Read more

If you want to review sandboxing basics read the basic data sandboxing article or the documentation for sandboxing.

Otherwise you can find out more about permissions and Enterprise Edition features here: