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_nameControls access to the entire data catalog.
EXTERNAL LOCATION
location_nameControls access to an external location.
FUNCTION
function_nameControls 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_nameControls access to a schema.
STORAGE CREDENTIAL
credential_nameControls access to a storage credential.
SHARE
share_name[ TABLE ]
table_nameControls access to a managed or external table. If the table cannot be found Databricks raises a TABLE_OR_VIEW_NOT_FOUND error.
VIEW
view_nameControls 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 |
|
Catalog |
Privileges for securable objects within a catalog can be granted at the catalog level. |
Schema |
Privileges for securable objects within a schema can be granted at the schema level. |
Table |
|
View |
|
External location |
|
Storage credential |
|
Function |
|
Share |
|
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 theUSE 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 theUSE SCHEMA
privilege on the schema. To create an external table, the user also requires theCREATE 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 theUSE 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 needsSELECT
on the table, view, or function, as well asUSE CATALOG
on the object’s catalog andUSE SCHEMA
on the object’s schema.SET SHARE PERMISSION
In Delta Sharing, this permission, combined with
USE SHARE
andUSE RECIPIENT
(or recipient ownership), gives a provider user the ability to grant a recipient access to a share. Combined withUSE 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.