SQL Server : impersonation avec EXECUTE AS USER
Changer de contexte utilisateur avec EXECUTE AS, vérifier l'identité et générer un script de droits sur toutes les bases.
EXECUTE AS permet de se faire passer temporairement pour un autre utilisateur (impersonation) dans une session SQL Server, par exemple pour tester ses droits. REVERT rétablit le contexte d'origine.
Changer de contexte et le vérifier
EXECUTE AS USER = 'CONTOSO\report_user';
SELECT SYSTEM_USER --> CONTOSO\user.name
SELECT USER --> dbo
SELECT CURRENT_USER --> dbo
SELECT USER_NAME() --> dbo
SELECT SESSION_USER --> dbo
SELECT @@SPID
SELECT ORIGINAL_LOGIN()
REVERT
ORIGINAL_LOGIN() renvoie toujours le login qui a ouvert la connexion, quel que soit le EXECUTE AS en cours.
Générer le script de droits pour chaque base
Cet exemple parcourt toutes les bases (sauf tempdb, model et msdb) et affiche, avec PRINT, le script qui crée un utilisateur, l'ajoute à db_owner et accorde IMPERSONATE sur cet utilisateur à un autre principal. Le script est affiché, pas exécuté.
EXECUTE AS USER = 'CONTOSO\admin_user'
DECLARE @database VARCHAR(2000);
DECLARE @sql VARCHAR(2000)
DECLARE cur_database CURSOR FOR
SELECT name FROM sys.databases WHERE name NOT IN ('tempdb','model','msdb')
OPEN cur_database
FETCH cur_database INTO @database
WHILE (@@FETCH_STATUS = 0 )
BEGIN
SET @sql = '
USE '+QUOTENAME(@database)+'
GO
CREATE USER [CONTOSO\app_user] FOR LOGIN [CONTOSO\app_user]
GO
ALTER ROLE [db_owner] ADD MEMBER [CONTOSO\app_user]
GO
GRANT IMPERSONATE ON USER::[CONTOSO\app_user] TO [CONTOSO\SQL_Reporting]
go'
PRINT (@sql)
FETCH cur_database INTO @database
END
/* pour tester un autre utilisateur, exécuter d'abord REVERT */
REVERT
Remarques
- Exécuter
REVERTavant de changer d'utilisateur : lesEXECUTE ASsuccessifs s'empilent. GOn'étant pas du T-SQL, le texte généré est destiné à être copié dans une fenêtre de requête.- Les noms de comptes et de groupes sont des exemples génériques.