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

> Settings for configuring users and roles.

# Users and roles settings

The `users` section of the `users.xml` configuration file contains user settings.

<Note>
  ClickHouse also supports [SQL-driven workflow](/concepts/features/security/access-rights#access-control-usage) for managing users. We recommend using it.
</Note>

Structure of the `users` section:

```xml theme={null}
<users>
    <!-- If user name was not specified, 'default' user is used. -->
    <user_name>
        <!-- Exactly one authentication method may be specified at the users.user_name level. For example: -->
        <password></password>
        <!-- Or (exclusive) -->
        <password_sha256_hex></password_sha256_hex>
 
        <!-- Or (exclusive) (N.B. multiple SSH keys are allowed for backwards compatibility) -->
        <ssh_keys>
            <ssh_key>
                <type>ssh-ed25519</type>
                <base64_key>AAAAC3NzaC1lZDI1NTE5AAAAIDNf0r6vRl24Ix3tv2IgPmNPO2ATa2krvt80DdcTatLj</base64_key>
            </ssh_key>
            <ssh_key>
                <type>ecdsa-sha2-nistp256</type>
                <base64_key>AAAAE2VjZHNhLXNoYTItbmlzdHAyNTYAAAAIbmlzdHAyNTYAAABBBNxeV2uN5UY6CUbCzTA1rXfYimKQA5ivNIqxdax4bcMXz4D0nSk2l5E1TkR5mG8EBWtmExSPbcEPJ8V7lyWWbA8=</base64_key>
            </ssh_key>
            <ssh_key>
                <type>ssh-rsa</type>
                <base64_key>AAAAB3NzaC1yc2EAAAADAQABAAABgQCpgqL1SHhPVBOTFlOm0pu+cYBbADzC2jL41sPMawYCJHDyHuq7t+htaVVh2fRgpAPmSEnLEC2d4BEIKMtPK3bfR8plJqVXlLt6Q8t4b1oUlnjb3VPA9P6iGcW7CV1FBkZQEVx8ckOfJ3F+kI5VsrRlEDgiecm/C1VPl0/9M2llW/mPUMaD65cM9nlZgM/hUeBrfxOEqM11gDYxEZm1aRSbZoY4dfdm3vzvpSQ6lrCrkjn3X2aSmaCLcOWJhfBWMovNDB8uiPuw54g3ioZ++qEQMlfxVsqXDGYhXCrsArOVuW/5RbReO79BvXqdssiYShfwo+GhQ0+aLWMIW/jgBkkqx/n7uKLzCMX7b2F+aebRYFh+/QXEj7SnihdVfr9ud6NN3MWzZ1ltfIczlEcFLrLJ1Yq57wW6wXtviWh59WvTWFiPejGjeSjjJyqqB49tKdFVFuBnIU5u/bch2DXVgiAEdQwUrIp1ACoYPq22HFFAYUJrL32y7RxX3PGzuAv3LOc=</base64_key>
            </ssh_key>
        </ssh_keys>

        <!-- Or (exclusive) for multiple authentication methods: -->
        <auth_methods>
            <method1>
                <password></password>
            </method1>
            <method2>
                <password_sha256_hex></password_sha256_hex>
            </method2>
            <!-- ... -->
            <methodN>
                <!-- ... -->
            </methodN>
        </auth_methods>

        <access_management>0|1</access_management>

        <networks incl="networks" replace="replace">
        </networks>

        <profile>profile_name</profile>

        <quota>default</quota>
        <default_database>default</default_database>
        <databases>
            <database_name>
                <table_name>
                    <filter>expression</filter>
                </table_name>
            </database_name>
        </databases>

        <grants>
            <query>GRANT SELECT ON system.*</query>
        </grants>
    </user_name>
    <!-- Other users settings -->
</users>
```

<h3 id="user-namepassword">
  user\_name/password
</h3>

Password can be specified in plaintext or in SHA256 (hex format).

* To assign a password in plaintext (**not recommended**), place it in a `password` element.

  For example, `<password>qwerty</password>`. The password can be left blank.

<a id="password_sha256_hex" />

* To assign a password using its SHA256 hash, place it in a `password_sha256_hex` element.

  For example, `<password_sha256_hex>65e84be33532fb784c48129675f9eff3a682b27168c0ea744b2cf58ee02337c5</password_sha256_hex>`.

  Example of how to generate a password from shell:

  ```bash theme={null}
  PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha256sum | tr -d '-'
  ```

  The first line of the result is the password. The second line is the corresponding SHA256 hash.

<a id="password_double_sha1_hex" />

* For compatibility with MySQL clients, password can be specified in double SHA1 hash. Place it in `password_double_sha1_hex` element.

  For example, `<password_double_sha1_hex>08b4a0f1de6ad37da17359e592c8d74788a83eb0</password_double_sha1_hex>`.

  Example of how to generate a password from shell:

  ```bash theme={null}
  PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha1sum | tr -d '-' | xxd -r -p | sha1sum | tr -d '-'
  ```

  The first line of the result is the password. The second line is the corresponding double SHA1 hash.

<h3 id="totp-authentication-configuration">
  TOTP Authentication Configuration
</h3>

Time-Based One-Time Password (TOTP) can be used to authenticate ClickHouse users by generating temporary access codes that are valid for a limited time.
This TOTP authentication method aligns with [RFC 6238](https://datatracker.ietf.org/doc/html/rfc6238) standards, making it compatible with popular TOTP applications like Google Authenticator, 1Password and similar tools.
It can be set up trough the `users.xml` configuration file in addition to password-based authentication.
It's not yet supported in SQL-driven Access Control.

To authenticate using TOTP, users must provide a primary password along with a one-time password generated by their TOTP application via the `--one-time-password` command line option or concatenated to main password together with a '+' character.
For example if the primary password is `some_password` and the generated TOTP code is `345123`, user may specify `--password some_password+345123` or `--password some_password --one-time-password 345123` when connecting to ClickHouse. If no password specified `clickhouse-client` will prompt for it interactively.

To enable TOTP authentication for a user, configure the `time_based_one_time_password` section in `users.xml`. This section defines the TOTP settings, such as secret, validity period, number of digits, and hash algorithm.

**Example**

````xml theme={null}
<clickhouse>
    <!-- ... -->
    <users>
        <my_user>
            <!-- Primary password-based authentication: -->
            <password>some_password</password>
            <password_sha256_hex>1464acd6765f91fccd3f5bf4f14ebb7ca69f53af91b0a5790c2bba9d8819417b</password_sha256_hex>
            <!-- ... or any other supported authentication method ... -->

            <!-- TOTP authentication configuration -->
            <time_based_one_time_password>
                <secret>JBSWY3DPEHPK3PXP</secret>      <!-- Base32-encoded TOTP secret -->
                <period>30</period>                    <!-- Optional: OTP validity period in seconds -->
                <digits>6</digits>                     <!-- Optional: Number of digits in the OTP -->
                <algorithm>SHA1</algorithm>            <!-- Optional: Hash algorithm: SHA1, SHA256, SHA512 -->
            </time_based_one_time_password>
        </my_user>
    </users>
</clickhouse>

Parameters:

- secret - (Required) The base32-encoded secret key used to generate TOTP codes.
- period - Optional. Sets the validity period of each OTP in seconds. Must be a positive number not exceeding 120. Default is 30.
- digits - Optional. Specifies the number of digits in each OTP. Must be between 4 and 10. Default is 6.
- algorithm - Optional. Defines the hash algorithm for generating OTPs. Supported values are SHA1, SHA256, and SHA512. Default is SHA1.

Generating a TOTP Secret

To generate a TOTP-compatible secret for use with ClickHouse, run the following command in the terminal:

```bash
$ base32 -w32 < /dev/urandom | head -1
````

This command will produce a base32-encoded secret that can be added to the secret field in users.xml.

To enable TOTP for a specific user, add to any existing password-based field (like `password` or `password_sha256_hex`) another `time_based_one_time_password` section.

The [qrencode](https://linux.die.net/man/1/qrencode) tool can be used to generate a QR code for the TOTP secret.

```bash theme={null}
$ qrencode -t ansiutf8 'otpauth://totp/ClickHouse?issuer=ClickHouse&secret=JBSWY3DPEHPK3PXP'
```

After configuring TOTP for a user, one-time password can be used as a part of the authentication process as described above.

<h3 id="user-sshkey">
  username/ssh-key
</h3>

This setting allows authenticating with SSH keys.

Given a SSH key (as generated by `ssh-keygen`) like

```text theme={null}
ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIDNf0r6vRl24Ix3tv2IgPmNPO2ATa2krvt80DdcTatLj john@example.com
```

The `ssh_key` element is expected to be

```xml theme={null}
<ssh_key>
     <type>ssh-ed25519</type>
     <base64_key>AAAAC3NzaC1lZDI1NTE5AAAAIDNf0r6vRl24Ix3tv2IgPmNPO2ATa2krvt80DdcTatLj</base64_key>
 </ssh_key>
```

Substitute `ssh-ed25519` with `ssh-rsa` or `ecdsa-sha2-nistp256` for the other supported algorithms.

<h3 id="multiple-authentication-methods">
  Multiple Authentication Methods
</h3>

A single user can be configured with multiple authentication methods using the `<auth_methods>` element. This allows a user to authenticate with any one of the listed methods — for example, a user could have both a password and an LDAP credential, and logging in with either one would succeed.

Each child element of `<auth_methods>` is an arbitrarily-named wrapper that contains exactly one authentication type. The wrapper name (e.g. `<method1>`, `<primary>`, `<a1>`) does not matter; only the inner authentication element is used.

**Example: multiple passwords**

```xml theme={null}
<users>
    <my_user>
        <auth_methods>
            <primary>
                <password>password_one</password>
            </primary>
            <secondary>
                <password_sha256_hex>65e84be33532fb784c48129675f9eff3a682b27168c0ea744b2cf58ee02337c5</password_sha256_hex>
            </secondary>
        </auth_methods>
    </my_user>
</users>
```

**Example: mixed authentication types**

```xml theme={null}
<users>
    <my_user>
        <auth_methods>
            <a1>
                <password>plaintext_pass</password>
            </a1>
            <a2>
                <password_sha256_hex>e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855</password_sha256_hex>
            </a2>
            <a3>
                <ldap>
                    <server>my_ldap_server</server>
                </ldap>
            </a3>
        </auth_methods>
    </my_user>
</users>
```

The following authentication types are supported inside `<auth_methods>`:

* **`password`** — plaintext password
* **`password_sha256_hex`** — SHA256 password hash
* **`password_scram_sha256_hex`** — SCRAM-SHA-256 password hash
* **`password_double_sha1_hex`** — double SHA1 password hash
* **`ldap`** — LDAP server authentication
* **`kerberos`** — Kerberos authentication
* **`ssl_certificates`** — SSL certificate authentication
* **`ssh_keys`** — SSH key authentication
* **`http_authentication`** — HTTP authentication

**Rules and restrictions:**

* `<auth_methods>` **cannot** be used together with authentication methods specified at the user level. Use one style or the other, not both.
* `<auth_methods>` must contain at least one authentication method.
* Each wrapper element inside `<auth_methods>` must contain exactly one authentication type (with the exception of `<ssh_keys>`, which can contain multiple, for backwards compatibility).
* TOTP (`<time_based_one_time_password>`) is specified at the user level (outside `<auth_methods>`) and applies to all password-based methods in the list. At least one password-based method is required when TOTP is enabled.

**Example: `auth_methods` with TOTP**

```xml theme={null}
<users>
    <my_user>
        <auth_methods>
            <a1>
                <password>my_password</password>
            </a1>
            <a2>
                <ldap>
                    <server>ldap_server_1</server>
                </ldap>
            </a2>
        </auth_methods>
        <time_based_one_time_password>
            <secret>JBSWY3DPEHPK3PXP</secret>
        </time_based_one_time_password>
    </my_user>
</users>
```

In this example, TOTP verification is applied to the password-based method (`<password>`), while the LDAP method authenticates against the external server independently.

<h3 id="access_management-user-setting">
  access\_management
</h3>

This setting enables or disables using of SQL-driven [access control and account management](/concepts/features/security/access-rights#access-control-usage) for the user.

Possible values:

* 0 — Disabled.
* 1 — Enabled.

Default value: 0.

<h3 id="grants-user-setting">
  grants
</h3>

This setting allows to grant any rights to selected user.
Each element of the list should be `GRANT` query without any grantees specified.

Example:

```xml theme={null}
<user1>
    <grants>
        <query>GRANT SHOW ON *.*</query>
        <query>GRANT CREATE ON *.* WITH GRANT OPTION</query>
        <query>GRANT SELECT ON system.*</query>
    </grants>
</user1>
```

This setting can't be specified at the same time with
`dictionaries`, `access_management`, `named_collection_control`, `show_named_collections_secrets`
and `allow_databases` settings.

<h3 id="user-namenetworks">
  user\_name/networks
</h3>

List of networks from which the user can connect to the ClickHouse server.

Each element of the list can have one of the following forms:

* `<ip>` — IP address or network mask.

  Examples: `213.180.204.3`, `10.0.0.1/8`, `10.0.0.1/255.255.255.0`, `2a02:6b8::3`, `2a02:6b8::3/64`, `2a02:6b8::3/ffff:ffff:ffff:ffff::`.

* `<host>` — Hostname.

  Example: `example01.host.ru`.

  To check access, a DNS query is performed, and all returned IP addresses are compared to the peer address.

* `<host_regexp>` — Regular expression for hostnames.

  Example, `^example\d\d-\d\d-\d\.host\.ru$`

  To check access, a [DNS PTR query](https://en.wikipedia.org/wiki/Reverse_DNS_lookup) is performed for the peer address and then the specified regexp is applied. Then, another DNS query is performed for the results of the PTR query and all the received addresses are compared to the peer address. We strongly recommend that regexp ends with \$.

All results of DNS requests are cached until the server restarts.

**Examples**

To open access for user from any network, specify:

```xml theme={null}
<ip>::/0</ip>
```

<Note>
  It's insecure to open access from any network unless you have a firewall properly configured or the server is not directly connected to Internet.
</Note>

To open access only from localhost, specify:

```xml theme={null}
<ip>::1</ip>
<ip>127.0.0.1</ip>
```

<h3 id="user-nameprofile">
  user\_name/profile
</h3>

You can assign a settings profile for the user. Settings profiles are configured in a separate section of the `users.xml` file. For more information, see [Profiles of Settings](/concepts/features/configuration/settings/settings-profiles).

<h3 id="user-namequota">
  user\_name/quota
</h3>

Quotas allow you to track or limit resource usage over a period of time. Quotas are configured in the `quotas`
section of the `users.xml` configuration file.

You can assign a quotas set for the user. For a detailed description of quotas configuration, see [Quotas](/concepts/features/configuration/server-config/quotas).

<h3 id="user-namedatabases">
  user\_name/databases
</h3>

In this section, you can limit rows that are returned by ClickHouse for `SELECT` queries made by the current user, thus implementing basic row-level security.

**Example**

The following configuration forces that user `user1` can only see the rows of `table1` as the result of `SELECT` queries, where the value of the `id` field is 1000.

```xml theme={null}
<user1>
    <databases>
        <database_name>
            <table1>
                <filter>id = 1000</filter>
            </table1>
        </database_name>
    </databases>
</user1>
```

The `filter` can be any expression resulting in a [UInt8](/reference/data-types/int-uint)-type value. It usually contains comparisons and logical operators. Rows from `database_name.table1` where filter results to 0 are not returned for this user. The filtering is incompatible with `PREWHERE` operations and disables `WHERE→PREWHERE` optimization.

<h2 id="roles">
  Roles
</h2>

You can create any predefined roles using the `roles` section of the `user.xml` configuration file.

Structure of the `roles` section:

```xml theme={null}
<roles>
    <test_role>
        <grants>
            <query>GRANT SHOW ON *.*</query>
            <query>REVOKE SHOW ON system.*</query>
            <query>GRANT CREATE ON *.* WITH GRANT OPTION</query>
        </grants>
    </test_role>
</roles>
```

These roles can also be granted to users from the `users` section:

```xml theme={null}
<users>
    <user_name>
        ...
        <grants>
            <query>GRANT test_role</query>
        </grants>
    </user_name>
<users>
```