Roles/Privilege Table Access
Hi,
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?
Thanks!
Dear tsatz,
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
,UA.CREATED
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;
Steve L.