CREATE USER

Creates user accounts. Syntax:

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.

Identification

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

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

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

Examples

The following username is name1 and does not require a password - which obviously doesn’t provide much security:
CREATE USER name1 NOT IDENTIFIED

To specify a plaintext password:

CREATE USER name2 IDENTIFIED WITH plaintext_password BY 'my_password'

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…

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:
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:
/var/lib/clickhouse/access $ cat 3843f510-6ebd-a52d-72ac-e021686d8a93.sql ATTACH USER name3 IDENTIFIED WITH sha256_hash BY '0C268556C1680BEF0640AAC1E7187566704208398DA31F03D18C74F5C5BE5053' SALT '4FB16307F5E10048196966DD7E6876AE53DE6A1D1F625488482C75F14A5097C7';

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’.

The double_sha1_password is not typically needed, but comes in handy when working with clients that require it (like the MySQL interface):
CREATE USER name4 IDENTIFIED WITH double_sha1_password BY 'my_password'
ClickHouse generates and runs the following query:
CREATE USER name4 IDENTIFIED WITH double_sha1_hash BY 'CCD3A959D6A004B9C3807B728BC2E55B67E10518'
The bcrypt_password is the most secure option for storing passwords. It uses the bcrypt algorithm, which is resilient against brute force attacks even if the password hash is compromised.
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:
<bcrypt_workfactor>12</bcrypt_workfactor>
The work factor must be between 4 and 31, with a default value of 12.

For applications with high-frequency authentication, consider alternative authentication methods due to bcrypt’s computational overhead at higher work factors.

The type of the password can also be omitted:
CREATE USER name6 IDENTIFIED BY 'my_password'
In this case, ClickHouse will use the default password type specified in the server configuration:
<default_password_type>sha256_password</default_password_type>
The available password types are: plaintext_password, sha256_password, double_sha1_password.

Multiple authentication methods can be specified:

CREATE USER user1 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. Therefore, you can only specify no_password if it is the only authentication method in the query.

User Host

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. 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 regular expressions when specifying user hosts. For example, HOST REGEXP '.*\.mysite\.com'.
HOST LIKE 'template' — Allows you to use the 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.

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.

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, 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 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.

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.

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

GRANTS Clause

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

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

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.

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, 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.

Examples

Create the user account mira protected by the password qwerty:

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:

CREATE USER john ROLE role1, role2;

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

CREATE USER john ROLE role1, role2 DEFAULT ROLE role1;

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:

CREATE USER john GRANTEES jack;

Use a query parameter to create the user account john:

SET param_user=john;
CREATE USER {user:Identifier};

SQL Reference

Data Types

Engines

Functions

Formats

Interfaces

Settings

System Tables

Data Lakes

Identification

Examples

User Host

VALID UNTIL Clause

VALID FOR Clause

GRANTS Clause

GRANTEES Clause

Examples

​Identification

​Examples

​User Host

​VALID UNTIL Clause

​VALID FOR Clause

​GRANTS Clause

​GRANTEES Clause

​Examples

Identification

Examples

User Host

VALID UNTIL Clause

VALID FOR Clause

GRANTS Clause

GRANTEES Clause

Examples