Gestion de la sécurité sous SQL Server

MH MALHIERE Sécurité sous SQL Server Page 1
Gestion de la sécurité sous SQL Server
I. Les différents modes de sécurité d’accès de SQL Server
SQL Server dispose d’un système de sécurité qui lui est propre. Nous savons d’autre part que
Windows dispose également de son système de sécurité à travers les comptes utilisateurs.
SQL Server permet de fédérer (ou non) ces deux systèmes et propose deux modes d’accès :
 Le mode de sécurité intégrée qui utilise la sécurité de Windows. De ce fait, les utilisateurs
conservent un seul nom d’accès et un seul mot de passe pour Windows et SQL Server.
 Le mode de sécurité mixte (sécurité SQL Server ou Windows). Dans ce cas l’utilisateur peut se
connecter au serveur SQL à partir de son compte Windows ou de son nom d’accès SQL qui peut
alors être différent.
Pour choisir un mode de sécurité :
Click droit sur le nom du serveur…Propriétés…Onglet Sécurité
L’utilisateur sa est un utilisateur intégré à SQL server qui possède tous les pouvoirs (System Admin).
Pour changer son mot de passe, voici la procédure, très simple :
C:\Program Files\Microsoft SQL Server\80\Tools\Binn en ligne de commande
OU cmd / sqlcmd
sp_password null, 'nouveau_mot_de_passe', 'sa'
go
exit
II. Principe de fonctionnement de la sécurité
Pour utiliser une base de données sur un serveur, il faut :
1. avoir la permission d’accéder à ce serveur (chaque utilisateur doit posséder un nom d’accès au
serveur appelé nom de connexion)
2. avoir la permission d’accéder à la base de données (chaque utilisateur doit posséder un nom
d’utilisateur de la base de données)
3. Chaque utilisateur de la base de données se verra attribuer des autorisations lui permettant de
manipuler les différents objets de la base (table, vue, procédures stockées,…).
Les utilisateurs disposant d’un statut spécial
On distingue trois utilisateurs particuliers :
L’administrateur de SQL Server qui a pour nom d’accès sa. Il dispose de tous les droits et n’a donc
pas de nom d’utilisateur pour les bases de données.
Le créateur d’une base de données appelé également propriétaire de la base ou DBO (Data Base
Owner). Il dispose de l’intégralité des permissions sur cette base.
Le créateur d’un objet dans une base de donnée (table, vue, procédure stockée, déclencheur,…)
devient le propriétaire de cet objet (DBOO) et dispose de toutes les permissions sur cet objet.
Accès à un serveur SQL Server par authentification Windows
Le nom de connexion à un serveur SQL Server correspond au nom du compte Windows
SQL Server n’est accessible qu’à partir de la validation d’ouverture de session.
Il est possible d’utiliser le nom d’un groupe comme nom de connexion à un serveur SQL Server.
Dans ce cas, tous les membres du groupe auront accès au serveur SQL sans avoir de nom de
connexion personnel.
Exemple :

MH MALHIERE Sécurité sous SQL Server Page 2
Le nom du groupe "Etudiants825" (classe de STS IG 1ère année) est utilisé comme nom de connexion au
serveur SQL Server. Chaque étudiant de cette classe accède à SQL Server car son compte fait partie du
groupe " Etudiants825".
Dans la base système "Master" sont stockés tous les noms de connexion et les mots de passe associés. À chaque
compte de connexion correspond un identifiant "sid" qui est utilisé par le système pour le relier au nom
d’utilisateur dans une base.
Toutes les connexions sont inscrites dans la base MASTER (table Syslogins)
Créer une connexion à SQL Server
Se positionner sur le nœud Sécurité-connexions-Menu contextuel Nouvelle connexion
Remarque : Il faut posséder une permission d’administrateur (sysadmin) ou une permission de
gestionnaire de sécurité (sécurityadmin) pour pouvoir réaliser les différentes opérations relatives à la
gestion des connexions.
Accès à une base de données
Une fois l’accès au serveur SQL Server obtenu, un nom d’utilisateur dans la base doit correspondre à un
nom de connexion.
La base de données "cible" contient tous les noms des utilisateurs. Un nom d’utilisateur détermine de manière
unique un nom de connexion stocké dans la base "Master".
Accès aux objets d’une base de données
Il existe deux types d’autorisations.
 Les autorisations d’instruction qui permettent principalement de créer des objets :
- création d’une base de données,
- création de tables, de vues dans une base,
- création de procédures stockées…
 Les autorisations sur les objets existants qui permettent de manipuler les objets :
Insert, Update, Delete, Select,…
Par défaut, une fois l’accès à la base obtenu, l’utilisateur n’a pas encore d’autorisation sur la base.
Seuls l’administrateur et le propriétaire de la base peuvent donner des droits. Ils peuvent les attribuer soit
directement aux utilisateurs, soit à des rôles.
Un rôle est l’équivalent d'un groupe : son but est de regrouper les utilisateurs afin de définir des
autorisations à des groupes d'utilisateurs ayant les mêmes besoins, plutôt qu'à des individus.
III. Les rôles
Les rôles regroupent des ensembles d’autorisations. Il est préférable d’attribuer des autorisations à des
rôles puis d’attribuer les rôles aux utilisateurs.
III.1 Rôle du serveur
Ce sont des rôles prédéfinis
Nom
Nom complet
Description
bulkadmin
Bulk Insert Administrators
Peut procéder à une opération d'insertion en bloc.
dbcreator
Database Creators
Possibilité de créer et de modifier les bases de données.
diskadmin
Disk Administrators
Possibilité de gérer les fichiers disque.
processadmin
Process Administrators
Possibilité de gérer les traitements s'exécutant sous SQL Server.
securityadmin
Security Administrators
Possibilité de gérer les connexions pour le serveur.
serveradmin
Server Administrators
Possibilité de configurer les paramètres à l'échelle du serveur.
setupadmin
Setup Administrators
Possibilité de gérer les procédures stockées étendues.

MH MALHIERE Sécurité sous SQL Server Page 3
sysadmin
System Administrators
Possibilité d'effectuer toute activité dans l'installation de SQL Server
Accorder un rôle avec SQL Server Management Studio
Pour attribuer un rôle serveur à une connexion :
Menu contextuel Propriétés de la connexion…Onglet Rôle du serveur
Cocher le ou les rôles de serveur attribués à cette connexion

MH MALHIERE Sécurité sous SQL Server Page 4
III.2 Rôles de base de données
III.2.1 Rôles prédéfinis
Il existe des rôles prédéfinis et il est possible de définir ses propres rôles
Le rôle public est un rôle prédéfini, bien particulier, car tous les utilisateurs de la base possèdent ce rôle.
Ainsi, lorsqu’une autorisation d’instruction ou d’objet est accordée, supprimée ou interdite à public,
instantanément tous les utilisateurs de la base bénéficient des modifications. Le rôle public contient
toutes les autorisations par défaut dont bénéficient les utilisateurs de la base.
A part le rôle public il existe des rôles prédéfinis dans la base de données.
Rôle de base de données fixe
Description
db_owner
Possède toutes les autorisations de la base de donnéescomme le propriétaire de la
base.
db_accessadmin
Peut ajouter ou supprimer des utilisateurs dans la base.
db_securityadmin
Peut gérer toutes les autorisations, les propriétés d'objet, les rôles et les membres des
rôles.
db_ddladmin
Permet d’ajouter (CREATE), modifier (ALTER) ou supprimer (DELETE) des
objets de la base de données
db_backupoperator
Permet d’effectuer des sauvegardes de la base de données.
db_datareader
Peut sélectionner toutes les données de toutes les tables utilisateur dans la base de
données.
db_datawriter
Peut modifier les données de toutes les tables utilisateur dans la base de données.
db_denydatareader
Interdit la visualisation des données de la base.
db_denydatawriter
Interdit la modifiaction des données de la base.
III.2.2 Les rôles définis par l’utilisateur
On peut définir ses propres rôles à l’intérieur d’une base de données.
Intérêt : Lorsque des utilisateurs authentifiés par SQL Server et des utilisateurs authentifiés par Windows
doivent partager les mêmes droits.
Les rôles peuvent être accordés soit directement à un utilisateur soit à un autre rôle.
Créer un rôle défini par l’utilisateur
Se positionner sur Sécurité-Rôles-Rôles de la base de données
Donner un nom de rôle et un sélectionner le propriétaireDans l’onglet Eléments sécurisables
Ajouter un élément sécurisable
Sélectionner les types d’objets que vous souhaitez sécurisés, cliquez sur parcourir et cocher les dans la
liste fournie
Cocher ensuite les autorisations
Remarque : Un rôle ne peut être supprimé que s’il ne contient aucun membre
III.2.3 Rôles d’application
Les rôles d’application sont des rôles définis au niveau de la base de données sur laquelle ils portent.
Le but est de permettre à tous les utilisateurs d’une application de posséder suffisamment de droits pour le
bon déroulement de l’application où les opérations sur la base de données sont contrôlées par le
programme client.
Les rôles d’application permettent d’obtenir un comportement standard de l’application quel que soit
l’utilisateurs Windows qui lance l’application.
Le principe reste le même que pour les autres rôles mais en plus, on affecte un mot de passe.
Remarque : pour modifier le mot de passe d’un rôle, il faut être membre du rôle db_owner ou
db_securityadmin.

MH MALHIERE Sécurité sous SQL Server Page 5
IV. Gestion des utilisateurs des bases de données
IV.1 Créer un utilisateur d’une base de données
Se positionner sur Sécurité-Utilisateur –Menu contextuel Nouvel utilisateur
Donner un nom utilisateur et choisissez dans nom d’accès la connexion à laquelle il se rapporte
Les utilisateurs de base de données sont stockés dans la table système sysusers de la base de données sur
laquelle l’utilisateur est défini.
IV.2 Gestion des autorisations des utilisateurs
Il faut attribuer des autorisations aux utilisateurs des bases de données.
Pour pouvoir définir ces autorisations au niveau d’une base de données, il faut posséder des droits de
propriétaire (db_owner) ou de gestionnaire des accès (db_accessadmin).
Tous les utilisateurs de la base se voient attribuer le rôle Public.
On peut donner des droits d’utilisation d’instructions et des droits d’utilisation d’objets
Les droits d’utilisation d’instructions
On peut accorder des droits sur les instructions : CREATE DATABASE, CREATE PROCEDURE,
CREATE TABLE, BACKUP DATABASE, CREATE DEFAULT, CREATE RULE, CREATE VIEW,
BACKUP LOG(sauvegarde du journal des transactions)
Pour accorder des droits d’instructions :
Click droit sur le nom de la base de données…Propriétés…Autorisations
Cocher les différentes autorisations accordées
Les droits d’utilisation d’objets
Au niveau de l’utilisateur :
Click droit sur le nom d’utilisateur…Propriétés…Onglet Eléments sécurisables
Ajouter un élément sécurisable
Sélectionner les types d’objets que vous souhaitez sécurisés, cliquez sur parcourir et
cocher les dans la liste fournie
Cocher ensuite les autorisations
Au niveau de l’objet (Table, vue, procédure) :
Click droit sur le nom de l’objet…Propriétés…Autorisations
Cliquer sur Ajouter dans la partie utilisateurs, puis sur parcourir pour obtenir la liste des
utilisateurs de la base de données.
Cocher les utilisateurs qui vous intéressent
Cocher les autorisations que vous lui accordez