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. You may need assistance from your Active Directory team to provide the appropriate user and password for building the connection. 

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

Note: When testing the connection, if the connection fails with "Server not found", try replacing the <ldap_url> with the I.P. address and port. If Exasol was not configured to use your DSN servers, using the I.P. address and port is a common workaround, at least until you have successfully implemented the solution. Here is a simple example of how it would appear. 

create or replace connection test_ldap_server to 'ldap://192.168.1.155:389' user 'cn=admin,dc=manhlab,dc=com' identified by 'abc';

You can find more LDAP connection and authentication help here:

Manual LDAP test       Force create user when connection fails      LDAP authentication fails on distinguished-name 

 

How to Synchronize AD users and groups

Step 1: Create Scripts

First, you must create the below scripts in the database. This script will perform the "searching" of active directory to get the user attributes for a specific group.  The credentials you use to connect to the ldap server to do the "searching" were defined in the connection above - this is to protect the username and password, like described here.

The Lua script that is created 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.

The scripts can be found in Github.

 

Step 2: Grant Permissions

If you are not running the scripts as a DBA, then you must grant the appropriate permissions on the scripts. In particular, you need to use the GRANT ACCESS syntax to allow the UDFs to read the credentials from the connection you created earlier:

GRANT EXECUTE ON EXA_TOOLBOX TO <user or role>;
GRANT ACCESS ON CONNECTION LDAP_SERVER FOR EXA_TOOLBOX TO <user or role>;
 

Step 3: Determine attributes to sync

You can use the LDAP_HELPER script to help determine which attributes in LDAP correspond to the list of users in the group and the usernames. These attributes are needed to perform the sync in the next step. The below commands will do this:

-- To find out which attributes contain the group members, you can run this:
select EXA_TOOLBOX.LDAP_HELPER('LDAP_SERVER', ROLE_COMMENT) from exa_Dba_roles where role_name = <role name>

-- To find out which attributes contain the username, you can run this:
select EXA_TOOLBOX.LDAP_HELPER('LDAP_SERVER', user_name) from exa_dba_connections WHERE connection_name = 'LDAP_SERVER'; 

-- For other purposes, you can run the script using the LDAP connection you created and the distinguished name of the object you want to investigate:
SELECT EXA_TOOLBOX.LDAP_HELPER(<LDAP connection>,<distinguished name>);

⚠️ These scripts will read all attributes of the given object specified. You can also ask your AD admins to give you this information. 

Step 4: Run the sync 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. 
  •  EXECUTION_MODE - either DEBUG or EXECUTE. In DEBUG mode, all queries are rolled back at the end so you can test it without commiting changes on the database

Some examples of the execution are below:

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

-- the below uses the default values for GROUP and USER ATTRIBUTE
EXECUTE SCRIPT EXA_TOOLBOX."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 EXA_TOOLBOX."SYNC_AD_GROUPS_TO_DB_ROLES_AND_USERS" ('LDAP_SERVER','member','uid','EXECUTE');

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

--the below runs the script in debug mode
EXECUTE SCRIPT EXA_TOOLBOX."SYNC_AD_GROUPS_TO_DB_ROLES_AND_USERS"('LDAP_SERVER','memberOf','sAMAccountName', 'DEBUG');

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! 

exa-Nico
Community Manager
Community Manager

This article (and corresponding scripts) has been updated. All the scripts are now in Github. In addition, the scripts have the following changes:

  • Created HELPER script to help debug problems with AD attributes
  • improved error handling in all Python scripts
  • Added LDAP timeout parameter to 5 seconds
  • Added comments to all scripts
  • Added enhanced error handling to the Lua script
  • Added DEBUG mode to Lua script, where all statements are rolled back at the end
  • Changed logic of SQL to only GRANT or REVOKE when role membership has changed (previously would always do it)
  • Added SQL logic to allow ALTERing a user in case the dn changes, but the username is the same
  • Removed the CASCADE option from DROP USER. The script will display an error in the output that the user cannot be dropped. This is a sign for DBA to take action
  • Changed output of script to display the query text, success/fail, and what the error message is. An error in one of the statements will no longer break the script
patrickbielski
Padawan

Hi,

is it right that we have to execute grant create session to USERNMAE manually after the synchronization? Why this step isn't part of the script?

exa-Nico
Community Manager
Community Manager

Hi @patrickbielski! Permissions are not included in the script because probably each role has different sets of permissions. You can grant all the permissions to the roles that are synced. Then whenever users are added they will inherit the permissions automatically.

For example:

GRANT CREATE SESSION TO <ROLE_NAME>;