Blog ENI : Toute la veille numérique !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
  1. Livres et vidéos
  2. SQL Server 2019
  3. Gestion de la sécurité des accès
Extrait - SQL Server 2019 Apprendre à administrer une base de données transactionnelle avec SQL Server Management Studio
Extraits du livre
SQL Server 2019 Apprendre à administrer une base de données transactionnelle avec SQL Server Management Studio
4 avis
Revenir à la page d'achat du livre

Gestion de la sécurité des accès

Introduction

Le contrôle d’accès représente une opération importante au niveau de la gestion de la sécurité sur un serveur de bases de données. La sécurisation des données nécessite une organisation des objets de façon indépendante des utilisateurs, ce qui est possible par les schémas. La sécurité passe également par un meilleur contrôle des autorisations et la possibilité d’accorder juste les privilèges nécessaires à chaque utilisateur pour qu’il puisse travailler de façon autonome.

Pour l’organisation de cette politique de sécurité, il faut prendre en compte l’organisation hiérarchique des éléments de sécurité, de façon à rendre la gestion des droits d’accès simple et efficace.

SQL Server s’appuie sur trois éléments clés qui sont :

  • Les entités de sécurité.

  • Les sécurisables.

  • Les autorisations.

Les entités de sécurité sont des comptes de sécurité qui disposent d’un accès au serveur SQL.

Les sécurisables représentent les objets gérés par le serveur. Ici, un objet peut être une table, un schéma ou une base de données par exemple.

Les autorisations sont accordées...

Gestion des accès serveur

Avant de pouvoir travailler avec les données gérées par les bases, il faut dans un premier temps se connecter au serveur SQL. Cette étape permet de se faire identifier par le serveur SQL et d’utiliser par la suite tous les droits qui sont accordés à notre connexion. Il existe dans SQL Server deux modes de gestion des accès au serveur de base de données.

Attention, dans cette section, seule la partie connexion au serveur est abordée. Il est important de bien distinguer la connexion au serveur et l’utilisation de bases de données. La connexion au serveur permet de se faire identifier par le serveur SQL comme un utilisateur valide, pour utiliser, par la suite, une base de données : les données et les objets. L’ensemble de ces droits seront définis ultérieurement. Ces droits sont associés à un utilisateur de base de données, pour lequel correspond une connexion.

On parlera de connexion au serveur ou de logins.

1. Mode de sécurité Windows

Ce type de gestion de la sécurité permet de s’appuyer sur les utilisateurs et les groupes Windows pour le domaine et le poste local. SQL Server utilise la gestion des utilisateurs de Windows (gestion des mots de passe...) et récupère uniquement les noms pour créer des connexions au serveur.

Une fonctionnalité très importante de SQL Server est de pouvoir autoriser des groupes Windows à venir se connecter. La gestion des groupes permet de simplifier grandement la gestion de l’accès aux ressources. Les mêmes groupes Windows peuvent donc être utilisés pour donner accès à des fichiers ou à des objets de bases de données SQL Server.

Avec une telle méthode de fonctionnement, comme un utilisateur Windows peut appartenir à plusieurs groupes, il peut posséder plusieurs droits de connexion à SQL Server.

images/04ec133.png

Authentification Windows

En mode sécurité Windows, seuls les noms d’utilisateurs sont stockés. La gestion des mots de passe et de l’appartenance aux différents groupes est laissée à Windows. Ce mode de fonctionnement permet de bien discerner les tâches de chacun et de spécialiser SQL Server sur la gestion des données...

Gestion des utilisateurs de base de données

Après la définition des connexions (login) au niveau du serveur, il est nécessaire de définir des utilisateurs dans les différentes bases de données.

C’est au niveau des utilisateurs de base de données que seront attribués les droits d’utilisation des objets définis dans la base de données. Lors de la définition d’une connexion, la base de données par défaut permet de positionner le compte de connexion sur une base de données pour commencer à travailler. Cependant, la connexion ne pourra réellement travailler sur la base que s’il existe un compte d’utilisateur défini au niveau base et associé à la connexion. C’est un point de passage obligatoire, sauf si la connexion s’est vue attribuée des privilèges de haut niveau.

Si aucune base de données par défaut n’est définie au niveau de la connexion, alors c’est la base Master qui est considérée comme base par défaut.

Les utilisateurs de base de données sont, en général, associés à une connexion au niveau du serveur. Cependant, l’utilisateur guest (qui est désactivé par défaut) n’est mappé à aucune connexion. Comme à n’importe quel compte utilisateur de base de données, des droits d’accès aux objets peuvent lui être accordés.

Le compte invité (guest) est activé sur les bases master et tempdb. Cela fait partie du mode de fonctionnement normal de SQL Server et il n’est pas possible de déroger à cette règle.

Si un utilisateur dispose d’une connexion à SQL Server mais s’il n’existe pas d’utilisateur de base de données lui permettant d’intervenir sur les bases, l’utilisateur ne peut réaliser que quelques opérations très limitées :

  • Sélectionner les informations contenues dans les tables système et exécuter certaines procédures stockées.

  • Accéder à toutes les bases de données utilisateur munies d’un compte d’utilisateur guest activé.

  • Exécuter les instructions qui ne nécessitent pas d’autorisation...

Gestion des schémas

L’objectif des schémas est de faciliter la gestion de la sécurité d’accès des utilisateurs vers les objets (tables, vues, procédures stockées, fonctions). Un peu comme sur un serveur de fichiers avec des dossiers et sous-dossiers où les fichiers vont être stockés, les droits d’accès sont posés en priorité sur les dossiers, ce qui sera plus simple que fichier par fichier. Pour le cas de SQL Server, les droits peuvent être posés sur les bases de données, les schémas et éventuellement sur les objets pour gérer une exception.

Dans le cas où on ne trouve pas de découpage logique de schémas pour une base de données, les objets seront placés dans le schéma par défaut à savoir dbo.

Pour accéder aux objets, même s’ils sont stockés dans le schéma par défaut de l’utilisateur, il est préférable de les nommer en utilisant leur schéma, c’est-à-dire nomSchema.nomObjet. Lors de l’utilisation d’un nom court (simplement le nom de l’objet sans le préfixer par le nom du schéma), SQL Server recherche l’existence de l’objet dans le schéma par défaut de l’utilisateur et ensuite dans le schéma dbo, donc si le nom de schéma est spécifié, cette étape n’a pas lieu d’être et cela optimise le temps de réponse des requêtes.

1. Création...

Gestion des droits

Tous les utilisateurs de base de données, y compris guest (l’invité), appartiennent au groupe public. Les droits qui vont être détaillés ci-dessous peuvent bien sûr être accordés directement à public.

Les droits sont organisés de façon hiérarchique par rapport aux éléments sécurisables du serveur.

images/04ec154_8.png

Il est possible de gérer l’attribution de privilèges au niveau du serveur, de la base de données, du schéma ou bien directement de l’objet. Ainsi, les privilèges peuvent être accordés soit à un utilisateur de base de données, soit à une connexion.

Les droits accordés au niveau du serveur sont particuliers et relèvent plus de droits pour des administrateurs délégués. Au niveau bases de données, il existe également des droits concernant des accès administratifs, mais aussi des droits concernant l’accès des utilisateurs aux objets. Ces mêmes droits peuvent également être posés sur les schémas ou directement sur les objets (il y a héritage des droits sur ces trois niveaux de la même manière que sur un serveur de fichiers entre les dossiers et les fichiers). 

SQL Server gère les privilèges avec trois types de mots-clés :

  • GRANT

  • REVOKE

  • DENY

C’est-à-dire qu’un privilège peut être accordé (GRANT) ou bien retiré (REVOKE) s’il a été accordé. L’instruction DENY permet d’interdire l’utilisation d’un privilège particulier même si le privilège en question a été accordé soit directement, soit par l’intermédiaire d’un rôle.

1. Droits d’utilisation d’instructions

Les droits d’utilisation des instructions SQL pour créer de nouveaux objets au sein de la base sont des autorisations pour réaliser l’exécution de certains ordres SQL. Un utilisateur qui dispose de tels droits est capable par exemple de créer ses propres tables, ses procédures... L’accord de ces droits peut être dangereux et comme pour tous les droits, doivent être accordés uniquement lorsque cela est nécessaire....

Contexte d’exécution

Le contexte d’exécution est directement lié à la connexion et à l’utilisateur de base de données associé. Le contexte d’exécution permet d’établir la liste des actions possibles et celles qui ne le sont pas. Cette liste est établie à partir des privilèges accordés à l’utilisateur soit directement, soit par l’intermédiaire de rôles.

Dans certains cas, il peut être nécessaire et souhaitable de modifier le contexte d’exécution afin de profiter de privilèges étendus, mais uniquement dans le cadre d’un script, d’une procédure ou d’une fonction.

Associée au contexte d’exécution, il est nécessaire de bien comprendre la notion de chaîne de propriétés dans SQL Server.

Par exemple, l’utilisateur Paul a reçu de Marie le droit d’utiliser (SELECT) une vue que possède Marie. La requête SELECT, à l’origine de cette vue, fait référence à une table également possédée par Marie. Paul ne dispose d’aucun privilège sur cette table, pourtant il sera en mesure d’utiliser la vue sans soucis car les deux objets (vue et table) ont le même propriétaire.

images/04ec37a.png

Dans le cas où la vue de Marie que Paul interroge accède à une table dont Marie n’est pas la propriétaire, alors les privilèges accordés à Paul sont vérifiés afin de savoir si Paul possède les privilèges nécessaires pour exécuter...

Rôles

Les rôles sont des ensembles de permissions. Ces ensembles existent à trois niveaux distincts : serveur, base de données et application. Les rôles permettent de regrouper les droits et de gérer plus facilement les différents utilisateurs et les connexions. Il est en effet toujours préférable d’attribuer des droits à des rôles puis d’accorder les rôles aux utilisateurs. Avec une telle structure, l’ajout et la modification de droits ou d’utilisateur sont beaucoup plus simples.

Il est possible de définir un rôle comme un ensemble nommé de privilèges. Pour faciliter la gestion des droits, SQL Server propose des rôles prédéfinis aussi appelés fixes car il n’est pas possible d’ajouter ou bien de supprimer des privilèges dans ces rôles.

Ces rôles fixes sont définis à deux niveaux :

  • Serveur

  • Base de données

En plus de ces rôles fixes, il est possible de gérer des rôles. Il convient alors de définir un nom unique pour définir un rôle, puis d’accorder un ou plusieurs privilèges en respectant une procédure en tout point similaire à celle utilisée pour accorder des permissions à des utilisateurs. Ces rôles peuvent être définis à trois niveaux :

  • Serveur

  • Base de données

  • Application

Les rôles permettent une gestion simplifiée des privilèges puisqu’il est possible ainsi de définir des profils types de privilèges, puis d’accorder à chaque utilisateur de base de données, un ou plusieurs profils de façon à lui fournir toutes les autorisations dont il a besoin pour travailler sur la base.

L’utilisateur dispose au final de l’ensemble des privilèges qui sont accordés :

  • Directement à la connexion utilisée.

  • Indirectement par l’intermédiaire d’un rôle de serveur accordé à la connexion.

  • Indirectement par l’intermédiaire des rôles accordés à l’utilisateur de base de données. 

  • Directement à l’utilisateur de base de données.

images/04ec167_2.png

En complément de ces différents rôles, il existe le rôle public. Ce rôle est à...

Exercice : mode de sécurité

1. Énoncé

Configurez le mode de sécurité mixte (SQL Server et Windows) sur l’instance Livre.

2. Corrigé

Pour activer le mode de sécurité mixte sur une instance SQL Server, il faut afficher la fenêtre présentant les propriétés de l’instance depuis l’explorateur d’objets dans SQL Server Management Studio. Depuis la fenêtre des propriétés, il faut afficher la page Sécurité comme présenté ci-dessous, puis sélectionner l’option Mode d’authentification SQL Server et Windows dans la zone Authentification du serveur.

images/04ri28.PNG

Pour que ce nouveau mode de configuration soit pris en compte il faut redémarrer le service associé à cette instance. Cette opération peut être effectuée directement depuis SQL Server Management Studio après un clic droit sur l’instance et en sélectionnant l’option Redémarrer.

images/04ri29.PNG

Exercice : compte sa

1. Énoncé

Pour l’instance Livre, activez le compte sa et définissez un mot de passe pour ce compte de niveau administrateur.

2. Corrigé

Depuis l’explorateur d’objets, en déroulant les nœuds Sécurité - Connexions, il est facile d’identifier les comptes désactivés car l’icône les représentant contient une croix rouge, comme c’est le cas pour le compte sa.

images/04ri30.PNG

Depuis la fenêtre des propriétés de cette connexion, il faut afficher la page État puis cocher le bouton radio Activé dans la zone Connexion.

images/04ri31.PNG

La même opération peut être faite à l’aide de la commande ALTER LOGIN de la façon suivante :

ALTER LOGIN sa ENABLE; 

L’étape suivante consiste à définir un mot de passe pour cette connexion. Cela peut être fait depuis la page Général.

images/04ri32.PNG

Le changement de mot de passe peut également être réalisé à l’aide du script Transact SQL suivant :

ALTER LOGIN sa WITH PASSWORD='P@$$w0rd'; 

Exercice : créer des utilisateurs SQL Server

1. Énoncé

Créez les connexions Paul et Jean au niveau de l’instance Livre. Ces deux comptes possèdent les caractéristiques suivantes.

Le compte Paul est créé avec un script Transact SQL, tandis que le compte Jean est créé depuis SQL Server Management Studio.

Compte Paul

Mot de passe : P@$$w0rd

Rôle de serveur : aucun

Base de données par défaut : aucune

Compte Jean

Mot de passe : P@$$w0rd

Rôle de serveur : aucun

Base de données par défaut : aucune

2. Corrigé

La création du compte Paul s’effectue depuis un script Transact SQL. Il est donc possible de saisir directement l’instruction.

CREATE LOGIN Paul  
  WITH PASSWORD='P@$$w0rd'; 

Pour effectuer le même type d’opération depuis SQL Server Management Studio et donc créer la connexion Jean, il faut sélectionner l’option Nouvelle connexion depuis le menu contextuel associé au nœud Sécurité - Connexions de l’explorateur d’objets.

images/04ri33.PNG

Exercice : créer des utilisateurs de base de données

1. Énoncé

Sur la base LivreTSQL, créez l’utilisateur Paul qui correspond à la connexion Paul définie au niveau du serveur.

2. Corrigé

Depuis SQL Server Management Studio, il faut sélectionner l’option Nouvel utilisateur depuis le menu associé au nœud Base de données - LivreTSQL - Sécurité - Utilisateur, puis configurer le nouvel utilisateur comme illustré ci-dessous :

images/04ri34.PNG

Il est également possible de faire appel à l’instruction CREATE USER, ce qui donne le script présenté ci-dessous :

USE LivreTSQL;  
GO  
CREATE USER Paul FOR LOGIN Paul; 

Il est nécessaire de débuter le script par l’instruction USE LivreTSQL afin de se positionner sur la bonne base de données.

Exercice : activer le compte invité

1. Énoncé

Sur la base LivreTSQL, activez le compte invité.

2. Corrigé

Le compte invité (ou guest) est par défaut désactivé sur les bases de données. C’est une bonne règle de sécurité. Il faut donc l’activer si l’on souhaite pouvoir l’utiliser. Cette opération peut être faite depuis SQL Server Management Studio en affichant les propriétés de la base données puis en sélectionnant la page Autorisations. À ce niveau, il faut ajouter le compte guest dans la zone Utilisateurs ou rôles puis, après avoir sélectionné le compte guest, il faut, dans la zone Autorisation pour guest, octroyer le privilège Connecter.

images/04ri35.PNG

Exercice : créer un rôle de base de données

1. Énoncé

Sur la base LivreTSQL, créez le rôle de base de données rôleTSQL et accordez-lui les privilèges de création de table et de création de vue.

2. Corrigé

Il faut travailler en deux étapes, soit dans un premier temps créer le rôle de base de données puis dans un second temps accorder les privilèges.

Ces deux étapes peuvent être effectuées depuis SQL Server Management Studio ou bien à l’aide d’un script Transact SQL.

Depuis SQL Server Management Studio

Depuis l’explorateur d’objets, sélectionnez le nœud Sécurité - Rôle - Rôle de base de données depuis la base LivreTSQL, et dans le menu contextuel de ce nœud, prenez le choix Nouveau rôle de base de données. La fenêtre de propriété permettant de créer un nouveau rôle est alors présentée et il faut la compléter comme illustré ci-dessous :

images/04ri36.PNG

Les privilèges de création de table et de vue sont des privilèges qu’il est possible d’accorder depuis la page Autorisations dans la boîte de dialogue Propriétés de la base TSQL.

images/04ri37.PNG

À l’aide d’un script Transact SQL

Il faut faire appel aux instructions CREATE ROLE pour créer...