# 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.

- Page: https://tecyfy.com/data-engineering/course/unity-catalog/privileges
- Course: Data engineering course (https://tecyfy.com/data-engineering/course), Module 5: Unity Catalog and governance, Privileges and grants
- Parts of this module: Catalogs, schemas and the 3-part name (https://tecyfy.com/data-engineering/course/unity-catalog); Privileges and grants (https://tecyfy.com/data-engineering/course/unity-catalog/privileges); Masking personal data (https://tecyfy.com/data-engineering/course/unity-catalog/personal-data); Lineage and audit logs (https://tecyfy.com/data-engineering/course/unity-catalog/lineage-and-audit); Build it: the shop's catalog (https://tecyfy.com/data-engineering/course/unity-catalog/build-it)

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

[Part 1](/data-engineering/course/unity-catalog) 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:

```text
[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:

```sql
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](/data-engineering/course/unity-catalog/personal-data) 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:

(An interactive demo goes here on the page: https://tecyfy.com/data-engineering/course/unity-catalog/privileges)

## 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:

```sql
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](/data-engineering/course/unity-catalog/personal-data) turns from tables to columns: the email that some readers may see and others may not.

## Check yourself

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?

   A. SELECT on the table gold.customer_spend itself, since a schema grant does not reach tables
   B. MODIFY on the schema
   C. SELECT on the silver tables the view reads
   D. USE CATALOG on the catalog shop

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?

   A. A new GRANT SELECT on the view, the day it is created
   B. The owner of the view to share it with them in Catalog Explorer
   C. Nothing: a privilege on a schema is inherited by every object in it, including those created later
   D. BROWSE on the catalog

### Answers

1. D. 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. C. 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

- [Unity Catalog privileges and securable objects](https://docs.databricks.com/aws/en/data-governance/unity-catalog/manage-privileges/privileges)
- [Manage privileges in Unity Catalog](https://docs.databricks.com/aws/en/data-governance/unity-catalog/manage-privileges/)
- [Unity Catalog permissions concepts](https://docs.databricks.com/aws/en/data-governance/unity-catalog/access-control/permissions-concepts)
- [What is a view?](https://docs.databricks.com/aws/en/views/)