ALTER USER

Changes ClickHouse user accounts. Syntax:

ALTER USER [IF EXISTS] name1 [RENAME TO new_name |, name2 [,...]] 
    [ON CLUSTER cluster_name]
    [{VALID UNTIL datetime | VALID FOR interval}]
    [NOT IDENTIFIED | RESET AUTHENTICATION METHODS TO NEW | {IDENTIFIED | ADD IDENTIFIED} {[WITH {plaintext_password | sha256_password | sha256_hash | double_sha1_password | double_sha1_hash}] BY {'password' | 'hash'}} | WITH NO_PASSWORD | {WITH ldap SERVER 'server_name'} | {WITH kerberos [REALM 'realm']} | {WITH ssl_certificate CN 'common_name' | SAN 'TYPE:subject_alt_name'} | {WITH ssh_key BY KEY 'public_key' TYPE 'ssh-rsa|...'} | {WITH http SERVER 'server_name' [SCHEME 'Basic']} [{VALID UNTIL datetime | VALID FOR interval}]
    [, {[{plaintext_password | sha256_password | sha256_hash | ...}] BY {'password' | 'hash'}} | {ldap SERVER 'server_name'} | {...} | ... [,...]]]
    [[ADD | DROP] HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]
    [IN access_storage_type]
    [DEFAULT ROLE role [,...] | ALL | ALL EXCEPT role [,...] ]
    [GRANTEES {user | role | ANY | NONE} [,...] [EXCEPT {user | role} [,...]]]
    [DROP ALL PROFILES]
    [DROP ALL SETTINGS]
    [DROP SETTINGS variable [,...] ]
    [DROP PROFILES 'profile_name' [,...] ]
    [ADD|MODIFY SETTINGS variable [=value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE|CONST|CHANGEABLE_IN_READONLY] [,...] ]
    [SET variable [=value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE|CONST|CHANGEABLE_IN_READONLY] [,...] ]
    [ADD PROFILES 'profile_name' [,...] ]

To use ALTER USER you must have the ALTER USER privilege. SET variable = value is an alias for MODIFY SETTING variable = value: it changes a single setting in place while keeping the rest. Prefer it (or MODIFY SETTING) over the bare SETTINGS clause, which replaces the whole settings list and also removes all inherited (parent) profiles.

GRANTEES Clause

Specifies users or roles which are allowed to receive privileges from this user on the condition this user has also all required access granted with GRANT OPTION. Options of the GRANTEES clause:

user — Specifies a user this user can grant privileges to.
role — Specifies a role this user can grant privileges to.
ANY — This user can grant privileges to anyone. It’s the default setting.
NONE — This user can grant privileges to none.

You can exclude any user or role by using the EXCEPT expression. For example, ALTER USER user1 GRANTEES ANY EXCEPT user2. It means if user1 has some privileges granted with GRANT OPTION it will be able to grant those privileges to anyone except user2.

Examples

Set assigned roles as default:

ALTER USER user DEFAULT ROLE role1, role2

If roles aren’t previously assigned to a user, ClickHouse throws an exception. Set all the assigned roles to default:

ALTER USER user DEFAULT ROLE ALL

If a role is assigned to a user in the future, it will become default automatically. Set all the assigned roles to default, excepting role1 and role2:

ALTER USER user DEFAULT ROLE ALL EXCEPT role1, role2

Allows the user with john account to grant his privileges to the user with jack account:

ALTER USER john GRANTEES jack;

Adds new authentication methods to the user while keeping the existing ones:

ALTER USER user1 ADD IDENTIFIED WITH plaintext_password by '1', bcrypt_password by '2', plaintext_password by '3'

Notes:

Older versions of ClickHouse might not support the syntax of multiple authentication methods. Therefore, if the ClickHouse server contains such users and is downgraded to a version that does not support it, such users will become unusable and some user related operations will be broken. In order to downgrade gracefully, one must set all users to contain a single authentication method prior to downgrading. Alternatively, if the server was downgraded without the proper procedure, the faulty users should be dropped.
no_password can not co-exist with other authentication methods for security reasons. Because of that, it is not possible to ADD a no_password authentication method. The below query will throw an error:

ALTER USER user1 ADD IDENTIFIED WITH no_password

If you want to drop authentication methods for a user and rely on no_password, you must specify in the below replacing form. Reset authentication methods and adds the ones specified in the query (effect of leading IDENTIFIED without the ADD keyword):

ALTER USER user1 IDENTIFIED WITH plaintext_password by '1', bcrypt_password by '2', plaintext_password by '3'

Reset authentication methods and keep the most recent added one:

ALTER USER user1 RESET AUTHENTICATION METHODS TO NEW

VALID UNTIL Clause

Allows you to specify the expiration date and, optionally, the time for an authentication method. It accepts a string as a parameter. It is recommended to use the YYYY-MM-DD [hh:mm:ss] [timezone] format for datetime. By default, this parameter equals 'infinity'. The accepted deadline range is 1900-01-01 00:00:00 UTC through 9999-12-31 09:59:59 UTC — the latest instant that stays within year 9999 in every time zone, so the stored instant is never clamped when it is rendered. A deadline in the past means the credentials are already expired. Deadlines before 1970-01-01 00:00:01 UTC are accepted only as an “already expired” marker: they are canonicalized to the smallest expired instant, one second after the Unix epoch (1970-01-01 00:00:01 UTC), so SHOW CREATE USER reports that instant instead of the deadline you wrote. Deadlines from that instant onward are stored exactly. A deadline is stored as an absolute instant, but SHOW CREATE USER and system.users render it in the server or session time zone, so the same stored instant appears as different wall-clock text on differently configured servers: the canonicalized expired instant above, for example, renders as 1970-01-01 00:00:01 on a server in UTC and as 1970-01-01 14:00:01 on a server in Pacific/Kiritimati. Enforcement always uses the stored instant, not its rendering. The placement of the clause determines which authentication methods it applies to:

Before the IDENTIFIED clause (or when the query specifies no authentication method at all): the deadline is a user-level deadline that applies to every authentication method of the user.
After an authentication method: the deadline applies to that method only. A clause written after the whole IDENTIFIED list therefore binds to the last method only, leaving the earlier methods non-expiring.

Examples:

ALTER USER name1 VALID UNTIL '2025-01-01'
ALTER USER name1 VALID UNTIL '2025-01-01 12:00:00 UTC'
ALTER USER name1 VALID UNTIL 'infinity'
ALTER USER name1 VALID UNTIL '2025-01-01' IDENTIFIED WITH plaintext_password BY 'password_1', bcrypt_password BY 'password_2' — the user-level deadline applies to both methods.
ALTER USER name1 IDENTIFIED WITH plaintext_password BY 'no_expiration', bcrypt_password BY 'expiration_set' VALID UNTIL '2025-01-01' — the deadline applies only to the bcrypt_password method; plaintext_password never expires.

VALID FOR Clause

The VALID FOR clause is a convenience shorthand for VALID UNTIL. Instead of an absolute date and time it accepts an interval, and the expiration deadline is computed as the current time plus that interval at the moment the query is executed. The result is stored in the VALID UNTIL form, so SHOW CREATE USER always displays the resolved absolute deadline. It follows the same placement rules as VALID UNTIL: before IDENTIFIED (or with no authentication method) it is a user-level deadline that applies to every method, while after an authentication method it applies to that method only. The deadline is stored and enforced with second precision, so sub-second intervals (NANOSECOND, MICROSECOND, MILLISECOND) are rejected; the smallest accepted unit is SECOND. A negative interval is accepted as a way to mark the credentials as already expired; if the resulting deadline falls before 1970-01-01 00:00:01 UTC, it is canonicalized to that smallest expired instant, which is what SHOW CREATE USER then reports — rendered in the server or session time zone, as described for VALID UNTIL. Examples:

ALTER USER name1 VALID FOR INTERVAL 1 DAY
ALTER USER name1 VALID FOR INTERVAL 3 MONTH
ALTER USER name1 VALID FOR INTERVAL 30 DAY IDENTIFIED WITH plaintext_password BY 'password_1', bcrypt_password BY 'password_2' — the user-level deadline applies to both methods.
ALTER USER name1 IDENTIFIED WITH plaintext_password BY 'no_expiration', bcrypt_password BY 'expiration_set' VALID FOR INTERVAL 30 DAY — the deadline applies only to the bcrypt_password method; plaintext_password never expires.

SQL Reference

Data Types

Engines

Functions

Formats

Interfaces

Settings

System Tables

Data Lakes

GRANTEES Clause

Examples

VALID UNTIL Clause

VALID FOR Clause

​GRANTEES Clause

​Examples

​VALID UNTIL Clause

​VALID FOR Clause

GRANTEES Clause

Examples

VALID UNTIL Clause

VALID FOR Clause