on 13-11-2020 03:54 PM
With the help of the below scripts, you can set up an automatic synchronization of LDAP/AD Groups with Exasol users and roles.
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';
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)
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'])
finally:
ldapClient.unbind_s()
/
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
/
You should execute this script periodically. The parameters of the script are:
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
This script can be used as a starting point and may require some modification to meet your exact use case.
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?
Hi @MaJo,
Nico has been off last week, I nudged him for an answer this morning. Keeü you posted.
Thanks for the question
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
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!
@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;
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>';
I worked with @MaJo on his problem running the script and the issue was
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!
Then it's time to become part of a unique family! Discover helpful tips and support other Community members with your knowledge.
Sign In