【clickhouse column】User initialization of new library role

1. Create a new database

The syntax for creating a database in clickhouse is almost the same as other relational databases. The difference is that clickhouse has the concepts of cluster cluster and library engine, which can be specified as needed. If there is no special requirement, the default is fine.

 CREATE  DATABASE  [IF  NOT  EXISTS] db_name [ON CLUSTER cluster]  [ENGINE  =  engine(...)]

When creating a database, we must first confirm that the directory where the database file is saved has read and write permissions for the clickhouse user. If you did not modify the database file directory during installation, the default directory is /var/lib/clickhouse/ . We can use the following command to change the owner of this directory and its subdirectories to the clickhouse user.

 chown clickhouse /var/lib/clickhouse/ -R

After completing the above steps, you can connect to the clichouse service instance through clickhouse-client -m --password <你的密码> , and the SQL to create the database is as follows:

 :) CREATE DATABASE acaidb;

After creating the database, use the show databases; command to see the current clickhouse and what databases are there. (acaidb is built by us, default and system are the databases created by clickhouse by default)

 :) show databases;

┌─name────┐
│ acaidb  │
│ default │
│ system  │
└─────────┘

2. Add administrative privileges to the default account

ClickHouse creates a database default by default and a user default by default. We now increase access management permissions for the default user, because this user does not have administrator permissions by default, that is, by default, this user cannot be used to add roles, add users, and other permissions management operations.

So in order to make the default user have more administrative rights, we modify the /etc/clickhouse-server/users.xml file, and in the users->default tag, remove the comment of access_management. (In some versions its default value is 0, modified to 1)

After modifying the configuration file, restart clickhouse-server and use the following command:

 clickhouse  restart

3. Create roles and users

Creating a Role

Create a role acaidb_rw, which has read and write permissions for the database acaidb.

 CREATE ROLE acaidb_rw;
GRANT SELECT, INSERT, ALTER UPDATE, ALTER DELETE ON acaidb.* TO acaidb_rw;

If a role is only allowed to select and not to modify data, the following statement will do.

 CREATE ROLE acaidb_read;
GRANT SELECT ON acaidb.* TO acaidb_read;

Create a regular account and empower

We create accounts reader and writer respectively, and then assign the read-only role ro to the reader and the read-write role to the writer. In this way, the reader has read-only permissions, and the writer has read-write permissions.
Create a new database user acai and set a password (replace the following <你的密码> ). And give the user acai the read and write permission role acaidb_rw.

 CREATE USER IF NOT EXISTS acai IDENTIFIED WITH sha256_password BY '<你的密码>';

GRANT acaidb_rw TO acai;

Use the following command to access the clickhouse data, show databases to get acaidb, which means we succeeded. Because acai users can only access acaidb.

 # clickhouse-client -m -u acai --password '<你的密码>' --query 'show databases;'
acaidb

Recommended reading

"clickhouse column"