Unity Catalog Privileges: Securable Hierarchy, Inheritance and Principals

1.The hierarchy you grant against

In Unity Catalog, access control is the act of granting privileges on securable objects to principals. The top-level container is the metastore. Inside it, data uses a three-level namespace: catalog.schema.table. Securables run from the metastore at the top, through catalogs and schemas, down to the data objects they hold: tables, views, volumes, functions and models.

Two levels are container objects. Catalogs hold schemas, and schemas hold tables, views, volumes and functions. Containers get special treatment in four ways. Grants on them can be inherited. They need usage privileges. Their owners can manage the child objects. They also take creation privileges such as CREATE SCHEMA on a catalog and CREATE TABLE on a schema. Tables, views, volumes and functions are non-containers because nothing sits beneath them.

The starting point is that nobody has access to anything. Users initially have no access to data in a metastore, and a principal must be explicitly granted a privilege to perform an action. Each privilege also belongs to particular levels. A table accepts SELECT, MODIFY, APPLY TAG, MANAGE and a few others. A catalog accepts USE CATALOG, CREATE SCHEMA and BROWSE, plus any privilege meant for objects inside it.

Sources12

2.Usage privileges: the three grants behind every read

USE CATALOG and USE SCHEMA are usage privileges. Neither one gives access to data. They are the prerequisite for doing anything inside a catalog or schema. For most operations on a table, view, volume or function, a principal needs three things: USE CATALOG on the parent catalog, USE SCHEMA on the parent schema, and the privilege for the operation itself (SELECT, MODIFY, EXECUTE and so on). It needs all three.

This gives higher-level administrators a gate. A table owner may want to share a table, but other users still cannot reach it without the usage privileges on its parents. Only catalog and schema owners, or users with MANAGE, can grant those, so a table owner cannot hand out access beyond the approved boundary.

The last row shows the one exception. MANAGE has reduced usage requirements: you need usage privileges only on the containers strictly above the level where you hold MANAGE. That relief covers metadata management only. With MANAGE, SELECT and MODIFY still need USE CATALOG and USE SCHEMA.

Sources1

3.Inheritance: granting high in the tree

Inheritance is why you pick the level of a grant on purpose. SELECT on a catalog lets the grantee read every table in it, provided the usage privileges are also in place. The privileges reference makes the same point for each container: catalog-level and schema-level grants of object privileges apply to current and future objects. If you grant at the schema level instead, the grant reaches only that schema.

The metastore works differently. Most metastore-level privileges, such as CREATE CATALOG or CREATE EXTERNAL LOCATION, are not inherited at all. READ METADATA is the exception: granted on the metastore, it inherits down to every object.

If MANAGE is granted on a container, the user also gets MANAGE on all child objects. One more nuance concerns composite privileges. MANAGE and MODIFY have child privileges (READ METADATA; INSERT, UPDATE, DELETE), and composites and children are granted and revoked independently.

Sources31

4.Principals: users, service principals and groups

A principal is a user, service principal or group that the metastore knows about. Each kind is written differently in SQL:

- A user is identified by email address. Because of the @, it must always be in backticks. - A service principal is identified by its applicationId, not by a display name. The ID contains dashes, so it also needs backticks. - A group is written by name. It needs backticks only if the name contains special characters. A name such as finance-team needs them, while finance_team does not, because underscores are not special.

The backtick rule applies to more than principals. Any object name with a hyphen needs backticks too, so a schema named sales-eu has to be written as ` sales-eu `.

-- Granting a privilege to the user alf@melmak.et
> GRANT SELECT ON TABLE t TO `alf@melmak.et`;

-- Granting a privilege to the service principal fab9e00e-ca35-11ec-9d64-0242ac120002
> GRANT SELECT ON TABLE t TO `fab9e00e-ca35-11ec-9d64-0242ac120002`;

-- Revoking a privilege from the general public group.
> REVOKE SELECT ON TABLE t FROM `account users`;

Group type matters. Account groups can receive privileges on Unity Catalog securables. Workspace-local groups cannot, and that includes the system groups users and admins. To grant something to everyone, use ` account users `, the root group that contains every user in the account. It needs backticks because of the space.

Sources2

5.ALL PRIVILEGES and what it leaves out

GRANT ALL PRIVILEGES ON TABLE forecasts TO finance reads as if it gives everything, but it does not. In Unity Catalog, ALL PRIVILEGES is evaluated when permissions are checked. It is not a one-time expansion into separate grants. On a table it implies SELECT, MODIFY and APPLY TAG.

It deliberately excludes MANAGE, READ METADATA, EXTERNAL USE SCHEMA and EXTERNAL USE LOCATION. The documentation says the exclusion is there to avoid accidental data exfiltration or privilege escalation. So a principal with ALL PRIVILEGES on a schema still cannot manage that schema's grants unless it also holds MANAGE or ownership.

Sources4

6.DENY, and ABAC DENY policies

DENY is the counterpart of GRANT. Denying a privilege takes precedence over any explicit or implicit grant, and a deny on a schema or catalog implicitly denies that privilege on the objects below it. You undo a DENY by running REVOKE for the same privilege. Be careful about scope, though: the SQL DENY statement is documented as not supported by Unity Catalog and applies only to the hive_metastore catalog and its objects.

In Unity Catalog the equivalent is the ABAC DENY policy (Beta). It is a separate mechanism from row filters and column masks. A DENY policy is attached at the metastore, catalog or schema level, matches securables by their governed tags, and denies a privilege to the principals it targets. Today the only privilege it can deny is MANAGE ACCESS CONTROL, a child of MANAGE that can be denied but not granted. Row filter and column mask policies are different: they restrict the content of data a user can already access, and they need a user-defined function, while DENY policies do not.

Sources56

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Granting SELECT on a table is enough for a user to query it.Why is that wrong?

   The user also needs USE CATALOG on the parent catalog and USE SCHEMA on the parent schema.

   Covered in Usage privileges: the three grants behind every read

2. 2.Granting to the workspace users group is a quick way to give everyone Unity Catalog access.Why is that wrong?

   users is a workspace-local group and cannot receive privileges on Unity Catalog securables. Use the account group account users instead.

   Covered in Principals: users, service principals and groups

3. 3.ALL PRIVILEGES makes the grantee able to manage permissions on the object.Why is that wrong?

   ALL PRIVILEGES excludes MANAGE, so it does not give the right to manage grants.

   Covered in ALL PRIVILEGES and what it leaves out