User access permissions generally can be configured in the configuration file which is named
user.xml by default and generally contains three sessions, namely,
Usernames are recorded in this configuration file. The
users session in the
user.xml configuration file contains user access permissions. In the
users session, each user has an independent subsession named after the username, which contains the following content:
profileattribute used to control user resource usage.
quotaattribute used to limit user resource usage with multi-dimensional thresholds in a time period.
A profile specifies the resource usage limits and can be configured in the
profiles session in
user.xml. You can configure multiple profiles at the same time, each of which has an independent subsession in the
profiles session and contains the following content:
0indicates no, and
1indicates read-only permission, and
0indicates non-read-only permission.
A quota allows you to set multi-dimensional thresholds to control resource access in a configurable time period. The dimensions include number of queries, number of exceptional queries, total number of rows of query results, number of rows that are read within the cluster (the number of read columns is usually higher than the number of result rows because of filtering) in the time period, and query execution time (wall time in seconds). ClickHouse allows you to configure multiple quotas, each of which is an independent subsession in the
quotas session and contains the following content:
If any of the limits above is reached in a time period, an exception will occur. If a limit is set to 0, it indicates that there is no limit for the corresponding metric. By default, the limits are set to 0.
<yandex> <!-- Profiles of settings. --> <profiles> <!-- Default settings. --> <default> <!-- Maximum memory usage for processing single query, in bytes. --> <max_memory_usage>10000000000</max_memory_usage> <!-- Use cache of uncompressed blocks of data. Meaningful only for processing many of very short queries. --> <use_uncompressed_cache>0</use_uncompressed_cache> <!-- How to choose between replicas during distributed query processing. random - choose random replica from set of replicas with minimum number of errors nearest_hostname - from set of replicas with minimum number of errors, choose replica with minimum number of different symbols between replica's hostname and local hostname (Hamming distance). in_order - first live replica is chosen in specified order. first_or_random - if first replica one has higher number of errors, pick a random one from replicas with minimum number of errors. --> <load_balancing>random</load_balancing> </default> <!-- Profile that allows only read queries. --> <readonly> <readonly>1</readonly> </readonly> </profiles> <!-- Users and ACL. --> <users> <!-- If user name was not specified, 'default' user is used. --> <default> <!-- Password could be specified in plaintext or in SHA256 (in hex format). If you want to specify password in plaintext (not recommended), place it in 'password' element. Example: <password>qwerty</password>. Password could be empty. If you want to specify SHA256, place it in 'password_sha256_hex' element. Example: <password_sha256_hex>65e84be33532fb784c48129675f9eff3a682b27168c0ea744b2cf58ee02337c5</password_sha256_hex> Restrictions of SHA256: impossibility to connect to ClickHouse using MySQL JS client (as of July 2019). If you want to specify double SHA1, place it in 'password_double_sha1_hex' element. Example: <password_double_sha1_hex>e395796d6546b1b65db9d665cd43f0e858dd4303</password_double_sha1_hex> How to generate decent password: Execute: PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha256sum | tr -d '-' In first line will be password and in second - corresponding SHA256. How to generate double SHA1: Execute: PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | openssl dgst -sha1 -binary | openssl dgst -sha1 In first line will be password and in second - corresponding double SHA1. --> <password></password> <!-- List of networks with open access. To open access from everywhere, specify: <ip>::/0</ip> To open access only from localhost, specify: <ip>::1</ip> <ip>127.0.0.1</ip> Each element of list has one of the following forms: <ip> IP-address or network mask. Examples: 220.127.116.11 or 10.0.0.1/8 or 10.0.0.1/255.255.255.0 2a02:6b8::3 or 2a02:6b8::3/64 or 2a02:6b8::3/ffff:ffff:ffff:ffff::. <host> Hostname. Example: server01.yandex.ru. To check access, DNS query is performed, and all received addresses compared to peer address. <host_regexp> Regular expression for host names. Example, ^server\d\d-\d\d-\d\.yandex\.ru$ To check access, DNS PTR query is performed for peer address and then regexp is applied. Then, for result of PTR query, another DNS query is performed and all received addresses compared to peer address. Strongly recommended that regexp is ends with $ All results of DNS requests are cached till server restart. --> <networks incl="networks" replace="replace"> <ip>::/0</ip> </networks> <!-- Settings profile for user. --> <profile>default</profile> <!-- Quota for user. --> <quota>default</quota> <!-- For testing the table filters --> <databases> <test> <!-- Simple expression filter --> <filtered_table1> <filter>a = 1</filter> </filtered_table1> <!-- Complex expression filter --> <filtered_table2> <filter>a + b < 1 or c - d > 5</filter> </filtered_table2> <!-- Filter with ALIAS column --> <filtered_table3> <filter>c = 1</filter> </filtered_table3> </test> </databases> </default> <!-- Example of user with readonly access. --> <!-- <readonly> <password></password> <networks incl="networks" replace="replace"> <ip>::1</ip> <ip>127.0.0.1</ip> </networks> <profile>readonly</profile> <quota>default</quota> </readonly> --> </users> <!-- Quotas. --> <quotas> <!-- Name of quota. --> <default> <!-- Limits for time interval. You could specify many intervals with different limits. --> <interval> <!-- Length of interval. --> <duration>3600</duration> <!-- No limits. Just calculate resource usage for time interval. --> <queries>0</queries> <errors>0</errors> <result_rows>0</result_rows> <read_rows>0</read_rows> <execution_time>0</execution_time> </interval> </default> </quotas> </yandex>