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

# SHOW DEFAULT PRIVILEGES

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

The `SHOW DEFAULT PRIVILEGES` <InternalLink path="sql-statements">statement</InternalLink> lists the <InternalLink path="security-reference/authorization#default-privileges">default privileges</InternalLink> for the objects created by <InternalLink path="security-reference/authorization#roles">users/roles</InternalLink> in the current database.

## Synopsis

<img src="https://mintcdn.com/cockroachlabs/hRoSoqt0mqHbZVjm/images/sql-diagrams/v23.1/show_default_privileges.svg?fit=max&auto=format&n=hRoSoqt0mqHbZVjm&q=85&s=219c5c3e14e9bf1bd05d73271b42dd72" alt="show_default_privileges syntax diagram" style={{maxWidth: "100%", overflowX: "auto"}} width="759" height="327" data-path="images/sql-diagrams/v23.1/show_default_privileges.svg" />

## 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.
</Note>

## Required privileges

To show default privileges, the user/role must have any <InternalLink path="security-reference/authorization#managing-privileges">privilege</InternalLink> 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 <InternalLink path="security-reference/authorization#privileges">privileges</InternalLink> 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

* <InternalLink path="alter-default-privileges">`ALTER DEFAULT PRIVILEGES`</InternalLink>
* <InternalLink path="show-roles">`SHOW ROLES`</InternalLink>
* <InternalLink path="sql-statements">SQL Statements</InternalLink>
* <InternalLink path="security-reference/authorization#default-privileges">Default Privileges</InternalLink>