• clickhouse 如何使用SQL 管理用户和角色

    clickhouse 数据库默认的用户是default ，clickhouse 数据库的用户管理有两种方式：1、直接修改配置文件（user.xml) 在用户配置文件里面配置需要创建的用户。2、使用SQL直接创建用户并授权（比较灵活，无需重启服务）与mysql 管理用户类似，但是需要修改一下默认的user配置文件。默认的配置default 用户没有创建用户的权限。

    0、clickhouse 使用默认配置启动后，登录clickhouse 服务，尝试创建用户时报如下错误：

    op-data-base :) create user clickhouse_admin@'%' identified with plaintext_password by 'clickhouse_password';

    CREATE USER clickhouse_admin IDENTIFIED WITH plaintext_password BY 'clickhouse_password'

    Query id: c3f78024-7d87-476d-b66e-b202aefa7bac


    0 rows in set. Elapsed: 0.001 sec.

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

    这是因为default用户没有access_management权限。

    1、开启default 用户的超级管理员权限添加 access_management>1；

     
       
               
                1

    2、重启clickhouse 服务

    clickhouse  restart

    [root@op-data-base clickhouse-server]# clickhouse restart
    /var/run/clickhouse-server/clickhouse-server.pid file exists and contains pid = 26366.
    The process with pid = 26366 is running.
    Sent terminate signal to process with pid 26366.
    Waiting for server to stop
    /var/run/clickhouse-server/clickhouse-server.pid file exists and contains pid = 26366.
    The process with pid = 26366 is running.
    Waiting for server to stop
    /var/run/clickhouse-server/clickhouse-server.pid file exists and contains pid = 26366.
    The process with pid = 26366 is running.
    Waiting for server to stop
    Now there is no clickhouse-server process.
    Server stopped
     chown -R clickhouse: '/var/run/clickhouse-server/'
    Will run clickhouse su 'clickhouse' /usr/bin/clickhouse-server --config-file /etc/clickhouse-server/config.xml --pid-file /var/run/clickhouse-server/clickhouse-server.pid --daemon
    Waiting for server to start
    Waiting for server to start
    Server started
     

    3、创建用户并授权

    op-data-base :) create user clickhouse_admin identified with plaintext_password by 'clickhouse_admin';

    CREATE USER clickhouse_admin IDENTIFIED WITH plaintext_password BY 'clickhouse_admin'

    Query id: eb4f758d-c3f8-4362-90ee-29785203351e

    Ok.

    0 rows in set. Elapsed: 0.001 sec.

    4、授与新用户拥有所有管理权限

    grant all on *.*  to  clickhouse_admin with grant option;

    5、使用clickhouse_admin 用户创建一个列管理用户；

     clickhouse-client  --user clickhouse_admin --password clickhouse_admin
     

    create user column_user identified  with plaintext_password by 'column_user'; 

    CREATE USER column_user IDENTIFIED WITH plaintext_password BY 'column_user'

    Query id: a62d60e1-c492-4404-acbf-7dff27d54c56

    Ok.

    0 rows in set. Elapsed: 0.001 sec.
     

    6、创建一个行管理用户；

    create user row_user identified with plaintext_password by 'row_user';

    7、建库建表并插入数据

    op-data-base :) select * from db1.t1;

    SELECT *
    FROM t1

    Query id: 01ec08a4-abff-42a6-ad30-c555125b0531

    ┌─id─┬─column1─┬─column2─┐
    │  1 │ A       │ abc     │
    │  2 │ A       │ def     │
    │  3 │ B       │ abc     │
    │  4 │ B       │ def     │
    └────┴─────────┴─────────┘
     

    8、创建角色

    1、创建一个角色 只允许查看表t1的Colum1列；

    create role column1_users;

    3. Creating roles​

    With this set of examples, roles for different privileges such as columns and rows will be created, privileges will be granted to the roles and users will be assigned to each role. Roles are used to define groups of users for certain privileges instead of managing each user seperately.

    1. Create a role to restrict users of this role to only see column1 in database db1 and table1:

      CREATE ROLE column1_users;

    2. Set privileges to allow view on column1

      GRANT SELECT(id, column1) ON db1.table1 TO column1_users;

    3. Add the column_user user to the column1_users role

      GRANT column1_users TO column_user;

    4. Create a role to restrict users of this role to only see selected rows, in this case only rows containing A in column1

      CREATE ROLE A_rows_users;

    5. Add the row_user to the A_rows_users role

      GRANT A_rows_users TO row_user;

    6. Create a policy to allow view on only where column1 has the values of A

      CREATE ROW POLICY A_row_filter ON db1.table1 FOR SELECT USING column1 = 'A' TO A_rows_users;

    7. Set privileges to the database and table

      GRANT SELECT(id, column1, column2) ON db1.table1 TO A_rows_users;

    8. grant explicit permissions for other roles to still have access to all rows

      CREATE ROW POLICY allow_other_users_filter ON db1.table1 FOR SELECT USING 1 TO clickhouse_admin, column1_users;

      NOTE

      When attaching a policy to a table, the system will apply that policy and only those users and roles defined will be able to do operations on the table, all others will be denied any operations. In order to not have the restrictive row policy applied to other users, another policy must be defined to allow other users and roles to have regular or other types of access.

    4. Testing role privileges with column restricted user​

    1. Log into the clickhouse client using the clickhouse_admin user

      clickhouse-client --user clickhouse_admin --password password

    2. Verify access to database, table and all rows with the admin user.

      1. SELECT *
      2. FROM db1.table1

      1. Query id: f5e906ea-10c6-45b0-b649-36334902d31d
      2.  
      3. ┌─id─┬─column1─┬─column2─┐
      4. │  1 │ A       │ abc     │
      5. │  2 │ A       │ def     │
      6. │  3 │ B       │ abc     │
      7. │  4 │ B       │ def     │
      8. └────┴─────────┴─────────┘

    3. Log into the ClickHouse client using the column_user user

      clickhouse-client --user column_user --password password

    4. Test SELECT using all columns

      1. SELECT *
      2. FROM db1.table1

      1. Query id: 5576f4eb-7450-435c-a2d6-d6b49b7c4a23
      2.  
      3. 0 rows in set. Elapsed: 0.006 sec.
      4.  
      5. Received exception from server (version 22.3.2):
      6. Code: 497. DB::Exception: Received from localhost:9000. DB::Exception: column_user: Not enough privileges. To execute this query it's necessary to have grant SELECT(id, column1, column2) ON db1.table1. (ACCESS_DENIED)

      NOTE

      Access is denied since all columns were specified and the user only has access to id and column1

    5. Verify SELECT query with only columns specified and allowed:

      1. SELECT
      2.     id,
      3.     column1
      4. FROM db1.table1

      1. Query id: cef9a083-d5ce-42ff-9678-f08dc60d4bb9
      2.  
      3. ┌─id─┬─column1─┐
      4. │  1 │ A       │
      5. │  2 │ A       │
      6. │  3 │ B       │
      7. │  4 │ B       │
      8. └────┴─────────┘

    5. Testing role privileges with row restricted user​

    1. Log into the ClickHouse client using row_user

      clickhouse-client --user row_user --password password

    2. View rows available

      1. SELECT *
      2. FROM db1.table1

      1. Query id: a79a113c-1eca-4c3f-be6e-d034f9a220fb
      2.  
      3. ┌─id─┬─column1─┬─column2─┐
      4. │  1 │ A       │ abc     │
      5. │  2 │ A       │ def     │
      6. └────┴─────────┴─────────┘

    4. Modifying Users and Roles​

    Users can be assigned multiple roles for a combination of privileges needed. When using multiple roles, the system will combine the roles to determine privileges, the net effect will be that the role permissions will be cumulative.

    For example, if one role1 allows for only select on column1 and role2 allows for select on column1 and column2 then the user will have access to both columns.

    1. Using the admin account, create new user to restrict by both row and column with default roles

      CREATE USER row_and_column_user IDENTIFIED WITH plaintext_password BY 'password' DEFAULT ROLE A_rows_users;

    2. Remove prior privileges for A_rows_users role

      REVOKE SELECT(id, column1, column2) ON db1.table1 FROM A_rows_users;

    3. Allow A_row_users role to only select from column1

      GRANT SELECT(id, column1) ON db1.table1 TO A_rows_users;

    4. Log into the ClickHouse client using row_and_column_user

      clickhouse-client --user row_and_column_user --password password;

    5. Test with all columns:

      1. SELECT *
      2. FROM db1.table1

      Query id: 8cdf0ff5-e711-4cbe-bd28-3c02e52e8bc4


    1. 0 rows in set. Elapsed: 0.005 sec.
    2.  
    3. Received exception from server (version 22.3.2):
    4. Code: 497. DB::Exception: Received from localhost:9000. DB::Exception: row_and_column_user: Not enough privileges. To execute this query it's necessary to have grant SELECT(id, column1, column2) ON db1.table1. (ACCESS_DENIED)
    5. ```

    1. Test with limited allowed columns:

      1. SELECT
      2.     id,
      3.     column1
      4. FROM db1.table1

      1. Query id: 5e30b490-507a-49e9-9778-8159799a6ed0
      2.  
      3. ┌─id─┬─column1─┐
      4. │  1 │ A       │
      5. │  2 │ A       │
      6. └────┴─────────┘

    2. Examples on how to delete privileges, policies, unassign users from roles, delete users and roles:

      • Remove privilege from a role
      REVOKE SELECT(column1, id) ON db1.table1 FROM A_rows_users;

      • Delete a policy
      DROP ROW POLICY A_row_filter ON db1.table1;

      • Unassign a user from a role
      REVOKE A_rows_users FROM row_user;

      • Delete a role
      DROP ROLE A_rows_users;

      • Delete a user
      DROP USER row_user;

    5. Troubleshooting​

    1. There are occasions when privileges intersect or combine to produce unexpected results, the following commands can be used to narrow the issue using an admin account

      • Listing the grants and roles for a user
      SHOW GRANTS FOR row_and_column_user


      1. Query id: 6a73a3fe-2659-4aca-95c5-d012c138097b
      2.  
      3. ┌─GRANTS FOR row_and_column_user───────────────────────────┐
      4. │ GRANT A_rows_users, column1_users TO row_and_column_user │
      5. └──────────────────────────────────────────────────────────┘

      • List roles in ClickHouse
      SHOW ROLES


      1. Query id: 1e21440a-18d9-4e75-8f0e-66ec9b36470a
      2.  
      3. ┌─name────────────┐
      4. │ A_rows_users    │
      5. │ column1_users   │
      6. └─────────────────┘

      • Display the policies
      SHOW ROW POLICIES


      1. Query id: f2c636e9-f955-4d79-8e80-af40ea227ebc
      2.  
      3. ┌─name───────────────────────────────────┐
      4. │ A_row_filter ON db1.table1             │
      5. │ allow_other_users_filter ON db1.table1 │
      6. └────────────────────────────────────────┘

      • View how a policy was defined and current privileges
      SHOW CREATE ROW POLICY A_row_filter ON db1.table1


      1. Query id: 0d3b5846-95c7-4e62-9cdd-91d82b14b80b
      2.  
      3. ┌─CREATE ROW POLICY A_row_filter ON db1.table1────────────────────────────────────────────────┐
      4. │ CREATE ROW POLICY A_row_filter ON db1.table1 FOR SELECT USING column1 = 'A' TO A_rows_users │
      5. └─────────────────────────────────────────────────────────────────────────────────────────────┘

    Summary​

    This article demostrated the basics of creating SQL users and roles and provided steps to set and modify privileges for users and roles. For more detailed information on each please refer to our user guides and reference documenation.


     

  • 相关阅读:
    【Linux操作系统教程】用户管理与权限管理你真的懂了吗（三）
    docker容器安装MySQL，navicat无法连接报错（10060/10061错误）
    Java Math.asin()方法具有什么功能呢？
    Java代码审计——WebGoat XML外部实体注入(XXE)
    什么是动态与静态IP
    在ts中val is Map是什么意思呢？
    MySQL触发器
    【TypeScript】项目中对于TypeScript的打包处理
    MySQL事务：特性、使用、并发事务问题和隔离级别
    变分自编码器 / 概率分布的重新理解 感觉悟了很多
  • 原文地址：https://blog.csdn.net/pang_2899/article/details/126266240