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.

@mrmiffo can I ask a clarifying question? I get it that the security later lives above the raw SQL and that native SQL queries won’t be filtered by the views managed by that security later. Is it, however, possible to reference the user’s attributes in an SQL query? Meaning, can the SQL query be written in such a way that the row-level enforcement is within the query itself?

Example. Let’s say we wanted to build a question that gave a record count of records that are for that logged-in user’s company. In the user’s record we’ve assigned “company_id = 1” to them. We write an SQL query that says select the count of records where company_id = {{user.company_id}} or something. Metabase isn’t applying the row-level filters for us, but we have in essence done so ourselves.

Is that attribute accessible?

@nutron Please use the support email, when using the Enterprise Edition.
Metabase currently does not support sandboxing on SQL questions, but it’s possible to use SQL to define what the sandboxing should do:
https://www.metabase.com/docs/latest/enterprise-guide/data-sandboxes.html#advanced-sandbox-examples

I will email your support team, though will also post here:

I understand that there is not currently support for sandboxing in raw SQL queries. I also understand that you can use an SQL query to determine what data is visible in the sandbox.

I wanted to share our use case and my suggestion on how you might offer that support.

We have a product with numerous businesses that all are in a shared database. We use row-level permissions to grant access to that data, which is great. However, there are some queries that cannot be built using the GUI builder. In particular, any kind of ratio, such as “percent of user records where X is true”, are not possible to build. We have these queries in our instance of metabase for our internal use, and we want to express them for customers, but we cannot reference the sandbox identifier to know what data we should limit.

Suggestion: allow SQL to reference the logged in user and its metadata, in particular, the sandbox variables. This way we would be able to publish a single query and grant them access to their own data if that query used that attribute to limit its output. There’s room for error of course – our query could “leak” but that’s on us to validate.

Can you confirm that, without something like this from your team, the only way to do this is embedding it into our product and passing in these limits programatically?