Privileges and securable objects (Databricks SQL)

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`;