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.
CREATE CONNECTION LDAP_SERVER TO 'ldap://<ldap_url>' user 'cn=admin,dc=ldap,dc=example,dc=org' identified by 'mysecretpassword';
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" ("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)
#The below line is only needed when connecting via ldaps
ldapClient.set_option(ldap.OPT_X_TLS_REQUIRE_CERT, ldap.OPT_X_TLS_NEVER) # required options for SSL without cert checking
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'] + ': ' + e.message['info'])
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:
--/
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
/
Step 3: Run the 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.
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
Additional Notes
This script can be used as a starting point and may require some modification to meet your exact use case.
Additional References
