Fine-grained DML privileges

Beta

This feature is in Beta. Workspace admins can control access to this feature from the Previews page. See Manage Databricks previews.

The fine-grained DML privileges INSERT, UPDATE, and DELETE are least-privilege alternatives to MODIFY on Unity Catalog tables. Grant them to principals that should change table data but not the table's schema or other metadata.

To exercise any of these privileges, a principal must also have the following privileges:

• SELECT on the table
• USE SCHEMA on the parent schema
• USE CATALOG on the parent catalog

Due to privilege inheritance, granting INSERT, UPDATE, or DELETE on a catalog or schema automatically grants the privilege on all current and future tables they contain.

Compute requirements

The fine-grained DML privileges require Databricks Runtime 18.1 or above. They are supported on serverless compute, SQL warehouses, and classic compute in standard access mode. They are not supported on dedicated access mode compute. On unsupported compute, use MODIFY instead.

note

Databricks Runtime 18 is newer than Databricks Runtime 18.0, 18.1, and 18.2. Features that would previously have shipped as a later numbered version now ship as dated updates to Databricks Runtime 18 instead. For details, see About unified release notes.

Relationship to MODIFY

MODIFY is a composite privilege, and INSERT, UPDATE, and DELETE are its children. MODIFY grants full write access to a table's data, while each fine-grained DML privilege grants a subset of that access, so you can delegate specific data-change operations without granting the full MODIFY privilege.

Composite and child privileges are granted and revoked independently: granting MODIFY does not grant INSERT, UPDATE, or DELETE, and revoking MODIFY does not revoke any of them that were granted explicitly. For more details, see Composite and child privileges.

Operations and required privileges

The INSERT, UPDATE, and DELETE privileges cover most DML operations. Other operations continue to require MODIFY.

The following table shows how common operations map to the fine-grained DML privileges. This is not a comprehensive list.

Operation	Required privileges
INSERT INTO	INSERT
INSERT OVERWRITE	INSERT and DELETE
INSERT REPLACE WHERE/USING/ON	INSERT and DELETE
Dynamic partition overwrite	INSERT and DELETE
UPDATE	UPDATE
DELETE	DELETE
MERGE INTO	INSERT, UPDATE, or DELETE, depending on the matched actions
TRUNCATE	DELETE
Operations that change schema or metadata, such as INSERT OVERWRITE with schema changes, INSERT with schema evolution, or MERGE INTO with schema evolution	MODIFY
ALTER TABLE operations, such as ALTER COLUMN, ADD COLUMN, DROP COLUMN, SET TBLPROPERTIES, and UNSET TBLPROPERTIES	MODIFY
Other operations, such as MSCK, OPTIMIZE, and VACUUM	MODIFY

Operation	Required privileges
INSERT INTO	INSERT
INSERT OVERWRITE	INSERT and DELETE
INSERT REPLACE WHERE/USING/ON	INSERT and DELETE
Dynamic partition overwrite	INSERT and DELETE
UPDATE	UPDATE
DELETE	DELETE
MERGE INTO	INSERT, UPDATE, or DELETE, depending on the matched actions
TRUNCATE	DELETE
Operations that change schema or metadata, such as INSERT OVERWRITE with schema changes, INSERT with schema evolution, or MERGE INTO with schema evolution	MODIFY
ALTER TABLE operations, such as ALTER COLUMN, ADD COLUMN, DROP COLUMN, SET TBLPROPERTIES, and UNSET TBLPROPERTIES	MODIFY
Other operations, such as MSCK, OPTIMIZE, and VACUUM	MODIFY

Limitations

• INSERT, UPDATE, and DELETE are only supported for table-based access. Path-based access to external tables requires using the MODIFY privilege.