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()