Roles/Privilege Table Access
Does anyone know a way to access and cross reference the roles and privilege tables outside of standard reports? Neither is configured for access in advanced search by default and I’m unable to locate the references for the tables in SQL developer. The data has to be stored somewhere, right?
Not sure about roles specifically. I see that there is a USER_ROLE table, but when I query mine I get nothing. I don’t know if that’s an access issue for me, or if our Agile instance simply does not use this table. That said, roles are typically assigned to groups and users also assigned to groups. With that I can help. The following query shows how the users and user groups are associated via the USER_ASSIGNMENT table, just as Swagoto mentions above.
SELECT UG.NAME GROUP_NAME,AU.LOGINID USERNAME, AU.EMAIL, AU.TITLE
FROM AGILE.USER_GROUP UG
INNER JOIN AGILE.USER_ASSIGNMENT UA ON UG.ID = UA.OBJECTID
INNER JOIN AGILE.AGILEUSER AU ON UA.USER_ID = AU.ID
ORDER BY UA.CREATED DESC;
It actually took a lot of bashing my head against the wall, but I think I’ve got something that works. We don’t use user groups, so I had to take the long way around.
This is hacked together like you wouldn’t believe, so I don’t doubt there’s room for optimization here. However, this is actually doing the job for me, if anyone else is looking for something to do something similar. I wouldn’t have gotten here without an answer to someone else’s question posted 3 years ago, so I’m happy to pay it forward.
SELECT DISTINCT role.description ROLE, priv.description PRIVILEGE
FROM user_assignment ua1
JOIN user_assignment ua2 ON ua1.ID = ua2.ID
JOIN nodetable role ON ua1.objectid = role.id
JOIN adminmsatt a ON ua2.objectID = a.parentID
JOIN nodetable priv ON priv.ID = a.value
WHERE ua1.objectID = ua2.objectID
AND priv.objtype = 22
AND priv.parentID = 12075
ORDER BY role.description, priv.description ASC;