On this page 
Warning:
As of November 24, 2023, CockroachDB v22.1 is no longer supported. 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
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?
