Oracle BI Get users with a role

 Very handy query to get all users with a given role.

SELECT pu.username,
       prdt.role_id,
       prdt.role_name,
       prd.role_common_name
FROM   per_user_roles pur,
       per_users pu,
       per_roles_dn_tl prdt,
       per_roles_dn prd
WHERE  pu.user_id = pur.user_id
       AND prdt.role_name LIKE 'IT Security Manager'
       AND prdt.role_id = pur.role_id
       AND prdt.role_id = prd.role_id
       AND prdt.language = Userenv ('lang')
       AND pu.active_flag = 'Y'
ORDER  BY pu.username,
          prdt.role_name