On this page 
Warning:
CockroachDB v22.1 is no longer supported. For more details, see 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
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
> 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
> 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
> CREATE USER max;
> 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
> CREATE SCHEMA test;
> ALTER DEFAULT PRIVILEGES IN SCHEMA test GRANT SELECT ON TABLES TO max;
> SHOW DEFAULT PRIVILEGES IN SCHEMA test;
role | for_all_roles | object_type | grantee | privilege_type
-------+---------------+-------------+---------+-----------------
demo | false | tables | max | SELECT
(1 row)
See also
Was this helpful?
