Creating User Accounts in ClickHouse

User accounts in ClickHouse are created using an SQL query. This is possible if you pass an environment variable when running the Docker container with the ClickHouse DBMS:

APP_USER_ACCESS_MANAGEMENT='1'

The query:

CREATE USER [IF NOT EXISTS | OR REPLACE] name1 [ON CLUSTER cluster_name1]
        [, name2 [ON CLUSTER cluster_name2] ...]
    [NOT IDENTIFIED | IDENTIFIED {[WITH {no_password | plaintext_password | sha256_password | sha256_hash | double_sha1_password | double_sha1_hash}] BY {'password' | 'hash'}} | {WITH ldap SERVER 'server_name'} | {WITH kerberos [REALM 'realm']}]
    [HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]
    [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'] [,...]

Where ON CLUSTER creates users in the cluster.

User Identification

The following methods can be used to identify users:

• 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 ldap SERVER 'server_name'
• IDENTIFIED WITH kerberos or IDENTIFIED WITH kerberos REALM 'realm'

For identification with sha256_hash, use SALT. The hash must be computed from the concatenation of 'password' and 'salt'.

The GRANTEES Section

In this section, specify users or roles that are allowed to receive privileges from the created user, provided that the user is also granted all necessary access using the GRANT OPTION. Parameters of the GRANTEES section:

• user — specifies the user allowed to receive privileges from the created user
• role — specifies the role allowed to receive privileges from the created user
• ANY — allows any user or role to receive privileges from the created user. This is the default value
• NONE — specifies that no user or role is allowed to receive privileges from the created user

You can exclude any user or role using the EXCEPT clause. For example:

CREATE USER user1 GRANTEES ANY EXCEPT user2

This means that if user1 has privileges granted using the GRANT OPTION, user1 can grant these privileges to anyone except user2.

The GRANT Statement

This statement grants privileges to users or roles in ClickHouse. It also assigns roles to users or other roles.

To revoke privileges, use the REVOKE statement. To display the list of granted privileges, use the SHOW GRANTS statement.

Syntax of Granting Privileges

GRANT [ON CLUSTER cluster_name] privilege[(column_name [,...])] [,...] ON {db.table|db.*|*.*|table|*} TO {user | role | CURRENT_USER} [,...] [WITH GRANT OPTION] [WITH REPLACE OPTION]

• privilege — type of privilege
• role — ClickHouse user role
• user — ClickHouse user
• WITH GRANT OPTION allows a user or a role to execute the GRANT query. Users can grant only the privileges that they have, of the same or lesser scope
• WITH REPLACE OPTION replaces all old privileges with new ones for the user or role, or adds new privileges if not specified

Syntax of Granting Roles

GRANT [ON CLUSTER cluster_name] role [,...] TO {user | another_role | CURRENT_USER} [,...] [WITH ADMIN OPTION] [WITH REPLACE OPTION]

• role — ClickHouse user role
• user — ClickHouse user
• WITH ADMIN OPTION assigns the ADMIN OPTION privilege to a user or role
• WITH REPLACE OPTION replaces all old roles with new ones for the user or role, or adds new roles if not specified

Syntax of Granting Current Privileges

GRANT CURRENT GRANTS{(privilege[(column_name [,...])] [,...] ON {db.table|db.*|*.*|table|*}) | ON {db.table|db.*|*.*|table|*}} TO {user | role | CURRENT_USER} [,...] [WITH GRANT OPTION] [WITH REPLACE OPTION]

• privilege — type of a privilege
• role — ClickHouse user role
• user — ClickHouse user

Using the CURRENT GRANTS clause allows you to assign all privileges that are specified and available for granting. If the list of privileges is not specified, the indicated user or role receives all privileges that are available for CURRENT_USER.

Privileges

A privilege is a permission to execute a specific type of query.

Privileges have a hierarchical structure. The set of allowed queries depends on the scope of the privilege. For a complete list of ClickHouse privileges, see the GRANT Statement page on ClickHouse website.

Examples

Create a user account mira protected by the password qwerty:

CREATE USER mira IDENTIFIED WITH sha256_password BY 'qwerty'

The user mira must run the client application on the host where ClickHouse is running.

Create a user account john, assign roles to it, and make these roles default:

CREATE USER john DEFAULT ROLE role1, role2

Create a user account john and make all its future roles default:

CREATE USER john DEFAULT ROLE ALL

When a role is assigned to the account john, it automatically becomes a default role.

Create a user account john and make all its future roles default, except role1 and role2:

CREATE USER john DEFAULT ROLE ALL EXCEPT role1, role2

Create a user account john and allow this user to grant their privileges to the user with the account jack:

CREATE USER john GRANTEES jack

Access Management

ClickHouse supports access management based on roles.

Objects of access control in ClickHouse:

• User account
• Role
• Row-level access policy
• Settings profile
• Quota

You can configure access control objects using:

• SQL-based access management functionality
• Server configuration files: users.xml and config.xml

It is recommended to use SQL-based access management functionality. Both configuration methods work simultaneously, so if you use configuration files for access management, you can easily switch to SQL-based access management.

Usage

By default, the ClickHouse server provides the default user account default with SQL-based access management disabled but with all rights and permissions. The default account is used in all cases when the username is not identified. For example, when logging in from the client or in distributed queries.

When processing a distributed query, default is used if the user and password properties are not specified in the server or cluster configuration.

If you have just started using ClickHouse, follow this scenario:

1. Enable SQL-based access management for the default user.
2. Log in as default and create all necessary users. Remember to create an administrator account:
   GRANT ALL ON *.* TO admin_user_account WITH GRANT OPTION
   

3. Limit permissions for the default user and disable SQL-based access management for the default account.

User Account

A user account is an access control object that allows a user to be authorized in ClickHouse.

An account contains:

• Identification information
• Privileges that determine the scope of queries that can be executed by a user
• Hosts that can connect to the ClickHouse server
• Assigned and default roles
• Settings and their restrictions that are applied by default when a user logs in
• Assigned settings profiles

Privileges are assigned to a user account using the GRANT statement or through assigning roles. You can revoke a privilege using the REVOKE statement. To display the list of assigned privileges, use the SHOW GRANTS statement.

Management queries:

• CREATE USER
• ALTER USER
• DROP USER
• SHOW CREATE USER

Applying Settings

Settings can be applied in different ways: for a user account, for roles assigned to the user, or in settings profiles. When a user logs in, if a setting is specified for different access control objects, the setting value and its restrictions are applied in the following order (from highest priority to lowest):

1. Account setting.
2. Settings for default account roles. If the setting is specified in multiple roles, the order of applying the setting is not defined.
3. Settings from settings profiles, assigned to a user or to default user roles. If the setting is specified in multiple accounts, the order of applying it is not defined.
4. Settings that are applied to the entire server by default, or settings from the default profile.

Role

A role is a container of access control objects that can be assigned to a user account.

A role contains:

• Privileges
• Settings and restrictions
• The list of assigned roles

Management queries:

• CREATE ROLE
• ALTER ROLE
• DROP ROLE
• SET ROLE
• SET DEFAULT ROLE
• SHOW CREATE ROLE

You can assign privileges to a role using the GRANT statement. To revoke privileges from a role, use the REVOKE statement.

For full documentation, visit the official ClickHouse website.