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