Manage access to data

Preview

Unity Catalog is in Public Preview. To participate in the preview, contact your Databricks representative.

This article shows how to manage access to objects and data in Unity Catalog. To learn more about data permissions and object ownership, see Data permissions.

Requirements

  • In Databricks, you must be an account admin.

  • Your Databricks account must be on the Premium plan.

  • If necessary, create a metastore.

  • To manage permissions for a table or view you must be a metastore admin, or you must be the table’s owner and have the USAGE permission on its parent catalog and schema.

  • To manage permissions for a schema, you must be a metastore admin, or you must be the schema’s owner and have the USAGE permission on its parent catalog.

  • To manage permissions for a catalog, you must be a metastore admin, or you must be the catalog’s owner.

  • To manage permissions for an external location or storage credential, you must be a metastore admin.

Note

Account-level admins can manage any object in any metastore in the account.

Show grants on an object

A securable object’s owner or a metastore admin can list all grants on the object. Optionally, you can list only the grants for a given principal on the object.

To show all grants on an object, you can use the Databricks SQL Data Explorer or SQL commands.

Use the Databricks SQL Data Explorer

  1. Log in to a workspace that is linked to the metastore.

  2. From the persona switcher, select SQL.

  3. Click Data Icon Data.

  4. Select the object, such as a catalog, schema, table, or view.

  5. Click Permissions.

Use SQL

Use the following syntax. Replace the placeholder values:

  • <securable_type>: The type of the securable object, such as catalog or table.

  • <securable_name>: The name of the securable object.

SHOW GRANTS ON <securable_type> <securable_name>;
display(spark.sql("SHOW GRANTS ON <securable_type> <securable_name>"))
library(SparkR)

display(sql("SHOW GRANTS ON <securable_type> <securable_name>"))
display(spark.sql("SHOW GRANTS ON <securable_type> <securable_name>"))

To show all grants for a given principal on an object, use the following syntax. Replace the placeholder values:

  • <principal>: The email address of an account-level user or the name of an account-level group.

  • <securable_type>: The type of the securable object, such as catalog or table.

  • <securable_name>: The name of the securable object.

SHOW GRANTS <principal> ON <securable_type> <securable_name>;
display(spark.sql("SHOW GRANTS <principal> ON <securable_type> <securable_name>"))
library(SparkR)

display(sql("SHOW GRANTS <principal> ON <securable_type> <securable_name>"))
display(spark.sql("SHOW GRANTS <principal> ON <securable_type> <securable_name>"))

Manage privileges

To manage privileges, you use GRANT and REVOKE statements. Only an object’s owner or a metastore admin can grant privileges on the object and its descendent objects. A built-in account-level group called account users includes all account-level users.

In Unity Catalog, you can grant the following privileges on a securable object:

  • USAGE: This privilege does not grant access to the securable itself, but allows the grantee to traverse the securable in order to access its child objects. For example, to select data from a table, users need to have the SELECT privilege on that table and USAGE privileges on its parent schema and parent catalog. Thus, you can use this privilege to restrict access to sections of your data namespace to specific groups.

  • SELECT: Allows a user to select from a table or view, if the user also has USAGE on its parent catalog and schema.

  • MODIFY: Allows the grantee to add, update and delete data to or from the securable if the user also has USAGE on its parent catalog and schema.

  • CREATE: Allows a user to create a schema if the user also has USAGE and CREATE permissions on its parent catalog. Allows a user to create a table or view if the user also has USAGE on its parent catalog and schema and the CREATE permission on the schema..

In addition, you can grant the following privileges on storage credentials and external locations.

  • CREATE TABLE: Allows a user to create external tables directly in your cloud tenant using a storage credential.

  • READ FILES: When granted on an external location, allows a user to read files directly from your cloud tenant using the storage credential associated with the external location.

    When granted directly on a storage credential, allows a user to read files directly from your cloud tenant using the storage credential.

  • WRITE FILES: When granted on an external location, allows a user to write files directly to your cloud tenant using the storage credential associated with the external location.

    When granted directly on a storage credential, allows a user to write files directly to your cloud tenant using the storage credential.

Note

Although you can grant READ FILE and WRITE FILE privileges on a storage credential, Databricks recommends that you instead grant these privileges on an external location. This allows you to manage permissions at a more granular level and provides a simpler experience to end users.

In Unity Catalog, privileges are not inherited on child securable objects. For example, if you grant the CREATE privilege on a catalog to a user, the user does not automatically have the CREATE privilege on all databases in the catalog.

The following table summarizes the privileges that can be granted on each securable object:

Securable

Privileges

Catalog

CREATE, USAGE

Schema

CREATE, USAGE

Table

SELECT, MODIFY

View

SELECT

External location

CREATE TABLE, READ FILES, WRITE FILES

Storage credential

CREATE TABLE, READ FILES, WRITE FILES

Grant a privilege

To grant a privilege, you can use the Databricks SQL Data Explorer or SQL commands. Keep in mind that when you grant a privilege on an object, you must also grant the USAGE privilege on its parent objects.

Use the Databricks SQL Data Explorer

  1. Log in to a workspace that is linked to the metastore.

  2. From the persona switcher, select SQL.

  3. Click Data Icon Data.

  4. Select the object, such as a catalog, schema, table, or view.

  5. Click Permissions.

  6. Click Grant.

  7. Enter the email address for a user or the name of a group.

  8. Select the permissions to grant.

  9. Click OK.

Use SQL

Use the following syntax. Replace the placeholder values:

  • <privilege>: The privilege to grant, such as SELECT or USAGE.

  • <securable_type>: The type of the securable object, such as catalog or table.

  • <securable_name>: The name of the securable object.

  • <principal>: The email address of an account-level user or the name of an account-level group.

GRANT <privilege> ON <securable_type> <securable_name> TO <principal>
spark.sql("GRANT <privilege> ON <securable_type> <securable_name> TO <principal>")
library(SparkR)

sql("GRANT <privilege> ON <securable_type> <securable_name> TO <principal>")
spark.sql("GRANT <privilege> ON <securable_type> <securable_name> TO <principal>")

Revoke a privilege

To revoke a privilege, you can use the Databricks SQL Data Explorer or SQL commands.

Use the Databricks SQL Data Explorer

  1. Log in to a workspace that is linked to the metastore.

  2. From the persona switcher, select SQL.

  3. Click Data Icon Data.

  4. Select the object, such as a catalog, schema, table, or view.

  5. Click Permissions.

  6. Select a privilege that has been granted to a user or group.

  7. Click Revoke.

  8. To confirm, click Revoke.

Use SQL

Use the following syntax. Replace the placeholder values:

  • <privilege>: The privilege to grant, such as SELECT or USAGE.

  • <securable_type>: The type of the securable object, such as catalog or table.

  • <securable_name>: The name of the securable object.

  • <principal>: The email address of an account-level user or the name of an account-level group.

REVOKE <privilege> ON <securable_type> <securable_name> FROM <principal>
spark.sql("REVOKE <privilege> ON <securable_type> <securable_name> FROM <principal>")
library(SparkR)

sql("REVOKE <privilege> ON <securable_type> <securable_name> FROM <principal>")
spark.sql("REVOKE <privilege> ON <securable_type> <securable_name> FROM <principal>")

Transfer ownership

To transfer ownership of an object within a metastore, you can use SQL.

Each securable object in Unity Catalog has an owner. The owner can be any account-level user or group, called a principal. The principal that creates an object becomes its initial owner. An object’s owner has all privileges on the object, such as SELECT and MODIFY on a table, as well as the permission to grant privileges to other principals.

The object’s owner can transfer ownership to another user or group. A metastore admin can transfer ownership of any object in the metastore to another user or group.

To see the owner of a securable object, use the following syntax. Replace the placeholder values:

  • <SECURABLE_TYPE>: The type of securable, such as CATALOG or TABLE.

  • <catalog>: The parent catalog for a table or view.

  • <schema>: The parent schema for a table or view.

  • <securable_name>: The name of the securable, such as a table or view.

DESCRIBE <SECURABLE_TYPE> EXTENDED <catalog>.<schema>.<securable_name>;
display(spark.sql("DESCRIBE <SECURABLE_TYPE> EXTENDED <catalog>.<schema>.<securable_name>"))
library(SparkR)

display(sql("DESCRIBE <SECURABLE_TYPE> EXTENDED <catalog>.<schema>.<securable_name>"))
display(spark.sql("DESCRIBE <SECURABLE_TYPE> EXTENDED <catalog>.<schema>.<securable_name>"))

To transfer ownership of an object, use a SQL command with the following syntax. Replace the placeholder values:

  • <SECURABLE_TYPE>: The type of securable, such as CATALOG or TABLE.

  • <SECURABLE_NAME>: The name of the securable.

  • <PRINCIPAL>: The email address of an account-level user or the name of an account-level group.

ALTER <SECURABLE_TYPE> <SECURABLE_NAME> OWNER TO <PRINCIPAL>;
spark.sql("ALTER <SECURABLE_TYPE> <SECURABLE_NAME> OWNER TO <PRINCIPAL>")
library(SparkR)

sql("ALTER <SECURABLE_TYPE> <SECURABLE_NAME> OWNER TO <PRINCIPAL>")
spark.sql("ALTER <SECURABLE_TYPE> <SECURABLE_NAME> OWNER TO <PRINCIPAL>")

For example, to transfer ownership of a table to the accounting group:

ALTER TABLE orders OWNER TO `accounting`;
spark.sql("ALTER TABLE orders OWNER TO `accounting`")
library(SparkR)

sql("ALTER TABLE orders OWNER TO `accounting`")
spark.sql("ALTER TABLE orders OWNER TO `accounting`")

Transfer ownership of a metastore

When possible, Databricks recommends group ownership of metastores over single-user ownership. The user who creates a metastore is its initial owner and metastore admin. The metastore admin can manage the privileges for all securable objects within a metastore, as well as create catalogs, external locations, and storage credentials.

To transfer the metastore admin role to a group:

  1. Log in to the account console.

  2. Click Data Icon Data.

  3. Click the name of a metastore to open its properties.

  4. Under Owner, click Edit.

  5. Select a group from the drop-down. You can enter text in the field to search for options.

  6. Click Save.

Dynamic views

Dynamic views allow you to manage which users have access to a view’s rows, columns, or even specific records by filtering or masking their values. See Create a dynamic view.