> ## 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 SESSION VARIABLES FOR ROLE

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 SESSION VARIABLES FOR ROLE` <InternalLink path="sql-statements">statement</InternalLink> lists the values for updated <InternalLink path="set-vars">session variables</InternalLink> that are applied to a given <InternalLink path="security-reference/authorization#roles">user or role</InternalLink>.

The results returned only include the values of session variables that are changed from the defaults. When no session variables have been changed from the defaults for a given role, the statement [returns no values](#output-when-no-session-variables-have-been-changed).

## Synopsis

<img src="https://mintcdn.com/cockroachlabs/M1Nto-joXUTgisRs/images/sql-diagrams/v25.3/show_default_session_variables_for_role.svg?fit=max&auto=format&n=M1Nto-joXUTgisRs&q=85&s=2c9dddc86ff38548cd0fddcbb6034f2d" alt="show_default_session_variables_for_role syntax diagram" style={{maxWidth: "100%", overflowX: "auto"}} width="505" height="191" data-path="images/sql-diagrams/v25.3/show_default_session_variables_for_role.svg" />

## Parameters

| Parameter                     | Description                                                                                                                                                                                                              |
| ----------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `FOR {role_or_group_or_user}` | The <InternalLink path="security-reference/authorization#roles">user, group, or role</InternalLink> whose updated <InternalLink path="session-variables">session variables</InternalLink> should be displayed.           |
| `FOR ROLE ALL`                | Denotes that changes to default <InternalLink path="session-variables">session variables</InternalLink> across all <InternalLink path="security-reference/authorization#roles">roles</InternalLink> should be displayed. |
| `FOR USER ALL`                | Alias for `FOR ROLE ALL`.                                                                                                                                                                                                |

## Response

| Column               | Description                                                                                                                                                                                                      |
| -------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `session_variables`  | The name of the <InternalLink path="session-variables">session variable</InternalLink> that has had its default value changed.                                                                                   |
| `default_values`     | The updated value of the <InternalLink path="session-variables">session variable</InternalLink>.                                                                                                                 |
| `database`           | The <InternalLink path="show-databases">database</InternalLink> where the change to the session variable will be applied.                                                                                        |
| `inherited_globally` | Whether the change to the variable's value is applied to all users. For more information, see <InternalLink path="alter-role#set-default-session-variable-values-for-all-users">`ALTER ROLE ALL`</InternalLink>. |

## Required Privileges

The <InternalLink path="security-reference/authorization#sql-users">user</InternalLink> issuing this statement must have at least one of the following <InternalLink path="security-reference/authorization#privileges">privileges</InternalLink>:

* `CREATEROLE`
* `MODIFYCLUSTERSETTING`
* `MODIFYSQLCLUSTERSETTING`

## Examples

### Output when no session variables have been changed

When no session variables have been changed from the defaults for a given role, the statement returns no values:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW DEFAULT SESSION VARIABLES FOR ROLE public;
```

```
SHOW DEFAULT SESSION VARIABLES FOR ROLE 0
```

Another way of confirming zero rows of output:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM [SHOW DEFAULT SESSION VARIABLES FOR ROLE public];
```

```
  session_variables | default_values | database | inherited_globally
--------------------+----------------+----------+---------------------
(0 rows)
```

### Show changed session variables that apply to a user

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER ROLE ALL SET application_name = 'movr';
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW DEFAULT SESSION VARIABLES FOR ROLE movr_auditor;
```

```
  session_variables | default_values | database | inherited_globally
--------------------+----------------+----------+---------------------
  application_name  | movr           | NULL     |         t
(1 row)
```

### Show changed session variables that apply to a user in different databases

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE DATABASE movr_audit;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER ROLE ALL IN DATABASE movr_audit SET application_name = 'movr_audit';
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW DEFAULT SESSION VARIABLES FOR ROLE movr_auditor;
```

```
  session_variables | default_values |  database  | inherited_globally
--------------------+----------------+------------+---------------------
  application_name  | movr_audit     | movr_audit |         t
  application_name  | movr           | NULL       |         t
(2 rows)
```

### Show updated default session variables that apply to all users

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW DEFAULT SESSION VARIABLES FOR ROLE ALL;
```

```
  session_variables | default_values |  database
--------------------+----------------+-------------
  application_name  | movr_audit     | movr_audit
  application_name  | movr           | NULL
(2 rows)
```

### Get inline help in the SQL shell

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
\h SHOW DEFAULT SESSION VARIABLES FOR ROLE
```

```
Command:     SHOW DEFAULT SESSION VARIABLES FOR ROLE
Description: list default session variables for role
Category:    privileges and security
Syntax:
SHOW DEFAULT SESSION VARIABLES FOR ROLE <name>
```

## See also

* <InternalLink path="alter-role#set-default-session-variable-values-for-all-users">`ALTER ROLE ALL`</InternalLink>
* <InternalLink path="session-variables">Session Variables</InternalLink>
* <InternalLink path="show-vars">`SHOW session variable`</InternalLink>
* <InternalLink path="sql-statements">SQL Statements</InternalLink>
* <InternalLink path="security-reference/authorization#default-privileges">Default Privileges</InternalLink>