Privileges and securable objects in Unity Catalog

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

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), you can 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 ] |
    SCHEMA | DATABASE } schema_name |
    EXTERNAL LOCATION location_name |
    FUNCTION function_name |
    METASTORE |
    SHARE share_name |
    STORAGE CREDENTIAL credential_name |
    [ TABLE ] table_name |
    VIEW view_name
    }

Parameters

  • CATALOG catalog_name

    Controls access to the entire data catalog.

  • { SCHEMA | DATABASE } schema_name

    Controls access to a schema.

  • 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.

  • 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 SHARE, CREATE RECIPIENT, CREATE PROVIDER

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 VIEW, 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

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 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 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.

  • CREATE VIEW

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

  • 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.

  • 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 SCHEMA

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

  • WRITE FILES

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