Application Module - User accessibility

This report provides insights into user visibility and access across different modules within a system, along with a total user count.

Key Components:

Module-Specific User Counts: The report first counts the number of distinct users who have visibility and access to specific modules
Total User Count: Additionally, the report calculates the total number of distinct users in the system, regardless of their access to specific modules.

The report allows system administrators or analysts to quickly understand how many users have access to each module and to compare that with the total number of users in the system. This can be useful for auditing permissions, optimizing module access, or ensuring proper user role assignments.

Oracle

SQL

SELECT module_name, user_count
FROM (
    SELECT sv."name" AS module_name, 
           COUNT(DISTINCT urp.id_user) AS user_count,
           0 AS sort_order
    FROM structure_visibility sv
    JOIN user_role_structure_permission ursp ON sv.id = ursp.id_structure_visibility
    JOIN user_role_rel urp ON ursp.id_role = urp.id_role
    WHERE sv.id_structure_category = 1 
      AND sv.visible = 1 
      AND ursp.visible = 1
    GROUP BY sv.id, sv."name"
    
    UNION ALL
    
    SELECT 'Total user count' AS module_name, 
           COUNT(DISTINCT id) AS user_count,
           1 AS sort_order
    FROM "user"
)
ORDER BY sort_order, module_name

MSSQL

CODE

SELECT module_name, user_count
FROM (
    SELECT sv.[name] AS module_name, 
           COUNT(DISTINCT urp.id_user) AS user_count,
           0 AS sort_order
    FROM structure_visibility sv
    JOIN user_role_structure_permission ursp ON sv.id = ursp.id_structure_visibility
    JOIN user_role_rel urp ON ursp.id_role = urp.id_role
    WHERE sv.id_structure_category = 1 
      AND sv.visible = 1 
      AND ursp.visible = 1
    GROUP BY sv.id, sv.[name]
 
    UNION ALL
 
    SELECT 'Total user count' AS module_name, 
           COUNT(DISTINCT id) AS user_count,
           1 AS sort_order
    FROM [user]
) AS combined_results
ORDER BY sort_order, module_name;