Column-level permissions

Row and column security was formerly called data sandboxing. It’s the same feature, it just now has a more descriptive name.

Row and column security is available on Pro and Enterprise plans as a way to specify what data people can access based on who they are. Our article on row-level permissions covered how to restrict rows based on a person’s user attribute. 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 limit the rows and columns of the Products table that 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 (row-level security)
• Only see the Title, Category, and Price columns (and not any of the other 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. Restrict Ms. Brown’s view of the Products table by displaying our query’s results instead of the original table.

4. Confirm our the access 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 restrict Ms.Brown’s view of the Products table . Let’s call it Restricted view questions and set permissions on this collection so that only admins can curate its questions. This way non-admins won’t be able to alter the question and change the “dimensions” of the restricted view, 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 bar, click on + New > SQL query to ask a SQL question. Select the Sample Database 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 ({{user_id}})]]

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 user with restricted access.

The double square brackets [[…]] around the WHERE clause make the clause optional. The double curly braces {{user_id}} define the variable. We’ll use this {{user_id}} variable when using this question to restrict access by user.

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

Now let’s save this question as Products from Orders, store it in the Restricted view 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 restrict.

Restrict the view of the Products table using our saved question

Now that we’ve created our Products from Orders question, it’s time to restrict the Products table. We’ll set up the permissions so that Metabase inserts the user_id attribute we gave Ms. Brown in the article on row permissions into the {{user_id}} variable in our saved SQL question, Products from Orders.

We’ll click on the gears icon, select Admin settings, and click on the Permissions tab. On the left, under Databases, we’ll click on Sample Database and Products.

If you went through our tutorial on row permissions already, you should have permissions for All users group set to “View data: Blocked” and “Create queries: Query builder only”. If not, you’ll need to make those edits before setting up permissions for the Customers group.

Since Ms. Brown is a member of the Customers group, and Metabase grants data permissions to groups, not individuals, we’ll restrict access to the Products table for the Customers group. Click on the droptdown in the “View data” column for the “Customers” group and select Row and column security.

When Metabase asks How do you want to filter this table? 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 Restricted view questions collection and select our question, Products from Orders. For PARAMETER OR VARIABLE, we’ll select the variable we included in our SQL query, {{user_id}}. For the USER ATTRIBUTE, we’ll select user_id.

Our summary now says:

• People in the Customers group
• Can view rows in the Products from Orders question
• Where the user_id variable’s value equals user_id attribute.

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

Check settings as the restricted user

Let’s see what our restricted Products table looks like from Ms. Brown’s perspective. Open a private browser window, navigate to our Metabase, and sign in as Ms. Brown.

When we open up the Products table using the data browser, we can 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.

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 custom view, she’ll see an error.

Use a SQL question to create a custom restricted view of a table

While in theory you could use a query builder question to create a custom view of a table, you should always use SQL questions instead. Behind the scenes, questions create SQL queries based off the filters, summaries, and joins in our question. When you create a custom view based on a query builder question, you may not realize the full extent of the information you’re giving people access to.

Recap

When restricting row and column access with questions:

• Use SQL questions.
• Make sure your saved SQL question only returns columns from the table you intend to restrict.
• Save questions that you’re using to create custom views in a dedicated admin-only collection.

Further reading

• Tutorial: row-level permissions
• Row and column security documentation
• Permissions overview
• Guide to data permissions
• Working with collection permissions
• Single sign on with SAML
• Configuring Metabase appearance