Managed and External Tables and Privileges

What it is

Every Unity Catalog table is either managed or external. The difference is who controls the data files: Unity Catalog, or you through a path you supply.

Access to any table is controlled by privileges granted to principals: users, groups and service principals. Privileges follow the hierarchy metastore, catalog, schema, table, and a grant at one level is inherited by everything below it.

How it works

Managed and external tables

Databricks recommends managed tables by default. Unity Catalog then handles the file lifecycle and maintenance.

CREATE TABLE main.sales.orders (id BIGINT, amount DECIMAL(10,2));
CREATE TABLE main.sales.orders_ext (id BIGINT, amount DECIMAL(10,2))
LOCATION 's3://acme-data/sales/orders';

The external table needs an external location covering that path, backed by a storage credential. Creating it needs CREATE EXTERNAL TABLE on the external location.

Modifying and deleting

Both types use the same DDL to modify: ALTER TABLE ... ADD COLUMN, RENAME TO, SET TBLPROPERTIES and OWNER TO. Delta operations such as INSERT, MERGE and OPTIMIZE work on both.

DROP TABLE on a managed table deletes the data as well. UNDROP TABLE recovers a dropped Unity Catalog table within the retention window, 7 days by default.

Converting between types

An external Delta table converts to managed in place, without recreating it.

ALTER TABLE main.sales.orders_ext SET MANAGED;
ALTER TABLE main.sales.orders_ext UNSET MANAGED;

SET MANAGED copies the data into managed storage and switches the table; it needs table ownership and Databricks Runtime 17.3 LTS or above, or serverless compute. UNSET MANAGED rolls the conversion back within 14 days.

There is no documented command that turns a managed table into an external one; UNSET MANAGED only rolls back a recent SET MANAGED. Otherwise, copy the data into a new external table at a LOCATION path, then drop the managed table.

The privilege hierarchy

To query a table, a principal needs all three levels.

Other common privileges are MODIFY (insert, update, delete), CREATE SCHEMA, CREATE TABLE, EXECUTE on functions, MANAGE (grant privileges on the object) and ALL PRIVILEGES. Grant SELECT on a schema and the principal can read every current and future table in it.

GRANT and REVOKE

GRANT USE CATALOG ON CATALOG main TO `analysts`;
GRANT USE SCHEMA, SELECT ON SCHEMA main.sales TO `analysts`;
GRANT MODIFY ON TABLE main.sales.orders TO `<sp-application-id>`;
REVOKE SELECT ON SCHEMA main.sales FROM `analysts`;
SHOW GRANTS ON TABLE main.sales.orders;

Names with special characters go in backticks, and a service principal is named by its application ID. Grant to groups rather than individual users, so access follows team membership.

In the UI, open the object in Catalog Explorer, select the Permissions tab and choose Grant. The owner, the owner of the containing catalog or schema, a user with MANAGE and a metastore admin can grant.

What about DENY

Unity Catalog has no DENY statement. Access is denied unless a privilege is granted, so you remove access with REVOKE.

The SQL DENY command applies only to the legacy hive_metastore catalog and its table access control. Unity Catalog also offers ABAC DENY policies, in Beta, which deny only the MANAGE ACCESS CONTROL privilege on tagged objects.

How the exam asks about this

• "Dropping the table must not delete the files that another tool reads." Use an external table with LOCATION.
• "Analysts must read every table in the sales schema, including future ones." Grant USE CATALOG on the catalog, and USE SCHEMA and SELECT on the schema, to the group.
• "Move an external Delta table under Unity Catalog management without rewriting jobs." ALTER TABLE ... SET MANAGED.