tencent cloud

用户访问权限通常配置在配置文件中，该配置文件默认名称为 user.xml。该文件通常包含三个子 session，分别是 users、profiles 和 quotas。

users 配置

用户名会记录在此配置文件中。在配置文件 user.xml 中，users session 中配置了用户访问权限，每一个用户在 users session 下有独立的子 session，以用户名命名，且包含如下内容：

• password：明文密码。
• password_sha256_hex：sha256 加密。
• password_double_sha1_hex：double sha1 加密。
• networks：配置访问源，可配置 IP、网段、支持通配符。
• profile：配置用的 profile 属性，控制用户资源使用。
• quota：配置 quota 属性，在一个周期内，通过多维度阈值，限制用户资源使用。

profiles 配置

profile 配置规定了资源使用限制。profile 配置在 user.xml 中的 profiles session 中。可以同时配置多个 profile，每一个 profile 在 profiles session 中单独一个子 session，其内容如下：

• max_memory_usage：单个查询最大使用内存量，单位 byte。
• use_uncompressed_cache：非压缩数据缓存开关，0表示关闭，1表示启用。
• load_balancing：采用分布式查询方式时，在多个 replicas 上查询时采取的访问策略，默认是 random。
• readonly：只读标志。1表示只读权限，0表示非只读权限。

quotas 配置

quota 允许用户在一个时间周期内（周期时长可配置），从多个维度设定阈值，达到资源访问控制的目的。这些维度包括：周期内查询次数、周期内异常查询次数、周期内查询结果的总行数、周期内在集群范围内查询读取行数（读取的列数因为过滤，通常要大于结果行数），以及查询执行时间（单位 s，wall time）。ClickHouse 允许同时配置多个 quota，每一个 quota 在 quotas session 中单独一个子 session，具体内容如下：

• duration：周期时长，单位 s。
• queries：周期内查询次数限制。
• errors：周期内查询异常次数限制。
• result_rows：周期内作为查询结果的行数限制。
• read_rows：周期内查询读取数据行数限制。
• execution_time：周期内查询执行时间，单位 s。

当用户在一个周期内，上述任何一个限制达到后，会有一个异常。如果上述限制值为0，表示没有限制。默认情况下，无限制。

附录

<?xml version="1.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: 213.180.204.3 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 &lt; 1 or c - d &gt; 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>