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.