Row and column security

Row and column security lets you give granular permissions to rows and columns for different groups of people. You can change what data a group can view, as well as what data a group can query with the query builder.

You can use row and column security to set up self-service analytics, so that each of your customers can only view the rows that match their customer ID. For example, if you have an Accounts table with information about your customers, you can add permissions to the table so that each customer only sees the data relevant to them.

Row and column security examples

You can skip the theory and go straight to examples of row and column security.

How row and column security works

You can think of row and column security as a bundle of permissions that includes:

• The filtered version of a table that will replace your original table, everywhere that the original table is used in Metabase.
• The group of people who should see the filtered version of the table.

You can define up to one row and column security policy for each table/group combo in your Metabase. This means you can display different versions of a table for different groups, such as “Accounts for Sales” to your salespeople, and “Accounts for Managers” for sales managers.

Types of row and column security

Row and column security show specific data to each person based on their user attributes. You can:

• Restrict rows
• Restrict columns (as well as rows) for specific people.

Row-level security: filter by a column in the table

You can restrict rows by filtering a column using a user attribute value.

For example, you can filter the Accounts table for a group so that:

• A person with the user attribute value “Basic” will see rows where Plan = "Basic" (rows where the Plan column matches the value “Basic”).
• A person with the user attribute value “Premium” will see the rows where Plan = "Premium" (rows where the Plan column matches the value “Premium”).

Custom row and column security: use a saved question to create a custom “view” of a table

To restrict rows and columns, you can use a saved question to filter the table. When someone views that table, they’ll instead see the question’s results, not the raw table.

For example, say your original Accounts table includes the columns: ID, Email, Plan, and Created At. If you want to hide the Email column, you can create a “Restricted Accounts” SQL question with the columns: ID, Plan, and Created At.

You can use a question to filter tables to:

• Display an edited column instead of hiding the column.
• Pass a user attribute to a SQL parameter.
• Pass a user attribute to a Markdown parameter.

Prerequisites for row security

• A group of people to add row security.
• User attributes for each person in the group.

Row security displays a filtered table, in place of an original table, to a specific group. How Metabase filters that table depends on the value in each person’s user attribute.

For example, you can set up a row security so that:

• Someone with the user attribute with key of “plan” and a value of “Basic” will see a version of the Accounts table with a filter for Plan = "Basic" (that is, only the rows where the Plan column matches the value “Basic”).
• Someone with a “plan” user attribute set to “Premium” will see a different version of the Accounts table with the filter Plan = "Premium" applied.

Choosing user attributes for row and column security

User attributes are required for row security, and optional for column security. When adding a new user attribute, you’ll set up a key-value pair for each person.

Metabase uses the user attribute key to look up the user attribute value for a specific person. User attribute keys can be mapped to parameters in Metabase.

The user attribute value must be an exact, case-sensitive match for the filter value. For example, if you add row security to the Accounts table with the filter Plan = "Basic", make sure that you enter “Basic” as the user attribute value. If you set the user attribute value to lowercase “basic” (a value which doesn’t exist in the Plan column of the Accounts table), the person will get an empty result instead of the table.

Examples of user attributes in play:

• Row security
• Restricting rows and columns
• Displaying custom text in Markdown dashboard cards

Adding row-level security

1. Make sure to do the prerequisites for row security first.
2. Go to Admin settings > Permissions.
3. Select the database and table that you want to secure.
4. Find the group that you want to put in the secure.
5. Click on the dropdown under View data for that group.
6. Select “Row and column security”.
7. Click the dropdown under Column and enter the column to filter the table on, such as “Plan”.
8. Click the dropdown under User attribute and enter the user attribute key, such as “Plan”.

If you have SQL questions that query data with row-level security, make sure to move all of those questions to admin-only collections. For more info, see You cannot secure the rows or columns of SQL results.

Check out row and column security examples.

Prerequisites for column-level security

• A group of people.
• An admin-only collection, with collection permissions set to No access for all groups except Administrators.
• A SQL question with the rows and columns to be displayed to the people in the group, stored in the admin-only collection.
• Optional: if you want to restrict rows as well, set up user attributes for each of the people in the group.

Creating a SQL question for Metabase to display instead of a table

Metabase will display the results of the question in place of an original table to a particular group.

Use a SQL question to define the exact rows and columns to be included in the custom view. Avoid using a query builder (GUI) question, as you might accidentally expose extra data, since GUI questions can include data from other questions or models.

Make sure to save the SQL question in an admin-only collection (collection permissions set to No access for all groups except Administrators). For more info, see You cannot secure the rows or columns of SQL results.

Displaying edited columns

Aside from excluding rows and columns, you can also display edited columns (without changing the columns in your database).

For example, you can create a “Edited Accounts” SQL question that truncates the Email column to display usernames instead of complete email addresses.

If you edit a column, the schema of the SQL question (the question you want to display instead of the table) must match the schema of the original table. That means the “Edited Accounts” SQL question must return the same number of columns and corresponding data types as the original Accounts table.

You cannot add columns.

Setting up column security

1. Make sure to do the prerequisites first.
2. Go to Admin settings > Permissions.
3. Select the database and table that you want to secure.
4. Find the group to restrict.
5. Click on the dropdown under Data access for that group.
6. Select “Row and column security”.
7. Select “Use a saved question to create a custom view for this table”.
8. Select your saved question. The question should be written in SQL. If the question contains parameters, those parameters must be required (they cannot be optional).
9. Optional: restrict rows based on people’s user attributes.

You can find sample setups in the Row and column security examples.

Restricting rows with user attributes using a SQL variable

If you set up column security, you can also restrict different rows for each person depending on their user attributes. For example, you can display the “Accounts” question with the filter Plan = "Basic" for one group, and the filter Plan = "Premium" for another group.

1. Make sure you’ve done all the prerequisites for column-level security.
2. Go to the SQL question that will be displayed to the people in place of the table.
3. Add a parameterized WHERE clause to your SQL query, such as WHERE plan = {{ plan_variable }} .
4. Save the SQL question.
5. Go to Admin settings > Permissions.
6. Find the group and table you want to secure.
7. Open the dropdown under View data.
8. Click Edit row and column security.
9. Scroll down and set Parameter or variable to the name of the parameter in your SQL question (such as “Plan Variable”).
10. Set the User attribute to a user attribute key (such as the key “User’s Plan”, not the value “Basic”).
11. Click Save.

For a sample SQL variable and user attribute setup, see the Row and column security examples.

How row restriction works with column security

A standard WHERE clause filters a table by setting a column to a fixed value:

WHERE column_name = column_value

In step 2 of the row restriction setup above, you’ll add a SQL variable so that the WHERE clause will accept a dynamic value. The SQL variable type must be text, number, or date:

WHERE plan = {{ plan_variable }}

In steps 9-10 of the row restriction setup above, you’re telling Metabase to map the SQL variable plan_variable to a user attribute key (such as “User’s Plan”). Metabase will use the key to look up the specific user attribute value (such as “Basic”) associated with a person’s Metabase account. When that person logs into Metabase and uses the secured table, they’ll see the query result that is filtered on:

WHERE plan = "Basic"

Note that the parameters must be required for SQL questions used to create a custom views. E.g., you cannot use an optional parameter; the following won’t work:

[[WHERE plan = {{ plan_variable }}]]

Learn more about SQL parameters

Preventing row and column security permissions conflicts

Some Metabase permissions can conflict with row and column security to give more permissive or more restrictive data access than you intended.

Say you’ve set up column security that hides the Email column from the Accounts table (for a particular group).

The Email column may get exposed to someone if:

• The person belongs to multiple row and column security policies.
• Someone else in a non-secured group shares the Email column from:
  • A SQL question.
  • A public link
  • An alert, or dashboard subscription

Multiple row and column security permissions

Multiple row and column security policies on the same table can create a permissions conflict. You can add a person to a maximum of one row and column security policy per table (via the person’s group).

For example, if you have:

• Set up security for the group “Basic Accounts” that filters the Accounts table on Plan = "Basic".
• Another setup for the group “Converted Accounts” that filters the Accounts table on Trial Converted = true.

If you put Vincent Accountman in both groups, he’ll have conflicting permissions for the Accounts table, and get an error message whenever he tries to use Accounts in Metabase.

To resolve row and column security permissions conflicts:

• Remove the person from all but one of the groups.
• Set the all but one of the group’s View data access to the datatabase to “Blocked”.

You cannot secure the rows or columns of SQL results

Row and column security permissions don’t apply to the results of SQL questions. That is, SQL questions will always display results from the original table rather than the secured table.

Say that you’ve set up column security on the Accounts table to hide the Email column. If someone creates a SQL question that includes the Email column, anyone with collection permissions to view that SQL question will be able to:

• See the Email column in the SQL question results.
• Use the SQL question to start a new question that includes the Email column (if they have query permissions).

To prevent the Email column from being exposed via a SQL question:

• Put any SQL questions that include the Email column in a separate collection.
• Set the collection permissions to No access for groups with row and column security set up to hide the Email column.

Collection permissions must be used to prevent secured groups from viewing SQL questions that reference secured tables.

Public sharing

Row and column security permissions don’t apply to public questions or public dashboards. If somone in an unsecured group person creates a public link using an original table, the original table will be displayed to anyone who has the public link URL.

To prevent this from happening, you’ll have to disable public sharing for your Metabase.

Metabase can only create row and column security using the group membership or user attributes of people who are logged in. Since public links don’t require logins, Metabase won’t have enough info to apply permissions.

Limitations

Row and column security is limited to questions built with the query builder.

Groups with native query permissions (access to the SQL editor) can bypass row and column security

You can’t set up query builder and native for groups with row and column security.

To enforce row-level permissions with the native query editor, check out impersonation.

You can’t apply row and column security to SQL questions

Since Metabase can’t parse SQL queries, the results of SQL questions will always use original tables.

Use collection permissions to prevent groups from viewing saved SQL questions with restricted data.

Non-SQL databases have limited row and column security

MongoDB only supports row-level security. Row and column security permissions are unavailable for Apache Druid.

Further reading

• Row and column security examples
• Permissions strategies
• Configuring permissions for embedding
• Securing embedded Metabase