title_wip

Example of mapping Windows Active Directory security groups to ClickHouse roles


## Example[​](#example "Direct link to 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 User Description|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | clickhouse\_ad\_admin ClickHouse Admin user|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | clickhouse\_db1\_user User with access to db1.table1|  |  |  |  | | --- | --- | --- | --- | | clickhouse\_db2\_user User with access to db2.table1|  |  | | --- | --- | | ch\_db1\_db2\_user User with access to both db1.table1 and db2.table1 | | | | | | | | | |

- 3 AD security groups

|  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- |
| AD Group Description|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | clickhouse\_ad\_admins ClickHouse Admins group|  |  |  |  | | --- | --- | --- | --- | | clickhouse\_ad\_db1\_users Group to map with access to db1.table1|  |  | | --- | --- | | clickhouse\_ad\_db2\_users Group to map with access to db2.table1 | | | | | | | |

- Example AD Environment and UO structure:

- Example AD Security Group Configuration:

- Example AD User Configuration:

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 Group ClickHouse Role|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | clickhouse\_ad\_admin clickhouse\_ad\_admins|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | clickhouse\_db1\_user clickhouse\_ad\_db1\_users|  |  |  |  | | --- | --- | --- | --- | | clickhouse\_db2\_user clickhouse\_ad\_db2\_users|  |  | | --- | --- | | ch\_db1\_db2\_user clickhouse\_ad\_db1\_users and clickhouse\_ad\_db2\_users | | | | | | | | | |

- Example user group membership:

2. In ClickHouse `config.xml`, add the `ldap_servers` configuration to each ClickHouse node.

```
<ldap_servers>
	<marsnet2_ad>
		<host>marsdc1.marsnet2.local</host>
		<port>389</port>
		<bind_dn>{user_name}@marsnet2.local</bind_dn>
		<user_dn_detection>
			<base_dn>OU=Users,OU=ClickHouse,DC=marsnet2,DC=local</base_dn>
			<search_filter>(&amp;(objectClass=user)(sAMAccountName={user_name}))</search_filter>
		</user_dn_detection>
		<enable_tls>no</enable_tls>
	</marsnet2_ad>
</ldap_servers>
```

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| xml tag Description Example Value|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | ldap\_servers Tag used to define the ldap servers that will be used by ClickHouse NA|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | marsnet\_ad This tag is arbitrary and is just a label to use to identify the server in `<user_directories>` section NA|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | host FQDN or IP Address of Active Directory server or domain marsdc1.marsnet2.local|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | port Active Directory Port, usually 389 for non-ssl or 636 for SSL 389|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | bind\_dn Which 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\_detection Settings on how ClickHouse will find the AD users NA|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | base\_dn AD OU path to start the search for the users OU=Users,OU=ClickHouse,DC=marsnet2,DC=local|  |  |  | | --- | --- | --- | | search\_filter ldap search filter to find the AD user `(&(objectClass=user)(sAMAccountName={user_name}))` | | | | | | | | | | | | | | | | | | | | | | | | | | |

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

3. In ClickHouse `config.xml`, add the `<user_directories>` configuration with `<ldap>` entries to each ClickHouse node.

```
<user_directories>
	<users_xml>
		<path>users.xml</path>
	</users_xml>
	<local_directory>
		<path>/var/lib/clickhouse/access/</path>
	</local_directory>
	<ldap>
		<server>marsnet2_ad</server>
		<role_mapping>
			<base_dn>OU=Groups,OU=ClickHouse,DC=marsnet2,DC=local</base_dn>
			<search_filter>(&amp;(objectClass=group)(member={user_dn}))</search_filter>
			<attribute>CN</attribute>
			<scope>subtree</scope>
			<prefix>clickhouse_</prefix>
		</role_mapping>
	</ldap>
</user_directories>
```

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| xml tag Description Example Value|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | user\_directories Defines which authenticators will be used NA|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | ldap This contains the settings for the ldap servers, in this AD that will be used NA|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | server This is the tag that was define in the `<ldap_servers>` section marsnet2\_ad|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | role\_mapping definition on how the users authenticated will be mapped between AD groups and ClickHouse roles NA|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | base\_dn AD path that the system will use to start search for AD groups OU=Groups,OU=ClickHouse,DC=marsnet2,DC=local|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | search\_filter ldap search filter to find the AD groups `(&(objectClass=group)(member={user_dn}))`| attribute Which AD attribute field should be used to identify the user CN|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | scope Which levels in the base DN the system should search for the groups subtree|  |  |  | | --- | --- | --- | | prefix Prefix for the names of the groups in AD, this prefix will be removed to find the roles in ClickHouse clickhouse\_ | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |