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

Add the distinguished name of the corresponding LDAP (Active Directory) group as 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';

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" ("SEARCH_STRING" VARCHAR(2000) UTF8, "ATTR"  VARCHAR(1000)) EMITS ("SEARCH_STRING" VARCHAR(2000) UTF8, "ATTR" VARCHAR(1000), "VAL" VARCHAR(1000) UTF8) AS
import ldap  #in EXASOL 5.0.15 the python ldap library is preinstalled

def run(ctx):

	try:
		uri = "ldap://192.168.99.100"  #ldap/AD server
		user = "cn=admin,dc=ldap,dc=example,dc=org"  # technical user for LDAP
		password = "mysecretpassword"  #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()		

/

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:

OPEN SCHEMA AD;

--/
CREATE OR REPLACE LUA SCRIPT "SYNC_AD_GROUPS_TO_DB_ROLES_AND_USERS" () RETURNS TABLE AS
dcl = query([[

WITH 

get_ad_group_members AS (
		SELECT  
		AD.GET_AD_ATTRIBUTE(ROLE_COMMENT, 'member')
		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(VAL, 'uid') 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(VAL, 'uid') 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 ;

]])

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

return dcl

/
 
 

 

Step 3: Run the script regularly

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

EXECUTE SCRIPT AD.SYNC_AD_GROUPS_TO_DB_ROLES_AND_USERS(); --execute this script periodically 

 

Additional Notes

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

 

Additional References