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.
Magalie et Fredo : MDR!