This article explains how to define SQL users and roles and then apply privileges and permissions to databases, tables, rows, and columns. CREATE, DROP, ALTER operations cannot be performed for the following objects, even if you are an admin user, without granting Access Management permission in ClickHouse, by default.
- Users
- Roles
- Quotas
- Profiles
When you try to do the following tasks without editing the access management parameter in users.xml, you will receive a ‘Not enough privileges.’ error.
CH01 :) CREATE USER test_user IDENTIFIED WITH sha256_password BY 'w@lcome1'; CREATE USER test_user IDENTIFIED WITH sha256_hash BY '87C9D692908B7A00197337CA5263D3DF244357BB19A9C9C0A2C0A09BB16CCDF4' SALT '2F89027275D386B57A1417AE47483F107BEBE52BA1B31890D953AB6626DCCEA6' Query id: 23e37f61-5b3a-49c6-8883-cf7028d435d3 Received exception from server (version 22.9.3): 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)
To come through this situation, you need to modify /etc/clickhouse-server/users.xml
<access_management>1</access_management>
When the tails on the right and left are erased around the parameter, you will be able to do these tasks instantly. This parameter modification is a dynamic procedure, and the transactions became possible after the relevant modification was made.
CH01 :) CREATE USER test_user IDENTIFIED WITH sha256_password BY 'w@lcome1'; CREATE USER test_user IDENTIFIED WITH sha256_hash BY '980186342F9C55B842859A8FE0E499167F3A4A9E6CB727797A552669B7581B27' SALT '9AA744A42A593E03A0FD1BDAF16F3DEF9FA611414D080ACDDB171AEEADF8D92E' Query id: 81acad6c-fc95-44da-bcd9-66adf63e337d Ok.