exa-Nico
Community Manager
Community Manager

Background

With the help of the below scripts, you can set up an automatic synchronization of LDAP/AD Groups with Exasol users and roles. 

Prerequisites

You must first configure the database to accept LDAP authentication. To do this, go to EXAoperation and shut down the database in question. Once the database is shut down, you can edit the database and add the ldap server in the field "LDAP Server URLs". The URL must start with ldap:// or ldaps://. Afterwards, start the database back up. You can find more information here. 
 
Afterwards, add the distinguished name of the corresponding LDAP (Active Directory) group as a comment on all database roles you want to synchronize with LDAP:
CREATE ROLE "EXAMPLE-READONLY"; 
COMMENT ON ROLE "EXAMPLE-READONLY" IS 'cn=example-readonly,ou=groups,dc=ldap,dc=example,dc=org'; 

CREATE ROLE "EXAMPLE-ADMIN"; 
COMMENT ON ROLE "EXAMPLE-ADMIN" IS 'cn=example-admin,ou=groups,dc=ldap,dc=example,dc=org';

 

Finally, create a CONNECTION with your LDAP information. In this connection, you should configure the ldap server (beginning with ldap:// or ldaps://), the user that will connect to the ldap server and pull the information for the groups, and that user's password. The specified user should be able to read the properties of users and groups.

CREATE CONNECTION LDAP_SERVER TO 'ldap://<ldap_url>' user 'cn=admin,dc=ldap,dc=example,dc=org' identified by 'mysecretpassword';

How to Synchronize AD users and groups

Step 1: Create Scripts

First, you must create the below python script in the database. This script will perform the "searching" of active directory to get the user attributes for a specific group.  Please edit the lines defining the URI, Username, and Password. this user and password should be the user which is searching through Active Directory. You can also hide these credentials in a database CONNECTION, like described here

CREATE SCHEMA AD;

--/
CREATE OR REPLACE PYTHON SCALAR SCRIPT AD."GET_AD_ATTRIBUTE" ("LDAP_CONNECTION" VARCHAR(2000),"SEARCH_STRING" VARCHAR(2000) UTF8, "ATTR"  VARCHAR(1000)) EMITS ("SEARCH_STRING" VARCHAR(2000) UTF8, "ATTR" VARCHAR(1000), "VAL" VARCHAR(1000) UTF8) AS
import ldap

def run(ctx):

	try:
		uri =   exa.get_connection(ctx.LDAP_CONNECTION).address #ldap/AD server
		user = exa.get_connection(ctx.LDAP_CONNECTION).user   #technical user for LDAP
		password = exa.get_connection(ctx.LDAP_CONNECTION).password  #pwd of technical user
		encoding = "utf8"  #may depend on ldap server, try latin1 or cp1252 if you get problems with special characters

		ldapClient = ldap.initialize(uri)
		
		#The below line is only needed when connecting via ldaps
		ldapClient.set_option(ldap.OPT_X_TLS_REQUIRE_CERT, ldap.OPT_X_TLS_NEVER)   # required options for SSL without cert checking
		
		ldapClient.bind_s(user, password)
	
		results = ldapClient.search_s(ctx.SEARCH_STRING.encode(encoding), ldap.SCOPE_BASE)
	
		for result in results:
			result_dn = result[0]
			result_attrs = result[1]

	  		if ctx.ATTR in result_attrs:
				for v in result_attrs[ctx.ATTR]:
			             ctx.emit(ctx.SEARCH_STRING, ctx.ATTR, str(v))

	except ldap.LDAPError, e:
		print('Error: ' + e.message['desc'])
		ctx.emit('error', ctx.ATTR, e.message['desc'] + ': ' + e.message['info'])
	finally:
		ldapClient.unbind_s()		

/

Step 2: Create Lua Script

The below script will generate and execute CREATE/DROP USERs and GRANTs / REVOKEs, e.g. for each of the database roles that are marked like in the prerequisite. In particular, it finds the users/roles that are in the associated groups and compares what is in the database vs the AD group and then performs the commands as needed:

--/
CREATE OR REPLACE LUA SCRIPT AD."SYNC_AD_GROUPS_TO_DB_ROLES_AND_USERS" (LDAP_CONNECTION, GROUP_ATTRIBUTE, USER_ATTRIBUTE) RETURNS TABLE AS

-- GROUP ATTRIBUTE refers to the attribute to search in the group for all of the members. Default is 'member'
-- USER ATTRIBUTE refers to the attribute of the user which contains the username. Default is uid

if GROUP_ATTRIBUTE == NULL then
        GROUP_ATTRIBUTE = 'member'
end

if USER_ATTRIBUTE == NULL then
        USER_ATTRIBUTE = 'uid'
end

dcl = query([[

WITH 

get_ad_group_members AS (
		SELECT  
		AD.GET_AD_ATTRIBUTE(:l, ROLE_COMMENT, :g)
		FROM
		EXA_DBA_ROLES
		where ROLE_NAME NOT IN ('PUBLIC','DBA') AND ROLE_COMMENT IS NOT NULL 
		--exclude default EXASOL groups, all other roles MUST be mapped to AD/LDAP groups
		--the mapping to a LDAP role is done via a COMMENT 
	)
, drop_users AS (
		select
		'DROP USER "' || USER_NAME || '" CASCADE; --' || DISTINGUISHED_NAME  AS DCL_STATEMENT, 4 ORDER_ID
		from
		EXA_DBA_USERS
		WHERE DISTINGUISHED_NAME IS NOT NULL
		AND
		DISTINGUISHED_NAME NOT IN 
		(
			SELECT distinct VAL
			FROM
 			get_ad_group_members 
		)
	)

, create_users AS (
		select
		'CREATE USER "' ||  VAL  || '"  IDENTIFIED AT LDAP AS ''' || SEARCH_STRING ||''';'  AS DCL_STATEMENT,1 ORDER_ID
		from

		(
			select AD.GET_AD_ATTRIBUTE(:l, VAL, :u) from
			(
				select distinct VAL
				from	
				get_ad_group_members 
				WHERE 
				VAL NOT IN 
				(
					SELECT distinct  DISTINGUISHED_NAME 
					FROM
		 			EXA_DBA_USERS
				)
			)  --get uid attribute as USER_NAME in database
		
		)WHERE VAL NOT like '%No such object%'

	)
,revokes AS (
		SELECT
		'REVOKE "' || GRANTED_ROLE || '" FROM ' || GROUP_CONCAT( '"' || GRANTEE || '"' )  || ';'  AS DCL_STATEMENT, 2 ORDER_ID
		FROM
		EXA_DBA_ROLE_PRIVS
		WHERE GRANTED_ROLE IN
		(
			select
			ROLE_NAME
			from 
			EXA_DBA_ROLES
			where ROLE_NAME NOT IN ('PUBLIC','DBA') AND ROLE_COMMENT IS NOT NULL
		)
		GROUP BY GRANTED_ROLE
	)
,all_user_names(DISTINGUISHED_NAME, VAL, USER_NAME)  as (

	select AD.GET_AD_ATTRIBUTE(:l, VAL, :u) from
	(
		select distinct VAL
		from	
		get_ad_group_members 
	)

)
, grants AS (

		SELECT 'GRANT "' || ROLE_NAME ||'" TO ' || GROUP_CONCAT( '"' || USER_NAME || '"' ) || ';' AS DCL_STATEMENT, 3 ORDER_ID
		FROM
		(
			select ROLE_NAME, USER_NAME
			FROM 
			get_ad_group_members ad
			JOIN 
			EXA_DBA_ROLES on ROLE_COMMENT=ad.SEARCH_STRING
			JOIN 
			all_user_names au on ad.VAL = au.DISTINGUISHED_NAME
			
		
		)  WHERE USER_NAME NOT like '%No such object%'

		GROUP BY ROLE_NAME
		
	)

select * from  create_users

union all

select * from revokes

union all

select * from grants

union all

select * from drop_users

order by ORDER_ID ;

]], {l=LDAP_CONNECTION, u=USER_ATTRIBUTE, g=GROUP_ATTRIBUTE})

for i=1,#dcl do
        output(dcl[i].DCL_STATEMENT)
	query(dcl[i].DCL_STATEMENT) --execute REVOKEs then GRANTs
end

return dcl

/
 

Step 3: Run the script regularly

You should execute this script periodically. The parameters of the script are:

  • LDAP_CONNECTION - the connection to the ldap server that you created in the prerequisites
  • GROUP_ATTRIBUTE - the LDAP Attribute which contains all of the group members. In most cases, this is 'member' or 'memberOf' or something similar. If you are unsure, the default is 'member', and you can enter an empty string.
  • USER_ATTRIBUTE - the LDAP attribute for the user containing their name. In most cases, this is 'uid' or 'sAMAccountName' or something similar. If you are unsure, the default is 'uid' and you can enter an empty string. 

Some examples of the execution are below:

-- the below execution shows the parameter names
EXECUTE SCRIPT AD."SYNC_AD_GROUPS_TO_DB_ROLES_AND_USERS" (LDAP_CONNECTION, GROUP_ATTRIBUTE, USER_ATTRIBUTE)

-- the below uses the default values for GROUP and USER ATTRIBUTE
EXECUTE SCRIPT AD."SYNC_AD_GROUPS_TO_DB_ROLES_AND_USERS" ('LDAP_SERVER','','');

--the below specifies values (note this matches the execution as the above because member and uid are the default attributes)
EXECUTE SCRIPT AD."SYNC_AD_GROUPS_TO_DB_ROLES_AND_USERS" ('LDAP_SERVER','member','uid');

--the below specifies values that are different from the defaults
EXECUTE SCRIPT AD."SYNC_AD_GROUPS_TO_DB_ROLES_AND_USERS"('LDAP_SERVER','memberOf','sAMAccountName');

If you would like to automate this, you can trigger this script via cron job. You can read more about scheduling database queries here

Additional Notes

This script can be used as a starting point and may require some modification to meet your exact use case. 

Additional References

Comments
MaJo
Padawan

THX for this useful article, I have followed up the steps described here and get the following message when trying to start LUA-Sync-Script (Step-3):

Time: 2020-10-28 10:22:33.585

org.jkiss.dbeaver.model
Fehler
Fri Oct 30 11:22:23 CET 2020
SQL-Fehler [43000]: "ldap error: Bad parameter to an ldap routine, use FORCE option to create user" caught in script "AD"."SYNC_AD_GROUPS_TO_DB_ROLES_AND_USERS" at line 116 (Session: 1681971808346567815)

 

Can anyone Help or give me a hint with further documentation and/or debugging workflows? 

exa-Chris
Community Manager
Community Manager

Hi @MaJo,

Nico has been off last week, I nudged him for an answer this morning. Keeü you posted.

Thanks for the question

@exa-Chris 

exa-Nico
Community Manager
Community Manager

Hey @MaJo can you check auditing and let me know the exact query text that was ran which returned that error message? I'm guessing it is a CREATE USER statement based on the error message. You can anonymize the user name, but the LDAP string should is probably incorrect

MaJo
Padawan

the following was excuted:

 

CREATE PYTHON SCALAR SCRIPT "GET_AD_ATTRIBUTE" ("SEARCH_STRING" VARCHAR(2000) UTF8, "ATTR" VARCHAR(1000) UTF8) EMITS ("SEARCH_STRING" VARCHAR(2000) UTF8, "ATTR" VARCHAR(1000) UTF8, "VAL" VARCHAR(1000) UTF8) AS
import ldap  #in EXASOL 5.0.15 the python ldap library is preinstalled

def run(ctx):

	try:
		uri = "ldap://10.10.10.10"  #ldap/AD server
		user = "cn=ldap-user,cn=Users,dc=example,dc=com"  # technical user for LDAP
		password = "_topsecret_"  #pwd of technical user: in version 6.0 this will be saved in a Bucket in BucketFS
	
		encoding = "utf8" # may depend on ldap server try latin1 or cp1252 if you get problems with special characters

		ldapClient = ldap.initialize(uri)
	
		ldapClient.bind_s(user, password)
	
		results = ldapClient.search_s(ctx.SEARCH_STRING.encode(encoding), ldap.SCOPE_BASE)
	
		for result in results:
			result_dn = result[0]
			result_attrs = result[1]

	  		if ctx.ATTR in result_attrs:
				for v in result_attrs[ctx.ATTR]:
					ctx.emit(ctx.SEARCH_STRING, ctx.ATTR, str(v))

	except ldap.LDAPError, e:
		ctx.emit(ctx.SEARCH_STRING, ctx.ATTR, e.message['desc'])
	finally:
		ldapClient.unbind_s()

...and I have used the LUA-Script as mentioned above!

 

exa-Nico
Community Manager
Community Manager

@MaJo the Lua script will generate CREATE USER statements. Could you check auditing and send the SQL statement that it created? 

SELECT * FROM EXA_DBA_AUDIT_SQL WHERE SESSION_ID = 1681971808346567815 AND SUCCESS IS FALSE;

 

MaJo
Padawan

I did (just edited the Session ID); output:

SELECT COMMAND_NAME,COMMAND_CLASS,ERROR_CODE,ERROR_TEXT,SQL_TEXT FROM EXA_DBA_AUDIT_SQL WHERE SESSION_ID = 1682352593554490471 AND SUCCESS IS FALSE;
EXECUTE SCRIPT	OTHER	43000	"ldap error: Bad parameter to an ldap routine, use FORCE option to create user" caught in script "AD"."SYNC_AD_GROUPS_TO_DB_ROLES_AND_USERS" at line 116	EXECUTE SCRIPT AD.SYNC_AD_GROUPS_TO_DB_ROLES_AND_USERS()
EXECUTE SCRIPT	OTHER	43000	"ldap error: Bad parameter to an ldap routine, use FORCE option to create user" caught in script "AD"."SYNC_AD_GROUPS_TO_DB_ROLES_AND_USERS" at line 116	EXECUTE SCRIPT AD.SYNC_AD_GROUPS_TO_DB_ROLES_AND_USERS()
CREATE USER	DCL	42500	ldap error: Bad parameter to an ldap routine, use FORCE option to create user	CREATE USER "Invalid DN syntax"  IDENTIFIED AT LDAP AS '<SECRET>';
CREATE USER	DCL	42500	ldap error: Bad parameter to an ldap routine, use FORCE option to create user	CREATE USER "Invalid DN syntax"  IDENTIFIED AT LDAP AS '<SECRET>';

 

exa-Nico
Community Manager
Community Manager

I worked with @MaJo on his problem running the script and the issue was 

  1. LDAP was not configured in EXAoperation
  2. The parameters that were used in his AD were not 'uid' or 'member' so the script was not finding the correct objects/attributes.

The script (v3) has now been updated to allow for specifying the AD attributes as an input and the note about Exaoperation has been added as a prerequisite! 

Version history
Revision #:
4 of 4
Last update:
‎17-02-2021 03:05 PM
Updated by:
 
Contributors