Privileges and securable objects in Unity Catalog

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime check marked yes 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`;