What privileges are needed to query a table in Unity Catalog?

SELECT on the table, USE SCHEMA on its schema and USE CATALOG on its catalog. The same 3 apply to a view. SELECT can come from a grant on the table or be inherited from a grant on its schema or catalog. To change the rows, a user also needs MODIFY.

What is the difference between BROWSE and SELECT in Unity Catalog?

BROWSE lets a user find objects and see their metadata, such as names and columns, and ask for access, without USE CATALOG or USE SCHEMA. It does not let them read any rows. SELECT is the privilege to read the data. Databricks recommends granting BROWSE on catalogs to the All account users group, so people can discover what exists.

Unity Catalog privileges: GRANT, USE CATALOG, USE SCHEMA and SELECT

To read a table or view in Unity Catalog, a user needs 3 privileges: SELECT on it, USE SCHEMA on its schema and USE CATALOG on its catalog. A privilege granted on a schema or catalog is inherited by every object inside, today's and the ones created later, so GRANT SELECT ON SCHEMA shop.gold opens all of gold. Grant to groups rather than to people, check with SHOW GRANTS and take back with REVOKE. The owner of an object can do everything with it and grant it to others.

Module 5: Unity Catalog and governance

At the end of this part you can:

Grant a group read access to the tables and views of 1 schema with 3 statements
Say why a query is refused, from the privileges on the path to the object
Choose between SELECT, MODIFY, CREATE TABLE and BROWSE for a job, and check grants with SHOW GRANTS

Take this module into your AI

ChatGPT Claude

Part 1 made 3 schemas, bronze, silver and gold, in the current catalog, with the shop's customers in silver.customers. The examples here call the catalog shop; use your own catalog's name, from SELECT current_catalog().

Open-source Spark has no access control. Run a GRANT on your computer and it does not even parse:

[INVALID_STATEMENT_OR_CLAUSE] The statement or clause: GRANT is not valid. SQLSTATE: 42601

So this part is SQL for Databricks, where every query passes through Unity Catalog's privileges.

What it takes to read a table

Unity Catalog checks the whole path to an object. To read a table or view, a user needs USE CATALOG on its catalog, USE SCHEMA on its schema and SELECT on the object. Missing any one of the 3, the query is refused. USE CATALOG and USE SCHEMA read nothing on their own; they open a level so that the privileges inside it count.

The shop's analysts should read the views in gold and nothing in silver, where the raw emails are. That is 3 statements:

GRANT USE CATALOG ON CATALOG shop TO `analysts`;
GRANT USE SCHEMA ON SCHEMA shop.gold TO `analysts`;
GRANT SELECT ON SCHEMA shop.gold TO `analysts`;

analysts is a group. Grant to groups, not to people: when someone joins or leaves, you change 1 membership instead of every grant they held.

The views in gold read from silver, yet the analysts need nothing there. On SQL warehouses, serverless compute and current Databricks Runtime versions, Unity Catalog checks the view owner's access to the tables underneath, so the owner must keep SELECT on them. That is what lets a view show a safe slice of a table its readers cannot open, the idea part 3 builds on.

A grant on a schema covers everything in it

Privileges flow down. SELECT granted on a schema applies to every table and view in it, those there today and those created later, and SELECT on a catalog applies to every schema in it. Grant at the level that matches what the group should see: a whole layer at the schema, a single object only as an exception.

Try it. Start with SELECT on 1 view, then add USE SCHEMA, then swap the view's grant for the schema's and query the other view:

Try itWho can read what in Unity Catalog

Granted to analysts

They query

SELECT * FROM shop.gold.customer_spend

Catalog shopneeds USE CATALOG: grantedOpen
Schema shop.goldneeds USE SCHEMA: not grantedMissing
View shop.gold.customer_spendneeds SELECT: granted on the view itselfOpen

The grants, in SQL

GRANT USE CATALOG ON CATALOG shop TO `analysts`;
GRANT SELECT ON TABLE shop.gold.customer_spend TO `analysts`;

Refused: the analysts lack USE SCHEMA on shop.gold. SELECT alone is not enough: every level above the object must be opened with its own USE privilege.

The rule from Databricks' docs: SELECT on the object, USE SCHEMA on its schema and USE CATALOG on its catalog; a privilege on a schema is inherited by its current and future objects. The shop catalog and the analysts group are this module's examples.

The other privileges a pipeline needs

Reading is 1 job of several. The ones a data team uses most:

Privilege	Lets a user	Grant it on
SELECT	Read rows of a table or view	The table, or its schema for all of them
MODIFY	Insert, update and delete rows; also needs SELECT	The tables a job writes
CREATE TABLE	Create tables in a schema	The schema, as Databricks recommends
BROWSE	See that objects exist and their metadata, and ask for access, without reading rows	The catalog
ALL PRIVILEGES	Every privilege that applies, except a few such as MANAGE	Rarely; prefer the ones above

The pipeline that loads silver needs SELECT and MODIFY on its tables and CREATE TABLE on the schema; the analysts need none of those. Databricks recommends BROWSE on catalogs for the All account users group, so people can find a table and request it without being able to read it.

The owner of an object, at first whoever created it, can do everything with it and grant it to others, though like anyone else it needs USE CATALOG and USE SCHEMA on the levels above. The owner of its schema or catalog, a user with MANAGE on it and a metastore admin can grant it too. Ownership is not a grant, so SHOW GRANTS does not list it.

Check and take back

SHOW GRANTS lists what is granted on an object, or what 1 group holds on it, and REVOKE takes a privilege back:

SHOW GRANTS ON SCHEMA shop.gold;
SHOW GRANTS `analysts` ON SCHEMA shop.gold;
REVOKE SELECT ON SCHEMA shop.gold FROM `analysts`;

A refusal is easiest to see with a second user. On your own, you own what you created, and SHOW GRANTS still lists every grant you made. Part 3 turns from tables to columns: the email that some readers may see and others may not.

Check yourself

2 questions, each in a new situation

1. The analysts have USE SCHEMA and SELECT on the schema shop.gold, but every query on gold.customer_spend is refused. What is missing?
Not quite. Every level above the object needs its USE privilege. SELECT on the schema does reach every table and view in it, so nothing is missing at the view or the schema, but without USE CATALOG on shop the analysts cannot get past the catalog. Reading a view does not need privileges on the tables under it, on current Databricks compute, because Unity Catalog checks the view owner's access to those.
2. Next week the team adds a view gold.weekly_sales. The analysts were given SELECT ON SCHEMA shop.gold, with USE CATALOG and USE SCHEMA. What do they need to read the new view?
Not quite. Unity Catalog's privileges flow down the hierarchy, and a grant on a schema covers its current and future tables and views. That is why granting at the schema is the usual way to open a layer such as gold to readers, and granting on single tables is for exceptions.

Questions

What privileges are needed to query a table in Unity Catalog?: SELECT on the table, USE SCHEMA on its schema and USE CATALOG on its catalog. The same 3 apply to a view. SELECT can come from a grant on the table or be inherited from a grant on its schema or catalog. To change the rows, a user also needs MODIFY.
What is the difference between BROWSE and SELECT in Unity Catalog?: BROWSE lets a user find objects and see their metadata, such as names and columns, and ask for access, without USE CATALOG or USE SCHEMA. It does not let them read any rows. SELECT is the privilege to read the data. Databricks recommends granting BROWSE on catalogs to the All account users group, so people can discover what exists.

Sources