Can I set a user which can read one database and read/write another database in clickhouse

Question

Clickhouse offical document implies one user can only have one profile, and database is configured in user section, readonly is configured in profile section, so, readonly will effect on all user's database. So, how can I set a user x which only can read database dbA, but can read and write database dbB?

Answer 1

Consider using the SQL-driven access control management:

1. Prepare test environment:

CREATE DATABASE db_01;
CREATE DATABASE db_02;
CREATE TABLE db_01.table_01 (id Int32) Engine = Memory();
CREATE TABLE db_02.table_02 (id Int32) Engine = Memory();
INSERT INTO db_01.table_01 VALUES (1), (2);
INSERT INTO db_02.table_02 VALUES (10), (20);

2. Create user and assign the required privileges:

CREATE USER user_01 HOST ANY PROFILE 'default';

/* Set readonly access to DB 'db_01'. */
GRANT SELECT ON db_01.* TO user_01;

/* Set read-write access to DB 'db_02'. */
GRANT SELECT ON db_02.* TO user_01;
GRANT INSERT ON db_02.* TO user_01;

3. Test (need to login as user_01):

SELECT currentUser(); /* Make sure that user is right. */
/*
┌─currentUser()─┐
│ user_01       │
└───────────────┘
*/

SHOW GRANTS; /* Double check the assigned privileges. */
/*
┌─GRANTS─────────────────────────────────────┐
│ GRANT SELECT ON db_01.* TO user_01         │
│ GRANT INSERT, SELECT ON db_02.* TO user_01 │
└────────────────────────────────────────────┘
*/

SELECT * FROM db_01.table_01; /* OK */
INSERT INTO db_01.table_01 VALUES (3); /* DB::Exception: user_01: Not enough privileges. */

SELECT * FROM db_02.table_02; /* OK */
INSERT INTO db_02.table_02 VALUES (30); /* OK */

The code above is just for the demo, you need to consider introduce roles/quotas/.., inherit another user-profile (more restrictive, not 'default') etc.