ALTER USER

The ALTER USER statement can be used to add, change, or remove a user’s 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.

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 role option.

Synopsis

See 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. Allow or disallow a role to cancel queries and sessions 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. Allow or disallow a role to run `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, resume, and cancel 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 or rename 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, alter, and drop 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. 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 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. A password should be entered as a string literal. For compatibility with PostgreSQL, a password can also be entered as an identifier. To prevent a role from using password authentication and to mandate certificate-based client authentication, set the password as `NULL`.
`SQLLOGIN`/`NOSQLLOGIN`	Deprecated in v22.2: Use the `NOSQLLOGIN`system privilege. Allow or disallow a role to log in using the SQL CLI with one of the client authentication methods. 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` format) after which the password is not valid.
`VIEWACTIVITY`/`NOVIEWACTIVITY`	Deprecated in v22.2: Use the `VIEWACTIVITY`system privilege. 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. `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. Allow or disallow a role to view the cluster settings with `SHOW CLUSTER SETTING` or to access the Cluster Settings 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. 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 for other users when using the `listSessions` endpoint through the 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

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

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:

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 and manage authentication methods for them:

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 or rename databases:

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, resume, and cancel jobs:

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 and sessions for other non-admin roles:

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:

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:

root@:26257/defaultdb> ALTER USER carl WITH MODIFYCLUSTERSETTING;

Statements

Syntax

Data Types

Spatial Data

Constraints

Considerations

Required privileges

Synopsis

Parameters

Role options

Examples

Change a user’s password

Prevent a user from using password authentication

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

Allow a user to create and rename databases

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

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

Allow a user to control changefeeds

Allow a user to modify cluster settings

See also

​Considerations

​Required privileges

​Synopsis

​Parameters

​Role options

​Examples

​Change a user’s password

​Prevent a user from using password authentication

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

​Allow a user to create and rename databases

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

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

​Allow a user to control changefeeds

​Allow a user to modify cluster settings

​See also

Considerations

Required privileges

Synopsis

Parameters

Role options

Examples

Change a user’s password

Prevent a user from using password authentication

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

Allow a user to create and rename databases

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

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

Allow a user to control changefeeds

Allow a user to modify cluster settings

See also