cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Permission Issue with IDENTIFIER clause

cadull
New Contributor II

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

2 REPLIES 2

madams
New Contributor II

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
New Contributor II

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.

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group