Hi,
I’m trying to work back from WORKFLOWAPPID to the roles within the workflow to the users with these roles using the database.
I’ve identified many of the ROLE related tables but I think I’m missing one. I seem to be going round in circles.
Does anyone already have some SQL handy for what I’m trying to do?
Thanks.
Thanks, Nate, that helps. But if I do
select * from roles where workflowappid = XX
to get a list of ROLEIDs, I thought I could use those ids to do something like
select * from psx_role_subjects where roleid in (A,B,C...)
to get a list of all the SUBJECTIDs and then use those on the PSX_SUBJECTS table.
However, the ROLEIDs I get back don’t exist in the PSX_ROLE_SUBJECTS table.
I’m clearly missing a step with these indexes.
Yeah that is odd between ROLES AND PSX_ROLES… I am not sure how they are joined.
You could try a variation of this. This gives all of the subjects per role.
SELECT PSX_ROLES.NAME, PSX_SUBJECTS.NAME FROM PSX_SUBJECTS INNER JOIN PSX_ROLE_SUBJECTS ON PSX_SUBJECTS.ID=PSX_ROLE_SUBJECTS.SUBJECTID
INNER JOIN PSX_ROLES ON PSX_ROLES.ID = PSX_ROLE_SUBJECTS.ROLEID
WHERE PSX_ROLE_SUBJECTS.ROLEID IN(SELECT DISTINCT PSX_ROLES.ID FROM ROLES INNER JOIN PSX_ROLES ON ROLES.ROLENAME=PSX_ROLES.NAME) ORDER BY PSX_ROLES.NAME,PSX_SUBJECTS.NAME;
It is joining PSX_ROLE off of ROLES.NAME. Will ask around and see if anyone knows a more direct route.
-n
Thanks, Nate, appreciate this. Ideally I’m trying to get from a content item to the list of roles and users that have permissions in that item’s workflow.
Nate is correct. The role NAME is the link between roles and PSX_ROLES. If you are trying to find for a particular item you need to consider the state the item is in, the state id and workflowappid are in the contentstatus table and the roleid for a state is in stateroles (http://help.percussion.com/cmsystem/tiki-index.php?page=STATEROLES) the role id in stateroles is the workflow role id in the roles table, you then need to join this to the psx_roles by name and psx_role_subjects by the id in that table.
Remember though the security model in percussion is not just the workflow permissions, other elements may affect the visibility of the item e.g. community visibility (acls), folder security e.t.c
The following will give you all users with Assignee or Admin for all content items just add where contentstatus.contentid = xxx if you want an individual id. the >2 can be changed to >1 if you want to include read access or a particular assigment. Type of assignment 1: None 2: Reader 3: Assignee 4: Admin. This assumes that all roles are valid for all communities. If the workflow roles have been added to specific communities the user may still not have access if they are not in the right community.
SELECT PSX_SUBJECTS.NAME,
CONTENTSTATUS.CONTENTID
FROM PSX_ROLES
INNER JOIN ROLES
ON ROLES.ROLENAME = PSX_ROLES.NAME
INNER JOIN STATEROLES
ON STATEROLES.WORKFLOWAPPID = ROLES.WORKFLOWAPPID
AND STATEROLES.ROLEID = ROLES.ROLEID
INNER JOIN CONTENTSTATUS
ON CONTENTSTATUS.CONTENTSTATEID = STATEROLES.STATEID
AND CONTENTSTATUS.WORKFLOWAPPID = STATEROLES.WORKFLOWAPPID
INNER JOIN PSX_ROLE_SUBJECTS
ON PSX_ROLES.ID = PSX_ROLE_SUBJECTS.ROLEID
INNER JOIN PSX_SUBJECTS
ON PSX_ROLE_SUBJECTS.SUBJECTID = PSX_SUBJECTS.ID
WHERE STATEROLES.ASSIGNMENTTYPE > 2