La version finale de SQL Server 2016 est sortie récemment. C’est donc le moment de s’intéresser aux nouvelles fonctionnalités apportées par cette version. Aujourd’hui nous allons mettre en place les Row-Level Permissions
.
Cette nouvelle fonctionnalité permet de créer des filtres implicites sur les tables en fonction de l’utilisateur actuellement connecté. Par exemple un vendeur ne verra que ses commandes alors qu’un manager verra toutes les commandes et cela avec la même requête SELECT * FROM Order
. On peut également prendre le cas d’une application multi-tenants. Dans ce type d’application un tenant ne doit pas voir les lignes d’un autre tenant. On ajoutera donc des filtres pour empêcher cela.
Pour une application l’utilisateur courant peut être identifié de 2 façons différentes :
- Le login SQL dans le cas où l’utilisateur se connecte directement à la base,
- L’identifiant, défini par une application par exemple dans le cas d’une application web.
Le principe de Row-Level Security est très simple :
- On définit une fonction qui renvoie les lignes filtrées
- On associe cette fonction à la table souhaitée
REATE SCHEMA Security; GO
CREATE FUNCTION Security.fn_securitypredicate(@employeeName AS sysname) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS fn_securitypredicate_result WHERE @employeeName = USER_NAME(); -- use your own logic
GO
CREATE SECURITY POLICY OrderFilter ADD FILTER PREDICATE Security.fn_securitypredicate(Order_EmployeeName) ON [dbo].[Order] WITH (STATE = ON); GO
Si on exécute la requête SELECT * FROM [Order]
seules les ligne dont Order_CustomerName = USER_NAME()
sont renvoyées.
Dans le cas où le login SQL ne peut pas être utilisé, on peut utiliser CONTEXT_INFO
. Pour cela l’application définit sa valeur avec l’identifiant de l’utilisateur courant puis exécute les requêtes souhaitées :
CREATE PROCEDURE sp_setContextInfo(@currentUserId uniqueidentifier) AS SET CONTEXT_INFO @currentUserId; GO
CREATE FUNCTION Security.fn_securitypredicate(@userId uniqueidentifier) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS fn_securitypredicate_result WHERE CONVERT(uniqueidentifier, CONTEXT_INFO()) = @userId; -- use your own logic GO
CREATE SECURITY POLICY OrderFilter ADD FILTER PREDICATE Security.fn_securitypredicate(Order_Customer_Id) ON dbo.[Order] WITH (STATE = ON); GO
Avant d’exécuter une requête, l’application commence par appeler cette procédure stockée avec comme paramètre l’identifiant de l’utilisateur courant :
EXEC sp_setContextInfo 'FD65DA72-D6FE-4DC9-9AC5-E554CDC8E57F' SELECT * FROM [Order]
Conclusion
Cette nouvelle fonctionnalité est simple à mettre en place et permet de sécuriser les données de la base. En effet même si les requêtes exécutées ne filtrent pas correctement les données, ces filtres implicites finiront le travail.