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

# ALTER USER

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 `ALTER USER` <InternalLink path="sql-statements">statement</InternalLink> can be used to add, change, or remove a <InternalLink path="create-user">user's</InternalLink> password and to change the role options for a user.

You can use the keywords `ROLE` and `USER` interchangeably. `ALTER USER` is an alias for <InternalLink path="alter-role">`ALTER ROLE`</InternalLink>.

## Considerations

* Password creation and alteration is supported only in secure clusters.

## Required privileges

To alter other users, the user must be a member of the `admin` role or have the <InternalLink path="create-role#create-a-role-that-can-create-other-roles-and-manage-authentication-methods-for-the-new-roles">`CREATEROLE`</InternalLink> [role option](#role-options).

## Synopsis

See <InternalLink path="alter-role#synopsis">`ALTER ROLE`: Synopsis</InternalLink>.

## Parameters

| Parameter | Description                                                   |
| --------- | ------------------------------------------------------------- |
| `name`    | The name of the user whose password or role options to alter. |

### Role options

| Role option                                     | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |                                                                                                                                                                                                                                                                                                                                    |
| ----------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| <a id="bypassrls" />                            | `BYPASSRLS`/`NOBYPASSRLS`                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | : Allow or disallow a role to bypass <InternalLink path="row-level-security">row-level security (RLS)</InternalLink> policies on a table. This option controls the access from an RLS perspective only; the user also needs sufficient <InternalLink path="grant">`GRANT`</InternalLink> privileges to read or write to the table. |
| `CANCELQUERY`/`NOCANCELQUERY`                   | **Deprecated in v22.2: Use the `CANCELQUERY`<InternalLink path="security-reference/authorization#supported-privileges">system privilege</InternalLink>.** Allow or disallow a role to cancel <InternalLink path="cancel-query">queries</InternalLink> and <InternalLink path="cancel-session">sessions</InternalLink> of other roles. Without this role option, roles can only cancel their own queries and sessions. Even with the `CANCELQUERY` role option, non-`admin` roles cannot cancel `admin` queries or sessions. This option should usually be combined with `VIEWACTIVITY` so that the role can view other roles' query and session information.  By default, the role option is set to `NOCANCELQUERY` for all non-`admin` roles.                                                                                                                                                                                                                                                                                                                                                                                                               |                                                                                                                                                                                                                                                                                                                                    |
| `CONTROLCHANGEFEED`/`NOCONTROLCHANGEFEED`       | **Deprecated in v23.1: Use the `CHANGEFEED`<InternalLink path="security-reference/authorization#supported-privileges">privilege</InternalLink>.** Allow or disallow a role to run <InternalLink path="create-changefeed">`CREATE CHANGEFEED`</InternalLink> on tables they have `SELECT` privileges on.  By default, the role option is set to `NOCONTROLCHANGEFEED` for all non-`admin` roles.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |                                                                                                                                                                                                                                                                                                                                    |
| `CONTROLJOB`/`NOCONTROLJOB`                     | Allow or disallow a role to <InternalLink path="pause-job">pause</InternalLink>, <InternalLink path="resume-job">resume</InternalLink>, and <InternalLink path="cancel-job">cancel</InternalLink> jobs. Non-`admin` roles cannot control jobs created by `admin` roles.  By default, the role option is set to `NOCONTROLJOB` for all non-`admin` roles.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |                                                                                                                                                                                                                                                                                                                                    |
| `CREATEDB`/`NOCREATEDB`                         | Allow or disallow a role to <InternalLink path="create-database">create</InternalLink> or <InternalLink path="alter-database#rename-to">rename</InternalLink> a database. The role is assigned as the owner of the database.  By default, the role option is set to `NOCREATEDB` for all non-`admin` roles.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |                                                                                                                                                                                                                                                                                                                                    |
| `CREATELOGIN`/`NOCREATELOGIN`                   | Allow or disallow a role to manage authentication using the `WITH PASSWORD`, `VALID UNTIL`, and `LOGIN/NOLOGIN` role options.  By default, the role option is set to `NOCREATELOGIN` for all non-`admin` roles.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |                                                                                                                                                                                                                                                                                                                                    |
| `CREATEROLE`/`NOCREATEROLE`                     | Allow or disallow the new role to <InternalLink path="create-role">create</InternalLink>, alter, and <InternalLink path="drop-role">drop</InternalLink> other non-`admin` roles.  By default, the role option is set to `NOCREATEROLE` for all non-`admin` roles.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |                                                                                                                                                                                                                                                                                                                                    |
| `LOGIN`/`NOLOGIN`                               | Allow or disallow a role to log in with one of the <InternalLink path="authentication#client-authentication">client authentication methods</InternalLink>. Setting the role option to `NOLOGIN` prevents the role from logging in using any authentication method.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |                                                                                                                                                                                                                                                                                                                                    |
| `MODIFYCLUSTERSETTING`/`NOMODIFYCLUSTERSETTING` | Allow or disallow a role to modify the <InternalLink path="cluster-settings">cluster settings</InternalLink> with the `sql.defaults` prefix.  By default, the role option is set to `NOMODIFYCLUSTERSETTING` for all non-`admin` roles.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |                                                                                                                                                                                                                                                                                                                                    |
| `PASSWORD password`/`PASSWORD NULL`             | The credential the role uses to <InternalLink path="authentication#client-authentication">authenticate their access to a secure cluster</InternalLink>. A password should be entered as a <InternalLink path="sql-constants#string-literals">string literal</InternalLink>. For compatibility with PostgreSQL, a password can also be entered as an identifier.  To prevent a role from using <InternalLink path="authentication#client-authentication">password authentication</InternalLink> and to mandate <InternalLink path="authentication#client-authentication">certificate-based client authentication</InternalLink>, <InternalLink path="create-role#prevent-a-role-from-using-password-authentication">set the password as `NULL`</InternalLink>.                                                                                                                                                                                                                                                                                                                                                                                                |                                                                                                                                                                                                                                                                                                                                    |
| `PROVISIONSRC`                                  | A system-managed role option that identifies the authentication method and identity provider that automatically provisioned this user. The format is `{method}:{provider}` where method is `ldap`, `oidc`, or `jwt\_token`. Examples: `ldap:example.com`, `oidc:https://accounts.google.com`, `jwt\_token:https://auth.example.com`. This option is set automatically during <InternalLink path="ldap-authentication#option-1-automatic-user-provisioning-recommended">LDAP</InternalLink>, <InternalLink path="sso-db-console#step-3-configure-user-creation">OIDC</InternalLink>, or <InternalLink path="sso-sql#configure-user-provisioning">JWT</InternalLink> user provisioning and cannot be modified or removed. Users with this option cannot change their own passwords.                                                                                                                                                                                                                                                                                                                                                                            |                                                                                                                                                                                                                                                                                                                                    |
| `SQLLOGIN`/`NOSQLLOGIN`                         | **Deprecated in v22.2: Use the `NOSQLLOGIN`<InternalLink path="security-reference/authorization#supported-privileges">system privilege</InternalLink>.** Allow or disallow a role to log in using the SQL CLI with one of the <InternalLink path="authentication#client-authentication">client authentication methods</InternalLink>. The role option to `NOSQLLOGIN` prevents the role from logging in using the SQL CLI with any authentication method while retaining the ability to log in to DB Console. It is possible to have both `NOSQLLOGIN` and `LOGIN` set for a role and `NOSQLLOGIN` takes precedence on restrictions.  Without any role options all login behavior is permitted.                                                                                                                                                                                                                                                                                                                                                                                                                                                              |                                                                                                                                                                                                                                                                                                                                    |
| `VALID UNTIL`                                   | The date and time (in the <InternalLink path="timestamp">`timestamp`</InternalLink> format) after which the [password](#parameters) is not valid.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |                                                                                                                                                                                                                                                                                                                                    |
| `VIEWACTIVITY`/`NOVIEWACTIVITY`                 | **Deprecated in v22.2: Use the `VIEWACTIVITY`<InternalLink path="security-reference/authorization#supported-privileges">system privilege</InternalLink>.** Allow or disallow a role to see other roles' <InternalLink path="show-statements">queries</InternalLink> and <InternalLink path="show-sessions">sessions</InternalLink> using `SHOW STATEMENTS`, `SHOW SESSIONS`, and the <InternalLink path="ui-statements-page">**Statements**</InternalLink> and <InternalLink path="ui-transactions-page">**Transactions**</InternalLink> pages in the DB Console. `VIEWACTIVITY` also permits visibility of node hostnames and IP addresses in the DB Console. With `NOVIEWACTIVITY`, the `SHOW` commands show only the role's own data, and DB Console pages redact node hostnames and IP addresses.  By default, the role option is set to `NOVIEWACTIVITY` for all non-`admin` roles.                                                                                                                                                                                                                                                                     |                                                                                                                                                                                                                                                                                                                                    |
| `VIEWCLUSTERSETTING` / `NOVIEWCLUSTERSETTING`   | **Deprecated in v22.2: Use the `VIEWCLUSTERSETTING`<InternalLink path="security-reference/authorization#supported-privileges">system privilege</InternalLink>.** Allow or disallow a role to view the <InternalLink path="cluster-settings">cluster settings</InternalLink> with `SHOW CLUSTER SETTING` or to access the <InternalLink path="ui-debug-pages">**Cluster Settings**</InternalLink> page in the DB Console.  By default, the role option is set to `NOVIEWCLUSTERSETTING` for all non-`admin` roles.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |                                                                                                                                                                                                                                                                                                                                    |
| `VIEWACTIVITYREDACTED`/`NOVIEWACTIVITYREDACTED` | **Deprecated in v22.2: Use the `VIEWACTIVITYREDACTED`<InternalLink path="security-reference/authorization#supported-privileges">system privilege</InternalLink>.** Allow or disallow a role to see other roles' queries and sessions using `SHOW STATEMENTS`, `SHOW SESSIONS`, and the Statements and Transactions pages in the DB Console. With `VIEWACTIVITYREDACTED`, a user will not have access to the usage of statements diagnostics bundle (which can contain PII information) in the DB Console, and will not be able to list queries containing <InternalLink path="sql-constants">constants</InternalLink> for other users when using the `listSessions` endpoint through the <InternalLink path="cluster-api">Cluster API</InternalLink>. It is possible to have both `VIEWACTIVITY` and `VIEWACTIVITYREDACTED`, and `VIEWACTIVITYREDACTED` takes precedence on restrictions. If the user has `VIEWACTIVITY` but doesn't have `VIEWACTIVITYREDACTED`, they will be able to see DB Console pages and have access to the statements diagnostics bundle.  By default, the role option is set to `NOVIEWACTIVITYREDACTED` for all non-`admin` roles. |                                                                                                                                                                                                                                                                                                                                    |

## Examples

<Note>
  The following statements are run by the `root` user that is a member of the `admin` role and has `ALL` privileges.
</Note>

### Change a user's password

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> ALTER USER carl WITH PASSWORD 'An0ther$tr0nGpassW0rD' VALID UNTIL '2021-10-10';
```

### Prevent a user from using password authentication

The following statement prevents the user from using password authentication and mandates certificate-based <InternalLink path="authentication#client-authentication">client authentication</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> ALTER USER carl WITH PASSWORD NULL;
```

### Allow a user to create other users and manage authentication methods for the new users

The following example allows the user to <InternalLink path="create-user">create other users</InternalLink> and <InternalLink path="authentication#client-authentication">manage authentication methods</InternalLink> for them:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> ALTER USER carl WITH CREATEROLE CREATELOGIN;
```

### Allow a user to create and rename databases

The following example allows the user to <InternalLink path="create-database">create</InternalLink> or <InternalLink path="alter-database#rename-to">rename</InternalLink> databases:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> ALTER USER carl WITH CREATEDB;
```

### Allow a user to pause, resume, and cancel non-admin jobs

The following example allows the user to <InternalLink path="pause-job">pause</InternalLink>, <InternalLink path="resume-job">resume</InternalLink>, and <InternalLink path="cancel-job">cancel</InternalLink> jobs:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> ALTER USER carl WITH CONTROLJOB;
```

### Allow a user to see and cancel non-admin queries and sessions

The following example allows the user to cancel <InternalLink path="cancel-query">queries</InternalLink> and <InternalLink path="cancel-session">sessions</InternalLink> for other non-`admin` roles:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> ALTER USER carl WITH CANCELQUERY VIEWACTIVITY;
```

### Allow a user to control changefeeds

The following example allows the user to run <InternalLink path="create-changefeed">`CREATE CHANGEFEED`</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> ALTER USER carl WITH CONTROLCHANGEFEED;
```

### Allow a user to modify cluster settings

The following example allows the user to modify <InternalLink path="cluster-settings">cluster settings</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
root@:26257/defaultdb> ALTER USER carl WITH MODIFYCLUSTERSETTING;
```

## See also

* <InternalLink path="drop-user">`DROP USER`</InternalLink>
* <InternalLink path="show-users">`SHOW USERS`</InternalLink>
* <InternalLink path="grant">`GRANT`</InternalLink>
* <InternalLink path="show-grants">`SHOW GRANTS`</InternalLink>
* <InternalLink path="cockroach-cert">Create Security Certificates</InternalLink>
* <InternalLink path="sql-statements">SQL Statements</InternalLink>