SHOW DEFAULT PRIVILEGES

The SHOW DEFAULT PRIVILEGES lists the for the objects created by 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.

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 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 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