ClickHouse’s role-based access control (RBAC) can feel like it’s not working at all until you understand that roles don’t grant permissions; they aggregate them, and users only inherit permissions from roles they are explicitly assigned.
Let’s see RBAC in action. Imagine you have a ClickHouse setup and want to create a read-only user for your analytics team.
First, create a role that encapsulates the read-only permissions.
CREATE ROLE readonly_role;
GRANT SELECT ON *.* TO readonly_role;
This readonly_role now has the SELECT privilege on all databases and tables.
Next, create a user and assign them this role.
CREATE USER analytics_user IDENTIFIED WITH sha256_password BY 'secure_password_here';
GRANT readonly_role TO analytics_user;
Now, when analytics_user connects, they will have SELECT privileges on everything, because their assigned role readonly_role has those privileges.
But what if you want to grant SELECT on a specific database, say production_db, and INSERT on a logs table within that database, but only to a specific engineering team?
CREATE ROLE db_admin_role;
GRANT SELECT ON production_db.* TO db_admin_role;
GRANT INSERT ON production_db.logs TO db_admin_role;
Then, assign this role to a user:
CREATE USER engineer_user IDENTIFIED WITH sha256_password BY 'another_secure_password';
GRANT db_admin_role TO engineer_user;
The engineer_user can now SELECT from any table in production_db and INSERT into the production_db.logs table. They cannot SELECT from other databases or INSERT into other tables unless those permissions are also granted via roles assigned to them.
The core mental model is that roles are simply named sets of privileges. Users don’t directly get granted privileges; they get granted roles, and by extension, inherit all the privileges associated with those roles. You can also grant roles to other roles, creating a hierarchy.
CREATE ROLE super_admin_role;
GRANT readonly_role TO super_admin_role; -- Inherits all SELECT privileges
GRANT db_admin_role TO super_admin_role; -- Inherits SELECT on production_db.* and INSERT on production_db.logs
GRANT ALTER ON *.* TO super_admin_role; -- Add new privileges
Then, grant super_admin_role to a user. This user now has all the privileges from readonly_role, db_admin_role, plus the ALTER privilege.
The most surprising thing is how default privileges interact with role grants. If a user has been granted a role, and that role has been granted a privilege on a specific object (like SELECT ON my_table), but the user has also been granted a different role that revokes that same privilege (like REVOKE SELECT ON my_table FROM another_role), the user will still have the privilege if the grant came from a role that was granted first. The order of GRANT statements for roles to users matters for privilege resolution when conflicts exist, and revoking a privilege from a role does not affect users who were granted that role before the revocation if the privilege was granted directly to the user.
Understanding the explicit GRANT role TO user and GRANT privilege ON object TO role is key, and always check the effective privileges by querying SHOW GRANTS FOR user.
The next common point of confusion will be when dealing with GRANT ALL PRIVILEGES and how it interacts with specific grants and revokes.