def update_ad_teams():
db = app.db.dbWriteInstance()
ad = app.ad.ActiveDirectory()
cur = db.cursor()
cur.execute("SELECT userid FROM tblusers WHERE team IS NULL")
users = {}
while True:
rc = cur.fetchone()
if not rc:
break
users[rc[0].strip().lower()] = rc[0].strip()
mapping = json.loads(config.group_mapping)
userMapping = {}
for group in mapping:
adGroup = group[0]
name = group[1]
print "Processing %s (%s)" % (adGroup, name)
allGroupUsers = ad.get_all_members_of_group(adGroup)
for u in allGroupUsers:
if u.lower() in users:
userMapping[users[u.lower()]] = name
del users[u.lower()]
for u in userMapping:
print "Mapping %s to %s" % (u, userMapping[u])
cur.execute("UPDATE tblusers SET team=%s WHERE userid=%s",
[userMapping[u], u])
db.commit()
def update_ad_teams():
db = app.db.dbWriteInstance()
ad = app.ad.ActiveDirectory()
cur = db.cursor()
cur.execute("SELECT userid FROM tblusers WHERE team IS NULL")
users = {}
while True:
rc = cur.fetchone()
if not rc:
break
users[rc[0].strip().lower()] = rc[0].strip()
mapping = json.loads(config.group_mapping)
userMapping = {}
for group in mapping:
adGroup = group[0]
name = group[1]
print "Processing %s (%s)" % (adGroup, name)
allGroupUsers = ad.get_all_members_of_group(adGroup)
for u in allGroupUsers:
if u.lower() in users:
userMapping[users[u.lower()]] = name
del users[u.lower()]
for u in userMapping:
print "Mapping %s to %s" % (u, userMapping[u])
cur.execute("UPDATE tblusers SET team=%s WHERE userid=%s", [userMapping[u], u])
db.commit()
def refresh_ad_caches(removeUsers=False):
db = app.db.dbWriteInstance()
ad = app.ad.ActiveDirectory()
cur = db.cursor()
print "Validating users in tblusers..."
cur.execute("SELECT userid,email,disabled FROM tblusers")
userCount = 0
usersToRemove = []
usersToDisable = []
usersToEnable = []
emailUpdates = []
while True:
rc = cur.fetchone()
if not rc:
break
userCount += 1
userid = rc[0].strip()
try:
adEmail = ad.get_email(userid)
dbEmail = rc[1] and rc[1].strip()
if adEmail != dbEmail:
emailUpdates.append((userid, adEmail))
adDisabled = ad.is_disabled(userid)
dbDisabled = rc[2]
if adDisabled and not dbDisabled:
usersToDisable.append(userid)
elif not adDisabled and dbDisabled:
usersToEnable.append(userid)
except KeyError:
usersToRemove.append(userid)
if len(usersToRemove) > (userCount / 20):
raise Exception(
"AD suggests >5% of users no longer exist, this seems unlikely...")
if removeUsers:
for u in usersToRemove:
print "Removing %s" % u
cur.execute("DELETE FROM tblusers WHERE userid=%s", [u])
elif len(usersToRemove) > 0:
print "There are %d users no longer in AD" % (len(usersToRemove))
for u, e in emailUpdates:
print "Updating email address for %s (%s)" % (u, e)
cur.execute("UPDATE tblusers SET email=%s WHERE userid=%s", [e, u])
for u in usersToEnable:
print "Enabling %s" % u
cur.execute("UPDATE tblusers SET disabled='0' WHERE userid=%s", [u])
for u in usersToDisable:
print "Disabling %s" % u
cur.execute("UPDATE tblusers SET disabled='1' WHERE userid=%s", [u])
print "\nRefreshing group cache..."
def _deleteGroup(groupid):
cur.execute("DELETE FROM tblgroupusers WHERE groupid=%s", [groupid])
cur.execute("DELETE FROM tblgroups WHERE groupid=%s", [groupid])
cur.execute("SELECT groupid,name FROM tblgroups")
groups = {}
while True:
rc = cur.fetchone()
if not rc:
break
groups[rc[0]] = rc[1].strip()
aclGroups = []
cur.execute("SELECT userid FROM tblaclentries WHERE type='group'")
while True:
rc = cur.fetchone()
if not rc:
break
aclGroups.append(rc[0].strip())
# Also put the admin group in the cache
if not config.admin_group in aclGroups:
aclGroups.append(config.admin_group)
# Add any groups not in the DB to the DB
extraGroups = set(aclGroups) - set(groups.values())
for g in extraGroups:
cur.execute(
"INSERT INTO tblgroups (name) VALUES (%s) RETURNING groupid", [g])
rc = cur.fetchone()
groups[rc[0]] = g
for gid in groups:
gname = groups[gid]
print gname,
# Check if the group is still actually in use by any acls
if not gname in aclGroups:
print "..No longer required, removing"
_deleteGroup(gid)
continue
# Is it valid in AD?
if not ad.is_valid_group(gname):
print "..not found in AD, removing"
_deleteGroup(gid)
# TODO: Email the ACL owner(s) to let them know we've removed entries pointing at the group
cur.execute(
"DELETE FROM tblaclentries WHERE type='group' AND userid=%s",
[gname])
continue
# Update the members
adMembers = ad.get_all_members_of_group(gname)
dbMembers = []
cur.execute("SELECT userid FROM tblgroupusers WHERE groupid=%s", [gid])
while True:
rc = cur.fetchone()
if not rc:
break
dbMembers.append(rc[0].strip())
am = set(adMembers)
dm = set(dbMembers)
newMembers = am - dm
removeMembers = dm - am
for m in newMembers:
cur.execute(
"INSERT INTO tblgroupusers (groupid,userid) VALUES (%s,%s)",
[gid, m])
for m in removeMembers:
cur.execute(
"DELETE FROM tblgroupusers WHERE groupid=%s AND userid=%s",
[gid, m])
print "..%d added, %d removed" % (len(newMembers), len(removeMembers))
db.commit()
def refresh_ad_caches(removeUsers=False):
db = app.db.dbWriteInstance()
ad = app.ad.ActiveDirectory()
cur = db.cursor()
print "Validating users in tblusers..."
cur.execute("SELECT userid,email,disabled FROM tblusers")
userCount = 0
usersToRemove = []
usersToDisable = []
usersToEnable = []
emailUpdates = []
while True:
rc = cur.fetchone()
if not rc:
break
userCount += 1
userid = rc[0].strip()
try:
adEmail = ad.get_email(userid)
dbEmail = rc[1] and rc[1].strip()
if adEmail != dbEmail:
emailUpdates.append((userid, adEmail))
adDisabled = ad.is_disabled(userid)
dbDisabled = rc[2]
if adDisabled and not dbDisabled:
usersToDisable.append(userid)
elif not adDisabled and dbDisabled:
usersToEnable.append(userid)
except KeyError:
usersToRemove.append(userid)
if len(usersToRemove) > (userCount / 20):
raise Exception("AD suggests >5% of users no longer exist, this seems unlikely...")
if removeUsers:
for u in usersToRemove:
print "Removing %s" % u
cur.execute("DELETE FROM tblusers WHERE userid=%s", [u])
elif len(usersToRemove) > 0:
print "There are %d users no longer in AD" % (len(usersToRemove))
for u,e in emailUpdates:
print "Updating email address for %s (%s)" % (u,e)
cur.execute("UPDATE tblusers SET email=%s WHERE userid=%s", [e,u])
for u in usersToEnable:
print "Enabling %s" % u
cur.execute("UPDATE tblusers SET disabled='0' WHERE userid=%s", [u])
for u in usersToDisable:
print "Disabling %s" % u
cur.execute("UPDATE tblusers SET disabled='1' WHERE userid=%s", [u])
print "\nRefreshing group cache..."
def _deleteGroup(groupid):
cur.execute("DELETE FROM tblgroupusers WHERE groupid=%s", [groupid])
cur.execute("DELETE FROM tblgroups WHERE groupid=%s", [groupid])
cur.execute("SELECT groupid,name FROM tblgroups")
groups = {}
while True:
rc = cur.fetchone()
if not rc:
break
groups[rc[0]] = rc[1].strip()
aclGroups = []
cur.execute("SELECT userid FROM tblaclentries WHERE type='group'")
while True:
rc = cur.fetchone()
if not rc:
break
aclGroups.append(rc[0].strip())
# Also put the admin group in the cache
if not config.admin_group in aclGroups:
aclGroups.append(config.admin_group)
# Add any groups not in the DB to the DB
extraGroups = set(aclGroups) - set(groups.values())
for g in extraGroups:
cur.execute("INSERT INTO tblgroups (name) VALUES (%s) RETURNING groupid", [g])
rc = cur.fetchone()
groups[rc[0]] = g
for gid in groups:
gname = groups[gid]
print gname,
# Check if the group is still actually in use by any acls
if not gname in aclGroups:
print "..No longer required, removing"
_deleteGroup(gid)
continue
# Is it valid in AD?
if not ad.is_valid_group(gname):
print "..not found in AD, removing"
_deleteGroup(gid)
# TODO: Email the ACL owner(s) to let them know we've removed entries pointing at the group
cur.execute("DELETE FROM tblaclentries WHERE type='group' AND userid=%s", [gname])
continue
# Update the members
adMembers = ad.get_all_members_of_group(gname)
dbMembers = []
cur.execute("SELECT userid FROM tblgroupusers WHERE groupid=%s", [gid])
while True:
rc = cur.fetchone()
if not rc:
break
dbMembers.append(rc[0].strip())
am = set(adMembers)
dm = set(dbMembers)
newMembers = am - dm
removeMembers = dm - am
for m in newMembers:
cur.execute("INSERT INTO tblgroupusers (groupid,userid) VALUES (%s,%s)", [gid, m])
for m in removeMembers:
cur.execute("DELETE FROM tblgroupusers WHERE groupid=%s AND userid=%s", [gid, m])
print "..%d added, %d removed" % (len(newMembers), len(removeMembers))
db.commit()
