Configuring ClickHouse User Permissions

Notes

• The deletion operation is irreversible. Even if a role with the same name is added immediately after the deletion, its permission may be different from that of the deleted one. Ensure that the role is not in use before deleting it.
• Before deleting a user, ensure that it is no longer needed. The deletion operation is irreversible. Even if an account with the same name is added immediately after the deletion, the permission may be different from that of the deleted one.
• The admin, root, and default users are default users in a ClickHouse cluster and cannot be deleted.
• Only the passwords of users in the user list can be changed. The passwords of the root user and default users cannot be changed.
• Users and roles cannot be created for a single node in a cluster.
• If the permissions on a table are granted to a role, the table permissions are not removed when the table is deleted. If a table with the same name is created, the role inherits the original permissions on the table. When necessary, you can manually remove the table permissions that have been granted to the role.
• If user A has the permissions to operate table B, user A must manually revoke this permission before deleting table B. If not, user A will retain the same operation permission on any new table B created by user C.
• The password of an existing account cannot be viewed. If you forget the password, you can reset it.

Prerequisites

• A ClickHouse cluster has been created and is running properly.
• You have installed the ClickHouse client.

Step 1: Create a ClickHouse Role

1. Log in to the CloudTable console.
2. Choose Cluster Management. In the right pane, select the target cluster. Choose Role Management on the page that is displayed.
3. Click Create Role. On the Create Role page, set parameters.

   Table 1 Role permissions

   Parameter	Description
   Rolename	The role name must start with a letter and can contain 1 to 64 characters.
   Global Permission	Global permissions refer to the permissions on all databases and tables, including the SELECT, LOAD, ALTER, CREATE, and DROP permissions.
   Each Level Permission	Database And Table refers to the databases and tables created in the cluster. A role has high-risk permissions for databases but only common permissions for tables. Permission Type High-risk permissions: CREATE TABLE, DROP TABLE, CREATE VIEW, and DROP VIEW Common permissions: SELECT, LOAD, and ALTER

4. Click OK.

Step 2 Create a ClickHouse User

1. After a role is created, choose Account Management.
2. Click Create Account. On the Create Account page, set the username and password.

   Table 2 User parameters

   Parameter	Description
   Username	The new username must start with a letter and contain 1 to 64 characters.
   Password	Password of the new user. NOTE: The password must meet the following requirements: Contain 8 to 16 characters. Contain at least four of the following: uppercase letters, lowercase letters, digits, and special characters ($@!%*?&) Cannot be the same as the username or the username spelled backwards.
   Confirm Password	Enter the password again.

3. Click OK.

Step 3: Bind a Role to the ClickHouse User

For example, role A has the permission to query, insert, modify, create, and delete data. After being bound to role A, the user has the permissions of role A.

1. After a role and user are created, choose Account Management, locate the target account, click More, and select Assign Role in the Operation column.
2. In the Assign Role dialog box, select a role. Then, click OK.
3. Click Permission in the Operation column and check that the user has the role permissions.
4. Connect to the cluster as the created user.
   For details about how to connect to a non-security cluster, see Using a Client to Connect to a ClickHouse Normal Cluster.

   ./clickhouse client --host Internal IP address of the cluster   --port 9000 --user admin --password Password

   For details about how to connect to a security cluster, see Using the ClickHouse Client to Connect to a Cluster.

   ./clickhouse client --host Internal IP address of the cluster   --port 9440 --user admin --password Password --secure --config-file /root/config.xml

5. Execute the query, insert, change, create, and delete commands in the CLI.
   • If these commands can be executed, the role is bound successfully.
   • If these commands cannot be executed, check whether the role has been configured with permissions and whether it has been bound to the user. If the fault persists, contact technical support.

Managing User Permissions

• Managing roles
  • Deleting a role: Click Delete in the Operation column. On the displayed page, enter DELETE in the text box or click Auto Enter, and click OK.
  • Modifying role permissions: Click Edit in the Operation column. On the displayed page, select permissions as needed and click OK.
  • Viewing role permissions: Click Permission in the Operation column. On the displayed page, view the role's permissions on databases and tables.
• Managing users
  • Viewing user permissions: Click Permission in the Operation column. On the displayed page, view the user's permissions on databases and tables.
  • Deleting a user: Click Delete in the Operation column. In the displayed dialog box, click Yes.
  • Changing the user password: Click More and select Update Password in the Operation column. On the displayed page, change the password and click OK.

Common Commands for User Permissions

• Commands used to grant permissions to storage-compute coupled users
  1. Creating a role (role_name indicates the name of the role to be created)

     CREATE role IF NOT EXISTS 'role_name' ON CLUSTER default_cluster;

  2. Granting permissions to a role
     • Granting all creation (database and table) permissions

       GRANT CREATE ON *.* TO role1 ON CLUSTER default_cluster;

     • Granting the permission to create tables in the test_db database

       GRANT CREATE TABLE ON test_db.* TO role1;

     • Granting the permission to delete all databases and tables

       GRANT DROP ON test_db.* TO role1;

  3. Deleting a role

     DROP ROLE 'role_name' ON CLUSTER default_cluster;

  4. Creating a user

     CREATE USER  IF NOT EXISTS name ON CLUSTER default_cluster IDENTIFIED WITH sha256_password BY 'password';

  5. Specifying an IP address

     CREATE USER name HOST IP '127.0.0.x' IDENTIFIED WITH sha256_password BY 'password';

  6. Changing a user password

     ALTER USER  IF EXISTS name ON CLUSTER  default_cluster IDENTIFIED WITH sha256_password BY 'password';

  7. Assigning a role to the user

     GRANT role1, role2 TO new_user ON CLUSTER default_cluster;

  8. Canceling the role assignment

     REVOKE role1 FROM user ON CLUSTER default_cluster;

  9. Deleting a user

     DROP USER IF EXISTS 'name1' ON CLUSTER default_cluster; 

  10. Querying the permissions and roles of a user
      • Querying permissions of a user

        show grants for all;

      • Querying permissions of a role

        show grants for role1;