ALTER USER

On this page Carat arrow pointing down
Warning:
CockroachDB v22.1 is no longer supported as of November 24, 2023. For more details, refer to the Release Support Policy.

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.

Note:

Since the keywords ROLE and USER can now be used interchangeably in SQL statements for enhanced PostgreSQL compatibility, ALTER USER is now 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 parameter set.

Synopsis

ALTER USER IF EXISTS name WITH PASSWORD password

Parameters

Parameter Description
name The name of the user whose role options you want to alter.
CANCELQUERY/NOCANCELQUERY Allow or disallow the user to cancel queries and sessions of other users. Without this privilege, users can only cancel their own queries and sessions. Even with the CANCELQUERY role option, non-admin users cannot cancel admin queries or sessions. This option should usually be combined with VIEWACTIVITY so that the user can view other users' query and session information.

By default, the role option is set to NOCANCELQUERY for all non-admin users.
CONTROLCHANGEFEED/NOCONTROLCHANGEFEED Allow or disallow the user to run CREATE CHANGEFEED on tables they have SELECT privileges on.

By default, the role option is set to NOCONTROLCHANGEFEED for all non-admin users.
CONTROLJOB/NOCONTROLJOB Allow or disallow the user to pause, resume, and cancel jobs. Non-admin users cannot control jobs created by admin users.

By default, the role option is set to NOCONTROLJOB for all non-admin users.
CREATEDB/NOCREATEDB Allow or disallow the user to create or rename a database. The user is assigned as the owner of the database.

By default, the role option is set to NOCREATEDB for all non-admin users.
CREATELOGIN/NOCREATELOGIN Allow or disallow the user to manage authentication using the WITH PASSWORD, VALID UNTIL, and LOGIN/NOLOGIN parameters.

By default, the role option is set to NOCREATELOGIN for all non-admin users.
CREATEROLE/NOCREATEROLE Allow or disallow the user to create, alter, and drop other non-admin users.

By default, the role option is set to NOCREATEROLE for all non-admin users.
LOGIN/NOLOGIN The LOGIN parameter allows a user to login with one of the client authentication methods. Setting the parameter to NOLOGIN prevents the user from logging in using any authentication method.
MODIFYCLUSTERSETTING/NOMODIFYCLUSTERSETTING Allow or disallow the user to modify the cluster settings with the sql.defaults prefix.

By default, the role option is set to NOMODIFYCLUSTERSETTING for all non-admin users.
password Let the user authenticate their access to a secure cluster using this new password. Passwords should be entered as a string literal. For compatibility with PostgreSQL, a password can also be entered as an identifier.

To prevent a user from using password authentication and to mandate certificate-based client authentication, set the password as NULL.
VALID UNTIL The date and time (in the timestamp format) after which the password is not valid.
VIEWACTIVITY/NOVIEWACTIVITY Allow or disallow a user to see other users' 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 user'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 users.
VIEWCLUSTERSETTING / NOVIEWCLUSTERSETTING Allow or disallow the user to view the cluster settings with SHOW CLUSTER SETTING, and to access the Cluster Settings page in the DB Console.

By default, the role option is set to NOVIEWCLUSTERSETTING for all non-admin users.

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

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:

icon/buttons/copy
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;

See also


Yes No
On this page

Yes No