Privileges and securable objects

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, CREATE_NAMED_FUNCTION, 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 other than external user defined functions (UDF) within the catalog or schema.

  • CREATE_NAMED_FUNCTION

    Create external user defined functions 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.

  • MODIFY_CLASSPATH

    Add files to the Spark class path to create named non-SQL functions.

  • 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

ANONYMOUS FUNCTION

ANY FILE

CATALOG

SCHEMA

EXTERNAL LOCATION

FUNCTION

Storage credential

TABLE

VIEW

CREATE

Yes

Yes

CREATE_NAMED_FUNCTION

Yes

Yes

CREATE TABLE

Yes

Yes

MODIFY

Yes

HMS

HMS

Yes

MODIFY_CLASSPATH

Yes

READ_METADATA

HMS

HMS

HMS

HMS

READ FILES

Yes

Yes

SELECT

Yes

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