Permission Issue with IDENTIFIER clause

[cadull]

Hi all,

we are parameterizing environment specific catalog names (like `mycatalog_dev` vs. `mycatalog_prd`) in Lakeview dashboard queries like this:

SELECT * FROM IDENTIFIER(:catalog_name || '.myschema.mytable')

Which works fine in most cases. We have one situation though, in which we are referencing a view in such a query and that view is defined as a select on a table in a different catalog. This apparently only works if the executing identity has permissions on that different catalog. In our view this should work irrespective of the identity's permissions on that other catalog.

For example, consider we have catalog_a and catalog_b, where catalog_a contains table_a (in schema_a) and catalog_b contains the following view:

CREATE VIEW catalog_b.schema_b.view_b AS SELECT * FROM catalog_a.schema_a.table_a

And we execute the following query with an identity that does not have any permissions on catalog_a but full permissions on catalog_b, schema_b and view_b:

SELECT * FROM IDENTIFIER('catalog_b.schema_b.view_b')

Then we would expect the query to succeed. Instead, we get "User does not have USE CATALOG on Catalog 'catalog_a'. SQLSTATE: 42501".

In contrast, with the same permissions, if we execute:

SELECT * catalog_b.schema_b.view_b

Then it just works.

Are we missing something?

Query was executed using a serverless warehouse.

Thanks,
Carsten

[madams]

I've had quite a bit of fun with UC and view permissions.  I don't think this is specific to using the IDENTIFIER() function, but I suspect it's related to UC permissions.  What you'll need to ensure:

1. The user or group who owns the view on catalog_b has select access on the tables behind the view on catalog_a.
2. The user who is trying to query the view has select access on the catalog_b view, and 'use catalog' (and I think 'use schema') on catalog_a. 

I'm not sure if this is documented yet, but Databricks Support helped me determine this at one point in the recent past. You do NOT, however, need to grant select to the table.  This allows us to do dynamic data masking on the view without granting access to the raw table behind it.

[cadull]

We are aware of the permission requirements you describe and that's exactly how we set it up. Yet in the same session the first of the following statements works and the other doesn't:

SELECT * catalog_b.schema_b.view_b; --works
SELECT * FROM IDENTIFIER('catalog_b.schema_b.view_b') --fails with "User does not have USE CATALOG on Catalog 'catalog_a'. SQLSTATE: 42501"

The only difference being the use of IDENTIFIER(). To me that is a strong hint to this problem being specific to that function.

For example, consider the implementation of IDENTIFIER() would rely on a mechanism that is listing all related UC objects in the background to find out which one to reference. If that code would run under the caller's identity (which does not have any permissions on that other catalog), then that would perfectly explain the behavior we are seeing.

That would be a bug to me, though. Functionally, both statements above are exactly equivalent and should simply do the same thing.

And it would be a case of a leaky abstraction. The caller should not see the name of that other catalog ('catalog_a') in the error message, if that identity does not have any permissions on it.