Sandbox/row level permissions and Native/SQL

Hi!

I’m reading through the documentation of the Data Sandbox in Enterprise edition of Metabase as we are considering updating, and I just want to make sure I understand it correctly and check if there might be a workaround that could work for our business case.

Under Limitations it sais that sandboxing does not work for Native/SQL. And specifically:

  1. If a user views a saved SQL/native query, data sandboxes will not filter the results of that query, even if the query includes data from a table for which the user only has sandboxed access.

Is this also true if the query contains the filter mentioned in “Example 2: using variables in a saved question”?

In our current set up all our queries are Native/SQL for a number of reasons, primarily to get dashboard filters to work as intended. However, the users viewing these queries/dashboards do NOT have permissions to create Native/SQL queries, and only has view access to the questions. So they will never be able to create new, or edit, Native/SQL. So we could make all our queries look for user attributes, but would that work?

If I create a question which is filtered on the user sandbox variable, and the user views this question (eg in a dashboard) will the results be filtered, or will the sandbox variable ONLY filter results if it is set up as a sandbox question for a table in the permissions screen?

As an example:

  1. On a user I add the parameter user_id = 5.
  2. I create an SQL query of orders including “where user_id = {user_id}”
  3. The user login and runs the query.

Will the user see all orders, or only orders where user_id = 5? (Note: I’ve not set any sandboxing on the table in permissions, but I want this particular query filtered)

Hi @mrmiffo,

Damon from Metabase here - if I understand your question correctly, you’re asking if when you create new SQL queries on a sandboxed table, if those new SQL queries will filter results based on the sandbox variable. (Let me know if that’s inaccurate).

The short answer is no. What “Example 2” is referring to is the ability to create a “saved question” that then becomes the sandboxed user’s view of the table.

Here’s another way to think about it. You can either sandbox the user directly against the table as it exists in your database using a pre-existing field, or you can create a custom view of that table with a saved question (this can be SQL). If you use option 2, when the sandboxed user goes to view the table, they’ll see the custom question you created instead. And you can optionally add a variable in that saved question that maps back to a user attribute.

However, if you create derivative SQL off of that table, those new SQL queries/questions will not be filtered. Doing so would require us to parse the SQL.

Another option here given that you have lots of SQL queries could be embedding. You’d have to build a little wrapperapp (we have some examples here - Metabase embedding reference apps), but you can pass signed/locked variables that the end user won’t be allowed to modify. Check the Metabase embedding docs for more details.

Hope that helps - feel free to sign up for a trial of the Enterprise edition to play around with.

Damon

I think you’ve got it right, and my question was a long shot.

Basically what I was hoping for was that the sandbox variable would work as the SQL filter variables do, that is it is inserted into the query at run time. Like:
[[and {{Username}}]] is replaced by and orderline.username = "mrmiffo" at run time if using Field filter (connected to orderline.username) and user sets the username “mrmiffo”.

As we can limit our users to not create/modify SQL queries, this would work to add a filter based on the user variable, instead of one of the question/dashboard filters which we can’t limit our users from editing (apart from embedding as you mention).

Thanks anyway! Would be great if this would be a feature in the future, but for now it seems like it is a show stopper for the Enterprise edition in our case.