SQL Get roles and access policies with number of users : Support Center

SQL Get roles and access policies with number of users Print

Created by: Yaniv Rozenzweig

Modified on: Tue, 5 Apr, 2022 at 2:05 PM

following query will return all roles and their accoisacted access policies + number of users in each role.

what is unique in this SQL it will also return roles without Access policies or users as well

SELECT r.Ugp_Name as "ROLE",Ugp_Description as "Role Description",apolicy.APNAME as "Access Policy", COUNT(u.ugp_key) AS "Number Of Users "

FROM

ugp r LEFT JOIN usg u ON u.ugp_key = r.ugp_key

LEFT JOIN (select pog.ugp_key, pol.pol_name as "APNAME" from pol,pog where pol.pol_key=pog.pol_key) apolicy ON u.ugp_key = apolicy.ugp_key

GROUP BY r.Ugp_Name,Ugp_Description,apolicy.APNAME;

Yaniv is the author of this solution article.

Did you find it helpful? Yes No

SQL Get roles and access policies with number of users Print