The role is nothing but the position or purpose of an employee in an organization. ClickHouse supports role based access control to manage the users and entities accessing the database server. Every ClickHouse database user should at least have one role assigned to him/her and can have multiple roles assigned to them. The roles define the privileges and constraints of a user.
Creating a role
CREATE statement is used to create a role in ClickHouse.
CREATE ROLE test_role;
Granting and revoking permissions
GRANT statement can be used to grant any permission to the role.
GRANT SELECT ON default.* TO test_role;
The above statement will grant read privilege to all the tables in the default database for the role called ‘test_role’. To revoke the existing privilege, we can use REVOKE statement.
REVOKE SELECT ON default.* FROM test_role;
Modifying Roles
ALTER statement can be used to modify a role. Let us look at an example of renaming an existing role.
ALTER ROLE test_role RENAME TO new_role;
Setting a role for a user
The SET statement sets the role to the current session user.
SET ROLE test_role;
Deleting a Role
DROP statement is used to drop a role from the server.
DROP ROLE new_role;
References
https://clickhouse.com/docs/en/sql-reference