Privileges and securable objects in the Hive metastore

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.

Securable objects in the Hive metastore

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
    { ANY FILE |
      CATALOG [ catalog_name ] |
      { SCHEMA | DATABASE } schema_name |
      FUNCTION function_name |
      [ TABLE ] table_name |
      VIEW view_name
      }

Parameters

  • ANY FILE

    Controls access to the underlying filesystem.

  • CATALOG catalog_name

    Controls access to the entire data catalog.

  • { SCHEMA | DATABASE } schema_name

    Controls access to a schema.

  • FUNCTION function_name

    Controls access to a named function.

  • [ TABLE ] table_name

    Controls access to a managed or external table.

  • VIEW view_name

    Controls access to SQL views.

Inheritance model

Securable objects in the Hive metastore are hierarchical and privileges are inherited downward. This means that granting or denying a privilege on the CATALOG automatically grants or denies the privilege to all schemas in the catalog. Similarly, privileges granted on a schema object are inherited by all objects in that schema. This pattern is true for all securable objects.

If you deny a user privileges on a table, the user can’t see the table by attempting to list all tables in the schema. If you deny a user privileges on a schema, the user can’t see that the schema exists by attempting to list all schemas in the catalog.

Privilege types

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

Privilege type

ANONYMOUS FUNCTION

ANY FILE

CATALOG

SCHEMA

FUNCTION

TABLE

VIEW

CREATE

Yes

Yes

MODIFY

Yes

Yes

Yes

Yes

READ_METADATA

Yes

Yes

Yes

Yes

SELECT

Yes

Yes

Yes

Yes

Yes

Yes

Yes

USAGE

Yes

Yes

  • 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

    Create objects within the catalog or schema.

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

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