Hi All,
I'm writing to seek assistance with a discrepancy we've encountered regarding default permissions queries and SOX compliance queries in our Bitbucket database.
Default Permissions Query:
SELECT distinct perm.perm_id,(case When perm.perm_id = 4 THEN 'Project Admin' WHEN perm.PERM_ID = 3 THEN 'Project Write' WHEN perm.PERM_ID = 2 THEN 'Project Read' END ) AS "Permissions", proj.project_key, proj.name, perm.user_id, perm.group_name
FROM project AS proj,sta_project_permission AS perm, sta_permission_type AS type WHERE proj.id=perm.project_id AND perm.user_id IS null AND type.perm_id=perm.perm_id AND perm.group_name IS null order by proj.name asc;
We have a query that retrieves default permissions from the sta_project_permission table where both user_id and group_name are NULL. This query seems to be working fine and returns the expected default permissions for projects.
Query 2 to get more information with Default Permissions:
select distinct p.id as "Project ID", p.project_key as "Project Key", p.name as "Project Name",cu.user_name as "Username (Individual)",
Concat(cu.first_name, ' ', cu.last_name) as "Fullname (Individual)",pp.group_name as "Group Access", cu1.user_name as "Username (Group)",Concat(cu1.first_name, ' ', cu1.last_name) as "Fullname (Group)",cd.directory_name as "Directory (Individual)",
cd.directory_type as "Directory Type (Group)", cd1.directory_name as "Directory (Group)",cd1.directory_type as "Directory Type (Group)", pp.perm_id as"Perm ID",
(case When pp.perm_id = 4 THEN 'Project Admin' WHEN PP.PERM_ID = 3 THEN 'Project Write' WHEN PP.PERM_ID = 2 THEN 'Project Read' END ) AS "Permissions"
from project p
join sta_normal_project np on np.project_id = p.id left join sta_project_permission pp on pp.project_id = p.id left join sta_normal_user u on pp.user_id = u.user_id left join cwd_user cu on cu.lower_user_name = u.name
left join cwd_membership cm on cm.lower_parent_name = pp.group_name left join cwd_user cu1 on cu1.lower_user_name = cm.lower_child_name left join cwd_directory cd on cd.id = cu.directory_id left join cwd_directory cd1 on cd1.id = cu1.directory_id
where pp.perm_id is not null and cu.user_name is null and pp.group_name is null order by p.name;
Our Query 2, which is more complex and involves multiple joins, is experiencing issues. Despite filtering for rows where user_name is NULL (indicating it's not an individual permission) and group_name is NULL (indicating default permissions), we're seeing extra rows compared to the default permissions query.
We've reviewed the join conditions, inspected data in related tables such as sta_normal_user, cwd_membership, and cwd_user, and verified the filtering conditions in the WHERE clause. However, we're still encountering discrepancies.
The additional projects which have both "Read Access" and "Write Access" and are not expected based on our understanding of default permissions.
We suspect these records may be stagnant and failing to update properly. Can any one help if our assumption is accurate, or provide insight into why these records are appearing in the query results despite our filtering criteria?
Your expertise and guidance in resolving this issue would be immensely valuable.
More Information:
Our Appliction is server Edition,
Application Version - 6.9.0
DB version - 9.4.8
