SQL Server 2016 – Row Level Security

Comme vous le savez, SQL Server 2016 est enfin disponible au téléchargement en version CTP2. Je vous propose de découvrir une fonctionnalité attendue : le Row Level Security, ou la capacité, sans avoir à modifier l’ordre SELECT (ou Update, Delete, Insert) de filtrer les données en fonction de l’identité de la personne connectée.

Jusqu’a SQL Server 2014 inclus, si l’on souhaitait ne présenter à un vendeur les ventes qu’il a réalisées, il fallait passer par une procédure stockée ou une fonction qui allait appliquer un filtre. Si l’utilisateur avait un GRANT SELECT sur la table, il suffisait alors de passer par Excel, par exemple, pour visualiser l’intégralité des données.

Cette fois ci, de manière totalement transparente, les données sont filtrées automatiquement par SQL Server, suivant le résultat d’une fonction appelée par une stratégie de sécurité.

Dans un premier temps, créons une base exemple et récupérons quelques données de la base Adventureworks. Notez que j’ai éliminé les doublons sur le prénom des contacts car la fonction que nous allons utiliser par la suite ne doit renvoyer qu’un seul enregistrement.

CREATE DATABASE DemoRLS
GO

USE DemoRLS
GO

SELECT *
INTO SalesOrderHeader
FROM AdventureWorks.Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL

SELECT ContactID,FirstName,LastName
INTO dbo.Contact
FROM AdventureWorks.Person.Contact;

-- But there are some duplicates 
With MyCE AS 
(
	SELECT *,
	row_number() OVER (PARTITION BY FirstName ORDER BY ContactID) as RID
	FROM dbo.Contact
)
DELETE FROM MyCE
WHERE RID > 1

Comptons a présent le nombre de ventes réalisées par chaque employé, afin de choisir deux personnes :

SELECT SalesPersonID,Count(*) AS OrderCount
FROM dbo.SalesOrderHeader
GROUP BY SalesPersonID
ORDER BY SalesPersonID

Pour la suite de la démo, les ID 275 et 285 seront utilisés. Un login spécifique a chaque utilisateur est créé. Bien sur il est également possible de s’appuyer sur des logins de type Windows.

CREATE USER Maciej WITHOUT LOGIN;
CREATE USER Gail WITHOUT LOGIN;
GO

GRANT SELECT ON SalesOrderHeader TO Maciej;
GRANT SELECT ON SalesOrderHeader TO Gail;
GO

Il est temps à présent de créer la fonction qui va permettre de valider ou non la lecture des données par l’utilisateur qui sera connecté.

La fonction que j’ai créé va simplement aller extraire l’ID dans la table contact correspondant à la session en cours (USER_NAME).

Pour finir, il suffit de créer une stratégie de sécurité qui va se fixer sur la table que l’on souhaite protéger en spécifiant la colonne sur laquelle on s’appuie pour valider l’accès.

CREATE FUNCTION dbo.fn_RLSsecuritypredicate(@SalesPerson AS int)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS Select_Granted
            WHERE @SalesPerson = (
					SELECT ContactID FROM dbo.Contact
					WHERE FirstName = USER_NAME()
					)
GO

CREATE SECURITY POLICY SalesOrderHeaderFilter
ADD FILTER PREDICATE dbo.fn_RLSsecuritypredicate(SalesPersonID) 
ON dbo.SalesOrderHeader
WITH (STATE = ON);

Ainsi, lorsque l’utilisateur 275 exécuter une requête SELECT, alors seuls les enregistrements du SalesPersonID de la table SalesOrderHeader seront remontées. Idem pour l’utilisateur 285.

EXECUTE AS USER = 'Maciej';
	SELECT SalesOrderID,OrderDate,CustomerID,SalesPersonID 
	FROM dbo.SalesOrderHeader
REVERT;

EXECUTE AS USER = 'Gail';
	SELECT SalesOrderID,OrderDate,CustomerID,SalesPersonID 
	FROM dbo.SalesOrderHeader
REVERT;

 

 

Cela fonctionne parfaitement ! Mais je me dois quand même de vous mettre en garde. Rien ne prévient que les données sont filtrées, sauf à regarder de près le plan d’exécution :

Où un Assert et un Stream Aggregate s’imposent avant la jointure. En regardant la version XML du plan d’exécution, on décèle l’appel à la fonction.

 

Je dois également attirer votre attention sur le fait que même un sysadmin est soumis à cette policy ! L’exécution du SELECT ne renvoie aucune données.

 

Il suffit alors de modifier la fonction pour prendre les cas particuliers en charge. Les administrateurs, les managers qui auraient visibilité sur les ventes des employés sont ils sont responsables, etc …

DROP SECURITY POLICY SalesOrderHeaderFilter;
GO

ALTER FUNCTION dbo.fn_RLSsecuritypredicate(@SalesPerson AS int)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS Select_Granted
            WHERE @SalesPerson = (
					SELECT ContactID FROM dbo.Contact
					WHERE FirstName = USER_NAME()
					)
               OR USER_NAME() = 'dbo';
GO


CREATE SECURITY POLICY SalesOrderHeaderFilter
ADD FILTER PREDICATE dbo.fn_RLSsecuritypredicate(SalesPersonID) 
ON dbo.SalesOrderHeader
WITH (STATE = ON);

 

Et on récupère la visibilité sur les données.

 

Cette fonctionnalité était attendue et malgré les possibilités offertes, elle est simple a mettre en œuvre. La policy s’applique bien niveau ligner et filtre les données à renvoyer. Rien pour les colonnes, il faut encore s’appuyer sur des vies/fonctions/procédures.

Enjoy !