Privileges and securable objects (Databricks SQL)

Is this page helpful?

Any additional feedback?

Feedback will be sent to Microsoft: By pressing the submit button, your feedback will be used to improve Microsoft products and services. Privacy policy.

A privilege is a right granted to a principal to operate on a securable object.

Securable objects

A securable object is an object defined in the metastore on which privileges can be granted to a principal.

To manage privileges on any object you must be its owner or an administrator.

Syntax

securable_object
  { ANONYMOUS FUNCTION |
    ANY FILE |
    CATALOG [ catalog_name ] |
    { SCHEMA | DATABASE } schema_name |
    EXTERNAL LOCATION location_name |
    FUNCTION function_name |
    STORAGE CREDENTIAL credential_name |
    [ TABLE ] table_name |
    VIEW view_name }

Parameters

• ANONYMOUS FUNCTION

  You can grant the privilege to SELECT from anonymous functions.

• ANY FILE

  You can grant the privilege to SELECT and MODIFY any file in the filesystem.

• CATALOG catalog_name

  You can grant CREATE, and USAGE on a catalog. The default catalog name is hive_metastore. If the catalog name is hive_metastore you can also grant SELECT, READ_METADATA, and MODIFY to grant these privileges on to any existing and future securable object within the catalog.

• { SCHEMA | DATABASE } schema_name

  You can grant CREATE, CREATE_NAMED_FUNCTION, and USAGE on a schema.

  You can also grant SELECT, READ_METADATA, and MODIFY to grant these privileges on to any existing and future securable object within the catalog.

• EXTERNAL LOCATION location_name

  You can grant CREATE TABLE, READ FILES, and WRITE FILES on an external location.

• FUNCTION function_name

  You can grant SELECT on a user defined function.

• STORAGE CREDENTIAL credential_name

  You can grant CREATE TABLE, READ FILES, and WRITE FILES on a storage credential.

• [ TABLE ] table_name

  You can grant SELECT, and MODIFY on a table.

  For the principal to use SELECT or MODIFY on a table it must also have USAGE privilege on the table’s schema and catalog.

• VIEW view_name

  You can grant SELECT on a view.

  For the principal to use SELECT from a view it must also have USAGE privilege on the view’s schema and catalog.

Privilege types

• CREATE

  Create objects within the catalog or schema.

• CREATE TABLE

  Create external tables using the storage credential or external location.

• MODIFY

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

  If the securable_object is the hive_metastore or a schema within it, granting MODIFY will grant MODIFY on all current and future tables and views within the securable object.

• READ_METADATA

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

  If the securable object is the hive_metastore catalog or a schema within it, granting READ_METADATA will grant READ_METADATA on all current and future tables and views within the securable object.

• 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 USAGE on the object’s schema and catalog.

  If the securable object is the hive_metastore or a schema within it, granting SELECT will grant SELECT on all current and future tables and views within the securable object.

• USAGE

  Required, but not sufficient to reference any objects in a catalog or 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.

Privilege matrix

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

Privilege type	ANY FILE	CATALOG	SCHEMA	EXTERNAL LOCATION	FUNCTION	Storage credential	TABLE	VIEW
CREATE		Yes	Yes
CREATE TABLE				Yes		Yes
MODIFY		HMS	HMS				Yes
READ_METADATA		HMS	HMS				HMS	HMS
READ FILES				Yes		Yes
SELECT	Yes	HMS	HMS		Yes		Yes	Yes
USAGE		Yes	Yes
WRITE FILES				Yes		Yes

HMS This privilege only applies for securable objects in the hive_metastore catalog.

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 USAGE ON SCHEMA some_schema FROM `alf@melmak.et`;

Related

• GRANT
• Principal
• REVOKE