> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-trino-dialect.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Documentation for User

# ALTER USER

Changes ClickHouse user accounts.

Syntax:

```sql theme={null}
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}] [GRANTS (privilege ON object [,...])]
    [, {[{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](/reference/statements/grant#access-management) 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.

<h2 id="grantees-clause">
  GRANTEES Clause
</h2>

Specifies users or roles which are allowed to receive [privileges](/reference/statements/grant#privileges) from this user on the condition this user has also all required access granted with [GRANT OPTION](/reference/statements/grant#granting-privilege-syntax). 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`.

<h2 id="examples">
  Examples
</h2>

Set assigned roles as default:

```sql theme={null}
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:

```sql theme={null}
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`:

```sql theme={null}
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:

```sql theme={null}
ALTER USER john GRANTEES jack;
```

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

```sql theme={null}
ALTER USER user1 ADD IDENTIFIED WITH plaintext_password by '1', bcrypt_password by '2', plaintext_password by '3'
```

Notes:

1. 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.
2. `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:

```sql theme={null}
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):

```sql theme={null}
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:

```sql theme={null}
ALTER USER user1 RESET AUTHENTICATION METHODS TO NEW
```

<h2 id="valid-until-clause">
  VALID UNTIL Clause
</h2>

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`](/reference/system-tables/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.

<h2 id="valid-for-clause">
  VALID FOR Clause
</h2>

The `VALID FOR` clause is a convenience shorthand for `VALID UNTIL`. Instead of an absolute date and time it accepts an [interval](/reference/data-types/special-data-types/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`](#valid-until-clause).

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.

<h2 id="grants-clause">
  GRANTS Clause
</h2>

Allows you to limit the access rights available to a session authenticated with a particular authentication method. See the [GRANTS clause of CREATE USER](/reference/statements/create/user#grants-clause) for details.

Together with `ADD IDENTIFIED`, this provides a convenient way to create tokens for applications: an additional credential with an expiration date and a limited set of privileges.

Example:

* `ALTER USER name1 ADD IDENTIFIED WITH plaintext_password BY 'app_token' VALID UNTIL '2026-12-31' GRANTS (SELECT ON db.table, INSERT ON db.table)`