On this page 
Warning:
As of June 5, 2024, CockroachDB v22.2 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 GRANTEE name |
Show the default privileges that user name received as a grantee. For more information, see Show default privileges for a grantee. |
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)
Show default privileges for a grantee
To show the default privileges that a user received as a grantee, issue the following statement:
SHOW DEFAULT PRIVILEGES FOR GRANTEE root;
role | for_all_roles | object_type | grantee | privilege_type | is_grantable
-------+---------------+-------------+---------+----------------+---------------
root | f | routines | root | ALL | t
root | f | schemas | root | ALL | t
root | f | sequences | root | ALL | t
root | f | tables | root | ALL | t
root | f | types | root | ALL | t
(5 rows)
See also
Was this helpful?
