SQL Server La gestion des utilisateurs

SQL Server
La gestion des utilisateurs
Version
Date
Auteur
1/5
1.0
18/03/2015
Guy Verghote
1. Étape numéro 1 : Connexion au serveur – Création d'un login
Pour pouvoir se connecter à SQL Server un utilisateur doit posséder un login (nom d'accès).
SQL Server peut utiliser la sécurité intégrée (il utilise le nom et le mot de passe Windows pour la
connexion) ou la sécurité standard (il gère ses propres noms d'accès et mots de passe).
Création d'un login en sécurité standard
create login nomLogin with password = 'mot de passe', default_database = database
Création d'un login en sécurité intégrée (compte Windows)
create login [stremi\roy] from windows
stremi est le nom du serveur, roy le nom du l'utilisateur
Il est possible de modifier le nom du login ou le mot de passe d'un login
alter login nomLogin with name = nouveauNomLogin;
alter login nomLogin with Password = 'nouveauMotDePasse'
Il est possible de désactiver une connexion (login)
alter login nomLogin disable
La connexion est réactivée à l'aide de l'option enabled
Une connexion peut être supprimée par la commande drop login
if exists(select name from master..syslogins where name = 'admin')
drop login admin
Il est aussi possible de refuser la connexion d'un login : Echec de l'ouverture de session utilisateur
'….'
deny connect sql to nomLogin
Pour l'autoriser de nouveau :
grant connect sql to nomLogin
Un utilisateur Windows ayant le rôle d'administrateur se retrouve avec les mêmes droits qu'un
administrateur sous SQL Server

SQL Server
La gestion des utilisateurs
Version
Date
Auteur
2/5
1.0
18/03/2015
Guy Verghote
2. Étape numéro 2 : Connexion à une base de données – Création d'un utilisateur
Pour pouvoir se connecter à une base de données, un login (nom d’accès) doit être relié à un nom
d’utilisateur dans cette base de données ou être le propriétaire de la base.
create user nomUtilisateur for login nomLogin with default_schema = nomSchema
Notion de schéma :
Un schéma est un espace de noms à l'intérieur d'une base de données.
Dans un espace de noms chaque élément porte un nom unique.
Une base de données pouvant contenir plusieurs schémas, on pourra ainsi avec deux tables dans
la même base de données portant le même nom si elle ne se trouve pas dans le même schéma.
Les noms d'objets complets sont donc constitués de quatre parties :
server.database.schema.object
Si le schéma par défaut n'est pas précisé, l'utilisateur sera attaché au schéma dbo
Commandes pour la gestion des schémas
Action
Définition
Création
create schema nomSchema [authorization nomUtilisateur]
L'option authorization permet de définir le propriétaire du schéma
Suppression
drop schema nomSchema
Le schéma doit être vide
Modification
alter schema nomSchema transfer nomSchema2.nomobjet
Transfère un objet d'un schéma à un autre
Si l'utilisateur n'est pas propriétaire du schéma, il n'a initialement aucun droit.
Par conséquent même si on donne le droit à l'utilisateur de créer des tables, il ne pourra le faire
car il n'a aucun droit dans le schéma.
Attribution de droit sur le schéma
La notion de schéma apporte donc un niveau supplémentaire sur lequel on peut donner des droits
aux utilisateurs
Autorisation
Définition
Ajouter un objet
grant alter on schema::nomSchema to nomUtilisateur
Appliquer l'int. ref.
grant references on schema::nomSchema to nomUtilisateur
Tous les droits
grant control on schema::nomSchema to nomUtilisateur
Il est possible de supprimer un utilisateur défini au niveau de la base de données
drop user nomUtilisateur

SQL Server
La gestion des utilisateurs
Version
Date
Auteur
3/5
1.0
18/03/2015
Guy Verghote
3. Étape numéro 3 : Donner des droits aux utilisateurs d'une base de données
Ce n'est pas parce que l'on a obtenu l'accès à SQL Server et à une base de données que l'on a le droit
d'y faire n'importe quoi. Un utilisateur normal, c'est à dire ni administrateur système, ni propriétaire
de base, ni propriétaire d'objets, reçoit des "autorisations". Il est possible de donner des droits aux
utilisateurs et aux groupes d'utilisateurs. Par défaut, un utilisateur n'a aucun droit dans la base de
données dans laquelle il a été défini, à moins que le rôle (groupe) auquel il appartient (par défaut,
Public) n'ait lui-même des droits.
Il existe quatre types d'utilisateur dans une base de données:
Les administrateurs systèmes (sa et les membres du rôle sysadmin) : ils ont tous les droits sur
tous les objets du système,
Le dbo ou propriétaire de la base : il a tous les droits sur la base de données qu'il possède.
Le dboo ou propriétaire d'objet de la base (table, procédure stockée, etc.) : il a tous les droits sur
les objets qu'il possède
Les autres utilisateurs : ils n'ont aucun droit explicite. Ils peuvent obtenir directement des
autorisations de la part des propriétaires et des administrateurs ou les hériter par l'intermédiaire
des rôles (groupes) auxquels ils appartiennent.
3.1. Gestion des droits par l'intermédiaire de rôle (groupe)
Il est possible de créer des groupes (rôles) pour faciliter l'affectation des droits aux utilisateurs.
Create role
Ajoute un rôle dans la base de données en cours
Exemple : Create role 'SLAM'
sp_addrolemember
Ajoute un utilisateur (ou un rôle) dans un rôle
Syntaxe : sp_addrolemenber 'nom de rôle', nom utilisateur (ou rôle) à ajouter
au rôle
Exemple : sp_addrolemember 'SLAM', nomUtilisateur
sp_addrolemember 'db_datareader', SLAM
Drop role
'nomRole'
Supprimer un rôle dans la base de données en cours
Remarque : Le rôle ne doit contenir de membre pour pouvoir être supprimé
sp_droprolemember
Supprime un utilisateur (ou un rôle) dans un rôle
Syntaxe : sp_droprolemenber 'nom de rôle', nom utilisateur (ou rôle) à
supprimer du rôle
Exemple : sp_dropolemember 'SLAM', Slam01

SQL Server
La gestion des utilisateurs
Version
Date
Auteur
4/5
1.0
18/03/2015
Guy Verghote
Il existe des rôles standards qui disposent déjà de permissions
Db_owner
Exécute les activités de tous les rôles
Db_accessadmin
Ajoute ou supprime des groupes / utilisateurs Windows, ou des utilisateurs
SQL Server
Db_datareader
Consulte les données de toutes les tables.
Db_datawriter
Ajoute, modifie ou supprime des données
Db_ddladmin
Ajoute, modifie ou supprime des objets
Db_securityadmin
Gère les rôles et les membres de rôles, les autorisations sur les instructions et
les objets
Tout utilisateur d'une base de données appartient automatiquement au groupe (rôle) Public.
3.2. Gestion des permissions sur les instructions
Les permissions d'instruction ne sont liées à aucun objet spécifique. Ces permissions ne peuvent
être accordées que par l'administrateur système (sa) ou par le propriétaire de base de données
Accorder
grant {create database | create table | create view | create procedure | … }
to {public | nomutilisateur | nomrole, … }
Révoquer
revoke { create database | create table | create view | all }
to {public | nomutilisateur, … }
Exemple :
Autorisation pour un utilisateur SQL et un utilisateur Windows de créer des bases et des tables
 grant create database, create table to nomUtilisateur, [stremi\ancenis]
Autorisation pour le groupe SLAM de créer des tables
 grant create table to slam

SQL Server
La gestion des utilisateurs
Version
Date
Auteur
5/5
1.0
18/03/2015
Guy Verghote
3.3. Gestion des permissions sur les objets
Les permissions permettent de préciser ce que chaque utilisateur peut faire sur les objets (tables,
index, vues, procédures stockées, etc.) d'une base de données. Elles sont accordées et révoquées
par le propriétaire du schéma ou un utilisateur autorisé.
Accorder
GRANT {SELECT | INSERT | DELETE | UPDATE | EXECUTE |ALL }
ON NomObjet [(colonne, …)] TO {PUBLIC | nomUtilisateur | nomRole, … }
[With Grant Option]
Révoquer
REVOKE {SELECT | INSERT | DELETE | UPDATE | ALL}
ON NomObjet [(colonne, …)] TO {PUBLIC | nomUtilisateur, … }
[Cascade]
Voir
sp_helprotect nomObjet
L'option WITH GRANT OPTION précise que l'utilisateur recevant les droits est habilité à
accorder des autorisations sur cet objet à d'autres utilisateurs. Pour révoquer ce type de droit, il
faut utiliser l'option Cascade de manière à traiter tous les héritiers de ce droit.
Les permissions d'instruction suivantes reviennent par défaut au propriétaire d'une table et ne sont
pas transmissibles à d'autres utilisateurs :
ALTER TABLE, DROP TABLE, TRUNCATE TABLE, UPDATE STATISTICS
CREATE INDEX, DROP INDEX, CREATE TRIGGER, DROP TRIGGER
Exemple
Droit de lecture de la table client pour tous les utilisateurs
Grant Select On Client To Public
Droit d'ajout, de modification et de suppression sur la table client pour trois utilisateurs
Grant Insert, Update, Delete On Client To Slam02 Slam03, Slam04
Transmission des autorisations : La table Client appartient à l'utilisateur SLAM01.
L'utilisateur donne le droit de lecture à Duval et au groupe SLAM avec le droit de transmettre ce
droit
Grant Select On Client To Duval With Grant Option
L'utilisateur Duval transmet ce droit à Dupont
Grant Select On Client To Dupont
L'utilisateur SLAM05 membre du groupe SLAM transmet ce droit à l'utilisateur C2101, qui n'est
pas membre de SLAM. SLAM05 doit utiliser la clause As pour bénéficier des autorisations
d'accord du rôle SLAM.
Grant Select On Client To C2101 As Slam
L'utilisateur SLAM01 supprime l'autorisation donné à duval sur la table client.
Revoke Select On Client To Duval Cascade
Avec l'option cascade, tous les utilisateurs ayant reçu ce droit le perde (Duval, Groupe SLAM,
C2101)