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)
