Unity Catalog Managed vs External Tables, GRANT, REVOKE and DENY (Databricks Data Engineer Associate)

A managed table is a Unity Catalog table whose data files live in managed storage that Unity Catalog chooses and controls; an external table is a Unity Catalog table registered over files at a path you name with a LOCATION clause, inside an external location. Both are governed by the same grants. What differs is who owns the files: the catalog, or you.

Databricks recommends managed tables by default. Choose an external table when the files must stay at a known path for tools outside Databricks, when another system keeps writing them, or when the data is in a format managed tables do not support, for example CSV files a vendor drops into a folder every hour. Note that a volume is a different object: it governs files, not a table, so it is not a substitute when users need a governed table to query.

A managed table is created by leaving out the location: CREATE TABLE retail.curated.store_visits (...) or a CTAS such as CREATE TABLE retail.curated.daily_visits AS SELECT .... Delta is the default format, so no USING clause is needed.

Unity Catalog stores the files in the most specific managed location available:

1. the schema's managed location, if the schema has one;
2. otherwise the catalog's managed location;
3. otherwise the metastore's managed storage.

So when one schema in a catalog has its own location and a sibling schema does not, the sibling's tables fall back to the catalog's location rather than failing. To isolate one schema's managed data in a dedicated bucket (for an audit or data-residency requirement) while leaving the rest of the catalog alone, set the location on that schema, not on the catalog:

CREATE SCHEMA retail.restricted
  MANAGED LOCATION 's3://retail-restricted/tables';

Setting a catalog's or schema's managed location needs CREATE MANAGED STORAGE on the external location that covers the path, in addition to the catalog-side privilege (for example CREATE SCHEMA on the catalog). Putting a LOCATION on individual tables instead would make them external tables, not managed ones in a new place.

An external table is created with a LOCATION on a path that an external location covers (an external location pairs a cloud path with a storage credential):

CREATE TABLE retail.landing.supplier_feed (
  supplier_id STRING, shipped_at TIMESTAMP, qty INT)
USING CSV
OPTIONS (header 'true')
LOCATION 's3://retail-landing/supplier-feed/';

Unity Catalog checks two sides before it registers the table:

• Storage side: CREATE EXTERNAL TABLE on the external location (or storage credential) covering the path. Databricks recommends granting it on the external location, because it is scoped to a path.
• Catalog side: USE CATALOG on the catalog, plus USE SCHEMA and CREATE TABLE on the destination schema.

Each use of an external location has its own privilege, and they are easy to confuse:

For least privilege, grant CREATE TABLE on the one schema that will hold the tables. Granting it on the catalog also works but inherits to every schema in it, including the ability to create managed tables elsewhere.

DROP TABLE behaves differently for the two table types. For a managed table, Unity Catalog marks the table dropped and keeps it recoverable for a recovery period (7 days by default, configurable at catalog or schema level, with the schema setting taking precedence); after that, Databricks deletes the data files. For an external table, DROP removes only the metadata: the files at the LOCATION stay in place, and engines that read them by path keep working.

Within the recovery period, UNDROP TABLE restores a dropped table with its data and history:

UNDROP TABLE retail.curated.store_visits;

-- several dropped tables share the name? find the right one
SHOW TABLES DROPPED IN retail.curated;
UNDROP TABLE WITH ID '<table-id>';

Undrop by name restores the most recently dropped table of that name owned by you. If a table was dropped, recreated and dropped again, each is a separate table with its own ID, so use SHOW TABLES DROPPED to find the older one and UNDROP TABLE WITH ID to restore it. Time travel cannot help here: RESTORE and DEEP CLONE need a table that exists, and the recreated table has its own separate history.

Permissions for UNDROP: you must own the table (or hold MANAGE on it) and have CREATE TABLE and USE SCHEMA on the schema and USE CATALOG on the catalog. Undropping an external table re-registers its path, so it also needs CREATE EXTERNAL TABLE on the external location or storage credential.

Once the recovery period has passed, an external table can still be brought back with its full Delta history by creating a table again with LOCATION on the same path, because its files and transaction log were never deleted (unless something outside Databricks removed them).

ALTER TABLE ... SET MANAGED converts an existing external Delta table into a Unity Catalog managed table in place. It copies the data and log into managed storage and keeps the table's name, settings, permissions, dependent views and history, so jobs and dashboards that reference the table by name keep working, and the table starts benefiting from managed-table features such as predictive optimization.

ALTER TABLE retail.landing.pos_events SET MANAGED;

Requirements and behaviour to know:

• Owner only. You must own the table; MANAGE is not enough for an external table.
• Compute: serverless, or Databricks Runtime 17.3 LTS or above. Readers and writers of the table should run Databricks Runtime 15.4 LTS or above.
• Downtime: the copy runs without downtime; writers are briefly blocked (minutes) during the switch.
• Streaming: existing read and write streams on the table fail after the conversion and must be restarted.
• Delta Sharing: Databricks-to-Databricks recipients do not automatically follow the table to its new location; reshare it with ALTER SHARE.

Re-granting privileges or recreating views after the conversion is unnecessary, because both are preserved. A CTAS or deep clone into a new managed table is the wrong tool when grants and history must be kept: each creates a new table without the original's grants (and CTAS has no history).

ALTER TABLE ... UNSET MANAGED rolls a SET MANAGED conversion back to an external table, and it only works within 14 days of that conversion (on serverless or Runtime 17.3 LTS and above). Databricks keeps the source data in the external location during that window, and the rollback keeps writes made after the conversion. Seven days after a rollback, Databricks deletes the copy in managed storage.

Two things UNSET MANAGED is not:

• It is not a general managed-to-external switch. A table that was created managed has no conversion to roll back.
• It is not available after the window. Past 14 days the table is simply a managed table.

In both cases, getting external files at a path means writing the data to a new external table, with a CTAS or a deep clone that specifies a LOCATION under an external location. A shallow clone does not help: it keeps the source's table type. Re-creating a table over the old pre-conversion path is also a trap, because that path stops receiving writes after the conversion and holds stale data, and RESTORE rewinds versions without changing the table type.

Unity Catalog grants privileges to three kinds of principal, each named in backticks after TO or FROM:

• Users, by the email address they sign in with: `dana.ortiz@example.com`. Display names do not resolve.
• Service principals, the non-human identities jobs and pipelines run as, by their application ID (a UUID), not their display name.
• Groups, by name. Unity Catalog accepts account-level groups only; workspace-local groups cannot be used in Unity Catalog grants. Granting to a group lets membership changes drive access with no grant edits.

Securables form a hierarchy: metastore → catalog → schema → table, view, volume, function or model, with storage credentials and external locations as separate securables at the metastore level. The full name of a table is three-level: catalog.schema.table.

Two rules govern how privileges flow through that hierarchy:

• Usage privileges gate everything. To touch any object you need USE CATALOG on its catalog and USE SCHEMA on its schema. SELECT on a table alone fails with a permission error. BROWSE and READ METADATA are discovery privileges and do not substitute.
• Grants inherit downward to current and future child objects. SELECT on a schema covers every table in it, including tables created later; USE SCHEMA granted on a catalog covers every schema in it, including future ones.

Choose the narrowest container that covers the need: a schema grant when a group must read everything in one schema (including new tables) but nothing else; a catalog grant only when every schema, present and future, is in scope; table grants when only specific tables are allowed.

GRANT adds privileges on one securable to one principal, and REVOKE removes a privilege that was granted on that same securable to that same principal:

GRANT USE CATALOG ON CATALOG retail TO `bi-readers`;
GRANT USE SCHEMA, SELECT ON SCHEMA retail.curated TO `bi-readers`;
REVOKE SELECT ON SCHEMA retail.curated FROM `bi-readers`;

Common privilege sets:

MODIFY on its own is not enough to write: Unity Catalog requires SELECT on the table too. For least privilege, grant both on the single target table rather than on its schema, which would open every table in the schema to writes.

The UI equivalent lives in Catalog Explorer: open the catalog, schema, table or other securable, go to its Permissions tab, and use Grant (pick principals and privileges) or select an existing grant and use Revoke. Owners, MANAGE holders and admins can do this. Workspace identity settings manage the principals themselves, and SQL warehouse permissions govern compute use; neither grants data access.

A REVOKE only removes a grant made on that exact object to that exact principal. It does not override access the principal gets from elsewhere, and it succeeds silently when there was nothing to remove. Access can survive a REVOKE through:

• Inheritance: a SELECT granted on the catalog or schema still reaches the table after a table-level REVOKE.
• Group membership: a user who also belongs to a group with access keeps that access after their personal grant is revoked.
• Ownership: an owner's implicit rights are not grants and cannot be revoked.

To actually remove access, change the grant that provides it: replace a broad catalog-level grant with grants on only the schemas the group should read, take the user out of the group, or transfer ownership.

DENY is the official guide's third statement, and it is where many learners slip. DENY belongs to the legacy table access control model and applies only to the hive_metastore catalog: there, a denied privilege overrode any grant, and denying on a schema or database implicitly denied every object in it. Unity Catalog does not support the DENY statement. When workloads move from hive_metastore, a "grant broadly, deny one table" pattern has to be rebuilt as narrow grants that never include the restricted object. Unity Catalog's attribute-based access control (ABAC) policies can also express restrictions centrally; they are covered in the lesson on column masking, row filters and ABAC. If you meet "ABAC DENY policies" in the docs, note that they are a separate, tag-based Beta mechanism (currently limited to the MANAGE ACCESS CONTROL privilege), not a DENY statement: for data privileges such as SELECT, the answer is still to grant narrowly.

Basic table modifications use ALTER TABLE (rename with RENAME TO, add or change columns, set properties, transfer ownership with OWNER TO), and who may run them depends on ownership and the MANAGE privilege. Every securable has one owner, by default its creator, who implicitly holds every capability on it. Those rights are not grants, so REVOKE cannot remove them; only a transfer does:

ALTER TABLE retail.curated.store_visits OWNER TO `retail-platform`;

Transferring ownership to a group is the usual hand-over when someone leaves the team; the old owner keeps no implicit rights on that table afterwards (they keep any access that comes from owning the parent schema or catalog, or from explicit grants). Owners, MANAGE holders and metastore admins can transfer ownership. Three ways of holding broad power over an object are easy to mix up:

MANAGE is the delegation privilege: a data steward can handle access requests, rename or drop tables, all without becoming owner. Granted on a schema or catalog, it extends to child objects, including future ones. Because a MANAGE holder can grant privileges to anyone, they can grant themselves SELECT, so MANAGE is not a data barrier; audit such self-grants if that matters. ALL PRIVILEGES does not include MANAGE.