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

# ALTER USER

The `ALTER USER` [statement](/docs/stable/sql-statements.md) can be used to add, change, or remove a [user's](/docs/stable/create-user.md) 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 [`ALTER ROLE`](/docs/stable/alter-role).

## 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 [`CREATEROLE`](/docs/stable/create-role#create-a-role-that-can-create-other-roles-and-manage-authentication-methods-for-the-new-roles) [role option](#role-options).

## Synopsis

See [`ALTER ROLE`: Synopsis](/docs/stable/alter-role#synopsis).

## Parameters

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

### Role options

| Role option | Description |
| - | - |
|  `BYPASSRLS`/`NOBYPASSRLS` | : Allow or disallow a role to bypass [row-level security (RLS)](/docs/stable/row-level-security.md) policies on a table. This option controls the access from an RLS perspective only; the user also needs sufficient [`GRANT`](/docs/stable/grant) privileges to read or write to the table. |
| `CANCELQUERY`/`NOCANCELQUERY` | **Deprecated in v22.2: Use the `CANCELQUERY` [system privilege](/docs/stable/security-reference/authorization.md#supported-privileges).** Allow or disallow a role to cancel [queries](/docs/stable/cancel-query.md) and [sessions](/docs/stable/cancel-session.md) 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` [privilege](/docs/stable/security-reference/authorization.md#supported-privileges).** Allow or disallow a role to run [`CREATE CHANGEFEED`](/docs/stable/create-changefeed) 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 [pause](/docs/stable/pause-job.md), [resume](/docs/stable/resume-job.md), and [cancel](/docs/stable/cancel-job.md) 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 [create](/docs/stable/create-database.md) or [rename](/docs/stable/alter-database.md#rename-to) 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 [create](/docs/stable/create-role.md), alter, and [drop](/docs/stable/drop-role.md) 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 [client authentication methods](/docs/stable/authentication.md#client-authentication). 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 [cluster settings](/docs/stable/cluster-settings.md) 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 [authenticate their access to a secure cluster](/docs/stable/authentication.md#client-authentication). A password should be entered as a [string literal](/docs/stable/sql-constants.md#string-literals). For compatibility with PostgreSQL, a password can also be entered as an identifier.  To prevent a role from using [password authentication](/docs/stable/authentication.md#client-authentication) and to mandate [certificate-based client authentication](/docs/stable/authentication.md#client-authentication), [set the password as `NULL`](/docs/stable/create-role#prevent-a-role-from-using-password-authentication). |
| `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 [LDAP](/docs/stable/ldap-authentication.md#option-1-automatic-user-provisioning-recommended), [OIDC](/docs/stable/sso-db-console.md#step-3-configure-user-creation), or [JWT](/docs/stable/sso-sql.md#configure-user-provisioning) 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` [system privilege](/docs/stable/security-reference/authorization.md#supported-privileges).** Allow or disallow a role to log in using the SQL CLI with one of the [client authentication methods](/docs/stable/authentication.md#client-authentication). 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 [`timestamp`](/docs/stable/timestamp) format) after which the [password](#parameters) is not valid. |
| `VIEWACTIVITY`/`NOVIEWACTIVITY` | **Deprecated in v22.2: Use the `VIEWACTIVITY` [system privilege](/docs/stable/security-reference/authorization.md#supported-privileges).** Allow or disallow a role to see other roles' [queries](/docs/stable/show-statements.md) and [sessions](/docs/stable/show-sessions.md) using `SHOW STATEMENTS`, `SHOW SESSIONS`, and the [**Statements**](/docs/stable/ui-statements-page.md) and [**Transactions**](/docs/stable/ui-transactions-page.md) 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` [system privilege](/docs/stable/security-reference/authorization.md#supported-privileges).** Allow or disallow a role to view the [cluster settings](/docs/stable/cluster-settings.md) with `SHOW CLUSTER SETTING` or to access the [**Cluster Settings**](/docs/stable/ui-debug-pages.md) 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` [system privilege](/docs/stable/security-reference/authorization.md#supported-privileges).** 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 [constants](/docs/stable/sql-constants.md) for other users when using the `listSessions` endpoint through the [Cluster API](/docs/stable/cluster-api.md). 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.

### 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 [client authentication](/docs/stable/authentication.md#client-authentication):

```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 [create other users](/docs/stable/create-user.md) and [manage authentication methods](/docs/stable/authentication.md#client-authentication) 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 [create](/docs/stable/create-database.md) or [rename](/docs/stable/alter-database.md#rename-to) 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 [pause](/docs/stable/pause-job.md), [resume](/docs/stable/resume-job.md), and [cancel](/docs/stable/cancel-job.md) 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 [queries](/docs/stable/cancel-query.md) and [sessions](/docs/stable/cancel-session.md) 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 [`CREATE CHANGEFEED`](/docs/stable/create-changefeed):

```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 [cluster settings](/docs/stable/cluster-settings.md):

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

## See also

* [`DROP USER`](/docs/stable/drop-user)
* [`SHOW USERS`](/docs/stable/show-users)
* [`GRANT`](/docs/stable/grant)
* [`SHOW GRANTS`](/docs/stable/show-grants)
* [Create Security Certificates](/docs/stable/cockroach-cert.md)
* [SQL Statements](/docs/stable/sql-statements.md)