SHOW GRANTS

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Displays all privileges (inherited, denied, and granted) that affect the securable object.

To run this command you must be either:

  • A workspace administrator or the owner of the object.

  • Have the MANAGE permission on the object, USE CATALOG on the object’s parent, and USE SCHEMA on its parent schema.

  • The user specified in principal.

Use SHOW GRANTS TO RECIPIENT to list which shares a recipient has access to.

Syntax 

SHOW GRANTS [ principal ] ON securable_object

You can also use GRANT as an alternative for GRANTS.

Parameters

  • principal

    An optional user or group for which to show the privileges granted or denied. If not specified SHOW will return privileges for all principals who have privileges on the object. You must enclose users, service principals, and group names with special characters in backticks ( ` ` ).

  • securable_object

    The object whose privileges to show.

Returns

A result set with the following columns:

  • principal STRING NOT NULL: The principal who has the permission.

  • actionType STRING NOT NULL: Which action the principal has permission to perform.

  • objectType STRING : The object which the permission is attached to.

  • objectKey STRING: The identifier of the object.

Example 

> SHOW GRANTS `alf@melmak.et` ON SCHEMA my_schema;
  principal     actionType objectType objectKey
  ------------- ---------- ---------- ---------
  alf@melmak.et USE        DATABASE   my_schema

> SHOW GRANTS ON SHARE some_share;
  recipient actionType objectType objectKey
  --------- ---------- ---------- ---------
  A_Corp    SELECT
  B.com     SELECT

> SHOW GRANTS ON CONNECTION mysql_connection;
  principal     actionType             objectType objectKey
  ------------- ---------------------- ---------- ------------------------------------
  alf@melmak.et CREATE FOREIGN CATALOG CONNECTION mysql_connection
  alf@melmak.et USE CONNECTION         CONNECTION mysql_connection