Inheritance model in Unity Catalog is not working as per documentation.

[Ela]

As per the documentation "Securable objects in Unity Catalog are hierarchical and privileges are inherited downward. The highest level object that privileges are inherited from is the catalog". Executed following statement "GRANT SELECT ON CATALOG uctest TO `user@***.com`;" expectation is the user should be able to access all the schema's inside the catalog "UCTest" but it getting exception "Error in SQL statement: AnalysisException: User does not have USE SCHEMA on Schema 'uctest.default'.

https://docs.databricks.com/data-governance/unity-catalog/manage-privileges/privileges.html#inherita...

Note : Privilege Model Version 1.0 is used.

"

[Hubert-Dudek]

Users need to have USAGE rights to be able to do anything. So you need to GRANT usage on the catalog and on the schema.

[Debayan]

Hi, Both permissions have to be granted.

[Ela]

Hello Debayan,

Thanks for your response. Do you mean both Usage & Select permissions to be granted at both catalog & schema levels? I was referring to this in data bricks documentation which states that granting permission at catalog or schema grants access to all current & future child objects. Is my understanding not correct?

[Jfoxyyc]

Your understanding is correct. Applying grant select and grant usage at the catalog level should grant said permissions on all current and future schemas and tables in said catalog.

Usage essentially adds user_can_see_this.

[Hubert-Dudek]

Here are a few screens which will help you. Usage is for traverse catalog. Select is on tables.

[Ela]

Hello Hubert & Jfoxyyc,

For granting user permission to a table I am using following 3 grants statements

grant usage on catalog demo_catalog to `user@***.com`

grant usage on schema demo_catalog.demo_schema to `user@***.com`

grant select on table demo_catalog.demo_schema.demo_table to `user@***.com`

Is there a way to grant this permission with single grant statement? Thanks in advance.

[Hubert-Dudek · Accepted solution]

GRANT USE_CATALOG ON CATALOG demo_catalog TO `user@***.com` ;

GRANT USE_SCHEMA ON SCHEMA demo_catalog.demo_schema TO `user@***.com` ;

GRANT SELECT ON CATALOG demo_catalogTO `user@***.com` ;

GRANT SELECT ON SCHEMA demo_catalog.demo_schema TO `user@***.com` ;

[Jfoxyyc]

The schema grants are redundant, I just tested and confirmed. Due to inheritance, adding anything at catalog shows the same permission at schema. It even shows a hint on the Grant page in Unity:

Granted privileges will be inherited by applicable objects (e.g. schemas, tables) in this catalog. Learn more

GRANT USE_CATALOG ON CATALOG dev to `user@userdomain.com`;

GRANT USE_SCHEMA ON CATALOG dev to ``user@userdomain.com`; 

GRANT SELECT ON CATALOG dev to ``user@userdomain.com`;

Catalog

 

Schema