SHOW DEFAULT PRIVILEGES

On this page Carat arrow pointing down
Warning:
CockroachDB v22.1 is no longer supported as of November 24, 2023. For more details, refer to the Release Support Policy.

The SHOW DEFAULT PRIVILEGES statement lists the default privileges for the objects created by users/roles in the current database.

Synopsis

SHOW DEFAULT PRIVILEGES FOR ROLE USER role_spec_list ALL ROLES IN SCHEMA qualifiable_schema_name ,

Parameters

Parameter Description
FOR ROLE name/FOR USER name List the default privileges on objects created by a specific user/role, or a list of users/roles.
FOR ALL ROLES List the default privileges on objects created by any user/role.
Note:

If you do not specify a FOR ... clause, CockroachDB returns the default privileges on objects created by the current user.

Required privileges

To show default privileges, the user/role must have any privilege on the current database.

Examples

Show default privileges for objects created by the current user

icon/buttons/copy
> SHOW DEFAULT PRIVILEGES;
  role | for_all_roles | object_type | grantee | privilege_type
-------+---------------+-------------+---------+-----------------
  root |     false     | schemas     | root    | ALL
  root |     false     | sequences   | root    | ALL
  root |     false     | tables      | root    | ALL
  root |     false     | types       | public  | USAGE
  root |     false     | types       | root    | ALL
(5 rows)

Show default privileges for objects created by any user/role

icon/buttons/copy
> SHOW DEFAULT PRIVILEGES FOR ALL ROLES;
  role | for_all_roles | object_type | grantee | privilege_type
-------+---------------+-------------+---------+-----------------
  NULL |     true      | types       | public  | USAGE
(1 row)

Show default privileges for objects created by a specific user/role

icon/buttons/copy
> CREATE USER max;
icon/buttons/copy
> SHOW DEFAULT PRIVILEGES FOR ROLE max;
  role | for_all_roles | object_type | grantee | privilege_type
-------+---------------+-------------+---------+-----------------
  max  |     false     | schemas     | max     | ALL
  max  |     false     | sequences   | max     | ALL
  max  |     false     | tables      | max     | ALL
  max  |     false     | types       | max     | ALL
  max  |     false     | types       | public  | USAGE
(5 rows)

Show default privileges for objects in a specific schema

icon/buttons/copy
> CREATE SCHEMA test;
icon/buttons/copy
> ALTER DEFAULT PRIVILEGES IN SCHEMA test GRANT SELECT ON TABLES TO max;
icon/buttons/copy
> SHOW DEFAULT PRIVILEGES IN SCHEMA test;
  role | for_all_roles | object_type | grantee | privilege_type
-------+---------------+-------------+---------+-----------------
  demo |     false     | tables      | max     | SELECT
(1 row)

See also


Yes No
On this page

Yes No