Unity Catalog grants cheat sheet - USE CATALOG, USE SCHEMA, SELECT, ownership and fixing \"permission denied\"

#The rule

To read main.sales.orders a principal needs all three:

1. USE CATALOG on main
2. USE SCHEMA on main.sales
3. SELECT on main.sales.orders (or on the schema or catalog, which inherits down)

Missing any one gives a permission error. Grant to groups, never to individual users.

#Common grant sets

Read-only analysts on a schema:

GRANT USE CATALOG ON CATALOG main TO `analysts`;
GRANT USE SCHEMA, SELECT ON SCHEMA main.sales TO `analysts`;

Grants on a schema apply to all current and future tables and views in it.

Data engineers who load the schema:

GRANT USE CATALOG ON CATALOG main TO `data-engineers`;
GRANT USE SCHEMA, SELECT, MODIFY, CREATE TABLE, CREATE VOLUME ON SCHEMA main.sales TO `data-engineers`;

A team that owns its own sandbox catalog:

CREATE CATALOG IF NOT EXISTS sandbox_marketing;
ALTER CATALOG sandbox_marketing OWNER TO `marketing-data-owners`;
GRANT USE CATALOG, CREATE SCHEMA ON CATALOG sandbox_marketing TO `marketing`;

Read one table only:

GRANT USE CATALOG ON CATALOG main TO `partner-readers`;
GRANT USE SCHEMA ON SCHEMA main.sales TO `partner-readers`;
GRANT SELECT ON TABLE main.sales.orders_shared TO `partner-readers`;

Volumes (files):

GRANT READ VOLUME ON VOLUME main.landing.raw_files TO `ingest-readers`;
GRANT WRITE VOLUME ON VOLUME main.landing.raw_files TO `ingest-writers`;

Functions:

GRANT EXECUTE ON FUNCTION main.sales.mask_email TO `analysts`;

Registered models in Unity Catalog also use the EXECUTE privilege, for loading a model for inference. Grant it in Catalog Explorer, or check the SQL syntax for models in the current documentation.

#Check grants

SHOW GRANTS ON SCHEMA main.sales;
SHOW GRANTS ON TABLE main.sales.orders;
SHOW GRANTS `analysts` ON SCHEMA main.sales;

Inherited grants don't appear on the child object. Check the parent levels too.

Across a catalog, using the information schema:

SELECT grantee, table_schema, table_name, privilege_type
FROM main.information_schema.table_privileges
WHERE grantee = 'analysts';

SELECT grantee, schema_name, privilege_type
FROM main.information_schema.schema_privileges;

#Ownership

The owner has all privileges and can grant to others. Make a group the owner, so access doesn't break when a person leaves.

ALTER SCHEMA main.sales OWNER TO `sales-data-owners`;
ALTER TABLE main.sales.orders OWNER TO `sales-data-owners`;
DESCRIBE TABLE EXTENDED main.sales.orders;   -- shows Owner

#Revoke

REVOKE SELECT ON SCHEMA main.sales FROM `contractors`;

Revoking at the schema level doesn't remove a direct grant on a table. Check SHOW GRANTS on the table as well.

#Fixing common errors

Check which identity a job runs as (Job > Run as) and grant the privileges to that service principal or its group.

#Who has a privilege (audit)

SELECT event_time, user_identity.email, action_name, request_params
FROM system.access.audit
WHERE service_name = 'unityCatalog'
  AND action_name IN ('updatePermissions')
  AND event_date >= current_date() - INTERVAL 7 DAYS
ORDER BY event_time DESC;

#Good habits

• Groups managed in Entra ID and synced with SCIM or automatic identity management.
• Grants at schema level for most cases; table-level grants only for exceptions.
• Group owners for catalogs, schemas and production tables.
• Grants defined in code (Terraform or SQL in CI) rather than clicked in Catalog Explorer.

#Checklist

• USE CATALOG + USE SCHEMA + object privilege for every reader
• Grants to groups only
• Group owners on production objects
• Job service principals granted explicitly
• Grants in code and reviewed