Microsoft: Privileged Azure Role Holders for the Access Review
An Azure security engineer is preparing a quarterly access review of people who can change permissions or own resources.
- Difficulty: Medium
- Topics: Strings, Aggregation, Subqueries
- Dialect: MySQL
- Problem: #790
Problem statement
An Azure security engineer is preparing a quarterly access review of people who can change permissions or own resources.
For every User principal holding Owner or User Access Administrator at any scope, return principal_name, roles — all the distinct role names that user holds (privileged or not), sorted alphabetically and joined by ', ' — and scope_count, the number of distinct scopes they hold any role at. Groups and service principals are reviewed separately and are not listed. Return the rows in any order.
A practice scenario set at Microsoft: the tables and data are invented, and this is not a question from, or endorsed by, Microsoft.
Tables
Table: RoleAssignment
| Column | Type |
|---|---|
| assignment_id | int |
| principal_name | varchar |
| principal_type | enum(User, Group, ServicePrincipal) |
| role_name | varchar |
| scope | varchar |
Primary key: assignment_id.
One row per Azure RBAC role assignment. scope is a subscription or resource-group path; a principal's name identifies it.
Examples
Example 1
RoleAssignment
| assignment_id | principal_name | principal_type | role_name | scope |
|---|---|---|---|---|
| 1 | priya@contoso.com | User | Owner | /subscriptions/sub-prod-01 |
| 2 | priya@contoso.com | User | Reader | /subscriptions/sub-dev-02 |
| 3 | priya@contoso.com | User | Owner | /subscriptions/sub-dev-02 |
| 4 | rahul@contoso.com | User | Contributor | /subscriptions/sub-prod-01 |
| 5 | grp-platform-admins | Group | Owner | /subscriptions/sub-prod-01 |
| 6 | sp-deploy-pipeline | ServicePrincipal | User Access Administrator | /subscriptions/sub-prod-01 |
| 7 | zara@contoso.com | User | User Access Administrator | /subscriptions/sub-prod-01/resourceGroups/rg-data |
| 8 | zara@contoso.com | User | Contributor | /subscriptions/sub-prod-01/resourceGroups/rg-data |
| 9 | zara@contoso.com | User | Key Vault Secrets User | /subscriptions/sub-prod-01/resourceGroups/rg-data |
Output
| principal_name | roles | scope_count |
|---|---|---|
| priya@contoso.com | Owner, Reader | 2 |
| zara@contoso.com | Contributor, Key Vault Secrets User, User Access Administrator | 1 |
How to solve Microsoft: Privileged Azure Role Holders for the Access Review
Two different row sets are involved: the who (users holding a privileged role anywhere) and the what (every role those users hold). Filtering WHERE role_name IN ('Owner', …) and then aggregating would answer the wrong question — Zara's list would shrink to just User Access Administrator, hiding the other access the reviewer needs to see. So the privileged test selects people in a subquery, and the outer query aggregates all of their assignments.
GROUP_CONCAT(DISTINCT role_name ORDER BY role_name SEPARATOR ', ') produces one readable line per user: Priya is Owner at two scopes but the role appears once. COUNT(DISTINCT scope) counts places, not assignments. Groups and service principals are excluded by type in both parts.
The alternative de-duplicates (principal, role) pairs in a derived table and moves the privileged test into HAVING with a conditional MAX, counting scopes with a correlated subquery. Same answer, different shape.
Reference solution (MySQL)
SELECT principal_name,
GROUP_CONCAT(DISTINCT role_name ORDER BY role_name SEPARATOR ', ') AS roles,
COUNT(DISTINCT scope) AS scope_count
FROM RoleAssignment
WHERE principal_type = 'User'
AND principal_name IN (
SELECT principal_name FROM RoleAssignment
WHERE principal_type = 'User' AND role_name IN ('Owner', 'User Access Administrator')
)
GROUP BY principal_nameAnother way
SELECT r.principal_name,
GROUP_CONCAT(r.role_name ORDER BY r.role_name SEPARATOR ', ') AS roles,
(SELECT COUNT(DISTINCT s.scope) FROM RoleAssignment s WHERE s.principal_name = r.principal_name) AS scope_count
FROM (SELECT DISTINCT principal_name, role_name FROM RoleAssignment WHERE principal_type = 'User') r
GROUP BY r.principal_name
HAVING MAX(CASE WHEN r.role_name IN ('Owner', 'User Access Administrator') THEN 1 ELSE 0 END) = 1← Microsoft: Teams Daily Stickiness (DAU/MAU) in March 2025 · Microsoft: Teams Attendees Present for at Least 75% of a Meeting →