Privileges and securable objects in Unity Catalog

Applies to: Databricks SQL Databricks Runtime Unity Catalog only

A privilege is a right granted to a principal to operate on a securable object in the metastore. The privilege model and securable objects differ depending on whether you are using a Unity Catalog metastore or the legacy Hive metastore. This article describes the privilege model for the Unity Catalog. If you are using the Hive metastore, see Privileges and securable objects in the Hive metastore

Note

This article refers to the Unity Catalog privileges and inheritance model in Privilege Model version 1.0. If you created your Unity Catalog metastore during the public preview (before August 25, 2022), upgrade to Privilege Model version 1.0 by following Upgrade to privilege inheritance.

Securable objects

A securable object is an object defined in the Unity Catalog metastore on which privileges can be granted to a principal. To manage privileges on any object, you must be its owner.

Syntax

securable_object
  { CATALOG [ catalog_name ] |
    EXTERNAL LOCATION location_name |
    FUNCTION function_name |
    METASTORE |
    { SCHEMA | DATABASE } schema_name |
    SHARE share_name |
    STORAGE CREDENTIAL credential_name |
    [ TABLE ] table_name |
    VIEW view_name
    }

Parameters

• CATALOG catalog_name

  Controls access to the entire data catalog.

• EXTERNAL LOCATION location_name

  Controls access to an external location.

• FUNCTION function_name

  Controls access to a user defined function.

• METASTORE

  Controls access to the Unity Catalog metastore attached to the workspace. When you manage privileges on a metastore, you do not include the metastore name in a SQL command. Unity Catalog will grant or revoke the privilege on the metastore attached to your workspace.

• { SCHEMA | DATABASE } schema_name

  Controls access to a schema.

• STORAGE CREDENTIAL credential_name

  Controls access to a storage credential.

• SHARE share_name

  Controls access on a share to a recipient.

• [ TABLE ] table_name

  Controls access to a managed or external table. If the table cannot be found Databricks raises a TABLE_OR_VIEW_NOT_FOUND error.

• VIEW view_name

  Controls access to a view. If the view cannot be found Databricks raises a TABLE_OR_VIEW_NOT_FOUND error.

Inheritance model

Securable objects in Unity Catalog are hierarchical, and privileges are inherited downward. This means that granting a privilege on the catalog automatically grants the privilege to all current and future schemas in the catalog. Similarly, privileges granted on a schema are inherited by all current and future tables and views in that schema.

For example, if you grant the SELECT privilege on a schema to a user, the user automatically is granted the SELECT privilege on all current and future tables, views, and materialized views in the schema.

Privilege types

The following table shows which Unity Catalog privileges are associated with which Unity Catalog securable objects.

Securable	Privileges
Metastore	CREATE CATALOG, CREATE EXTERNAL LOCATION, CREATE RECIPIENT, CREATE SHARE, CREATE PROVIDER, USE PROVIDER, USE SHARE, USE RECIPIENT, SET SHARE PERMISSION
Catalog	USE CATALOG, CREATE SCHEMA Privileges for securable objects within a catalog can be granted at the catalog level.
Schema	USE SCHEMA, CREATE TABLE, CREATE FUNCTION Privileges for securable objects within a schema can be granted at the schema level.
Table	SELECT, MODIFY
View	SELECT
External location	CREATE EXTERNAL TABLE, READ FILES, WRITE FILES, CREATE MANAGED STORAGE
Storage credential	CREATE EXTERNAL TABLE, READ FILES, WRITE FILES, CREATE EXTERNAL LOCATION
Function	EXECUTE
Share	SELECT (can be granted to RECIPIENT)
Recipient	None.
Provider	None.

• ALL PRIVILEGES

  Used to grant or revoke all privileges applicable to the securable and its child objects without explicitly specifying them. This expands to all available privileges at the time permissions checks are made.

• CREATE CATALOG

  Create catalogs in a Unity Catalog metastore.

• CREATE EXTERNAL LOCATION

  Create an external location using the storage credential. When applied to a storage credential, allows a user to create an external location using the storage credential. This privilege also needs to be granted to a user on the metastore to allow them to create an external location in that metastore.

• CREATE EXTERNAL TABLE

  Create external tables using the storage credential or external location.

• CREATE FUNCTION

  Create a function in a schema. The user also requires the USE CATALOG privilege on the catalog and the USE SCHEMA privilege on the schema.

• CREATE MANAGED STORAGE

  Allows a user to specify a location for storing managed tables at the catalog or schema level, overriding the default root storage for the Unity Catalog metastore.

• CREATE PROVIDER

  (For Delta Sharing data recipients) Create a provider in a Unity Catalog metastore.

• CREATE RECIPIENT

  (For Delta Sharing data providers) Create a recipient in a Unity Catalog metastore.

• CREATE SCHEMA

  Create a schema in a catalog. The user also requires the USE CATALOG privilege on the catalog.

• CREATE SHARE

  (For Delta Sharing data providers) Create a share in a Unity Catalog metastore.

• CREATE TABLE

  Create a table or view in a schema. The user also requires the USE CATALOG privilege on the catalog and the USE SCHEMA privilege on the schema. To create an external table, the user also requires the CREATE EXTERNAL TABLE privilege on the external location and storage credential.

• EXECUTE

  Invoke a user defined function. The user also requires the USE CATALOG privilege on the catalog and the USE SCHEMA privilege on the schema.

• MODIFY

  COPY INTO, UPDATE DELETE, INSERT, or MERGE INTO the table.

• READ_METADATA

  Discover the securable object in SHOW and interrogate the object in DESCRIBE

• READ FILES

  Query files directly using the storage credential or external location.

• SELECT

  Query a table or view, invoke a user defined or anonymous function, or select ANY FILE. The user needs SELECT on the table, view, or function, as well as USE CATALOG on the object’s catalog and USE SCHEMA on the object’s schema.

• SET SHARE PERMISSION

  In Delta Sharing, this permission, combined with USE SHARE and USE RECIPIENT (or recipient ownership), gives a provider user the ability to grant a recipient access to a share. Combined with USE SHARE, it gives the ability to transfer ownership of a share to another user, group, or service principal.

• USE CATALOG

  Required, but not sufficient to reference any objects in a catalog. The principal also needs to have privileges on the individual securable objects.

• USE PROVIDER

  In Delta Sharing, gives a recipient user read-only access to all providers in a recipient metastore and their shares. Combined with the CREATE CATALOG privilege, this privilege allows a recipient user who is not a metastore admin to mount a share as a catalog. This enables you to limit the number of users with the powerful metastore admin role.

• USE RECIPIENT

  In Delta Sharing, gives a provider user read-only access to all recipients in a provider metastore and their shares. This allows a provider user who is not a metastore admin to view recipient details, recipient authentication status, and the list of shares that the provider has shared with the recipient.

  In Databricks Marketplace, this gives provider users the ability to view listings and consumer requests in the Provider console.

• USE SCHEMA

  Required, but not sufficient to reference any objects in a schema. The principal also needs to have privileges on the individual securable objects.

• USE SHARE

  In Delta Sharing, gives a provider user read-only access to all shares defined in a provider metastore. This allows a provider user who is not a metastore admin to list shares and list the assets (tables and notebooks) in a share, along with the share’s recipients.

  In Databricks Marketplace, this gives provider users the ability to view details about the data shared in a listing.

• WRITE FILES

  Directly COPY INTO files governed by the storage credential or external location.

Examples

-- Grant a privilege to the user alf@melmak.et
> GRANT SELECT ON TABLE t TO `alf@melmak.et`;

-- Revoke a privilege from the general public group.
> REVOKE USE SCHEMA ON SCHEMA some_schema FROM `alf@melmak.et`;

Related

• GRANT

• Principal

• REVOKE