SHOW DEFAULT PRIVILEGES

On this page Carat arrow pointing down

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