Microsoft: Privileged Azure Role Holders for the Access Review

Medium
790.

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.

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

ColumnTypeValues
assignment_idint
principal_namevarchar
principal_typeenumUser, Group, ServicePrincipal
role_namevarchar
scopevarchar

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.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

RoleAssignment

assignment_idprincipal_nameprincipal_typerole_namescope
1priya@contoso.comUserOwner/subscriptions/sub-prod-01
2priya@contoso.comUserReader/subscriptions/sub-dev-02
3priya@contoso.comUserOwner/subscriptions/sub-dev-02
4rahul@contoso.comUserContributor/subscriptions/sub-prod-01
5grp-platform-adminsGroupOwner/subscriptions/sub-prod-01
6sp-deploy-pipelineServicePrincipalUser Access Administrator/subscriptions/sub-prod-01
7zara@contoso.comUserUser Access Administrator/subscriptions/sub-prod-01/resourceGroups/rg-data
8zara@contoso.comUserContributor/subscriptions/sub-prod-01/resourceGroups/rg-data
9zara@contoso.comUserKey Vault Secrets User/subscriptions/sub-prod-01/resourceGroups/rg-data

Output

principal_namerolesscope_count
priya@contoso.comOwner, Reader2
zara@contoso.comContributor, Key Vault Secrets User, User Access Administrator1
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
assignment_idint
principal_namevarchar
principal_typeenum(User, Group, ServicePrincipal)
role_namevarchar
scopevarchar

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_idprincipal_nameprincipal_typerole_namescope
1priya@contoso.comUserOwner/subscriptions/sub-prod-01
2priya@contoso.comUserReader/subscriptions/sub-dev-02
3priya@contoso.comUserOwner/subscriptions/sub-dev-02
4rahul@contoso.comUserContributor/subscriptions/sub-prod-01
5grp-platform-adminsGroupOwner/subscriptions/sub-prod-01
6sp-deploy-pipelineServicePrincipalUser Access Administrator/subscriptions/sub-prod-01
7zara@contoso.comUserUser Access Administrator/subscriptions/sub-prod-01/resourceGroups/rg-data
8zara@contoso.comUserContributor/subscriptions/sub-prod-01/resourceGroups/rg-data
9zara@contoso.comUserKey Vault Secrets User/subscriptions/sub-prod-01/resourceGroups/rg-data

Output

principal_namerolesscope_count
priya@contoso.comOwner, Reader2
zara@contoso.comContributor, Key Vault Secrets User, User Access Administrator1

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_name

Another 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 →