5.2 Access Control and Permissions
Key Takeaways
- Unity Catalog uses SQL GRANT and REVOKE statements to manage permissions on securable objects.
- Permissions follow an inheritance model — granting permissions on a catalog automatically applies to all schemas and objects within it.
- Key roles include metastore admin, catalog owner, and object owner, each with different levels of control.
- Groups are the recommended way to manage permissions at scale rather than granting to individual users.
- The principle of least privilege should be applied — grant only the minimum permissions needed for each role.
Access Control and Permissions
Quick Answer: Unity Catalog manages permissions via SQL GRANT/REVOKE statements. Permissions inherit from parent to child (catalog → schema → table). Grant to groups, not individuals. Follow the principle of least privilege.
Permission Model
Key Privileges
| Privilege | Applies To | Description |
|---|---|---|
| SELECT | Table, View | Read data |
| MODIFY | Table | Insert, update, delete data |
| CREATE TABLE | Schema | Create new tables in the schema |
| CREATE SCHEMA | Catalog | Create new schemas in the catalog |
| CREATE CATALOG | Metastore | Create new catalogs |
| USE CATALOG | Catalog | Access the catalog (required before accessing anything inside) |
| USE SCHEMA | Schema | Access the schema (required before accessing tables) |
| EXECUTE | Function | Run a UDF |
| ALL PRIVILEGES | Any | Grant all applicable privileges |
GRANT and REVOKE Syntax
-- Grant SELECT on a specific table to a group
GRANT SELECT ON TABLE prod.sales.orders TO `data-analysts`;
-- Grant USE CATALOG and USE SCHEMA (required for access)
GRANT USE CATALOG ON CATALOG prod TO `data-analysts`;
GRANT USE SCHEMA ON SCHEMA prod.sales TO `data-analysts`;
-- Grant all privileges on a schema
GRANT ALL PRIVILEGES ON SCHEMA prod.sales TO `data-engineers`;
-- Revoke permissions
REVOKE SELECT ON TABLE prod.sales.orders FROM `intern-group`;
-- Show grants on an object
SHOW GRANTS ON TABLE prod.sales.orders;
-- Show grants for a principal
SHOW GRANTS TO `data-analysts`;
Permission Inheritance
Permissions granted at a higher level automatically apply to all objects below:
GRANT SELECT ON CATALOG prod TO `analysts`;
→ Automatically grants SELECT on ALL schemas and tables in prod catalog
GRANT SELECT ON SCHEMA prod.sales TO `analysts`;
→ Automatically grants SELECT on ALL tables/views in prod.sales schema
Important: Even with inherited SELECT, users still need USE CATALOG and USE SCHEMA to navigate the hierarchy.
Minimum Access Pattern
-- For a user to SELECT from prod.sales.orders, they need:
GRANT USE CATALOG ON CATALOG prod TO `analyst-group`;
GRANT USE SCHEMA ON SCHEMA prod.sales TO `analyst-group`;
GRANT SELECT ON TABLE prod.sales.orders TO `analyst-group`;
Key Roles
| Role | Scope | Capabilities |
|---|---|---|
| Metastore admin | Entire metastore | Create catalogs, manage top-level permissions, manage storage |
| Catalog owner | Specific catalog | Full control over the catalog and everything inside |
| Schema owner | Specific schema | Full control over the schema and its objects |
| Object owner | Specific table/view | Full control over the specific object |
Ownership
-- Transfer ownership of a table
ALTER TABLE prod.sales.orders SET OWNER TO `data-platform-team`;
-- Transfer ownership of a catalog
ALTER CATALOG prod SET OWNER TO `platform-admin-group`;
Best Practices
1. Grant to Groups, Not Users
-- Good: Grant to a group
GRANT SELECT ON SCHEMA prod.sales TO `data-analysts`;
-- Avoid: Grant to individual users
-- GRANT SELECT ON SCHEMA prod.sales TO `alice@company.com`;
2. Principle of Least Privilege
- Grant the minimum permissions needed for the role
- Start with no access; add permissions as needed
- Use separate catalogs for environment isolation (dev/staging/prod)
- Review and audit permissions regularly
3. Service Principals for Production
-- Production jobs should run as service principals, not user accounts
GRANT SELECT ON CATALOG prod TO `etl-service-principal`;
GRANT MODIFY ON SCHEMA prod.silver TO `etl-service-principal`;
On the Exam: Know the GRANT/REVOKE syntax, understand that USE CATALOG and USE SCHEMA are required before accessing objects, and recognize that permissions inherit from parent to child objects.
A data analyst has been granted SELECT on the prod.sales.orders table but receives an error when trying to query it. What is the most likely issue?
If a data engineer is granted SELECT on a catalog, what effect does this have on tables within that catalog?
Which Unity Catalog best practice should be followed when setting up permissions for a team of 50 data analysts?