> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# SHOW GRANTS

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

The `SHOW GRANTS` <InternalLink path="sql-statements">statement</InternalLink> lists one of the following:

* The <InternalLink path="security-reference/authorization#sql-users">roles</InternalLink> granted to <InternalLink path="security-reference/authorization#sql-users">users</InternalLink> in a cluster.
* The <InternalLink path="security-reference/authorization#managing-privileges">privileges</InternalLink> <InternalLink path="grant">granted</InternalLink> to <InternalLink path="security-reference/authorization#sql-users">users</InternalLink> on <InternalLink path="create-database">databases</InternalLink>, <InternalLink path="create-function">user-defined functions</InternalLink>, <InternalLink path="create-schema">schemas</InternalLink>, <InternalLink path="create-table">tables</InternalLink>, <InternalLink path="enum">user-defined types</InternalLink>, or <InternalLink path="create-external-connection">external connections</InternalLink>.

## Syntax

### Show privilege grants

Use the following syntax to show the privileges granted to users on database objects:

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW GRANTS [ON [DATABASE | FUNCTION | SCHEMA | TABLE | TYPE | EXTERNAL CONNECTION] <targets...>] [FOR <users...>]
```

When `DATABASE` is omitted, the schema, tables, and types in the <InternalLink path="sql-name-resolution#current-database">current database</InternalLink> are listed.

### Show role grants

Use the following syntax to the show the role grants for users in a cluster.

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW GRANTS ON ROLE [<roles...] [FOR <users...>]
```

## Parameters

| Parameter | Description                                                                                                                                                                                                                                                                              |
| --------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `targets` | A comma-separated list of database, function, schema, table, or user-defined type names.  If the function name is not unique, you must provide the full function signature.  To list the privilege grants for all tables in the current database, you can use `SHOW GRANTS ON TABLE \*`. |
| `users`   | A comma-separated list of the <InternalLink path="security-reference/authorization#sql-users">users</InternalLink> whose privileges or roles you want to show.                                                                                                                           |
| `roles`   | A comma-separated list of the roles whose grants you want to show.                                                                                                                                                                                                                       |

## Response

### Privilege grants

The `SHOW GRANTS ON [DATABASE | FUNCTION | SCHEMA | TABLE | TYPE | EXTERNAL CONNECTION]` statement can return the following fields, depending on the target object specified:

| Field              | Description                                                                                                                                           |
| ------------------ | ----------------------------------------------------------------------------------------------------------------------------------------------------- |
| `database\_name`   | The name of the database.                                                                                                                             |
| `function\_name`   | The name of the user-defined function.                                                                                                                |
| `schema\_name`     | The name of the schema.                                                                                                                               |
| `table\_name`      | The name of the table.                                                                                                                                |
| `type\_name`       | The name of the user-defined type.                                                                                                                    |
| `connection\_name` | The name of the external connection.                                                                                                                  |
| `grantee`          | The name of the user or role that was granted the <InternalLink path="security-reference/authorization#managing-privileges">privilege</InternalLink>. |
| `privilege\_type`  | The name of the privilege.                                                                                                                            |
| `is\_grantable`    | `TRUE` if the grantee has the grant option on the object; `FALSE` if not.                                                                             |

### Role grants

The `SHOW GRANTS ON ROLE` statement returns the following fields:

| Field        | Description                                                                                                           |
| ------------ | --------------------------------------------------------------------------------------------------------------------- |
| `role\_name` | The name of the role.                                                                                                 |
| `member`     | The users in the role.                                                                                                |
| `is\_admin`  | If `true`, the role is an <InternalLink path="security-reference/authorization#role-admin">admin</InternalLink> role. |

## Required privileges

* No <InternalLink path="security-reference/authorization#managing-privileges">privileges</InternalLink> are required to view privileges granted to users.
* For `SHOW GRANTS ON ROLES`, the user must have the <InternalLink path="select-clause">`SELECT`</InternalLink> <InternalLink path="security-reference/authorization#managing-privileges">privilege</InternalLink> on the system table.

## Examples

### Show all grants

To list all grants for all users and roles on the current database and its tables:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW GRANTS;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  database_name |    schema_name     |                 object_name                 | object_type | grantee | privilege_type | is_grantable
----------------+--------------------+---------------------------------------------+-------------+---------+----------------+---------------
...
  movr          | public             | promo_codes                                 | table       | admin   | ALL            |      t
  movr          | public             | promo_codes                                 | table       | root    | ALL            |      t
  movr          | public             | rides                                       | table       | admin   | ALL            |      t
  movr          | public             | rides                                       | table       | root    | ALL            |      t
  movr          | public             | user_promo_codes                            | table       | admin   | ALL            |      t
  movr          | public             | user_promo_codes                            | table       | root    | ALL            |      t
  movr          | public             | users                                       | table       | admin   | ALL            |      t
  movr          | public             | users                                       | table       | root    | ALL            |      t
  movr          | public             | vehicle_location_histories                  | table       | admin   | ALL            |      t
  movr          | public             | vehicle_location_histories                  | table       | root    | ALL            |      t
  movr          | public             | vehicles                                    | table       | admin   | ALL            |      t
  movr          | public             | vehicles                                    | table       | root    | ALL            |      t
(609 rows)
```

### Show a specific user or role's grants

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE USER max WITH PASSWORD roach;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> GRANT ALL ON DATABASE movr TO max WITH GRANT OPTION;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW GRANTS FOR max;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  database_name | schema_name | object_name | object_type | grantee | privilege_type | is_grantable
----------------+-------------+-------------+-------------+---------+----------------+---------------
  movr          | NULL        | NULL        | database    | max     | ALL            |      t
  movr          | public      | NULL        | schema      | public  | CREATE         |      f
  movr          | public      | NULL        | schema      | public  | USAGE          |      f
(3 rows)
```

### Show grants on databases

**Specific database, all users and roles:**

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW GRANTS ON DATABASE movr;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  database_name | grantee | privilege_type | is_grantable
----------------+---------+----------------+---------------
  movr          | admin   | ALL            |      t
  movr          | max     | ALL            |      t
  movr          | public  | CONNECT        |      f
  movr          | root    | ALL            |      t
(4 rows)
```

**Specific database, specific user or role:**

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW GRANTS ON DATABASE movr FOR max;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  database_name | grantee | privilege_type | is_grantable
----------------+---------+----------------+---------------
  movr          | max     | ALL            |      t
  movr          | public  | CONNECT        |      f
(2 rows)
```

### Show grants on tables

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> GRANT ALL ON TABLE users TO max WITH GRANT OPTION;
```

**Specific table, all users and roles:**

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW GRANTS ON TABLE users;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  database_name | schema_name | table_name | grantee | privilege_type | is_grantable
----------------+-------------+------------+---------+----------------+---------------
  movr          | public      | users      | admin   | ALL            |      t
  movr          | public      | users      | max     | ALL            |      t
  movr          | public      | users      | root    | ALL            |      t
(3 rows)
```

**Specific table, specific role or user:**

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW GRANTS ON TABLE users FOR max;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  database_name | schema_name | table_name | grantee | privilege_type | is_grantable
----------------+-------------+------------+---------+----------------+---------------
  movr          | public      | users      | max     | ALL            |      t
(1 row)
```

**All tables, all users and roles:**

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW GRANTS ON TABLE *;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  database_name | schema_name |         table_name         | grantee | privilege_type | is_grantable
----------------+-------------+----------------------------+---------+----------------+---------------
  movr          | public      | promo_codes                | admin   | ALL            |      t
  movr          | public      | promo_codes                | root    | ALL            |      t
  movr          | public      | rides                      | admin   | ALL            |      t
  movr          | public      | rides                      | root    | ALL            |      t
  movr          | public      | user_promo_codes           | admin   | ALL            |      t
  movr          | public      | user_promo_codes           | root    | ALL            |      t
  movr          | public      | users                      | admin   | ALL            |      t
  movr          | public      | users                      | max     | ALL            |      t
  movr          | public      | users                      | root    | ALL            |      t
  movr          | public      | vehicle_location_histories | admin   | ALL            |      t
  movr          | public      | vehicle_location_histories | root    | ALL            |      t
  movr          | public      | vehicles                   | admin   | ALL            |      t
  movr          | public      | vehicles                   | root    | ALL            |      t
(13 rows)
```

**All tables, specific users or roles:**

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW GRANTS ON TABLE * FOR max;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  database_name | schema_name | table_name | grantee | privilege_type | is_grantable
----------------+-------------+------------+---------+----------------+---------------
  movr          | public      | users      | max     | ALL            |      t
(1 row)
```

### Show grants on schemas

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE SCHEMA cockroach_labs;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> GRANT ALL ON SCHEMA cockroach_labs TO max WITH GRANT OPTION;
```

**Specific schema, all users or roles:**

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW GRANTS ON SCHEMA cockroach_labs;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  database_name |  schema_name   | grantee | privilege_type | is_grantable
----------------+----------------+---------+----------------+---------------
  movr          | cockroach_labs | admin   | ALL            |      t
  movr          | cockroach_labs | max     | ALL            |      t
  movr          | cockroach_labs | root    | ALL            |      t
(3 rows)
```

**Specific schema, specific users or roles:**

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW GRANTS ON SCHEMA cockroach_labs FOR max;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  database_name |  schema_name   | grantee | privilege_type | is_grantable
----------------+----------------+---------+----------------+---------------
  movr          | cockroach_labs | max     | ALL            |      t
(1 row)
```

### Show grants on user-defined types

To show privileges on <InternalLink path="create-type">user-defined types</InternalLink>, use the following statements.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TYPE status AS ENUM ('available', 'unavailable');
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> GRANT ALL ON TYPE status TO max WITH GRANT OPTION;
```

**Specific type, all users or roles:**

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW GRANTS ON TYPE status;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  database_name | schema_name | type_name | grantee | privilege_type | is_grantable
----------------+-------------+-----------+---------+----------------+---------------
  movr          | public      | status    | admin   | ALL            |      t
  movr          | public      | status    | max     | ALL            |      t
  movr          | public      | status    | public  | USAGE          |      f
  movr          | public      | status    | root    | ALL            |      t
(4 rows)
```

**Specific type, specific users or roles:**

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW GRANTS ON TYPE status FOR max;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  database_name | schema_name | type_name | grantee | privilege_type | is_grantable
----------------+-------------+-----------+---------+----------------+---------------
  movr          | public      | status    | max     | ALL            |      t
  movr          | public      | status    | public  | USAGE          |      f
(2 rows)
```

### Show grants on user-defined functions

To show the grants defined on the `num_users` function created in <InternalLink path="create-function#create-a-function-that-references-a-table">Create a function that references a table</InternalLink>, run:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW GRANTS ON FUNCTION num_users;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  database_name | schema_name | routine_id | routine_signature | grantee | privilege_type | is_grantable
----------------+-------------+------------+-------------------+---------+----------------+---------------
  movr          | public      |     100118 | num_users()       | admin   | ALL            |      t
  movr          | public      |     100118 | num_users()       | public  | EXECUTE        |      f
  movr          | public      |     100118 | num_users()       | root    | ALL            |      t
(3 rows)
```

### Show all grants on external connections

To show all grants defined on an <InternalLink path="create-external-connection">external connection</InternalLink>, run:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW GRANTS ON EXTERNAL CONNECTION my_backup_bucket;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  connection_name   |  grantee  | privilege_type | is_grantable
--------------------+-----------+----------------+-------------
  my_backup_bucket  | alice     | DROP           |     t
  my_backup_bucket  | alice     | USAGE          |     t
  my_backup_bucket  | max       | DROP           |     f
  my_backup_bucket  | max       | USAGE          |     f
  my_backup_bucket  | root      | ALL            |     f
(5 rows)
```

### Show grants on external connections by user

To show the grants defined on an <InternalLink path="create-external-connection">external connection</InternalLink> for a specific user, run:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW GRANTS ON EXTERNAL CONNECTION my_backup_bucket FOR alice;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  connection_name   |  grantee  | privilege_type | is_grantable
--------------------+-----------+----------------+-------------
  my_backup_bucket  | alice     | DROP           |     t
  my_backup_bucket  | alice     | USAGE          |     t
(2 rows)
```

### Show role memberships

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE ROLE moderator;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> GRANT moderator TO max;
```

**All members of all roles:**

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW GRANTS ON ROLE;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  role_name | member | is_admin
------------+--------+-----------
  admin     | root   |    t
  moderator | max    |    f
(2 rows)
```

**Members of a specific role:**

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW GRANTS ON ROLE moderator;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  role_name | member | is_admin
------------+--------+-----------
  moderator | max    |    f
(1 row)
```

**Roles of a specific user or role:**

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW GRANTS ON ROLE FOR max;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  role_name | member | is_admin
------------+--------+-----------
  moderator | max    |    f
(1 row)
```

## See also

* <InternalLink path="authorization">Authorization</InternalLink>
* <InternalLink path="create-role">`CREATE ROLE`</InternalLink>
* <InternalLink path="drop-role">`DROP ROLE`</InternalLink>
* <InternalLink path="show-roles">`SHOW ROLES`</InternalLink>
* <InternalLink path="grant">`GRANT`</InternalLink>
* <InternalLink path="revoke">`REVOKE`</InternalLink>
* <InternalLink path="show-grants">`SHOW GRANTS`</InternalLink>
* <InternalLink path="security-reference/authorization#create-and-manage-users">Manage Users</InternalLink>
* <InternalLink path="cockroach-commands">`cockroach` Commands Overview</InternalLink>
* <InternalLink path="information-schema">Information Schema</InternalLink>