Skip to main content

Mapping Windows Active Directory security groups to ClickHouse roles

Example of mapping Windows Active Directory security groups to ClickHouse roles

Example

This example shows how AD users that belong to different AD security groups can be given role access in ClickHouse. It also shows how a user may be added to multiple AD user groups so they can have access provided by multiple roles.

In this environment, we have the following:

  • A Windows Active Directory domain: marsnet2.local
  • A ClickHouse Cluster, cluster_1S_3R with 3 nodes on a cluster configuration of 1 shard, 3 replicas
  • 3 AD users
AD UserDescription
clickhouse_ad_adminClickHouse Admin user
clickhouse_db1_userUser with access to db1.table1
clickhouse_db2_userUser with access to db2.table1
ch_db1_db2_userUser with access to both db1.table1 and db2.table1
  • 3 AD security groups
AD GroupDescription
clickhouse_ad_adminsClickHouse Admins group
clickhouse_ad_db1_usersGroup to map with access to db1.table1
clickhouse_ad_db2_usersGroup to map with access to db2.table1
  • Example AD Environment and UO structure:

Example_AD_Env_and_UO_structure

  • Example AD Security Group Configuration:

Example_AD_Group_clickhouse_ad_db1_users

  • Example AD User Configuration:

Example_AD_user_clickhouse_db1_user

  1. In Windows AD Users and Groups, add each user to their respective group(s), they will be mapped to the ClickHouse roles (example in the next step).
AD Security GroupClickHouse Role
clickhouse_ad_adminclickhouse_ad_admins
clickhouse_db1_userclickhouse_ad_db1_users
clickhouse_db2_userclickhouse_ad_db2_users
ch_db1_db2_userclickhouse_ad_db1_users and clickhouse_ad_db2_users
  • Example user group membership:

Example_AD_user_to_group

  1. In ClickHouse config.xml, add the ldap_servers configuration to each ClickHouse node.
xml tagDescriptionExample Value
ldap_serversTag used to define the ldap servers that will be used by ClickHouseNA
marsnet_adThis tag is arbitrary and is just a label to use to identify the server in <user_directories> sectionNA
hostFQDN or IP Address of Active Directory server or domainmarsdc1.marsnet2.local
portActive Directory Port, usually 389 for non-ssl or 636 for SSL389
bind_dnWhich user will be used to create the bind to AD, it can be a dedicated user if regular users are not allowed to{user_name}@marsnet2.local
user_dn_detectionSettings on how ClickHouse will find the AD usersNA
base_dnAD OU path to start the search for the usersOU=Users,OU=ClickHouse,DC=marsnet2,DC=local
search_filterldap search filter to find the AD user(&(objectClass=user)(sAMAccountName={user_name}))

Refer to documentation for full set of options: https://clickhouse.com/docs/en/operations/external-authenticators/ldap#ldap-server-definition

  1. In ClickHouse config.xml, add the <user_directories> configuration with <ldap> entries to each ClickHouse node.
xml tagDescriptionExample Value
user_directoriesDefines which authenticators will be usedNA
ldapThis contains the settings for the ldap servers, in this AD that will be usedNA
serverThis is the tag that was define in the <ldap_servers> sectionmarsnet2_ad
role_mappingdefinition on how the users authenticated will be mapped between AD groups and ClickHouse rolesNA
base_dnAD path that the system will use to start search for AD groupsOU=Groups,OU=ClickHouse,DC=marsnet2,DC=local
search_filterldap search filter to find the AD groups(&(objectClass=group)(member={user_dn}))
attributeWhich AD attribute field should be used to identify the userCN
scopeWhich levels in the base DN the system should search for the groupssubtree
prefixPrefix for the names of the groups in AD, this prefix will be removed to find the roles in ClickHouseclickhouse_

Refer to documentation for full set of options: https://clickhouse.com/docs/en/operations/external-authenticators/ldap#ldap-external-user-directory

note::: Since the AD security groups were prefixed in the example - i.e. clickhouse_ad_db1_users- when the system retrieves them, the prefix will be removed and the system will look for a ClickHouse role called ad_db1_users to map to clickhouse_ad_db1_users. :::

  1. Create example databases.
  1. Create example tables.
  1. Insert sample data.
  1. Create ClickHouse Roles.
  1. Grant the privileges to the roles.
  1. Test access for restricted db1 user. For example:
  1. Test access for the user that has access to both databases, db1 and db2. For example:
  1. Test access for the Admin user. For example:
· 6 min read