Groupes Entra ID et permissions SQL : requêtes d'audit
Lister les groupes d'un utilisateur et leurs membres avec PowerShell, puis auditer utilisateurs, rôles et permissions d'une base SQL.
Lorsque l'accès SQL est accordé via des groupes Microsoft Entra ID (Azure AD), deux questions reviennent : à quels groupes appartient un utilisateur, et que peuvent faire ces groupes dans une base ? Cet article regroupe les extraits PowerShell et T-SQL correspondants.
Lister les groupes d'un utilisateur
Avec le module AzureAD :
Connect-AzureAD
Get-AzureADUser -ObjectId "user@example.com" | Get-AzureADUserMembership | Select DisplayName
Exemple de résultat (noms de groupes génériques) :
| DisplayName |
|---|
| azgr-prd-app1-sql-user |
| azgr-prd-app2-sql-user |
| azgr-prd-TechnicalAccounts |
Le même résultat avec Microsoft Graph :
# Installer le module Microsoft Graph (si nécessaire)
Install-Module Microsoft.Graph -Scope CurrentUser
# Se connecter à Microsoft Graph
Connect-MgGraph -Scopes "User.Read.All", "GroupMember.Read.All"
# Remplacer par l'UPN de l'utilisateur
Get-MgUser -UserId "user@example.com" | Get-MgUserMemberOf | Select DisplayName
Lister les membres de groupes
<#
1. Install-Module AzureAD
Import-Module AzureAD
Get-Module AzureAD
#>
Connect-AzureAD
$groups = @(
'azgr-dev-app-sql-admin'
)
foreach ($group in $groups)
{
$a = Get-AzureADGroup -SearchString $group
$a.DisplayName
Get-AzureADGroupMember -ObjectId $a.ObjectId | Sort-Object DisplayName | Format-Table DisplayName, ObjectId
}
Lister les utilisateurs de la base courante
SELECT name, type_desc, authentication_type_desc
FROM sys.database_principals
WHERE type IN ('S', 'E', 'X', 'G', 'U') -- SQL, External, Windows, Groups, Users
AND name NOT IN ('guest', 'INFORMATION_SCHEMA', 'sys');
Lister les rôles et leurs membres
SELECT
dp1.name AS RoleName,
dp2.name AS MemberName
FROM
sys.database_role_members drm
JOIN
sys.database_principals dp1 ON drm.role_principal_id = dp1.principal_id
JOIN
sys.database_principals dp2 ON drm.member_principal_id = dp2.principal_id
ORDER BY
dp1.name, dp2.name;
Permissions accordées aux rôles ou utilisateurs
SELECT
dp.name AS PrincipalName,
dp.type_desc AS PrincipalType,
p.class_desc,
p.permission_name,
p.state_desc,
OBJECT_NAME(p.major_id) AS ObjectName
FROM
sys.database_permissions p
JOIN
sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
ORDER BY
PrincipalName, ObjectName, permission_name;
Permissions d'un rôle ou utilisateur précis
SELECT
dp.name AS PrincipalName,
p.permission_name,
p.state_desc,
OBJECT_NAME(p.major_id) AS ObjectName
FROM
sys.database_permissions p
JOIN
sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
WHERE
dp.name = 'role-app-sql-user';