Open Hyhyhyhyhyhyh opened 4 years ago
# -*- coding:utf-8 -*-
import sys
import base64
import binascii
import cx_Oracle
import logging
import os
from ldap3 import Server, Connection, ALL, MODIFY_REPLACE
os.environ['NLS_LANG'] = ''
os.environ['ORACLE_HOME'] = ''
# 源关系型数据库连接
oa_conn = cx_Oracle.connect('')
# openLDAP连接
ldap_conn = Connection(Server(ldap_ip, ldap_port, use_ssl=True, get_info=ALL), user=ldap_dn,
password=ldap_passwd, auto_bind=True, version=3)
# 由于源系统中的密码经过MD5加密后存放在库表中,因此只需要对MD5密码进行base64加密即可转换为openLDAP中的密文密码
def MD5toBASE64(pwd_md5):
"""
把MD5密文进行base64编码为LDAP的密码格式
"""
pwd_byte = binascii.a2b_hex(pwd_md5)
pwd_base64 = base64.b64encode(pwd_byte).decode('utf-8')
return '{MD5}' + pwd_base64
def oa_query_org():
"""
查询OA组织架构
:return: [公司1, 公司2, ...]
"""
curs = oa_conn.cursor()
try:
sql = """select company_name from xxx"""
curs.execute(sql)
subcompany_list = curs.fetchall()
subcompany_list = [c[0] for c in subcompany_list]
return subcompany_list
except Exception as e:
sys.exit(0)
finally:
curs.close()
def ldap_create_ou(company):
"""
创建子公司ou
:param subcompany: 子公司名
"""
ldap_add_dn = 'ou={0},ldap_base_dn'.format(company)
ldap_conn.add(ldap_add_dn, ['top', 'organizationalUnit'], attributes={'ou': company})
def ldap_add_user(username, company):
"""
创建ldap账号
:param username: OA账号名
:param company: 账号所属公司
:return:
"""
# 获取OA账号信息
curs = oa_conn.cursor()
sql = """select password,lastname,id
from xxx where company={0} and username={1}""".format(company, username)
curs.execute(sql)
result = curs.fetchone()
userpwd = result[0]
displayname = result[1]
userid = result[2]
try:
# 判断新账号所在的公司ou是否存在,不存在则新建ou
if not ldap_conn.compare(f'ou={company},ldap_base_dn', 'ou', company):
ldap_create_ou(company)
# 在LDAP新建用户
ldap_add_dn = 'uid={0},ou={1},ldap_base_dn'.format(username, company)
ldap_conn.add(ldap_add_dn, ['inetorgperson', 'posixAccount'], attributes={'cn': username,
'sn': username,
'userPassword': MD5toBASE64(userpwd),
'uidNumber': userid,
'gidNumber': userid,
'displayName': displayname,
'homeDirectory': '/'})
return True
except:
return False
def sync_full(company):
"""
全量同步账号密码
:param company:
"""
curs = oa_conn.cursor()
sql = """select username,password,userid,a.lastname
from xxx where company={}""".format(company)
curs.execute(sql)
people_list = curs.fetchall()
# 把OA中的账号加入到对应ou下
for t in people_list:
try:
ldap_add_dn = f'uid={t[0]},ou={company},ldap_base_dn'
# print(ldap_add_dn)
userpwd = MD5toBASE64(t[1])
ldap_conn.add(ldap_add_dn, ['inetorgperson', 'posixAccount'], attributes={'cn': t[0],
'sn': t[0],
'userPassword': userpwd,
'uidNumber': t[2],
'gidNumber': t[2],
'displayName': t[3],
'homeDirectory': '/'})
except Exception as e:
print(t[0], t[1], str(e))
def sync_inc(company):
"""
增量同步用户信息
:param company:
"""
curs = oa_conn.cursor()
sql = """select username,password,userid,a.lastname
from xxx where company={}""".format(company)
curs.execute(sql)
people_list = curs.fetchall()
# 获取LDAP用户信息
sync_list = []
for t in people_list:
try:
username = t[0]
oa_userpwd = MD5toBASE64(t[1])
user_dn = 'uid={0},ou={1},ldap_base_dn'.format(username, company)
# 如果在ldap中找到相同用户名,则与OA的公司名和密码对比,以OA的信息更新ldap
if ldap_conn.search('ldap_base_dn', '(uid={})'.format(username), attributes=['userPassword']):
ldap_userpwd = ldap_conn.response[0]['attributes']['userPassword'][0].decode('utf-8')
ldap_company = ldap_conn.response[0]['dn'].split(',ou=')[1]
else:
# 如果没找到用户名,则把用户信息插入LDAP
if ldap_add_user(username, company):
sync_list.append("用户" + username + "...新增成功")
else:
sync_list.append("用户" + username + "...新增失败")
# 将ldap账号的公司更新为OA账号的公司
if company != ldap_company:
ldap_conn.modify_dn(f'uid={username},ou={ldap_company},ldap_base_dn',
f'uid={username}',
new_superior=f'ou={company},ldap_base_dn')
# 将账号的OA密码更新至ldap
if oa_userpwd != ldap_userpwd:
ldap_conn.modify(user_dn, {'userPassword': [(MODIFY_REPLACE, [oa_userpwd])]})
sync_list.append("用户" + username + "...密码更新成功")
except Exception as e:
return t[0], t[1], str(e)
curs.close()
return sync_list
if __name__ == '__main__':
logging.basicConfig(
level=logging.INFO,
format="%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s", # 日志的格式
datefmt=" %Y-%m-%d %H:%M:%S", # 时间格式
filename="./ldap_sync.log", # 指定文件位置
filemode="a",
)
# 增量同步账号密码
company_list = oa_query_org()
for i in company_list:
sync_info = sync_inc(i)
logging.info(i + '增量同步了{}个用户:'.format(len(sync_info)))
logging.info(sync_info)
logging.info('-' * 100)
oa_conn.close()
ldap_conn.unbind()
需求
定时将关系型数据库(OA/人力系统等)用户组织架构到openLDAP中,同步信息如下: