I'm trying to create a dashboard in Databricks SQL, parameterized by table name. We have a metadata table which contains the names of all the eligible tables, and we use it to populate a drop-down box for the dashboard. This is a simplified version of the query:
SELECT CONCAT(m.database_name, '.', m.table_name)
FROM metadata m
However, the drop-down box should only contain the tables that the current user has actually permission to read. So the question is: is it possible to modify the previous query to take permissions into account?
In other words, this is what I'm looking for (pseudo-code):
SELECT CONCAT(m.database_name, '.', m.table_name)
FROM metadata m
INNER JOIN permission_metadata p ON m.database_name = p.database_name AND m.table_name = p.table_name
where permission_metadata only contains the tables that the current user can read.
