> ## 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

# CREATE USER

Creates [user accounts](/concepts/features/security/access-rights#user-account-management).

Syntax:

```sql theme={null}
CREATE USER [IF NOT EXISTS | OR REPLACE] name1 [, name2 [,...]] [ON CLUSTER cluster_name]
    [{VALID UNTIL datetime | VALID FOR interval}]
    [NOT IDENTIFIED | 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'} | {...} | ... [,...]]]
    [HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]
    [IN access_storage_type]
    [ROLE role [,...]]
    [DEFAULT ROLE role [,...]]
    [DEFAULT DATABASE database | NONE]
    [GRANTEES {user | role | ANY | NONE} [,...] [EXCEPT {user | role} [,...]]]
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY | WRITABLE] | PROFILE 'profile_name'] [,...]
```

`ON CLUSTER` clause allows creating users on a cluster, see [Distributed DDL](/reference/statements/distributed-ddl).

<h2 id="identification">
  Identification
</h2>

There are multiple ways of user identification:

* `IDENTIFIED WITH no_password`
* `IDENTIFIED WITH plaintext_password BY 'qwerty'`
* `IDENTIFIED WITH sha256_password BY 'qwerty'` or `IDENTIFIED BY 'password'`
* `IDENTIFIED WITH sha256_hash BY 'hash'` or `IDENTIFIED WITH sha256_hash BY 'hash' SALT 'salt'`
* `IDENTIFIED WITH double_sha1_password BY 'qwerty'`
* `IDENTIFIED WITH double_sha1_hash BY 'hash'`
* `IDENTIFIED WITH bcrypt_password BY 'qwerty'`
* `IDENTIFIED WITH bcrypt_hash BY 'hash'`
* `IDENTIFIED WITH ldap SERVER 'server_name'`
* `IDENTIFIED WITH kerberos` or `IDENTIFIED WITH kerberos REALM 'realm'`
* `IDENTIFIED WITH ssl_certificate CN 'mysite.com:user'`
* `IDENTIFIED WITH ssh_key BY KEY 'public_key' TYPE 'ssh-rsa', KEY 'another_public_key' TYPE 'ssh-ed25519'`
* `IDENTIFIED WITH http SERVER 'http_server'` or `IDENTIFIED WITH http SERVER 'http_server' SCHEME 'basic'`
* `IDENTIFIED BY 'qwerty'`

Password complexity requirements can be edited in [config.xml](/concepts/features/configuration/server-config/configuration-files). Below is an example configuration that requires passwords to be at least 12 characters long and contain 1 number. Each password complexity rule requires a regex to match against passwords and a description of the rule.

```xml theme={null}
<clickhouse>
    <password_complexity>
        <rule>
            <pattern>.{12}</pattern>
            <message>be at least 12 characters long</message>
        </rule>
        <rule>
            <pattern>\p{N}</pattern>
            <message>contain at least 1 numeric character</message>
        </rule>
    </password_complexity>
</clickhouse>
```

<Note>
  In ClickHouse Cloud, by default, passwords must meet the following complexity requirements:

  * Be at least 12 characters long
  * Contain at least 1 numeric character
  * Contain at least 1 uppercase character
  * Contain at least 1 lowercase character
  * Contain at least 1 special character
</Note>

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

1. The following username is `name1` and does not require a password - which obviously doesn't provide much security:

   ```sql theme={null}
   CREATE USER name1 NOT IDENTIFIED
   ```

2. To specify a plaintext password:

   ```sql theme={null}
   CREATE USER name2 IDENTIFIED WITH plaintext_password BY 'my_password'
   ```

<Tip>
  The password is stored in a SQL text file in `/var/lib/clickhouse/access`, so it's not a good idea to use `plaintext_password`. Try `sha256_password` instead, as demonstrated next...
</Tip>

3. The most common option is to use a password that is hashed using SHA-256. ClickHouse will hash the password for you when you specify `IDENTIFIED WITH sha256_password`. For example:

   ```sql theme={null}
   CREATE USER name3 IDENTIFIED WITH sha256_password BY 'my_password'
   ```

   The `name3` user can now login using `my_password`, but the password is stored as the hashed value above. The following SQL file was created in `/var/lib/clickhouse/access` and gets executed at server startup:

   ```bash theme={null}
   /var/lib/clickhouse/access $ cat 3843f510-6ebd-a52d-72ac-e021686d8a93.sql
   ATTACH USER name3 IDENTIFIED WITH sha256_hash BY '0C268556C1680BEF0640AAC1E7187566704208398DA31F03D18C74F5C5BE5053' SALT '4FB16307F5E10048196966DD7E6876AE53DE6A1D1F625488482C75F14A5097C7';
   ```

<Tip>
  If you have already created a hash value and corresponding salt value for a username, then you can use `IDENTIFIED WITH sha256_hash BY 'hash'` or `IDENTIFIED WITH sha256_hash BY 'hash' SALT 'salt'`. For identification with `sha256_hash` using `SALT` - hash must be calculated from concatenation of 'password' and 'salt'.
</Tip>

4. The `double_sha1_password` is not typically needed, but comes in handy when working with clients that require it (like the MySQL interface):

   ```sql theme={null}
   CREATE USER name4 IDENTIFIED WITH double_sha1_password BY 'my_password'
   ```

   ClickHouse generates and runs the following query:

   ```response theme={null}
   CREATE USER name4 IDENTIFIED WITH double_sha1_hash BY 'CCD3A959D6A004B9C3807B728BC2E55B67E10518'
   ```

5. The `bcrypt_password` is the most secure option for storing passwords. It uses the [bcrypt](https://en.wikipedia.org/wiki/Bcrypt) algorithm, which is resilient against brute force attacks even if the password hash is compromised.

   ```sql theme={null}
   CREATE USER name5 IDENTIFIED WITH bcrypt_password BY 'my_password'
   ```

   The length of the password is limited to 72 characters with this method.
   The bcrypt work factor parameter, which defines the amount of computations and time needed to compute the hash and verify the password, can be modified in the server configuration:

   ```xml theme={null}
   <bcrypt_workfactor>12</bcrypt_workfactor>
   ```

   The work factor must be between 4 and 31, with a default value of 12.

<Warning>
  For applications with high-frequency authentication,
  consider alternative authentication methods due to
  bcrypt's computational overhead at higher work factors.
</Warning>

6. The type of the password can also be omitted:

   ```sql theme={null}
   CREATE USER name6 IDENTIFIED BY 'my_password'
   ```

   In this case, ClickHouse will use the default password type specified in the server configuration:

   ```xml theme={null}
   <default_password_type>sha256_password</default_password_type>
   ```

   The available password types are: `plaintext_password`, `sha256_password`, `double_sha1_password`.

7. Multiple authentication methods can be specified:

   ```sql theme={null}
   CREATE USER user1 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. Therefore, you can only specify
   `no_password` if it is the only authentication method in the query.

<h2 id="user-host">
  User Host
</h2>

User host is a host from which a connection to ClickHouse server could be established. The host can be specified in the `HOST` query section in the following ways:

* `HOST IP 'ip_address_or_subnetwork'` — User can connect to ClickHouse server only from the specified IP address or a [subnetwork](https://en.wikipedia.org/wiki/Subnetwork). Examples: `HOST IP '192.168.0.0/16'`, `HOST IP '2001:DB8::/32'`. For use in production, only specify `HOST IP` elements (IP addresses and their masks), since using `host` and `host_regexp` might cause extra latency.
* `HOST ANY` — User can connect from any location. This is a default option.
* `HOST LOCAL` — User can connect only locally.
* `HOST NAME 'fqdn'` — User host can be specified as FQDN. For example, `HOST NAME 'mysite.com'`.
* `HOST REGEXP 'regexp'` — You can use [pcre](http://www.pcre.org/) regular expressions when specifying user hosts. For example, `HOST REGEXP '.*\.mysite\.com'`.
* `HOST LIKE 'template'` — Allows you to use the [LIKE](/reference/functions/regular-functions/string-search-functions#like) operator to filter the user hosts. For example, `HOST LIKE '%'` is equivalent to `HOST ANY`, `HOST LIKE '%.mysite.com'` filters all the hosts in the `mysite.com` domain.

Another way of specifying host is to use `@` syntax following the username. Examples:

* `CREATE USER mira@'127.0.0.1'` — Equivalent to the `HOST IP` syntax.
* `CREATE USER mira@'localhost'` — Equivalent to the `HOST LOCAL` syntax.
* `CREATE USER mira@'192.168.%.%'` — Equivalent to the `HOST LIKE` syntax.

<Tip>
  ClickHouse treats `user_name@'address'` as a username as a whole. Thus, technically you can create multiple users with the same `user_name` and different constructions after `@`. However, we do not recommend to do so.
</Tip>

<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, where `[timezone]` must be a numeric offset such as `+09:00` or one of `UTC`, `GMT`, `Z`, `MSK`, `MSD`; named IANA zones like `Asia/Tokyo` are not recognized (see the note below). 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:

* `CREATE USER name1 VALID UNTIL '2025-01-01'`
* `CREATE USER name1 VALID UNTIL '2025-01-01 12:00:00 UTC'`
* `CREATE USER name1 VALID UNTIL '2025-01-01 12:00:00 +09:00'`
* `CREATE USER name1 VALID UNTIL 'infinity'`
* `CREATE 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.
* `CREATE 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.

<Note>
  The datetime string is parsed by `parseDateTimeBestEffort`, which only recognizes the timezone tokens `UTC`, `GMT`, `Z`, `MSK`, `MSD`, and numeric offsets such as `+09:00` or `-05:00`. Named IANA timezones like `Asia/Tokyo` or `Europe/London` are not supported, and a fixed offset is not equivalent to an IANA zone for regions that observe daylight saving time, so you must compute the correct offset for the specific date you are encoding.
</Note>

<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 then stored in the `VALID UNTIL` form, so `SHOW CREATE USER` always displays the resolved absolute deadline. It can be used everywhere `VALID UNTIL` can, and it follows the same placement rules: 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:

* `CREATE USER name1 VALID FOR INTERVAL 1 DAY`
* `CREATE USER name1 VALID FOR INTERVAL 3 MONTH`
* `CREATE USER name1 VALID FOR INTERVAL 1 DAY + INTERVAL 12 HOUR`
* `CREATE 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.
* `CREATE 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. It accepts a list of privileges in the same form as the [GRANT](/reference/statements/grant) statement, in parentheses. The clause is specified after an authentication method (after its `VALID UNTIL` clause, if any) and applies only to that method.

When a user logs in with such an authentication method, the access rights of the session are the intersection of the user's access rights (including the rights from granted roles) with the privileges listed in the clause. The clause never adds any access rights: if a listed privilege is not granted to the user, the session does not have it. Sessions authenticated with such a method also cannot grant privileges (the `GRANT OPTION` never survives the intersection) or administer roles. Administering roles includes not only creating, altering, dropping, granting and revoking roles, but also changing which roles are activated by default for a user (`SET DEFAULT ROLE` and `ALTER USER ... DEFAULT ROLE`), which is rejected as well.

`EXECUTE AS` switches the principal of the session, so a statement running under impersonation is limited by the intersection of the **target** user's access rights with the listed privileges, rather than by the rights of the user who logged in. The limit itself is never shed, and impersonating requires `IMPERSONATE ON target` to be both granted to the user and listed in the clause, so a limited credential can never reach further than the same user's unlimited credential.

This provides a convenient way to create tokens for applications: an additional credential with an expiration date and a limited set of privileges, which is tied to the user - it is displayed in `system.query_log` and `system.processes` as the user, it stops working if the user is deleted, and it loses access rights when the user loses them.

<Warning>
  **Enforcement is initiator-only.** The authentication-method `GRANTS` limit and its `VALID UNTIL` expiration are enforced only on the node that receives the query (the initiator). They are **not** propagated to other nodes of a cluster, so do not rely on the clause to constrain execution cluster-wide. Remote nodes retain their usual role scoping. The clause is also not available in `users.xml`. The [query result cache](/concepts/features/performance/caches/query-cache) is shared by all authentication methods of a user: it isolates entries by user and roles, and a cache hit is not re-checked against the `GRANTS` of the method the session logged in with.
</Warning>

Examples:

* `CREATE USER name1 IDENTIFIED BY 'qwerty' GRANTS (SELECT ON db.*)`
* `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)`

Note that the limit is a property of the authentication method, captured at the moment of the login: changing the clause with `ALTER USER` affects new sessions, not the already established ones.

Filtered source grants such as `READ ON S3('s3://bucket/.*')` are not supported in the clause yet: the intersection compares a source filter as an opaque string and cannot narrow one filter to another, so such a grant is rejected rather than silently granting no access.

The clause is supported only for authentication methods whose credentials are verified purely locally by the server. For methods whose verification contacts (or, in the case of `jwt`, may contact — for example to fetch the signing keys) an external system (`ldap`, `kerberos`, `http`, `jwt`) the clause is rejected: when several authentication methods accept the same credential, the limit is enforced by re-checking the credential against the other methods, and an extra probe of an external system is unsafe, so another method accepting the same credential could bypass the limit.

When the same effective credential is accepted by more than one authentication method, the login is limited fail-close by all of them: the session gets the intersection of the `GRANTS` of all matching methods and expires at the earliest of their `VALID UNTIL`. The earliest `VALID UNTIL` wins even when it has already passed — the login is rejected, exactly as if the single matched method had expired, so the expiry of a token never silently hands the shared credential the rights or lifetime of a broader method.

This combination is only checked among authentication methods verified locally by the server, for the same reason the clause itself is rejected on an externally verified method above: re-checking the credential there would require an unsafe extra probe of the external system. So if the same credential also happens to be accepted by an externally verified method (`ldap`, `kerberos`, `http`, `jwt`) on the same user, that method's own `VALID UNTIL` is not part of the combination, and an earlier expiry configured on it does not shorten the session obtained through the locally verified method.

<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, `CREATE 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-1">
  Examples
</h2>

Create the user account `mira` protected by the password `qwerty`:

```sql theme={null}
CREATE USER mira HOST IP '127.0.0.1' IDENTIFIED WITH sha256_password BY 'qwerty';
```

`mira` should start client app at the host where the ClickHouse server runs.

Create the user account `john` and assign roles:

```sql theme={null}
CREATE USER john ROLE role1, role2;
```

Create the user account `john`, assign roles and make some of them default:

```sql theme={null}
CREATE USER john ROLE role1, role2 DEFAULT ROLE role1;
```

or

```sql theme={null}
CREATE USER john ROLE role1, role2 DEFAULT ROLE ALL EXCEPT role2;
```

Create the user account `john` and allow him to grant his privileges to the user with `jack` account:

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

Use a query parameter to create the user account `john`:

```sql theme={null}
SET param_user=john;
CREATE USER {user:Identifier};
```