> ## 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 {children} ; }; The `SHOW GRANTS` statement lists one of the following: * The roles granted to users in a cluster. * The privileges granted to users on databases, user-defined functions, schemas, tables, user-defined types, or external connections. ## Syntax ### Show privilege grants Use the following syntax to show the privileges granted to users on database objects: ``` SHOW GRANTS [ON [DATABASE | FUNCTION | SCHEMA | TABLE | TYPE | EXTERNAL CONNECTION] ] [FOR ] ``` The object-focused form (`ON ...`) shows only privileges granted directly on the object. It does not resolve privileges inherited through role membership. The principal-focused form (`FOR ...`) includes privileges inherited through role membership for the specified users and can be combined with `ON ...` to filter by object. For an example of inherited grants, refer to [Show direct and inherited grants](#show-direct-and-inherited-grants). For inherited privileges, the `grantee` column reports the role that carries the privilege. To inspect role memberships, use [`SHOW GRANTS ON ROLE`](#show-role-grants) or `SHOW ROLES`. When `DATABASE` is omitted, the schema, tables, and types in the current database are listed. ### Show role grants Use the following syntax to the show the role grants for users in a cluster. ``` SHOW GRANTS ON ROLE [] ``` ## 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 users 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 Results for `SHOW GRANTS ON ...` without `FOR` list only direct grants on the object. Results for `SHOW GRANTS ... FOR ` include privileges inherited through role membership for the specified users, and the `grantee` column can report roles that grant those privileges. 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 the row reports privileges for. When you omit `FOR`, this is the direct grantee on the object and has the listed privilege. When you use `FOR`, this can be a role that grants inherited privileges to the specified users. | | `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 admin role. | ## Required privileges * No privileges are required to view privileges granted to users. * For `SHOW GRANTS ON ROLES`, the user must have the `SELECT` privilege 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; ``` ``` 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; ``` ``` 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; ``` ``` 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; ``` ``` 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; ``` ``` 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; ``` ``` 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 *; ``` ``` 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; ``` ``` database_name | schema_name | table_name | grantee | privilege_type | is_grantable ----------------+-------------+------------+---------+----------------+--------------- movr | public | users | max | ALL | t (1 row) ``` ### Show direct and inherited grants In this example, a role has `ALL` on a table and a user inherits that role. The object-focused form (`SHOW GRANTS ON ...`) lists only the direct role grant, while the principal-focused form (`SHOW GRANTS ... FOR `) lists the inherited privileges for the user. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE ROLE analysts; GRANT ALL ON TABLE users TO analysts; CREATE USER priya; GRANT analysts TO priya; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW GRANTS ON TABLE users; ``` ``` database_name | schema_name | table_name | grantee | privilege_type | is_grantable ----------------+-------------+------------+----------+----------------+--------------- movr | public | users | admin | ALL | t movr | public | users | analysts | ALL | f movr | public | users | root | ALL | t (3 rows) ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW GRANTS ON TABLE users FOR priya; ``` ``` database_name | schema_name | table_name | grantee | privilege_type | is_grantable ----------------+-------------+------------+----------+----------------+--------------- movr | public | users | analysts | ALL | f (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; ``` ``` 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; ``` ``` 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 user-defined types, 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; ``` ``` 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; ``` ``` 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 Create a function that references a table, run: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW GRANTS ON FUNCTION num_users; ``` ``` 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 external connection, run: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW GRANTS ON EXTERNAL CONNECTION my_backup_bucket; ``` ``` 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 external connection for a specific user, run: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW GRANTS ON EXTERNAL CONNECTION my_backup_bucket FOR alice; ``` ``` 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; ``` ``` 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; ``` ``` 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; ``` ``` role_name | member | is_admin ------------+--------+----------- moderator | max | f (1 row) ``` ## See also * Authorization * `CREATE ROLE` * `DROP ROLE` * `SHOW ROLES` * `GRANT` * `REVOKE` * `SHOW GRANTS` * Manage Users * `cockroach` Commands Overview * Information Schema