有三台CentOS7服务器安装了ClickHouse

HostName	IP	安装程序	程序端口
centf8118.sharding1.db	192.168.81.18	clickhouse-server，clickhouse-client	9000
centf8119.sharding2.db	192.168.81.19	clickhouse-server，clickhouse-client	9000
centf8120.sharding3.db	192.168.81.20	clickhouse-server，clickhouse-client	9000

1：创建角色

CREATE ROLE [IF NOT EXISTS | OR REPLACE] name
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]

2：创建账号

CREATE USER [IF NOT EXISTS | OR REPLACE] name [ON CLUSTER cluster_name]
    [IDENTIFIED [WITH {NO_PASSWORD|PLAINTEXT_PASSWORD|SHA256_PASSWORD|SHA256_HASH|DOUBLE_SHA1_PASSWORD|DOUBLE_SHA1_HASH}] BY {'password'|'hash'}]
    [HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]
    [DEFAULT ROLE role [,...]]
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]

# 创建角色
centf8118.sharding1.db :) CREATE ROLE DBA;

CREATE ROLE DBA


Received exception from server (version 20.6.4):
Code: 497. DB::Exception: Received from localhost:9000. DB::Exception: default: Not enough privileges. To execute this query it's necessary to have the grant CREATE ROLE ON *.*. 

0 rows in set. Elapsed: 0.003 sec.

# 创建账号
centf8118.sharding1.db :) CREATE USER dba_u@'192.168.%' IDENTIFIED WITH sha256_password BY '123456';

CREATE USER `dba_u@192.168.%` IDENTIFIED WITH sha256_hash BY '8D969EEF6ECAD3C29A3A629280E686CF0C3F5D5A86AFF3CA12020C923ADC6C92' HOST LIKE '192.168.%'


Received exception from server (version 20.6.4):
Code: 497. DB::Exception: Received from 192.168.81.18:9000. DB::Exception: xinchen: Not enough privileges. To execute this query it's necessary to have the grant CREATE USER ON *.*. 

0 rows in set. Elapsed: 0.004 sec. 

centf8118.sharding1.db :)

这里不管创建角色还是账号都会报错，提示没有足够权限：

Not enough privileges. To execute this query it's necessary to have the grant CREATE ROLE ON *.*.

Not enough privileges. To execute this query it's necessary to have the grant CREATE USER ON *.*.

这里clickhouse的权限不像mysql直接创建角色，用户。而是需要在配置文件中添加角色用户。

默认的配置文件路径是：/etc/clickhouse-server
其中有config.xml，users.xml，一个是服务器相关配置，一个是用户权限的配置。下面看看users.xml。这里面分几部分。

1. 用户名，用户密码，访问来源地址

2. 资源限制，和greenplum有点像。

3. 配置设置，这其中有用户是否只读。目前只有select和insert。所以权限也比较简单。

密码需要特殊说明，如果不写，那么就是空密码，也可以写明文密码，也可以写密文，可以用如下命令生成密文密码：

PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha256sum | tr -d '-'

我这里执行随机生成的明文和加密后的密码：

[root@centf8118 ~]# PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha256sum | tr -d '-'
Jt9Us0rG   ## 明文密码
de5a7f4866fd2205876184dce54bc89921052a9057a9fc4e4346f4e073a2123d   ## 加密后的密码

然后在users.xml中加入要新创建的账号。

        <dba>
            <password>123456</password>
            <networks incl="networks" replace="replace">
                <ip>::/0</ip>
            </networks>
            <profile>default</profile>
            <quota>default</quota>
            <allow_databases>
                <database>default</database>
            </allow_databases>
            <access_management>1</access_management>
        </dba>

        <xinchen>
            <password_sha256_hex>de5a7f4866fd2205876184dce54bc89921052a9057a9fc4e4346f4e073a2123d</password_sha256_hex>
            <networks incl="networks" replace="replace">
                <ip>::/0</ip>
            </networks>
            <profile>readonly</profile>
            <quota>default</quota>
            <allow_databases>
                <database>default</database>
                <database>testdb</database>
            </allow_databases>
        </xinchen>

我这里是创建两个用户：dba和xinchen，

dba用户采用明文密码，<profile>默认权限，<allow_databases>允许访问的数据库是default。
admin用户采用密文密码，<profile>只读权限，<allow_databases>允许访问的数据库是default,testdb。

然后用xinchen账号登录就可以了：

[root@centf8118 ~]# clickhouse-client -u xinchen -h 192.160.81.18 --password Jt9Us0rG
ClickHouse client version 20.6.4.44 (official build).
Connecting to 192.168.81.18:9000 as user xinchen.
Connected to ClickHouse server version 20.6.4 revision 54436.

centf8118.sharding1.db :) show databases;

SHOW DATABASES

┌─name────┐
│ default │
│ testdb  │
└─────────┘

2 rows in set. Elapsed: 0.007 sec. 

centf8118.sharding1.db :)

【users.xml配置文件介绍】

配置users.xml前可以先看下相关官方文档说明：

Access Control and Account Management
Settings profiles
User settings
Constraints on Settings
Quotas
Permissions for queries
Access Rights

注意一点，修改了user.xml的参数之后是即时生效的，如有问题可以查看其错误日志。

※ Settings profiles ：设置用户配置文件

profile的作用类似于用户角色，可以在user.xml中定义多组profile，并可以为每组profile定义不同的配置项，类限制资源的使用。多个profile的配置可以复用。咋眼一看有点和MySQL的Proxy权限类似。

    <!-- Profiles of settings. -->
    <profiles>  --配置profiles
        <!-- Default settings. -->
        <default>  --自定义profile，系统自动定义的。
            <max_memory_usage>10000000000</max_memory_usage>
            <use_uncompressed_cache>0</use_uncompressed_cache>
            <load_balancing>random</load_balancing>
        </default>

        <!-- Profile that allows only read queries. -->
        <readonly>  --自定义profile
            <readonly>1</readonly>
            <max_memory_usage>200000000</max_memory_usage>
        </readonly>

        <test>  --自定义profile，并继承readonly
           <profile>readonly</profile>
           <max_memory_usage>10000</max_memory_usage>
        </test>
    </profiles>

说明：

<default>：自定义profile，可以在它下面设置相关参数，如：最大内存使用、只读等等。更多的配置参数后续会介绍，也而已看官网文档，可以设置多个profile。

该示例指定了三个profile：default，readonly和test。默认<default>有一个特殊用途：必须始终存在并且在启动服务器时应用。

profile文件可以相互继承，只需要在配置文件中列出即可，如上面定义的test的profile。

test的profile继承了readonly的profile，包含了其所有的配置，并且使用新参数来覆盖其原有的配置。设置了之后如何使用呢？

有二种方法，第1是直接在终端命令行里进行设置，第2个是在users.xml中的users选项组里进行指定（后面会说明）。

[root@centf8118 ~]# clickhouse-client
ClickHouse client version 20.6.4.44 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 20.6.4 revision 54436.

centf8118.sharding1.db :) set profile = 'test'

SET profile = 'test'

Ok.

0 rows in set. Elapsed: 0.002 sec. 

centf8118.sharding1.db :) set max_memory_usage = 123456

SET max_memory_usage = 123456


Received exception from server (version 20.6.4):
Code: 164. DB::Exception: Received from localhost:9000. DB::Exception: Cannot modify 'max_memory_usage' setting in readonly mode. 

0 rows in set. Elapsed: 0.004 sec. 

centf8118.sharding1.db :)

测试报错：

Cannot modify 'max_memory_usage' setting in readonly mode.

说明已经把readonly的profile的参数（readonly）继承过来了。

※ Constraints on Settings：约束

在user.xml配置文件的profile选项组下constraints选项组里定义对设置的约束，并禁止用户使用SET查询更改某些设置。constraints标签可以设置一组约束条件，以限制profile内的参数值被随意修改，约束条件有如下三种规则：

min：最小值约束，在设置相应参数的时候，取值不能小于该阈值；
max：最大值约束，在设置相应参数的时候，取值不能大于该阈值；
readonly：只读约束，该参数值不允许被修改。

需要在profile选项组里设置constraints，模板：

<profiles>
  <user_name>
    <constraints>
      <setting_name_1>
        <min>lower_boundary</min>
      </setting_name_1>
      <setting_name_2>
        <max>upper_boundary</max>
      </setting_name_2>
      <setting_name_3>
        <min>lower_boundary</min>
        <max>upper_boundary</max>
      </setting_name_3>
      <setting_name_4>
        <readonly/>
      </setting_name_4>
    </constraints>
  </user_name>
</profiles>

说明：如果违反约束，则会引发异常，并且设置实际上不会更改。支持三种约束类型：最小，最大，只读。最小和最大约束为数字设置指定上限和下限，并且可以组合使用。只读约束指定用户完全不能更改相应的设置。如：

<profiles>
        <default>
            <max_memory_usage>10000000000</max_memory_usage>
            <use_uncompressed_cache>0</use_uncompressed_cache>
            <force_index_by_date>0</force_index_by_date>
            <load_balancing>random</load_balancing>
            <constraints>
                <max_memory_usage>
                    <min>100000</min>
                    <max>20000</max>
                </max_memory_usage>
                <force_index_by_date>
                    <readonly/>
                </force_index_by_date>
            </constraints>
        </default>
    </profiles>

说明：在default默认profile中定义的constraints约束，将作为默认的全局约束，自动被其他profile继承。例子中约束了参数max_memory_usage的最大最小值和参数force_index_by_date的只读属性，不能修改。关于更多的参数后续会再进行说明，也可以看官方文档。如果违反约束则会报错：

Code: 452. DB::Exception: Received from localhost:9000. DB::Exception: Setting max_memory_usage shouldn't be less than 100000.

Code: 452. DB::Exception: Received from localhost:9000. DB::Exception: Setting force_index_by_date should not be changed.

※ Quotas：配额，限制使用资源，限制有二种类型：一是在固定周期里的执行次数(quotas)，二是限制用户或则查询的使用资源（profiles）

在user.xml配置文件的选项组quotas里设置，限制该用户一段时间内的资源使用，即对一段时间内运行的一组查询施加限制，而不是限制单个查询。还具有限制单个查询的复杂性的功能。

模板：

<!-- Quotas. -->
    <quotas>
        <!-- Name of quota. -->
        <default> --指定quotas名
            <!-- 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>

默认情况下，配额仅跟踪每小时的资源消耗，而没有限制使用情况。在每个请求之后，将为每个时间间隔计算的资源消耗输出到服务器日志。

说明：

<default>：配额规则名。
<interval>：配置时间间隔，每个时间内的资源消耗限制。
<duration>：时间周期，单位秒。
<queries>：时间周期内允许的请求总数，0表示不限制。
<errors>：时间周期内允许的异常总数，0表示不限制。
<result_rows>：时间周期内允许返回的行数，0表示不限制。
<read_rows>：时间周期内允许在分布式查询中，远端节点读取的数据行数，0表示不限制。
<execution_time>：时间周期内允许执行的查询时间，单位是秒，0表示不限制。

上面示例中的配置，属性值均为0，所以资源配额不做任何限制。现在继续声明另外一组配额：

<statbox>
    <interval>
        <duration>3600</duration>
        <queries>1000</queries>
        <errors>100</errors>
        <result_rows>1000000000</result_rows>
        <read_rows>100000000000</read_rows>
        <execution_time>900</execution_time>
    </interval>

    <interval>
        <duration>86400</duration>
        <queries>10000</queries>
        <errors>1000</errors>
        <result_rows>5000000000</result_rows>
        <read_rows>500000000000</read_rows>
        <execution_time>7200</execution_time>
    </interval>
</statbox>

说明：对于“ statbox”配额，每小时和每24小时（86,400秒）设置限制，如果超过限制则会执行失败，并给出何时才能执行的错误：

Code: 201. DB::Exception: Received from localhost:9000. DB::Exception: Quota for user `default` for 10s has been exceeded: queries = 4/3. Interval will end at 2020-08-27 15:29:40. Name of quota template: `default`.

从实施定义的固定时刻开始计算时间间隔。间隔结束时，将清除所有收集的值。接下来的一个小时，配额计算将重新开始。对于分布式查询处理，累积量存储在请求者服务器上。因此，如果用户转到另一台服务器，则那里的配额将重新开始。重新启动服务器后，配额将重置。

quotas 在配置的“用户”部分分配给用户，如果不是根据时间周期而是根据查询的资源消耗来进行限制，则在user.xml里的profile里进行设置，如参数：

1：max_memory_usage：在单个ClickHouse服务进程中，运行一次查询限制使用的最大内存用量，默认值为10G；
2：max_memory_usage_for_user：在单个ClickHouse服务进程中，以用户为单位进行统计，单个用户在运行查询时，限制使用的最大内存用量，默认值为0，即不做限制；
3：max_memory_usage_for_all_queries：在单个ClickHouse服务进程中，所有运行的查询累加在一起，限制使用的最大内存用量，默认为0不做限制；
4：max_partitions_per_insert_block：在单次INSERT写入的时候，限制创建的最大分区个数，默认值为100个。如果超出这个阈值数目，将会得到异常；
5:max_rows_to_group_by：在执行GROUP BY聚合查询的时候，限制去重后的聚合KEY的最大个数，默认值为0，即不做限制。当超过阈值数量的时候，其处理方式由group_by_overflow_mode参数决定；
6:group_by_overflow_mode：当max_rows_to_group_by熔断规则触发的时候，有三种处理形式: 
throw抛出异常，此乃默认值；
break立即停止查询，并返回当前部分的数据；
any仅以当前已存在的聚合KEY，继续完成聚合查询；
7:max_bytes_before_external_group_by：在执行GROUP BY聚合查询的时候，限制使用的最大内存用量，默认值为0，即不做限制。当超过阈值数量的时候，聚合查询将会进一步借用本地磁盘。

※ User settings：用户配置

在user.xml配置文件的users选项组是配置自定义用户，定义一个新用户，必须包含以下几项属性：用户名、密码、访问ip、数据库、表等等。它还可以应用上面的profile、quota。

模板：

<users>
    <!-- If user name was not specified, 'default' user is used. -->
    <user_name> --配置的用户
        <password></password> --明文密码
        <!-- Or -->
        <password_sha256_hex></password_sha256_hex> --加密密码，二选一

        <networks incl="networks" replace="replace"> --允许登录的地址，用于限制用户登录的客户端地址
        </networks>

        <profile>profile_name</profile>   --指定用户的profile

        <quota>default</quota>    -- 指定用户的quota，限制用户使用资源

        <databases>               --指定数据库
            <database_name>
                <table_name>      --指定数据表
                    <filter>expression</filter>
                </table_name>
            </database_name>
        </databases>
    </user_name>
    <!-- Other users settings -->
</users>

说明：默认配置了default用户，在此之前的所有示例中，一直使用的是这个用户。

<user_name>：自定义用户

<password>：用户密码
密码可以以纯文本、SHA256（十六进制格式）、password_double_sha1_hex（和MySQL兼容）指定，设置方法如下：

1.纯文本：
<password>password</password>
2.sha256:
<password_sha256_hex>password</password_sha256_hex>
从shell生成密码的示例：
PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha256sum | tr -d '-'
第一行明文，第二行sha256
3.sha1:
<password_double_sha1_hex>password</password_double_sha1_hex>
从shell生成密码的示例：
PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha1sum | tr -d '-' | xxd -r -p | sha1sum | tr -d '-'
第一行明文，第二行sha1

<networks>：限制用户登录的客户端地址
可以通过IP，主机等进行限制

<ip>:IP地址，如10.0.0.1
<host>：主机名，如example01.host.ru
<host_regexp>：^exampledd-dd-d.host.ru$

来自任何IP：
<ip> :: / 0 </ ip>
来自本机：
<ip>::1</ip>
<ip>127.0.0.1</ip>

<profile>：指定用户的profile
<quota>：指定用户的quota，限制用户使用资源
<database_name>：指定用户访问的数据库
<table_name>：指定用户访问的表
<filter>：指定用户访问的过滤器，限制返回符合条件的行。如：id = 1 ，即查询表只返回id=1的行

例子：

    <users>
        <default>
            <password>123456</password>
            <networks incl="networks" replace="replace">
                <ip>::/0</ip>
            </networks>
            <profile>default</profile>
            <quota>default</quota>
        </default>

        <zhoujy>
            <password_double_sha1_hex>6bb4837eb74329105ee4568dda7dc67ed2ca2ad9</password_double_sha1_hex>
            <networks incl="networks" replace="replace">
                <ip>::/0</ip>
            </networks>
            <profile>default</profile>
            <quota>default</quota>
            <allow_databases>
                <database>test</database>
            </allow_databases>
            <databases>
                <test>  
                    <xx>
                        <filter>id >= 500 </filter>  --行级限制
                    </xx>
                </test>
            </databases>
        </zhoujy>

    </users>

该示例指定了两个用户：

default：指定了密码、访问IP、profile、quota。
zhoujy ：指定了密码、访问IP、profile、quota，以及它只能使用test库，并且只能返回test库xx表id大于等于500的数据。

※ Permissions for queries：查询权限管理

查询可以分为以下几种类型：

读：SELECT，SHOW，DESCRIBE，EXISTS
写：INSERT，OPTIMIZE。
DDL：CREATE，ALTER，RENAME，ATTACH，DETACH，DROP TRUNCATE。
设置：SET，USE。
KILL

以上的权限通过配置标签来控制：

readonly ：读权限、写权限和设置权限，由此标签控制，它有三种取值：

0，不进行任何限制（默认值）；
1，只拥有读权限（只能执行SELECT、EXISTS、SHOW和DESCRIBE）；
2，拥有读权限和设置权限（在读权限基础上，增加了SET查询）。

当设置readonly=1后，用户将无法在当前会话中更改readonly和allow_ddl设置；也可以通过约束来限制更改权限。

allow_ddl：DDL权限由此标签控制，它有两种取值：

当取值为0时，不允许DDL查询；
当取值为1时，允许DDL查询（默认值）

如果当前会话的allow_ddl = 0，则无法执行SET allow_ddl = 1

注意：KILL QUERY可以在任何设置上执行，readonly和allow_ddl需要定义在用户profiles中。

    <profiles>   --在profiles里设置
        ...
        <normal> --只读，不能DDL
            <readonly>1</readonly>
            <allow_ddl>0</allow_ddl>
        </normal>

        <normal_1> --读且能set，不能DDL
            <readonly>2</readonly>
            <allow_ddl>0</allow_ddl>
        </normal_1>

        <normal_2> --只读，即使DDL允许
            <readonly>1</readonly>
            <allow_ddl>1</allow_ddl>
        </normal_2>

        <normal_3> --读写，能DDL
            <readonly>0</readonly>
            <allow_ddl>1</allow_ddl>
        </normal_3>

    </profiles>

...
    <users>
        ...
        <test>
            <password>123456</password>
            <networks incl="networks" replace="replace">
                <ip>::/0</ip>
            </networks>
            <profile>normal_3</profile> --用户引用相关profile
            <quota>default</quota>
        </test>
    </users>
...

说明：在profiles里设置相应的权限角色，再在users里引用，继承这些参数的限制。

※ Access Rights：访问权限控制

访问权限在users.xml中的users选项组里设置，用于在群集中组合的服务器之间交换信息的用户不得有任何限制或配额-否则，分布式查询将失败。不能授予对一个数据库有完全访问权限，而对另一数据库具有只读访问权限。权限控制包含如下：

网络访问控制：通过IP地址或则host主机名
数据库访问控制：通过read_only、allow_ddl来控制读、写、设置、DDL、KILL等
指定数据库访问：通过<allow_databases>指定访问数据库
指定表的访问：通过filter指定表达式来访问表中的数据行

前面都是配置users.xml的形式配置用户账号权限的。其实也可以SQL驱动方式配置。

【SQL驱动方式配置用户权限】

1：SQL驱动方式创建用户

在配置config.xml中添加access_control_path 配置项，具体如下：

<access_control_path>/var/lib/clickhouse/access/</access_control_path> # 默认配置

<access_control_path>/data/clickhouse/access/</access_control_path> # 我的配置

通过 SQL 形式创建的用户、角色等信息将以文件的形式被保存在这个目录。

在users.xml中为默认用户default添加access_management配置项。

<access_management>1</access_management>

0代表disabled，1代表enabled。默认为0。

这样就能成功创建了，如下：

[root@centf8118 clickhouse-server]# clickhouse-client
ClickHouse client version 20.6.4.44 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 20.6.4 revision 54436.

centf8118.sharding1.db :) show databases;

SHOW DATABASES

┌─name───────────────────────────┐
│ _temporary_and_external_tables │
│ default                        │
│ system                         │
│ testdb                         │
└────────────────────────────────┘

4 rows in set. Elapsed: 0.003 sec. 

centf8118.sharding1.db :) create role dba;

CREATE ROLE dba

Ok.

0 rows in set. Elapsed: 0.002 sec. 

centf8118.sharding1.db :) CREATE USER dba_u@'192.168.%' IDENTIFIED WITH sha256_password BY '123456';

CREATE USER `dba_u@192.168.%` IDENTIFIED WITH sha256_hash BY '8D969EEF6ECAD3C29A3A629280E686CF0C3F5D5A86AFF3CA12020C923ADC6C92' HOST LIKE '10.30.%'

Ok.

0 rows in set. Elapsed: 0.002 sec. 

centf8118.sharding1.db :)

2：SQL驱动方式赋予权限

CREATE ROLE manager; //创建角色
GRANT SELECT,UPDATE,INSERT,DELETE ON *.* TO manager; //给角色授权

CREATE USER dba_u@'10.30.%' IDENTIFIED WITH sha256_password BY '123456'; //创建用户
GRANT INSERT,SELECT ON testdb.* TO dba_u; //给用户授权

GRANT manager TO dba_u; //对用户授予角色信息
CREATE USER test_u@'10.30.%' IDENTIFIED WITH sha256_password BY '123456' DEFAULT ROLE manager; //创建用户赋予默认的角色

3：SQL驱动方式限额

CREATE QUOTA [IF NOT EXISTS | OR REPLACE] name [ON CLUSTER cluster_name]

    [KEYED BY {'none' | 'user name' | 'ip address' | 'client key' | 'client key or user name' | 'client key or ip address'}]

    [FOR [RANDOMIZED] INTERVAL number {SECOND | MINUTE | HOUR | DAY}

        {MAX { {QUERIES | ERRORS | RESULT ROWS | RESULT BYTES | READ ROWS | READ BYTES | EXECUTION TIME} = number } [,...] |

         NO LIMITS | TRACKING ONLY} [,...]]

    [TO {role [,...] | ALL | ALL EXCEPT role [,...]}]

创建一个配额举例：（限制最大的查询次数）

CREATE QUOTA qA FOR INTERVAL 15 MONTH MAX QUERIES 123 TO user_01

这里可以根据xml中配额的各项参数，通过修改自己所需要的参数名称和数值来定义配额。

删除一个配额信息

DROP QUOTA [IF EXISTS] name [,...] [ON CLUSTER cluster_name]

查询配额信息

SHOW CREATE QUOTA [name | CURRENT]

参考文章： https://www.cnblogs.com/zhoujinyi/p/12613026.html

参考文章：https://blog.csdn.net/weixin_42502414/article/details/107469394#_Toc20421

【ClickHouse】3：clickhouse基本操作一 用户权限管理 背景介绍： 有三台CentOS7服务器安装了ClickHouse