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
