User Management in ClickHouse

In this article, we'll describe how to create a ClickHouse user and configure its settings: add a password and grant permissions.

Creating a User

Let's say we need to create a new user named hostman_admin. This is done using the ClickHouse command CREATE USER:

CREATE USER hostman_admin IDENTIFIED BY 'password'

We created a user and set a password. 

However, you can also set a password in another way. The main ClickHouse user settings can be found in the users.xml configuration file (this file is located in the /etc/clickhouse-server/ directory on the ClickHouse server) in the users section. Open this file and add the following lines:

<users>
   <hostman_admin>
       <password></password>
       <!-- Or -->
       <password_sha256_hex></password_sha256_hex>
   </hostman_admin>
</users>

This means that we can assign either a regular password or one in hexadecimal format, leaving one of the values empty (default). If we are creating a database for tests or it contains no sensitive information, a simple password can be used, for example:

<users>
   <hostman_admin>
       <password>simplepass</password>
   </hostman_admin>
</users>

Otherwise, you can input the password in hexadecimal format (0-f), leaving the first value empty, for example:

<users>
   <hostman_admin>
       <password></password>
       <!-- Or -->  
       <password_sha256_hex>76f95cf446430c895d5923a7860af00476f95cf446430c895d5923a7860af004</password_sha256_hex>
   </hostman_admin>
</users>

You can get the hash for a specific string as follows:

echo -n 'secretpass' | sha256sum

If you plan to work with one of the MySQL clients, you'll need to create a SHA1-type password, which is added using the following tag:

<password_double_sha1_hex>76f95cf446430c895d5923a7860af00476f95cf4</password_double_sha1_hex>

You can get the hash similarly:

echo -n 'secretpass' | sha1sum

Configuring User Permissions in ClickHouse

At this point, our created user does not have any special privileges. Let's assign them the admin role using the GRANT command. This way we grant all privileges to the user:

GRANT ALL ON my_database.my_table TO hostman_admin WITH GRANT OPTION;

In the users.xml file, pay attention to the <access_management> tag, which can take values 0 and 1. If you set the value to 1, it will turn on access control via SQL:

<access_management>1</access_management>

Now let's configure the network through which we will access ClickHouse. To do this, find the <networks> block in the configuration file and specify the necessary IP addresses in any suitable format: IPv4 or IPv6. Here's an example configuration:

<networks>`211.182.215.2`, `2b03:6d9::2`, `3b13:7c9::3/64`</networks>

To specify local access, you can add the following (replace with the necessary IP):

<ip>::1</ip>
<ip>127.0.0.1</ip>

Restricting Data Access

Sometimes, it's useful to restrict a user by giving them access to only certain parts of the database. Let's create a new user and ensure they can only access specific rows in the database table. First, create the user:

CREATE USER hostman_new IDENTIFIED BY 'password'

Next, we'll need to create a view that filters data based on certain conditions and then grant access to this view to the user, limiting their data visibility. The view is created like this (replace original_table, some_column, and certain_value with the appropriate values):

CREATE VIEW limited_view AS
SELECT *
FROM original_table
WHERE some_column = 'certain_value'

Now, grant our user access to this view:

GRANT SELECT ON limited_view TO hostman_new

We need to verify that we assigned the permissions correctly. For this, we can query system tables like system.grants and system.privileges, for example:

SELECT * 
FROM system.grants 
WHERE user_name = 'hostman_new'
  AND table = 'limited_view'

If you see a row with SELECT permissions, then the permissions were granted correctly.

Deleting a User Account

If the user is no longer needed, you can delete it. This is done using the DROP command:

DROP USER hostman_admin

That's all. We've learned how to manage user accounts in ClickHouse: create, configure permissions, and delete them.