SQL query to find the Users that has role provisioned by an role mapping — Cloud Customer Connect
You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register

SQL query to find the Users that has role provisioned by an role mapping

I'm trying to create a query that return the MAPPING_NAME from the PER_ROLE_MAPPINGS table and the USER_ID from the PER_USER_ROLES table to know which USER_ID is associated with which MAPPING_NAME. However, these two tables are not directly linked, it is necessary to carry out checks on other tables to reach this junction.

I created an SQL query with the information I found and this was the result:

SELECT prm.MAPPING_NAME, COUNT(pur.USER_ID) AS TOTAL_USER_IDS

FROM PER_ROLE_MAPPINGS prm

LEFT JOIN PER_ROLE_MAPPING_ROLES prmr ON prm.ROLE_MAPPING_ID = prmr.ROLE_MAPPING_ID

Howdy, Stranger!

Log In

To view full details, sign in.

Register

Don't have an account? Click here to get started!