SQL Server 2016 : Les nouveautés

Appliquer la sécurité par ligne

Testez gratuitement nos 1270 formations

pendant 10 jours !

Tester maintenant Afficher tous les abonnements
La sécurité de niveau ligne permet de filtrer la lecture de vos tables selon un prédicat défini dans une fonction. Cela va empêcher la lecture de données aux utilisateurs non autorisés.
08:17

Transcription

Dernière fonctionnalité intéressante en matière de sécurité, la sécurité par ligne. Alors on a toujours eu des permissions sur les objets, permission de lire une table ou de modifier une table. On a quelque chose d'un peu caché depuis longtemps et d'assez peu utilisé, la permission sur les colonnes, permission à quelqu'un de faire un Select qui mentionne la colonne ou non. Et ce qui nous manquait, c'est une permission sur les lignes. C'est-à-dire, est-ce qu'un utilisateur connecté a la permission de lire toutes les lignes de la table en faisant une clause ware, ou bien est-ce que certaines lignes doivent lui être cachées ? Dans les faits, c'est quelque chose qu'on peut réaliser par procédure stockée avec des vues, avec des fonctions table, par exemple, mais il n'y a pas de mécanisme intégré à eskul serveur, pour le faire. En tout cas, il n'y avait pas de mécanisme intégré jusqu'en eskul serveur 2016. Donc on va parler ici de Role level permission, ou Role level security, donc de permission ou de privilège au niveau de la ligne. Je vais vous montrer de quoi il s'agit. Prenons notre table d'employés. On a une liste, ici, d'employés de la société PachaData. Ils sont liés à des contacts. Donc si je regarde, je vais aller un peu plus loin, à une requête qui va suivre mes employés liés à mes contacts, on voit que par exemple, la directrice s'appelle Mme Bertin, le responsable RH, M Robitaillie, etc. Pour une raison ou une autre, peut-être qu'il y a les salaires, qui sont ajoutés devant les employés. Eh bien, je voudrais cacher à quelqu'un des lignes qui ne le concernent pas. On va dire - c'est un peu arbitraire - que, par exemple, le nom doit correspondre à l'utilisateur qui est connecté et quelqu'un ne va pouvoir lire que les lignes d'employés pour des personnes qui ont le même nom que lui. C'est un exemple un peu tarabiscoté, mais on va faire avec ça. Je vais créer une fonction qui va assurer le filtrage lorsque je fais un Select sur ma table. Pour cela, pour rester propre, je vais créer un schéma que je vais appeler " Sécurité ", dans lequel je vais créer ma fonction. Je supprime le schéma s'il existe, je crée mon schéma sécurité, tout cela pour ranger proprement mes fonctions de filtre, si vous voulez. Je supprime mes objets au cas où ils existent déjà, et ici, je vais créer une fonction dans mon schéma sécurité, que je vais appeler comme je veux - là, je l'appelle EmployeSecurite - et je vais, ou non, lui passer un paramètre. Alors, en général, il vaut mieux lui passer un paramètre, parce que ce paramètre va être quelque chose que je vais extraire de la ligne de ma table en question qui va passer dans la fonction et qui va retourner, finalement, vrai ou faux. Ça retourne une table, WITH SCHEMABINDING, et, à l'intérieur, je vais retourner soit simplement un 1, soit rien du tout. Si, lorsqu'on fait une requête, cette fonction va passer pour les lignes de la table, pour chaque ligne de la table, on va appeler la fonction. Cette fonction va retourner soit 1, et à ce moment-là, eskul serveur affichera la ligne à l'utilisateur, soit rien du tout, et la ligne sera cachée. Si vous voulez, c'est comme si cette fonction ajoute un critère supplémentaire dans la clauseware de la requête que va lancer l'utilisateur. Ce qui va permettre un filtre supplémentaire caché. Ici, je vais faire un truc un peu compliqué, c'est pas une bonne idée en termes de performance, mais bon. Je vais aller regarder dans ma table Contact.Contact, par rapport au contact ID qui va m'être envoyé, ce sera le contact ID de chaque ligne de la table Employe, Je vais regarder, donc, dans ma table Contact avec ce Contact Id et je vais regarder si le nom de ce contact correspond à l'utilisateur qui est connecté dans la base et qui fait la requête. Si c'est le cas, je retourne 1. Je fais une chose supplémentaire : je dis OU la personne qui fait la requête est membre du rôle db_owner dans la base de données. Si je ne mets pas ça, ça veut dire que, lorsque None ne correspond à aucun nom, et même s'il est 6_admin, même s'il est db_owner, il ne pourra voir aucune ligne. Alors ça, c'est drôle, parce que normalement, quand vous êtes 6_admin dans eskul serveur, vous avez tous les privilèges à tous les niveaux. On ne peut rien vous enlever. Mais ici, il ne s'agit pas vraiment d'une interdiction, il s'agit d'un filtre de requête supplémentaire ajouté dans la clause ware. Donc que vous soyez 6_admin ou non, on va vous filtrer les lignes que vous ne pouvez pas voir avec ce prédicat, ici, qui va être construit dans la fonction. Donc si je ne mets pas ça, par exemple, je vais être SA et je ne verrai rien du tout. Alors je le rajoute. Je crée donc cette fonction et ensuite je vais créer un objet particulier qui s'appelle une règle, policy de sécurité. Je vais nommer cette policy, et je vais ajouter un prédicat de filtre qui est la fonction que je viens de créer. Je lui passe en paramètre l'EmployeContactId qui se trouve dans la table Contact.Employe. Si je reviens ici et qu'on regarde ce qu'on a dans Employe, eh bien, j'ai EmployeContactId et c'est cet EmployeContactId, lorsque je vais faire un Select sur cette table, que je vais envoyer à la fonction. La fonction, vous vous en souvenez, le prend en paramètre, regarde dans les contacts si on trouve ce ContactId et si le nom correspond au Current User, c'est-à-dire à l'utilisateur connecté dans la base. J'ajoute ce filtre et je dis que son état est à ON. Je l'active directement. Maintenant, je regarde mes employés, et je les vois tous, puisque je suis membre de db_owner. Donc je vais créer un utilisateur particulier, qui s'appelle Bertin. Vous vous souvenez, j'ai des contacts, notamment la directrice s'appelle Bertin. Mais il se trouve que j'ai aussi Nathalie Bertin qui est responsable pédagogique. Je vais donc créer un utilisateur qui s'appelle Bertin, sans log in. Je ne vais pas me connecter en tant que Bertin, mais je vais faire un EXECUTE AS, pour faire de l'impersonation. Je dois attribuer le privilège de select à Bertin pour qu'il ou elle ait au moins la permission de lire la table, c'est un peu le minimum. Ensuite, je deviens Bertin. Je fais de l'impersonation. Et maintenant,en tant que Bertin, si je regarde mon CURRENT_USER, je suis bien Bertin. Et maintenant, vous voyez, je fais une requête SELECT sans aucun filtre ware, sans aucun prédicat. Parce que le prédicat va être ajouté automatiquement par un SECURITY POLICY dans ma requête sur CONTACT.EMPLOYE. Il va aller chercher, pour chaque ContactId, dans la fonction si ça retourne 1 ou rien du tout et, eh bien le miracle va se produire, vous allez voir : je n'ai que mes deux lignes, qui correspondent à la directrice et la responsable pédagogique, les deux Bertin qui sont des employés. Je reviens en arrière et maintenant, je suis 6_admin, db_owner, donc je peux lire toutes les lignes Si je veux revenir en arrière, bien sûr, il me suffira soit de désactiver ma SECURITY POLICY, soit de la supprimer. Cette policy, vous allez la retrouver ici, dans votre base de données, dans les stratégies de sécurité, et vous voyez, j'ai un filtre Employe qui est activé. Je peux désactiver la stratégie de sécurité ici, bien entendu, générer un script pour recréer ma stratégie, mais je peux la gérer ici, en désactivant et en réactivant la stratégie de sécurité au besoin. On voit, là, c'est assez intelligemment fait. Méfiez-vous peut-être de problématiques de performances, parce que la fonction doit réellement filtrer les lignes, donc ça peut diminuer un peu les performances de vos requêtes, mais c'est toujours le même équilibre entre performance et sécurité.

SQL Server 2016 : Les nouveautés

Découvrez les nouveautés de SQL Server 2016. Voyez les options de configuration limitées aux bases de données, le chiffrage de données à partir des applications clientes, etc.

2h26 (27 vidéos)
Aucun commentaire n´est disponible actuellement
 

Votre formation est disponible en ligne avec option de téléchargement. Bonne nouvelle : vous ne devez pas choisir entre les deux. Dès que vous achetez une formation, vous disposez des deux options de consultation !

Le téléchargement vous permet de consulter la formation hors ligne et offre une interface plus conviviale. Si vous travaillez sur différents ordinateurs ou que vous ne voulez pas regarder la formation en une seule fois, connectez-vous sur cette page pour consulter en ligne les vidéos de la formation. Nous vous souhaitons un excellent apprentissage avec cette formation vidéo.

N'hésitez pas à nous contacter si vous avez des questions !