Skip to main content
Version: 5.3

Role Model Translation to ClickHouse

ClickHouse Configuration​

Creating a User for Role Management​

For initial setup, you need a ClickHouse user with permissions to create and manage roles. This can be done by executing queries under an administrator account or by creating a separate user.

Example​

Create the sm_admin user with permissions to manage roles in ClickHouse.

CREATE USER sm_admin IDENTIFIED BY '<password>'
CREATE ROLE sm_role_manager;
GRANT CREATE ROLE, DROP ROLE, ROLE ADMIN ON *.* TO sm_role_manager;
GRANT SELECT, INSERT, ALTER, TRUNCATE ON *.* TO sm_role_manager WITH GRANT OPTION;
GRANT sm_role_manager TO sm_admin;

Creating the Primary User​

For role model translation, all queries must run under a single user. Create a user without default roles, for example sm_user.

CREATE USER sm_user IDENTIFIED BY '<password>';
SET DEFAULT ROLE NONE TO sm_user;

Creating a Role​

Before use, create a role, grant required permissions, and assign it to the primary user. All translated roles must be assigned to this user.

Example​

Create the analyst role with permission to query data from the default.auditd table.

CREATE ROLE analyst;
GRANT SELECT ON default.auditd TO analyst;
GRANT analyst TO sm_user;

OpenSearch Configuration​

Connection Configuration​

To translate the role model, create a Search Anywhere connection of type ClickHouse and specify the user to which roles are assigned. In this example, it is sm_user. See connection setup in the related article.

Role Configuration​

For correct role mapping between OpenSearch and ClickHouse, create a placeholder role without permissions named clk_<role_name> and assign it to required users. See role setup in the related article.

Example​

To assign the ClickHouse role analyst to an internal user, create the role clk_analyst and assign it to that user. After that, when the user works with the previously created connection, the role is propagated automatically in queries.