Voici une nouveauté intéressante de SQL Server 2016

Le Row Lever Security (RLS)

Qu’est-ce que c’est ?

Le Row Level Security permet de sécuriser des données au niveau des lignes.

Grace au Row Level sécurité on peut attribuer des rôles et afficher les données correspondant au rôle correspondant à l’utilisateur logué.

Exemple :

Creation de 2 tables :

CREATE TABLE dbo.Departement (

DepartementID INT NOT NULL,

DepartementName VARCHAR(30),

CONSTRAINT PK_Departement PRIMARY KEY CLUSTERED (DepartementID)

);

GO

CREATE TABLE dbo.Employe (

EmployeID INT NOT NULL,

Prenom VARCHAR(30) NOT NULL,

DeuxiemePrenom VARCHAR(30) NULL,

Nom VARCHAR(30) NOT NULL,

DepartementID INT NOT NULL,

CONSTRAINT PK_Employe PRIMARY KEY CLUSTERED (EmployeID),

CONSTRAINT FK_Employe_Departement FOREIGN KEY (DepartementID) REFERENCES dbo.Departement (DepartementID)

);

GO

INSERT INTO dbo.Departement (DepartementID, DepartementName) VALUES (1, ‘IT’);

INSERT INTO dbo.Departement (DepartementID, DepartementName) VALUES (2, ‘RH’);

INSERT INTO dbo.Departement (DepartementID, DepartementName) VALUES (3, ‘Marketing’);

INSERT INTO dbo.Departement (DepartementID, DepartementName) VALUES (4, ‘Achat’);

INSERT INTO dbo.Departement (DepartementID, DepartementName) VALUES(5,’Communication’);

GO

 

INSERT INTO dbo.Employe (EmployeID, Prenom, DeuxiemePrenom, Nom, DepartementID) VALUES (1, ‘Jean’, NULL, ‘Dujardin’, 1);

INSERT INTO dbo.Employe (EmployeID, Prenom, DeuxiemePrenom, Nom, DepartementID) VALUES (2, ‘Vincent’, NULL, ‘Cassel’, 2);

INSERT INTO dbo.Employe (EmployeID, Prenom, DeuxiemePrenom, Nom, DepartementID) VALUES (3, ‘Josiane’, NULL, ‘Balasko’, 3);

INSERT INTO dbo.Employe (EmployeID, Prenom, DeuxiemePrenom, Nom, DepartementID) VALUES (4, ‘Jean Paul’, NULL, ‘Belmondo’, 4);

INSERT INTO dbo.Employe (EmployeID, Prenom, DeuxiemePrenom, Nom, DepartementID) VALUES (5, ‘Leatitia’, NULL, ‘Casta’, 5);

INSERT INTO dbo.Employe (EmployeID, Prenom, DeuxiemePrenom, Nom, DepartementID) VALUES (6, ‘Jamel’,  », ‘Debouz’, 1);

INSERT INTO dbo.Employe (EmployeID, Prenom, DeuxiemePrenom, Nom, DepartementID) VALUES (7, ‘Alain’, NULL, ‘Delon’, 2);

INSERT INTO dbo.Employe (EmployeID, Prenom, DeuxiemePrenom, Nom, DepartementID) VALUES (8, ‘Christine’,  », ‘Bravo’, 3);

INSERT INTO dbo.Employe (EmployeID, Prenom, DeuxiemePrenom, Nom, DepartementID) VALUES (9, ‘Lambert’, NULL, ‘Wilson’, 4);

INSERT INTO dbo.Employe (EmployeID, Prenom, DeuxiemePrenom, Nom, DepartementID) VALUES (10, ‘Omar’, NULL, ‘Sy’, 5);

GO

 

SELECT * FROM dbo.Departement

SELECT * FROM dbo.Employe

On sélectionne les données pour vérifier :

— 3 tables de sécurité

 

Creation d’une table sécurité permettant d’afficher les données suivants les filtres :

 

CREATE TABLE dbo.Securite (

DepartementID INT NOT NULL,

RoleName NVARCHAR(255),

CONSTRAINT PK_Securite PRIMARY KEY CLUSTERED (DepartementID, RoleName)

)

GO

 

INSERT INTO dbo.Securite (DepartementID, RoleName) VALUES (1, ‘IT’);

INSERT INTO dbo.Securite (DepartementID, RoleName) VALUES (2, ‘RH’);

INSERT INTO dbo.Securite (DepartementID, RoleName) VALUES (3, ‘Marketing’);

INSERT INTO dbo.Securite (DepartementID, RoleName) VALUES (4, ‘Achat’);

GO

— 4 creation d’une vue

CREATE VIEW dbo.DepartementEmployes

AS

SELECT Emp.Prenom, Emp.DeuxiemePrenom, Emp.Nom, Dept.DepartementName

FROM dbo.Departement Dept

INNER JOIN dbo.Employe Emp

ON Dept.DepartementID = Emp.DepartementID

LEFT JOIN dbo.Securite

ON Emp.DepartementID = dbo.Securite.DepartementID

WHERE IS_MEMBER(dbo.Securite.RoleName) = 1

–OR IS_MEMBER(‘RH’) = 2

OR IS_MEMBER(‘db_owner’) = 1;

GO

 

GRANT SELECT ON dbo.DepartementEmployes TO Communication;

GRANT SELECT ON dbo.DepartementEmployes TO RH;

GRANT SELECT ON dbo.DepartementEmployes TO IT;

GRANT SELECT ON dbo.DepartementEmployes TO Marketing;

GRANT SELECT ON dbo.DepartementEmployes TO Achat;

 

 

 

— 5 création des utilisateurs et tests avec des utilisateurs

 

— Requete vue DBA

SELECT * FROM dbo.DepartementEmployes;

 

— Requete Vue RH

 

 

CREATE USER Magalie WITHOUT LOGIN;

GO

 

EXEC sp_addrolemember @membername = ‘Magalie’, @rolename = ‘RH’;

–EXEC sp_addrolemember @membername = ‘Magalie’, @rolename = ‘Communication’;

GO

On exécute la requête avec le niveau de droit de l’utilisateur :

EXECUTE AS USER = ‘Magalie’;

GO

 

SELECT * FROM dbo.DepartementEmployes;

GO

REVERT; — Permet de se reloguer en utilisateur initial

 

GO

 

— Requete pour le marketing

 

 

CREATE USER Fredo WITHOUT LOGIN;

GO

 

EXEC sp_addrolemember @membername = ‘Fredo’, @rolename = ‘Marketing’;

–EXEC sp_addrolemember @membername = ‘Fredo’, @rolename = ‘Communication’;

GO

 

EXECUTE AS USER = ‘Fredo’;

GO

 

SELECT * FROM dbo.DepartementEmployes;

GO

REVERT;

GO

 

— Requete pour voir le niveau IT

 

CREATE USER Loman WITHOUT LOGIN;

GO

EXEC sp_addrolemember @membername = ‘Loman’, @rolename = ‘IT’;

–EXEC sp_addrolemember @membername = ‘Loman’, @rolename = ‘Communication’;

GO

EXECUTE AS USER = ‘Loman’;

GO

SELECT * FROM dbo.DepartementEmployes;

GO

REVERT;

GO

— Requete pour les commerciaux

CREATE USER Djem WITHOUT LOGIN;

GO

EXEC sp_addrolemember @membername = ‘Djem’, @rolename = ‘Achat’;

–EXEC sp_addrolemember @membername = ‘Djem’, @rolename = ‘Communication’;

GO

EXECUTE AS USER = ‘Djem’;

GO

SELECT * FROM dbo.DepartementEmployes;

GO

REVERT;

GO

Conclusion :

En quelques lignes nous avons appris à mettre en place le row level sécurity et introduire un niveau de sécurité au niveau des lignes de manière simple et intuitive.