> ## 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](https://docs.cockroachlabs.com/docs/v24.2/sql-statements) can be used to add, change, or remove a [user's](https://docs.cockroachlabs.com/docs/v24.2/create-user) 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`](https://docs.cockroachlabs.com/docs/v24.2/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`](https://docs.cockroachlabs.com/docs/v24.2/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](https://docs.cockroachlabs.com/docs/v24.2/alter-role#synopsis).

## Parameters

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

### Role options

| Role option | Description |
| - | - |
| `CANCELQUERY`/`NOCANCELQUERY` | **Deprecated in v22.2: Use the `CANCELQUERY` [system privilege](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization#supported-privileges).** Allow or disallow a role to cancel [queries](https://docs.cockroachlabs.com/docs/v24.2/cancel-query) and [sessions](https://docs.cockroachlabs.com/docs/v24.2/cancel-session) 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](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization#supported-privileges).** Allow or disallow a role to run [`CREATE CHANGEFEED`](https://docs.cockroachlabs.com/docs/v24.2/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](https://docs.cockroachlabs.com/docs/v24.2/pause-job), [resume](https://docs.cockroachlabs.com/docs/v24.2/resume-job), and [cancel](https://docs.cockroachlabs.com/docs/v24.2/cancel-job) 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](https://docs.cockroachlabs.com/docs/v24.2/create-database) or [rename](https://docs.cockroachlabs.com/docs/v24.2/alter-database#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](https://docs.cockroachlabs.com/docs/v24.2/create-role), alter, and [drop](https://docs.cockroachlabs.com/docs/v24.2/drop-role) 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](https://docs.cockroachlabs.com/docs/v24.2/authentication#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](https://docs.cockroachlabs.com/docs/v24.2/cluster-settings) 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](https://docs.cockroachlabs.com/docs/v24.2/authentication#client-authentication). A password should be entered as a [string literal](https://docs.cockroachlabs.com/docs/v24.2/sql-constants#string-literals). For compatibility with PostgreSQL, a password can also be entered as an identifier.  To prevent a role from using [password authentication](https://docs.cockroachlabs.com/docs/v24.2/authentication#client-authentication) and to mandate [certificate-based client authentication](https://docs.cockroachlabs.com/docs/v24.2/authentication#client-authentication), [set the password as `NULL`](https://docs.cockroachlabs.com/docs/v24.2/create-role#prevent-a-role-from-using-password-authentication). |
| `SQLLOGIN`/`NOSQLLOGIN` | **Deprecated in v22.2: Use the `NOSQLLOGIN` [system privilege](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization#supported-privileges).** Allow or disallow a role to log in using the SQL CLI with one of the [client authentication methods](https://docs.cockroachlabs.com/docs/v24.2/authentication#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`](https://docs.cockroachlabs.com/docs/v24.2/timestamp) format) after which the [password](#parameters) is not valid. |
| `VIEWACTIVITY`/`NOVIEWACTIVITY` | **Deprecated in v22.2: Use the `VIEWACTIVITY` [system privilege](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization#supported-privileges).** Allow or disallow a role to see other roles' [queries](https://docs.cockroachlabs.com/docs/v24.2/show-statements) and [sessions](https://docs.cockroachlabs.com/docs/v24.2/show-sessions) using `SHOW STATEMENTS`, `SHOW SESSIONS`, and the [**Statements**](https://docs.cockroachlabs.com/docs/v24.2/ui-statements-page) and [**Transactions**](https://docs.cockroachlabs.com/docs/v24.2/ui-transactions-page) 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](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization#supported-privileges).** Allow or disallow a role to view the [cluster settings](https://docs.cockroachlabs.com/docs/v24.2/cluster-settings) with `SHOW CLUSTER SETTING` or to access the [**Cluster Settings**](https://docs.cockroachlabs.com/docs/v24.2/ui-debug-pages) 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](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization#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](https://docs.cockroachlabs.com/docs/v24.2/sql-constants) for other users when using the `listSessions` endpoint through the [Cluster API](https://docs.cockroachlabs.com/docs/v24.2/cluster-api). 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](https://docs.cockroachlabs.com/docs/v24.2/authentication#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](https://docs.cockroachlabs.com/docs/v24.2/create-user) and [manage authentication methods](https://docs.cockroachlabs.com/docs/v24.2/authentication#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](https://docs.cockroachlabs.com/docs/v24.2/create-database) or [rename](https://docs.cockroachlabs.com/docs/v24.2/alter-database#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](https://docs.cockroachlabs.com/docs/v24.2/pause-job), [resume](https://docs.cockroachlabs.com/docs/v24.2/resume-job), and [cancel](https://docs.cockroachlabs.com/docs/v24.2/cancel-job) 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](https://docs.cockroachlabs.com/docs/v24.2/cancel-query) and [sessions](https://docs.cockroachlabs.com/docs/v24.2/cancel-session) 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`](https://docs.cockroachlabs.com/docs/v24.2/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](https://docs.cockroachlabs.com/docs/v24.2/cluster-settings):

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

## See also

* [`DROP USER`](https://docs.cockroachlabs.com/docs/v24.2/drop-user)
* [`SHOW USERS`](https://docs.cockroachlabs.com/docs/v24.2/show-users)
* [`GRANT`](https://docs.cockroachlabs.com/docs/v24.2/grant)
* [`SHOW GRANTS`](https://docs.cockroachlabs.com/docs/v24.2/show-grants)
* [Create Security Certificates](https://docs.cockroachlabs.com/docs/v24.2/cockroach-cert)
* [SQL Statements](https://docs.cockroachlabs.com/docs/v24.2/sql-statements)