Skip to content

Unity Catalog Managed Tables, External Tables and Privileges

Step 27 of 30

Unity Catalog Managed Tables, External Tables and Privileges

Introduction

This lesson opens the Governance and Security section of the official exam outline (15% of the exam outline). Building on the Databricks lakehouse and Unity Catalog architecture lesson, this lesson prepares you to manage Unity Catalog tables and privileges for the Data Engineer Associate exam. The exam expects a data engineer to know how data is organized and protected in Unity Catalog, the governance layer of the Databricks Intelligence Platform: the object hierarchy, the difference between managed and external tables, how privileges are granted and inherited, and how views and volumes fit into the model.

After this lesson you will be able to read a scenario such as "the analysts group can query the table but gets an error" and name the missing privilege, choose between a managed and an external table, and write the GRANT, REVOKE and SHOW GRANTS statements that implement a least-privilege design. The next lesson builds on this one with row filters, column masks and attribute-based access control.

Learning objectives

  • Describe the Unity Catalog hierarchy: metastore, catalog, schema, and the objects inside a schema.
  • Use the three-level namespace catalog.schema.object.
  • Compare managed and external tables, including what DROP TABLE does to the data.
  • Explain storage credentials, external locations and volumes.
  • Grant, revoke and inspect privileges with SQL, and explain privilege inheritance and ownership.
  • List the privileges required to read, write and create objects, including the USE CATALOG and USE SCHEMA rule.
  • Explain how views extend access control and why granting on a group is better than granting on a user.

1. The Unity Catalog object model

Unity Catalog is the centralized governance solution for data and AI assets in Databricks. It provides a single place to define access, with an audit trail and lineage, across all workspaces attached to the same metastore.

Level Object Role
1 Metastore Regional top-level governance container. Workspaces attach to a metastore; catalog workspace bindings can further restrict access.
2 Catalog First level of the namespace. Usually models an environment (dev, prod) or a business domain.
3 Schema (database) Groups related objects inside a catalog.
4 Tables, views, volumes, functions, models The objects that hold or describe data and AI assets.

Schema-level data objects have a three-level namespace; shorter names resolve against the session context:

SQL
SELECT * FROM prod.sales.orders;   -- catalog.schema.table
USE CATALOG prod;
USE SCHEMA sales;
SELECT * FROM orders;              -- resolved with the current catalog and schema

Besides data objects, Unity Catalog also governs storage credentials, external locations, connections (for query federation) and shares (Delta Sharing). Identities are users, service principals and groups, all managed at the account level, so that the same group works in every workspace.

Concrete example. A retailer has the catalogs dev, staging and prod. Each has a schema per domain (sales, finance, marketing). The table prod.sales.orders is the same logical object whichever workspace queries it. Data engineers can write to dev.sales, but only the production job's service principal can write to prod.sales.

2. Tables in Unity Catalog: managed and external

Managed tables

A managed table is a table whose data files and metadata are fully managed by Unity Catalog. You do not choose the path: the files are stored in the managed storage location of the schema, the catalog or the metastore (the most specific one that is defined). Managed tables are the default and recommended type, and current Unity Catalog supports managed Delta Lake and Apache Iceberg. The SQL examples here use Delta. Because Unity Catalog controls the files, it can offer extra features: predictive optimization, automatic liquid clustering, automatic statistics and others.

SQL
CREATE TABLE prod.sales.orders (
  order_id BIGINT,
  customer_id BIGINT,
  amount DECIMAL(12,2),
  region STRING,
  customer_email STRING,
  country STRING,
  card_number STRING,
  card_last4 STRING
) USING DELTA;   -- managed: no LOCATION clause

For a managed table, DROP removes its catalog registration and starts the recovery window, 7 days by default. UNDROP TABLE can recover it during that window, subject to eligibility and privileges. Physical files are purged asynchronously after recovery expires, not at an exact seven-day instant. Configurable recovery is Public Preview: catalog/schema settings can shorten it to 0 (no recovery) or extend it to 7–30 days, so inspect the effective setting before relying on recovery. This is separate from VACUUM retention on a live table.

SQL
DROP TABLE prod.sales.orders;
UNDROP TABLE prod.sales.orders;   -- only while the applicable recovery window permits

External tables

An external table stores its data in a cloud storage path that you choose and manage; Unity Catalog manages only the metadata and the permissions. You create one with a LOCATION, and the path must be covered by an external location.

SQL
CREATE TABLE prod.raw.partner_feed (
  id BIGINT,
  payload STRING
)
LOCATION 's3://acme-lake/partner/feed';   -- the same idea applies on Azure (abfss://) and GCP (gs://)

DROP TABLE on an external table removes only the metadata: the files stay in the storage location. External tables fit when other systems must read or write the same files, when the data already exists in a governed location, or when you must control the path for compliance reasons.

Question Managed table External table
Who chooses the storage path? Unity Catalog You (LOCATION)
What does DROP TABLE do to the data? Recovery window defaults to 7 days; asynchronous purge follows it Keeps the files
Predictive optimization Yes No
Typical use Default for lakehouse tables Shared files, existing data, specific path requirements
Required to create CREATE TABLE on the schema (plus USE CATALOG and USE SCHEMA) Also CREATE EXTERNAL TABLE on the external location

Storage credentials and external locations

Unity Catalog uses governed cloud identities rather than requiring analysts to receive raw cloud keys. These controls apply to access mediated by Unity Catalog; independent cloud IAM access to raw files is a separate security boundary.

  • A storage credential wraps a cloud identity (an IAM role, an Azure managed identity or a service account) that can access cloud storage.
  • An external location links a storage path with a storage credential, and has its own privileges.

Privileges on external locations include READ FILES, WRITE FILES and CREATE EXTERNAL TABLE. Creating an external table requires the external-location privilege plus schema creation/usage rights. Reading an already registered table by name uses SELECT and parent usage privileges; its reader does not also need READ FILES on the external location. Granting direct path privileges can expose raw files outside table-level controls, so do not confuse file access with table SELECT.

Volumes

Volumes govern non-tabular files (CSV drops, images, PDFs, model artifacts) in a catalog and schema. They can be managed or external, and they are accessed with a path:

Text
/Volumes/prod/raw/landing/orders/2026-03-14.csv

Reading files needs READ VOLUME; writing needs WRITE VOLUME, with USE CATALOG and USE SCHEMA on the parents. Grant both only when the workflow needs both. Volumes are the recommended way to give governed access to files, replacing direct cloud paths and the legacy DBFS mounts.

3. Privileges and how they are granted

Unity Catalog uses a deny-by-default model: a principal can do nothing on an object until a privilege is granted (or inherited), with ownership providing administrative authority. Effective access can also depend on inherited/group grants, workspace bindings and applicable governance policies. Privileges are granted with GRANT, removed with REVOKE, and inspected with SHOW GRANTS.

SQL
GRANT USE CATALOG ON CATALOG prod TO `analysts`;
GRANT USE SCHEMA  ON SCHEMA  prod.sales TO `analysts`;
GRANT SELECT      ON TABLE   prod.sales.orders TO `analysts`;

REVOKE SELECT ON TABLE prod.sales.orders FROM `analysts`;

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

The main privileges

Privilege On which object Allows
USE CATALOG Catalog Navigate into the catalog. Required to use anything inside it.
USE SCHEMA Schema Navigate into the schema. Required to use anything inside it.
SELECT Table, view (or higher level, inherited) Read the data.
MODIFY Table Broad DML write privilege; operations such as UPDATE/DELETE/MERGE can also need SELECT. Current fine-grained INSERT/UPDATE/DELETE privileges are Beta.
CREATE TABLE Schema Create tables and views in the schema.
CREATE SCHEMA Catalog Create schemas in the catalog.
EXECUTE Function, model Call a function or use a model.
READ VOLUME / WRITE VOLUME Volume Read or write files.
BROWSE Catalog (discovery of eligible child objects) See an object in the catalog explorer and its metadata, without reading data.
MANAGE Supported securable Administer privileges/ownership and supported delete operations; does not automatically grant SELECT or MODIFY. Parent usage rights are still needed.
ALL PRIVILEGES Supported securable Broad implied applicable privileges, excluding MANAGE, READ METADATA, EXTERNAL USE SCHEMA and EXTERNAL USE LOCATION in the current reference.

The two "USE" privileges

The most frequent real-world error and exam trap: to query prod.sales.orders a principal needs three things, not one:

  1. USE CATALOG on prod,
  2. USE SCHEMA on prod.sales,
  3. SELECT on the table.

Having SELECT on a table without USE CATALOG and USE SCHEMA on its parents fails. The USE privileges do not give access to data; they only allow navigation.

Inheritance

Privileges are inherited downward. An inheritable privilege granted on a catalog/schema applies to its existing and future eligible descendants, not to unrelated operations on every object type. Most metastore-level privileges do not inherit in this way.

SQL
GRANT SELECT ON SCHEMA prod.sales TO `analysts`;   -- includes tables created later

This is powerful and economical, but it must be used with care: granting SELECT on a catalog exposes everything in it, including sensitive tables added tomorrow. The rule of thumb is to grant at the lowest level that matches the need, and to isolate sensitive data in its own schema or catalog.

Ownership

Every securable object has an owner, by default the principal that created it. Owners normally hold administrative authority and can grant privileges, subject to applicable policy restrictions. Owning a catalog or schema does not make you the owner of the objects below it: ownership is not inherited, only privileges are. A catalog/schema owner can manage privileges on child objects even without being their recorded owner; lack of inherited ownership does not remove that authority. Ownership can be transferred, and best practice is to make a group or a service principal the owner of production objects, so that the object does not depend on one person's account.

SQL
ALTER TABLE prod.sales.orders OWNER TO `data-platform-admins`;

Grant to groups, not individuals

Granting to groups (managed in the account, usually synchronized from the identity provider) keeps access reviewable: when someone changes team, you change group membership, not hundreds of grants. Granting directly to users creates privileges that nobody remembers.

Who can grant

You can grant a privilege on an object if you are its owner, if you hold MANAGE on it (or on a parent), if you own the containing catalog/schema, or if you are the metastore admin. Do not assume every workspace or account administrator automatically has all table privileges. Having SELECT does not let you pass SELECT on to others.

4. Views and controlled sharing

A view is a saved query. The example assumes supported Unity Catalog compute; older dedicated runtimes have different view permission checks, so use current SQL warehouses or compatible runtime/access modes. In Unity Catalog, a view has an important property: the owner of the view needs the privileges on the underlying tables, but a consumer needs only SELECT on the view (plus USE CATALOG and USE SCHEMA on where the view lives). Consumers do not need access to the base tables. This makes views a classic way to expose a subset of columns or rows.

SQL
CREATE VIEW prod.sales.orders_eu AS
SELECT order_id, customer_id, amount
FROM prod.sales.orders
WHERE region = 'EU';

GRANT SELECT ON VIEW prod.sales.orders_eu TO `eu-analysts`;

A dynamic view adds identity-based logic with the built-in functions is_account_group_member() or current_user():

SQL
CREATE VIEW prod.sales.orders_masked AS
SELECT
  order_id,
  CASE WHEN is_account_group_member('pii-readers') THEN customer_email
       ELSE '*****' END AS email,
  amount
FROM prod.sales.orders;

Dynamic views remain useful for curated or joined data without granting base-table access; a view can combine multiple tables. The row filters, column masks and ABAC lesson explains reusable table protections and tag-driven policies. Restricting base-table SELECT is essential when the view is the intended access boundary.

Temporary views are session-scoped and not governed objects: they disappear when the session ends and are not visible to other users.

REVOKE is not a Unity Catalog DENY override

Revoking a direct grant does not remove access inherited from another group or parent. Inspect all effective grant paths before concluding access is gone. The SQL DENY privilege ON object statement documented for hive_metastore is not supported by Unity Catalog. For example, this legacy syntax must not be copied onto a UC table:

SQL
-- Legacy hive_metastore table access control only, not a Unity Catalog grant.
DENY SELECT ON TABLE hive_metastore.legacy.orders TO `analysts`;

Unity Catalog has separate ABAC DENY policies (Beta); the currently documented denied privilege is MANAGE ACCESS CONTROL, not a generic DENY SELECT mechanism. Keep these scopes distinct when reading the exam outline or older tutorials.

Converting an external Delta table to managed

For an eligible external Delta table, current guidance supports ALTER TABLE prod.raw.partner_feed SET MANAGED with table ownership and DBR 17.3 LTS+ or supported serverless compute. Verify client compatibility, managed storage and streaming restart requirements first. This retains table identity/history while moving lifecycle management to Unity Catalog; an ordinary CTAS copy does not automatically preserve every grant or history entry.

A recently converted external table can be rolled back with UNSET MANAGED within the documented 14-day conversion rollback window. This is not a universal conversion command for any long-existing managed table. Conversion with Iceberg reads and foreign-table conversion have distinct syntax and prerequisites; use their current reference rather than applying this simplified external-Delta example blindly.

5. Discovering, auditing and tracing data

Governance is more than permissions:

  • Audit logs and system tables record who accessed what (for example access to tables and the audit events of the account), so that security teams can answer "who read this table last week?".
  • Lineage captures table-level and column-level relationships from queries and jobs across notebooks, jobs and dashboards, which helps with impact analysis ("which dashboards break if I drop this column?").
  • Tags and comments document objects and help discovery; BROWSE lets users find assets without reading them.
  • Delta Sharing lets you share governed tables with other organizations or platforms without copying data.

Worked example: designing least-privilege access for a new domain

Scenario. A new schema prod.finance contains three tables: invoices, payments and salaries. Requirements: the group finance-analysts reads invoices and payments; the group hr-admins reads salaries; a service principal etl-finance loads invoices and payments; other ordinary business groups must not receive data grants. Administrative authority and metadata discovery are separate from this data-access requirement. The platform team wants new finance tables to be readable by analysts automatically but not the salary data.

Step 1: Isolate the sensitive table. Granting SELECT on the whole schema to finance-analysts would include salaries and any future table. Move salaries to a separate schema, prod.hr.

Step 2: Grant navigation. These snippets assume the schemas/groups already exist and the sensitive table has been migrated and validated in prod.hr. Execute one GRANT per principal; a comma-separated list of privileges is allowed, but the TO clause takes one principal here. The illustrative service principal application id below represents etl-finance; replace it with the real application id, not its display name.

SQL
GRANT USE CATALOG ON CATALOG prod TO `finance-analysts`;
GRANT USE CATALOG ON CATALOG prod TO `hr-admins`;
GRANT USE CATALOG ON CATALOG prod TO `11111111-2222-3333-4444-555555555555`;
GRANT USE SCHEMA ON SCHEMA prod.finance TO `finance-analysts`;
GRANT USE SCHEMA ON SCHEMA prod.finance TO `11111111-2222-3333-4444-555555555555`;
GRANT USE SCHEMA ON SCHEMA prod.hr TO `hr-admins`;

Step 3: Grant data access at the schema level (inheritance).

SQL
GRANT SELECT ON SCHEMA prod.finance TO `finance-analysts`;
GRANT SELECT, MODIFY ON SCHEMA prod.finance TO `11111111-2222-3333-4444-555555555555`;
GRANT SELECT ON SCHEMA prod.hr TO `hr-admins`;

Step 4: Verify.

SQL
SHOW GRANTS ON SCHEMA prod.finance;

Verify the effective group membership and inherited grants using representative identities, including an ordinary non-member. SHOW GRANTS alone is not a test of row policies or cloud IAM. The schema-level ETL grant is appropriate only if the service principal should load all present/future finance tables; otherwise grant on invoices and payments individually.

Step 5: Ownership. Transfer schema ownership to data-platform-admins; inspect existing child-table owners separately rather than assuming they changed too.

Why the alternatives are worse. Granting SELECT on the catalog to analysts exposes the HR schema. Granting table by table works but forgets new tables. Giving ALL PRIVILEGES to the ETL service principal violates least privilege: it only needs SELECT and MODIFY. Granting to individual users makes later audits painful.

Common pitfalls and exam tips

  • A query needs USE CATALOG + USE SCHEMA + SELECT. Missing a USE privilege is the most common reason for a permission error.
  • Privileges are inherited down (catalog -> schema -> table) and apply to future objects; ownership is not inherited.
  • DROP TABLE on a managed table has a default 7-day recovery window before asynchronous purge; check configured recovery and UNDROP eligibility; on an external table the files stay.
  • External tables need an external location and CREATE EXTERNAL TABLE; Unity Catalog does not hand cloud keys to users.
  • Managed tables are the default and the only ones benefiting from predictive optimization.
  • MODIFY is the broad DML write privilege; current fine-grained INSERT/UPDATE/DELETE privileges are Beta. SELECT alone does not grant DML writes.
  • ALL PRIVILEGES is rarely the right answer to a least-privilege question.
  • Grant to groups, and make groups or service principals owners of production objects.
  • A view consumer needs SELECT on the view only; the view owner needs access to the sources.

Exam tip: When a question asks for "the minimum privileges for a group to read one table", the answer lists USE CATALOG on the catalog, USE SCHEMA on the schema and SELECT on the table. When it asks about preventing future tables from being exposed, avoid granting at a level higher than needed.

Mini case study

Situation. A data engineer, Priya, gets the error "User does not have USE SCHEMA on schema prod.sales" when running SELECT * FROM prod.sales.orders. A colleague says: "Just give her ALL PRIVILEGES on the catalog." Meanwhile, the team also wants to know what happens to the files if somebody drops the table prod.raw.partner_feed, which is an external table, and prod.sales.orders, which is managed.

Task. Recommend the fix and explain the drop behavior.

Solution.

  1. The error shows a missing navigation privilege, not a missing SELECT. Priya's group (for example data-engineers) should receive USE SCHEMA on prod.sales, and USE CATALOG on prod if it is also missing.
  2. ALL PRIVILEGES on the catalog is far too broad: it would cover every present and future schema. The least-privilege fix is GRANT USE SCHEMA ON SCHEMA prod.sales TO `data-engineers`;, then confirm with SHOW GRANTS.
  3. Dropping prod.raw.partner_feed (external) removes only the metadata; the files stay in the external location. Dropping prod.sales.orders (managed) starts the effective recovery window, 7 days by default; eligible UNDROP recovery is possible before asynchronous purge. Confirm SELECT too: the reported missing USE privilege does not prove that all other required grants exist.

Key terms

  • Unity Catalog: centralized governance for data and AI assets across workspaces.
  • Metastore / catalog / schema: the top three levels of the object hierarchy.
  • Three-level namespace: catalog.schema.object.
  • Managed table: table whose lifecycle is managed by Unity Catalog, with a configurable/default recovery window before purge after DROP.
  • External table: table over a path you manage; DROP removes metadata only.
  • Storage credential / external location: governed link between cloud identities, storage paths and privileges.
  • Volume: governed storage for non-tabular files.
  • Privilege inheritance: grants on a parent apply to its current and future children.
  • Owner: principal holding all privileges on an object; not inherited.
  • Dynamic view: view whose result depends on the identity of the reader.

Summary checklist

  • I can describe the metastore, catalog, schema and object levels.
  • I can explain the difference between managed and external tables and what DROP TABLE does to each.
  • I know that reading a table needs USE CATALOG, USE SCHEMA and SELECT.
  • I can write GRANT, REVOKE and SHOW GRANTS statements.
  • I understand that privileges are inherited and ownership is not.
  • I can use views to expose a subset of data and explain who needs which privilege.
  • I know why grants should target groups and why owners should be groups or service principals.

For the maintained gold tables these privileges protect, see gold objects and data quality.

Official sources