Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-445

Synchronization of LDAP / Active Directory Groups and Members to EXASOL Database Users and Roles

    Details

    • Type: How To
    • Status: Obsolete
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: EXAPowerlytics, EXASolution
    • Labels:
      None
    • Solution:
      Hide

      Note: This solution is no longer maintained. Please find the latest information in our Knowledge Base:

      https://community.exasol.com/t5/database-features/synchronization-of-ldap-active-directory-groups-and-members-to/ta-p/1686

      -----------------------------------------------------------------------------------------------------------------------

      Tag your database roles

      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';
      

      Base Python UDF to retrieve information/ attributes from LDAP

      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()		
      
      /
      

      Write a LUA Script for synchronization/mapping of the LDAP groups to database users and roles

      The script will generate and execute CREATE/DROP USERs and GRANTs / REVOKEs, e.g.:

      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
      
      /
      

      Execute the script for synchronization

      You can also trigger the script every n minutes from cron for regular synchronization

      EXECUTE SCRIPT AD.SYNC_AD_GROUPS_TO_DB_ROLES_AND_USERS(); --execute this script periodically 
      
      Show
      Note: This solution is no longer maintained. Please find the latest information in our Knowledge Base: https://community.exasol.com/t5/database-features/synchronization-of-ldap-active-directory-groups-and-members-to/ta-p/1686 ----------------------------------------------------------------------------------------------------------------------- Tag your database roles 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' ; Base Python UDF to retrieve information/ attributes from LDAP 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() / Write a LUA Script for synchronization/mapping of the LDAP groups to database users and roles The script will generate and execute CREATE/DROP USERs and GRANTs / REVOKEs, e.g.: 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 / Execute the script for synchronization You can also trigger the script every n minutes from cron for regular synchronization EXECUTE SCRIPT AD.SYNC_AD_GROUPS_TO_DB_ROLES_AND_USERS(); --execute this script periodically
    • Category 1:
      Database Administration
    • Category 2:
      UDFs and In-Database Analytics

      Attachments

        Activity

          People

          • Assignee:
            CaptainEXA Captain EXASOL
            Reporter:
            CaptainEXA Captain EXASOL
          • Votes:
            4 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: