SHOW DEFAULT PRIVILEGES

New in v21.2: 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 name , ALL ROLES

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)

See also

YesYes NoNo