> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# SHOW DEFAULT PRIVILEGES

The `SHOW DEFAULT PRIVILEGES` [statement](https://docs.cockroachlabs.com/docs/stable/sql-statements.md) lists the [default privileges](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization.md#default-privileges) for the objects created by [users/roles](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization.md#roles) in the current database.

## Synopsis

![show\_default\_privileges syntax diagram](https://mintcdn.com/cockroachlabs/UZNSFbmU2EwEm6G5/images/sql-diagrams/common/show_default_privileges.svg?fit=max\&auto=format\&n=UZNSFbmU2EwEm6G5\&q=85\&s=598bbaf28be8368bd1201ec3c5bff245)

## 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](#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](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization.md#managing-privileges) on the current database.

## Examples

### Show default privileges for objects created by the current user

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE USER max;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE SCHEMA test;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER DEFAULT PRIVILEGES IN SCHEMA test GRANT SELECT ON TABLES TO max;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization.md#privileges) that a user received as a grantee, issue the following statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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

* [`ALTER DEFAULT PRIVILEGES`](https://docs.cockroachlabs.com/docs/stable/alter-default-privileges)
* [`SHOW ROLES`](https://docs.cockroachlabs.com/docs/stable/show-roles)
* [SQL Statements](https://docs.cockroachlabs.com/docs/stable/sql-statements.md)
* [Default Privileges](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization.md#default-privileges)