def get_public_project_pairs(self, anon_id): """ :param anon_id: Anonymous user_id :return: List of tuples (project_id, trac_environment_key) """ # TODO: it's not clearly defined when project is public perms = ['PROJECT_VIEW', 'TRAC_ADMIN'] perm_ids = [get_permission_id(perm) for perm in perms] query = """SELECT DISTINCT p.project_id, p.trac_environment_key FROM projects p INNER JOIN `group` g ON g.trac_environment_key = p.trac_environment_key INNER JOIN user_group ON user_group.group_key = g.group_id INNER JOIN group_permission ON group_permission.group_key = g.group_id WHERE user_group.user_key = {0} AND group_permission.permission_key IN ({1}) """.format(anon_id, ', '.join([str(int(id)) for id in perm_ids])) id_pairs = [] with admin_query() as cursor: try: cursor.execute(query) for row in cursor: id_pair = (int(row[0]), int(row[1])) id_pairs.append(id_pair) except Exception as e: if self.verbose is not None: print "Exception. In method get_public_project_pairs, the following query failed." print query print e conf.log.exception("Exception. ProjectUserVisibilityGenerator.get_public_project_pairs " "failed with query '''%s'''." % query) return id_pairs
def get_public_project_pairs(self, anon_id): """ :param anon_id: Anonymous user_id :return: List of tuples (project_id, trac_environment_key) """ # TODO: it's not clearly defined when project is public perms = ['PROJECT_VIEW', 'TRAC_ADMIN'] perm_ids = [get_permission_id(perm) for perm in perms] query = """SELECT DISTINCT p.project_id, p.trac_environment_key FROM projects p INNER JOIN `group` g ON g.trac_environment_key = p.trac_environment_key INNER JOIN user_group ON user_group.group_key = g.group_id INNER JOIN group_permission ON group_permission.group_key = g.group_id WHERE user_group.user_key = {0} AND group_permission.permission_key IN ({1}) """.format( anon_id, ', '.join([str(int(id)) for id in perm_ids])) id_pairs = [] with admin_query() as cursor: try: cursor.execute(query) for row in cursor: id_pair = (int(row[0]), int(row[1])) id_pairs.append(id_pair) except Exception as e: if self.verbose is not None: print "Exception. In method get_public_project_pairs, the following query failed." print query print e conf.log.exception( "Exception. ProjectUserVisibilityGenerator.get_public_project_pairs " "failed with query '''%s'''." % query) return id_pairs
def upgrade(self): if self.applied(): return meta_perms = ['VIEW', 'MODIFY', 'CREATE', 'DELETE'] meta_perm_ids = [] perm_map = {} for meta_perm in meta_perms: meta_perm_ids.append(get_permission_id(meta_perm)) # retrieve all permissions and build map of {'metaperm': [normal perm ids]} # get straight from the db since eg. home env does not have all permissions in it for perm in PermissionSystem(MockEnvironment()).get_actions(): for meta_perm in meta_perms: if perm.endswith(meta_perm): perm_map.setdefault(meta_perm, []).append(get_permission_id(perm)) # combine so that DELETE permission for instance contains VIEW as well gives = {} gives['DELETE'] = ['DELETE', 'VIEW', 'MODIFY', 'CREATE'] gives['CREATE'] = ['CREATE', 'VIEW', 'MODIFY'] gives['MODIFY'] = ['MODIFY', 'VIEW'] gives['VIEW'] = ['VIEW'] combined_map = {} for what, metaperms in gives.iteritems(): perms = [] for p in metaperms: perms.extend(perm_map[p]) combined_map[what] = perms perm_map = combined_map queries = [] # previous get_permission_id calls may create new permissions, that are not in this transaction # hack around that ... verified manually and via CHECK TABLE that the result is consistent queries.append('SET foreign_key_checks = 0') # replace meta perms for meta_perm in meta_perms: meta_perm_id = get_permission_id(meta_perm) # go trough all groups having this permission with admin_query(cursors.DictCursor) as cursor: cursor.execute( 'SELECT group_key FROM group_permission WHERE permission_key=%s', meta_perm_id) for row in cursor: group_key = row['group_key'] for new_perm in perm_map[meta_perm]: query = 'REPLACE INTO `group_permission` SET `group_key`=%s, `permission_key`=%s' % \ (group_key, new_perm) queries.append(query) # delete metaperms for meta_perm in meta_perms: queries.append( "DELETE FROM `action` WHERE `action_string` = '%s'" % meta_perm) for id in meta_perm_ids: queries.append( "DELETE FROM group_permission WHERE permission_key = %s" % id) queries.append('SET foreign_key_checks = 1') return self.manager.db_upgrade(queries)
def get_participated_projects(self, user, by_organization=False, by_ldap=False, public_only=False): """ Get those projects that user has participated. Optionally can list by organization, or by public status. :param User user: User object :param boolean by_organization: List projects by organization :param boolean public_only: Get public projects as well :returns: List of Project objects """ # Anonymous does not have organization if not user.organization_keys: by_organization = False and_anon_condition= '' union_all_organization_condition = '' union_all_ldap_condition = '' perm_ids = ', '.join([str(safe_int(get_permission_id(action))) for action in ('TEAM_VIEW',)]) if public_only: # fetch anonymous user id # FIXME: Would be nice if we didn't have to do this all the time anon = get_userstore().getUser('anonymous') if not anon: conf.log.warning("Error in get_participated_projects: No anonymous user obtained!") raise TracError("Error while fetching user's projects.") and_anon_condition = """ AND EXISTS (SELECT group.trac_environment_key FROM `group` INNER JOIN user_group ON user_group.group_key = group.group_id INNER JOIN group_permission ON group.group_id = group_permission.group_key WHERE user_group.user_key = {anon_id} AND group.trac_environment_key = projects.trac_environment_key AND group_permission.permission_key IN ({perm_ids}) ) """.format(anon_id = safe_int(anon.id), perm_ids = perm_ids) if by_organization: # See if the user has access into projects through organizations union_all_organization_condition += """ UNION ALL SELECT group.trac_environment_key FROM `trac_admin`.`group` INNER JOIN organization_group ON organization_group.group_key = group.group_id WHERE organization_group.organization_key IN({organization_ids}) """.format(organization_ids = ', '.join([str(safe_int(org_id)) for org_id in user.organization_keys])) if by_ldap and conf.ldap_groups_enabled: # See if any ldap groups are allowed in environment auth_store = CQDEAuthenticationStore.instance() is_ldap_account = auth_store.is_ldap(user.authentication_key) if is_ldap_account: # See if user belongs to any of the allowed ldap groups ldapuser_store = conf.getAuthenticationStore() user_ldapgroups = ldapuser_store.getGroups(user.username) if user_ldapgroups: union_all_ldap_condition = """ UNION ALL SELECT group.trac_environment_key FROM `trac_admin`.`group` INNER JOIN ldapgroup_group ON ldapgroup_group.group_key = group.group_id INNER JOIN ldapgroup ON ldapgroup.ldapgroup_id = ldapgroup_group.ldapgroup_key WHERE ldapgroup.ldapgroup_name IN ({ldapgroup_names}) """.format(ldapgroup_names = ', '.join(["'{0}'".format(safe_string(group)) for group in user_ldapgroups])) query = """ SELECT projects.* FROM projects WHERE projects.trac_environment_key IN ( SELECT group.trac_environment_key FROM `trac_admin`.`group` INNER JOIN user_group ON user_group.group_key = group.group_id WHERE user_group.user_key = {user_id} {union_all_organization} {union_all_ldap} ) {and_anon} ORDER BY projects.project_name ASC """.format(user_id = safe_int(user.id), union_all_organization = union_all_organization_condition, union_all_ldap = union_all_ldap_condition, and_anon = and_anon_condition) conf.log.debug("queried participated projects with %s" % query) projects = self.queryProjectObjects(query) return projects
def upgrade(self): if self.applied(): return meta_perms = ['VIEW', 'MODIFY', 'CREATE', 'DELETE'] meta_perm_ids = [] perm_map = {} for meta_perm in meta_perms: meta_perm_ids.append(get_permission_id(meta_perm)) # retrieve all permissions and build map of {'metaperm': [normal perm ids]} # get straight from the db since eg. home env does not have all permissions in it for perm in PermissionSystem(MockEnvironment()).get_actions(): for meta_perm in meta_perms: if perm.endswith(meta_perm): perm_map.setdefault(meta_perm, []).append(get_permission_id(perm)) # combine so that DELETE permission for instance contains VIEW as well gives = {} gives['DELETE'] = ['DELETE', 'VIEW', 'MODIFY', 'CREATE'] gives['CREATE'] = ['CREATE', 'VIEW', 'MODIFY'] gives['MODIFY'] = ['MODIFY', 'VIEW'] gives['VIEW'] = ['VIEW'] combined_map = {} for what, metaperms in gives.iteritems(): perms = [] for p in metaperms: perms.extend(perm_map[p]) combined_map[what] = perms perm_map = combined_map queries = [] # previous get_permission_id calls may create new permissions, that are not in this transaction # hack around that ... verified manually and via CHECK TABLE that the result is consistent queries.append('SET foreign_key_checks = 0') # replace meta perms for meta_perm in meta_perms: meta_perm_id = get_permission_id(meta_perm) # go trough all groups having this permission with admin_query(cursors.DictCursor) as cursor: cursor.execute('SELECT group_key FROM group_permission WHERE permission_key=%s', meta_perm_id) for row in cursor: group_key = row['group_key'] for new_perm in perm_map[meta_perm]: query = 'REPLACE INTO `group_permission` SET `group_key`=%s, `permission_key`=%s' % \ (group_key, new_perm) queries.append(query) # delete metaperms for meta_perm in meta_perms: queries.append("DELETE FROM `action` WHERE `action_string` = '%s'" % meta_perm) for id in meta_perm_ids: queries.append("DELETE FROM group_permission WHERE permission_key = %s" % id) queries.append('SET foreign_key_checks = 1') return self.manager.db_upgrade(queries)