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

    XMLWordPrintable

    Details

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

      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
      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:
            2 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated: