Privileges and securable objects in the Hive metastore

  • Article

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.

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 legacy Hive metastore. If you are using Unity Catalog, see Privileges and securable objects in Unity Catalog.

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