Gestion des utilisateurs, des groupes et des rôles dans SQL Server 2008

Gestion des utilisateurs,

des groupes et des rles

dans SQL Server 2008 Version 1.0

Z

Grgory CASANOVA

Dotnet France Association

2 Les utilisateurs, les groupes et les rles

Sommaire

1 Introduction ..................................................................................................................................... 4

2 Gestion des accs serveur ............................................................................................................... 5

2.1 Mode de scurit Windows .................................................................................................... 5

2.1.1 En gnral ........................................................................................................................ 5

2.1.2 En dtail ........................................................................................................................... 5

2.2 Mode de scurit Mixte .......................................................................................................... 5

2.2.1 En gnral ........................................................................................................................ 5

2.2.2 En dtail ........................................................................................................................... 5

2.3 Choisir son mode de scurit .................................................................................................. 5

2.3.1 Avec lassistant dinstallation .......................................................................................... 6

2.3.2 Avec SSMS ....................................................................................................................... 6

2.4 Gestion des connections SQL Server .................................................................................... 6

2.4.1 En mode scurit Windows ............................................................................................. 6

2.4.2 En mode de scurit mixte .............................................................................................. 7

2.4.3 Les certificats ................................................................................................................... 9

2.4.4 Les credentials ................................................................................................................. 9

3 Gestion des utilisateurs de base de donnes ................................................................................ 10

3.1 Cration dutilisateurs de base de donnes .......................................................................... 10

3.1.1 Avec SSMS ..................................................................................................................... 10

3.1.2 Avec du code T-SQL ....................................................................................................... 11

3.2 Modification dutilisateur de base de donnes ..................................................................... 11

3.2.1 Avec SQL Server Management Studio .......................................................................... 11

3.2.2 Avec du code T-SQL ....................................................................................................... 11

3.3 Suppression dutilisateur de base de donnes ...................................................................... 11

3.3.1 Avec SSMS ..................................................................................................................... 11

3.3.2 Avec du code T-SQL ....................................................................................................... 11

4 Gestion des schmas ..................................................................................................................... 13

4.1 Cration dun schma ............................................................................................................ 13

4.1.1 Avec SSMS ..................................................................................................................... 13

4.1.2 Avec du code T-SQL ....................................................................................................... 13

4.2 Modification dun schma ..................................................................................................... 14



4.2.1 Avec SSMS ..................................................................................................................... 14

4.2.2 Avec du code T-SQL ....................................................................................................... 14

4.3 Suppression dun schma ...................................................................................................... 14

4.3.1 Avec SSMS ..................................................................................................................... 14

4.3.2 Avec du code T-SQL ....................................................................................................... 14

5 Gestion des droits .......................................................................................................................... 14

5.1 Droits dinstruction ................................................................................................................ 15

5.1.1 Avec SSMS ..................................................................................................................... 15

5.1.2 Avec du code T-SQL ....................................................................................................... 15

5.2 Droits dutilisation ................................................................................................................. 16

5.2.1 Avec SSMS ..................................................................................................................... 16

5.2.2 Avec du code T-SQL ....................................................................................................... 17

5.3 Droits au niveau base de donnes ........................................................................................ 17

5.3.1 Avec SSMS ..................................................................................................................... 17

5.3.2 Avec du code T-SQL ....................................................................................................... 18

5.4 Droit au niveau serveur ......................................................................................................... 20

6 Les rles ......................................................................................................................................... 20

6.1 Les rles prdfinis ................................................................................................................ 21

6.2 Les rles dfinis par lutilisateur ............................................................................................ 21

6.2.1 Avec SQL Server Management Studio ........................................................................... 22

6.2.2 Avec du code T-SQL ....................................................................................................... 22

7 Conclusion ..................................................................................................................................... 23



1 Introduction La scurit est un domaine important dans les bases de donnes. Sans scurit, nos donnes

sont exposes des risques daltration de tous types, le plus souvent cause des utilisateurs finaux,

ou dune mauvaise politique de gestion des bases. Il existe dans SQL Server 2008, trois types dobjets

qui vont nous permettre de scuriser nos bases et donc garantir une plus grande chance dintgrits

pour nos donnes. Ces trois types sont :

- Les entits de scurit : compte de scurit qui dispose dun accs au serveur de donnes

SQL.

- Les scurisables : objets grs par le serveur. Nous avons vu prcdemment ce qutait un

objet dans une base de donnes ou dans un serveur.

- Les autorisations : celles-ci sont accordes aux entits de scurit afin de pouvoir travailler

avec les scurisables.

Ces lments de scurit ont une organisation de type hirarchique. Cette hirarchie va nous

permettre de mettre en place une politique sure, afin de maintenir lintgrit de nos donnes, en

faisant en sorte que cette gestion des droits daccs soit simple, mais efficace. Par exemple, de faon

hirarchique, on pourra accorder lautorisation SELECT , un scurisable, par exemple un schma,

pour permettre lentit de scurit qui reoit cette autorisation deffectuer lopration SELECT sur

tous les objets contenus dans le schma dfinit. Parlons rapidement des schmas. Il en existe trois

types dfinis. Les schmas de niveau serveur, qui vont sappliquer toutes les bases de donnes

implants sur le serveur. Les schmas de niveau base de donnes, actifs seulement sur la base de

donnes ou il est dfinit. Et enfin les rles dapplication, dfinis sur la base de donnes utilisateur et

qui vont permettre le bon fonctionnement dune application cliente. Nous parlerons plus amplement

des schmas dans la suite de ce chapitre. Dautres scurisables existent, par exemple les utilisateurs

simples, les groupes Leur organisation vis--vis des autres lments de scurit ce fait aussi de

manire hirarchique, comme pour les schmas.

Il est possible dobtenir un catalogue complet des utilisateurs et de leurs privilges, grce aux vues

systme. Voici quelques une de ces vues :

- Sys.server_permissions : Liste des permissions au niveau serveur et de leurs bnficiaires.

- Sys.server_principals : Entits de scurit dfinis au niveau serveur.

- Sys.sql_logins : Liste des connexions au niveau serveur.

- Sys.Server_role_members : Liste des bnficiaires dun rle au niveau serveur.

- Sys.database_permissions : Liste des permissions et de leurs bnficiaires au niveau base de

donnes.

- Sys.database_principals : Entits de scurit au niveau base de donnes.

- Sys.database_role_members : Liste des bnficiaires dun rle de base de donnes.

Apprenons maintenant toutes les manires de prserver lintgrit des donnes de nos bases, en

mettant en place une politique de scurit efficace.



2 Gestion des accs serveur Avant de pouvoir commencer travailler avec les donnes de nos bases, il est impratif de se

logger sur le serveur SQL. Cette tape permet de sidentifier au niveau du serveur SQL, afin de

pouvoir exploiter les droits qui ont ts attribus notre connexion. Dans SQL Server, il existe

plusieurs modes dauthentification que nous allons dfinir dans cette partie.

2.1 Mode de scurit Windows

2.1.1 En gnral

Dans ce mode de scurit, SQL Server sappui sur les fonctionnalits de Windows. En effet, il

se sert de la scurit de Windows (login et mot de passe) pour crer des connexions aux instances et

donc aux bases de donnes. Puisque le mode de scurit Windows se sert de la scurit du systme

sur lequel SQL Server est install, la gestion des groupes est aussi possible. Il est donc plus facile

dadministrer des connexions aux bases de donnes, de manire plus souple quutilisateur par

utilisateur.

2.1.2 En dtail

En ralit, seuls les login sont sauvegards dans linstance, de faon permettre laccs aux

bases par la suite. Le mot de passe de chaque utilisateur est dtenu par Windows. Vous comprendrez

aisment quil est bien plus scuris de ne pas stocker le mot de passe dun utilisateur directement

dans une base systme. De plus, la politique dadministration et de scurit qui nonce la rgle

suivante : un mot de passe par utilisateur, est bien plus simple mettre en place.

2.2 Mode de scurit Mixte

2.2.1 En gnral

Le mode de scurit mixte repose sur une double authentification : lauthentification

Windows puis lauthentification SQL Server. Cest le mode le plus connu pour la scurit des bases de

donnes. Dans le principe, cest SQL Server qui ce charge de vrifier que lutilisateur existe et quil

possde le bon mot de passe. Cela signifie que les utilisateurs sont entirement grs par SQL Server,

autant les login que les mots de passe. Ce type didentification est bien adapt pour une gestion des

utilisateurs qui ne passent pas par une authentification Windows la base.

2.2.2 En dtail

Il est faux de penser que toute la scurit du serveur est gre par le serveur de base de

donnes. Le fonctionnement mixte est bas sur un principe de vrification simple. Le systme

Windows vrifie si lutilisateur existe et possde les droits de se connecter au serveur. Si cest le cas,

lutilisateur sera connect. Si ce nest pas le cas, une erreur est leve, nonant que lutilisateur

nexiste pas sur le systme. Le serveur de base de donnes est alors charg de demander le login et

le mot de passe de la connexion.

2.3 Choisir son mode de scurit On peut directement choisir le mode de scurit de SQL Server de deux manires diffrentes :

lors de linstallation de linstance SQL Server, ou bien directement dans SSMS, sans passer par

lassistant dinstallation de SQL Server. Dcouvrons les diffrentes manires de changer de mode de

scurit.



2.3.1 Avec lassistant dinstallation

Lors de linstallation dune instance de SQL Server, il vous sera demand de prciser quel

mode de scurit vous dsirez mettre en place. Choisissez bien entendue le mode qui vous convient.

2.3.2 Avec SSMS

Avec SSMS, il va falloir vous rendre directement dans les proprits de votre instance afin de

modifier le mode de scurit. Voici la fentre sur laquelle vous devez vous rendre afin doprer aux

modifications :

Dans le menu Scurit, dans la partie Authentification du Server, choisissez simplement le

mode de scurit mettre en place. Cliquez sur OK pour valider votre choix.

2.4 Gestion des connections SQL Server

2.4.1 En mode scurit Windows

Les noms de groupes ou les utilisateurs doivent tre les mmes que sous Windows. Il existe

plusieurs faons de crer des connexions au serveur de base de donnes. Nous allons les dtailler.

2.4.1.1 Avec SQL Server Management Studio

Pour crer une connexion en mode graphique, par lintermdiaire de SSMS, il vous suffit de

vous rendre dans lexplorateur dobjet, de dployer les nuds jusqu Scurit-Connexion. Dans le

menu contextuel de ce nud, choisir nouvelle connexion. La fentre suivante apparait :



Linterface graphique SSMS permet tout aussi bien de modifier les proprits dune

connexion ou bien de la supprimer simplement.

2.4.1.2 Avec du code T-SQL

Avec du code T-SQL, la syntaxe de cration dune connexion SQL Server est la suivante :

Linstruction CREATE LOGIN permet dannoncer SQL Server que nous allons crer une

connexion. Il est ncessaire de donner le nom de la connexion Windows associe. La clause FROM

WINDOWS, permet de dire que le login existe dans le systme dexploitation Windows. Les deux

options suivantes permettent quand elles de choisir, et la base par dfaut de la connexion, et la

langue par dfaut de cette mme connexion.

Note : Il est important de noter que les instructions UPDATE et DELETE peuvent tre appliques dans le cas dun

changement de proprit de connexion ou dune suppression de connexion.

2.4.2 En mode de scurit mixte

Comme pour toute cration dobjet dans SQL Server, et donc comme pour le mode de

scurit Windows, il est possible de crer une connexion par deux moyens. La mise en place de ce

mode de scurit est en tous points semblables la mise en place du mode de scurit Windows,

une chose prs : il faut mettre en place les rgles de gestion des mots de passe.

2.4.2.1 Avec SQL Server Management Studio

La procdure est la mme que pour le mode de scurit Windows. Nous ne la dtaillerons

pas une deuxime fois.

CREATE LOGIN nom_connexion

FROM WINDOWS

WITH DEFAULT_DATABASE=Master

DEFAULT_LANGUAGE=langue



Dans le menu Gnral, nous choisirons Authentification SQL Server. Il ne vous restera donc

plus qu donner un nom la connexion et un mot de passe dans le champs prvu cet effet.

Comme prcdemment, linterface graphique permet de modifier ou bien supprimer une connexion.

2.4.2.2 Avec du code T-SQL

Linstruction CREATE LOGIN annonce SQL Server quune nouvelle connexion va tre cre. On

donne imprativement un nom cette connexion. La clause FROM permet de donner plusieurs

proprits cette connexion, qui sont les suivantes :

- PASSWORD : Permet de prciser un mot de passe. Loption HASHED, permet de hacher le

mot de passe lors de son stockage en fonction de la chaine de caractres prcise avant de

mot cl.

- DEFAULT_DATABASE : Permet de prciser le nom de la base de donnes par default.

- DEFAULT_LANGUAGE : Permet de prciser un langage par dfaut.

- CHECK_EXPIRATION : A OFF par dfaut. Il nest possible dactiver cette option que si

CHECK_POLICY est aussi activ. Elle permet dappliquer la politique de changement des

mots de passe dfini sur le serveur.

- CHECK_POLICY : A ON par dfaut, cette option permet de rcuprer au niveau serveur, les

options dfinies pour la politique de scurit.

CREATE LOGIN nom_connexion

FROM PASSWORD='monmotdepasse'

Motdepasse HASHED MUST_CHANGED,

DEFAULT_DATABASE=Master,

DEFAULT_LANGUAGE=langue,

CHECK_EXPIRATION=ON,

CHECK_POLICY=ON,

CREDENTIAL=Nom



- CREDENTIAL : Permet de relier la connexion un credential cr auparavant. Nous verrons

par la suite ce quest un credential.

2.4.3 Les certificats

SQL Server donne la possibilit de mettre en place des connexions au travers de certificats,

qui sont un moyen sur de connexion. Il est possible dutiliser des certificats dj contenus dans un

fichier, ou bien demander SQL Server de gnrer ce dit certificat.

2.4.4 Les credentials

Ces objets permettent des connexions en mode de scurit SQL Server daccder des

ressources externes au serveur de base de donnes. Les credentials vont donc contenir un login et

un mot de passe Windows qui sera ventuellement rattach une connexion de type mixte. Voyons

maintenant comment crer un credential :

Linstruction CREATE CREDENTIAL permet dannoncer SQL Server que nous allons crer un

credential. Par la suite, il est impratif de prciser un nom unique qui va nous permettre de

reconnatre ce credential parmi les autres credentials prsents sur le serveur de base de donnes.

Loption WITH IDENTITY permet de prciser le compte Windows utiliser dans le credential et

SECRET permet de prciser le mot de passe. Les credentials peuvent tre crs avec linterface

graphique dans lexplorateur dobjets en slectionnant Nouvelle information didentification dans

le menu contextuel du nud Information didentification.

CREATE CREDENTIAL nomducredential

WITH IDENTITY = 'nom', SECRET = 'secret'



3 Gestion des utilisateurs de base de donnes Maintenant que nous avons dfinis les connexions (objets de niveau serveur), nous allons

montrer comment dfinir les objets de niveau base de donnes que sont les utilisateurs. En effet, les

droits daccs, dcriture, de lecture sur les bases de donnes ne sont pas attribus aux connexions,

mais aux utilisateurs de bases de donnes. Il faut alors savoir que chaque utilisateur de base de

donnes est affili une connexion de niveau serveur. Cependant, il existe des utilisateurs, qui eux

ne sont pas associs une connexion particulire (guest, sys). Il est donc bon de savoir que si un

utilisateur dispose dune connexion de base de donnes et que celle-ci nappartient aucun

utilisateur de base de donnes capable dintervenir sur la base, les actions que celui-ci peut effectuer

sont trs limites. Il existe alors deux types de droits :

- Ceux qui permettent ou non dutiliser les objets de telle ou telle base.

- Ceux qui permettent dutiliser telle ou telle instruction.

Comme nous lavons dit plus tt, ces droits sont accords ou refuss aux utilisateurs de bases de

donnes et non aux connexions serveur.

Note : Les utilisateurs de bases de donnes sont stocks dans la table systme sysuser. De plus, sa cration, un

utilisateur ne dispose daucun droit particulier si ce nest de celui de guest.

3.1 Cration dutilisateurs de base de donnes Il existe deux manires de crer des utilisateurs dans SQL Serveur, comme pour crer la

plupart des objets prsents dans une base de donnes. La cration dun utilisateur de base de

donnes va permettre de lier lutilisateur la connexion, et donc autoriser lutilisation de cette base.

3.1.1 Avec SSMS

Pour crer un utilisateur dans SSMS, ouvrez lexplorateur dobjets, et placez-vous sur la base

de donnes concerne. Dveloppez alors le nud Scurit et placez vous sur le nud Utilisateurs.

Dans le menu contextuel du nud Utilisateurs, choisissez Nouvel utilisateur. Une nouvelle fentre

souvre.



Prcisez dans un premier temps le nom de lutilisateur de base de donnes et dans un second

temps, la connexion serveur lui associer. A partir de cette fentre, il est, entre autres, possible

dattacher des schmas cet utilisateur ainsi que des rles de base de donnes. Cliquez alors sur OK.

Votre utilisateur est cr et mapp sur une connexion existante.

3.1.2 Avec du code T-SQL

En Transact SQL, linstruction permettant de crer des utilisateurs et de les mapper aux

diffrentes connexions est CREATE USER. Voici la syntaxe dtaille de cette instruction :

- FOR LOGIN : Dfinit la connexion o sera mapp lutilisateur.

- CERTIFICATE : Dfinit le certificat utiliser. Ne peut pas sutiliser si une connexion est utilise

dans la clause FOR LOGIN.

- ASYMETRIC KEY : Dfinit la cl asymtrique utiliser utiliser.

- WITH DEFAULT SCHEMA : Nom du schma de base de donnes donner lutilisateur.

Note : Il existe des procdures stockes pour crer des utilisateurs et leurs donner des droits sur les bases de donnes qui

sont sp_grantdbaccess et sp_adduser. Celles-ci sont maintenues pour des raisons de compatibilit ascendante,

cependant, il est conseill de ne plus les utiliser car elles sont voues disparaitre dans les prochaines versions de SQL

Server.

3.2 Modification dutilisateur de base de donnes

3.2.1 Avec SQL Server Management Studio

Pour modifier un utilisateur de base de donnes via linterface graphique, il vous suffit de

dployer lensemble des nuds qui mne cet utilisateur dans lexplorateur dobjets, afficher le

menu contextuel et slectionner proprits. Vous arriverez alors directement sur la fentre de

cration dun utilisateur, ou certain champs seront remplis. Il vous suffira de changer les

informations que vous dsirer modifier.


Pour modifier un utilisateur avec du code, nous allons utiliser linstruction ALTER USER. Voici

la syntaxe de modification des proprits dun utilisateur.

3.3 Suppression dutilisateur de base de donnes

3.3.1 Avec SSMS

La procdure est la mme que pour la modification. Simplement, au lieu dafficher le menu

contextuel et de slectionner proprit, nous allons slectionner supprimer.


Pour supprimer un utilisateur, nous allons utiliser linstruction DROP USER. Voici la syntaxe de

cette instruction :

ALTER USER nomutilisateur

WITH NAME=nouveaunom,

DEFAULT_SCHEMA=nouveauschema

CREATE USER nom

FOR LOGIN connexion

CERTIFICATE nomcertificat

ASYMETRIC KEY nomcle

WITH DEFAULT_SCHEMA = nomschema



DROP USER nomutilisateur



4 Gestion des schmas Un schma est un ensemble logique dobjets lintrieur des bases de donnes sur le serveur.

Leur but est de faciliter, entre autre, lchange de donnes entre les utilisateurs, sans pour autant

affecter la scurit. Concrtement, les schmas permettent une gestion plus aise des privilges

dutilisation des objets. Comme nous lavons vu prcdemment, un utilisateur est mapp sur un

schma ds sa cration, obligatoirement. Si toute fois, aucun nom de schma nest prcis, alors

lutilisateur sera mapp sur dbo par dfaut. Pour expliquer simplement le fonctionnement des

schmas prenons un exemple. Un utilisateur est mapp sur un schma nomm RU. Pour requter sur

les objets de la base, il pourra crire directement le nom seul de cet objet si celui-ci est compris dans

le schma sur lequel il est mapp. Dans le cas contraire, lutilisateur devra prciser le schma de

lobjet, le nom de lobjet et ce moment l, SQL Server cherchera si le dit utilisateur possde les

droit dutiliser lobjet auquel il tente daccder. On peut retenir que les schmas servent

essentiellement faciliter le partage dinformation entre les utilisateurs mapps sur ce schma.

4.1 Cration dun schma

4.1.1 Avec SSMS

Pour crer un schma grce SSMS, il vous suffit de dployer tous les nuds jusqu scurit,

dafficher le menu contextuel (clic droit) du nud schma et de slectionner Nouveau schma. Une

nouvelle fentre apparait.


Voici la syntaxe de cration dun schma de base de donnes :

Pour crer un schma de base de donnes, nous nous servons de linstruction CREATE

SCHEMA. Comme tout objet dans une base de donnes, un schma doit avoir un nom unique dans la

CREATE SCHEMA nomschema

AUTHORIZATION nomproprietaire

options



famille des schmas. Il est donc ncessaire de prciser un nom derrire cette instruction. La clause

instruction va nous permettre en revanche de prciser lutilisateur de base de donnes qui sera

propritaire du schma. Pour finir, ce que nous avons reprsent par option reprsente lespace

dans lequel nous pouvons directement faire la dfinition de nos tables, vues et privilges rattachs

nos tables.

4.2 Modification dun schma

4.2.1 Avec SSMS

Avec linterface graphique, la manipulation est la mme que pour la cration dun schma.

Cependant, il faudra veiller ne pas crer un nouveau schma, mais se rendre dans les proprits

du schma que nous voulons modifier. Il faut en revanche prendre en compte que lon ne peut pas

changer le nom du schma, seulement les tables, vue qui y sont contenues, les autorisations et le

propritaire du schma.


Pour modifier un schma avec du code, nous allons utiliser cette syntaxe :

Pour modifier un schma, nous allons utiliser linstruction ALTER SCHEMA suivie du nom du

schma modifier. Aprs la clause TRANSFERT, on peut spcifier les divers objets dplacer dans le

schma. Il faut savoir que le nom de ces objets doit tre au format suivant :

AncienSchema.NomObjet.

4.3 Suppression dun schma

4.3.1 Avec SSMS

Pour supprimer un schma de base de donnes avec SSMS, rendez vous sur le nud schma

de la base de donnes ou est mapp le schma. Dployez ce nud pour laisser apparaitre les

schmas existants. Faites un clic droit sur le schma supprimer et slectionnez Supprimer.


Pour supprimer un schma, nous allons utiliser linstruction DROP SCHEMA, avec la syntaxe

suivante :

5 Gestion des droits Les droits sont les autorisations qui vont nous permettre de travailler avec notre base de

donnes. Ils sont organiss de faon hirarchique par rapport aux lments scurisables du serveur.

DROP SCHEMA nomschema

ALTER SCHEMA nomschema

TRANSFERT nomobjet



Lattribution des droits peut tre faite tous les niveaux, que se soit au niveau serveur, au niveau

base de donnes ou encore directement sur les schmas ou les objets. Par consquent, ils peuvent

tre accords soit un utilisateur, soit une connexion. Il est possible de grer ces permissions grce

3 instructions simples dans SQL Server : GRANT, DENY, REVOKE. GRANT permet de donner un

privilge, REVOKE permet de le retirer si celui-ci a t donn auparavant et DENY permet de

linterdire mme si il a t donn au travers dun rle.

5.1 Droits dinstruction Ces droits correspondent aux droits qui permettent de crer (mettre jour, supprimer) de

nouveau objets dans la base. Les utilisateurs qui possdent de tels droits sont donc capable de crer

leurs propres tables Voici les principaux droits disponibles : CREATE DATABASE, CREATE TABLE,

CREATE FUNCTION, CREATE PROCEDURE, CREATE VIEW, BACKUP DATABASE, BACKUP LOG Nous

allons maintenant apprendre donner, retirer ou interdire des droits.

5.1.1 Avec SSMS

Avec SSMS, on peut administrer ces droits via la fentre proprit de la base de donnes concerne.

Pour cela, rendez-vous sur le menu Autorisations de cette mme page.

Pour ajouter des droits un utilisateur, slectionnez lutilisateur dans le premier champ, o

nous avons notre utilisateur Guest, et octroyez-lui, enlevez-lui ou refusez-lui des permissions en

cochant ou dcochant les checkbox en consquence.


Pour ajouter, supprimer ou interdire un droit un utilisateur de base de donnes, nous allons

utiliser les instructions GRANT, DENY et REVOKE comme nonc prcdemment. Voici la syntaxe de

don, suppression ou interdiction de droits :



5.2 Droits dutilisation Les droits dutilisation permettent de dterminer si un utilisateur possde les droits pour

travailler sur les objets, par exemple lire les informations, insrer des donnes Dans le cas gnral,

cest le propritaire de lobjet qui dfinit les droits dutilisation. En ce qui concerne les droits de

lecture et de mise jour des donnes, le propritaire possde la facult de choisir quelles colonnes

lutilisateur peut lire ou mettre jour. Voici la liste des principaux droits dutilisation : INSERT,

UPDATE, SELECT, DELETE, EXECUTE.

5.2.1 Avec SSMS

Les droits dutilisation peuvent tre grs deux niveaux dans SQL Server, au niveau

utilisateur et au niveau objet. Dans SSMS, pour les deux niveaux cits, la gestion de ces droits ce fait

grce la fentre de proprit. Voici les deux cas de fentre dans lesquelles vous pouvez changer les

droits dutilisation.

- Au niveau objet :

- Au niveau utilisateur :

--Ajout :

GRANT CREATE TABLE TO Guest

--Suppression :

REVOKE CREATE TABLE FROM Guest

--Interdiction :

DENY CREATE TABLE TO Guest




Pour donner des droits dutilisation ou les retirer avec du code T-SQL, nous allons retrouver

les trois mots cls GRANT, DENY et REVOKE. La syntaxe en revanche sera changeante. La voici :

5.3 Droits au niveau base de donnes Les droits au niveau des bases de donnes vont donner des droits aux utilisateurs qui ne

seront valables que sur une base de donnes prcise. Au niveau base de donnes, il est possible de

donner des droits un utilisateur, un schma, une assembly ou encore un objet service broker.

Ces droits peuvent tre attribus de plusieurs manires, soit par du code Transact SQL, soit par les

proprits de la base de donnes.

5.3.1 Avec SSMS

Pour accorder des droits, vous procderez de la faon suivante. Droulez la totalit des

nuds qui mnent votre base de donnes, affichez le menu contextuel de cette base de donnes

en effectuant un clic droit et slectionnez proprit. Vous aboutissez sur la fentre suivante (Pensez

vous rendre dans la partie Autorisations) :

--Ajout :

GRANT CREATE TABLE TO Guest

--Suppression :

REVOKE CREATE TABLE FROM Guest

--Interdiction :

DENY CREATE TABLE TO Guest



Il vous suffit dajouter des utilisateurs dans la premire partie nomme Utilisateurs ou rles

et de leur donner des droits dans la seconde partie nomme Autorisations. Cliquez enfin sur OK pour

valider vos choix.


Avec du code T-SQL, nous utiliserons la syntaxe suivante :

- Permissiondebasededonnes : La ou les permissions de base de donnes accorder

lutilisateur de base de donnes.

- Utilisateur : Lutilisateur qui va recevoir les permissions de base de donnes prcises aprs

linstruction GRANT.

- Groupeourole : Constitue le contexte de scurit qui va nous permettre daccorder les

privilges.

Note : Il est vident que nous pouvons utiliser linstruction GRANT, aussi bien que les instructions DENY et REVOKE, pour

interdire et supprimer des droits aux utilisateurs.

Nous avons not plus haut que nous prcisons le nom des permissions de bases de donnes

aprs linstruction principale GRANT. Nous allons lister la quasi-totalit de ces permissions ci-aprs :

- ALTER

- ALTER ANY APPLICATION ROLE

- ALTER ANY ASSEMBLY

- ALTER ANY ASYMMETRIC KEY

- ALTER ANY CERTIFICATE

- ALTER ANY CONTRACT

- ALTER ANY DATABASE DDL TRIGGER

GRANT permissiondebasededonnes

TO utilisateur

AS groupeourole



- ALTER ANY DATABASE EVENT NOTIFICATION

- ALTER ANY DATASPACE

- ALTER ANY FULLTEXT CATALOG

- ALTER ANY MESSAGE TYPE

- ALTER ANY REMOTE SERVICE BINDING

- ALTER ANY ROLE

- ALTER ANY ROUTE

- ALTER ANY SCHEMA

- ALTER ANY SERVICE

- ALTER ANY SYMMETRIC KEY

- ALTER ANY USER

- AUTHENTICATE

- BACKUP DATABASE

- BACKUP LOG

- CHECKPOINT

- CONNECT

- CONNECT REPLICATION

- CONTROL

- CREATE AGGREGATE

- CREATE ASSEMBLY

- CREATE ASYMMETRIC KEY

- CREATE CERTIFICATE

- CREATE CONTRACT

- CREATE DATABASE

- CREATE DATABASE DDL EVENT NOTIFICATION

- CREATE DEFAULT

- CREATE FULLTEXT CATALOG

- CREATE FUNCTION

- CREATE MESSAGE TYPE

- CREATE PROCEDURE

- CREATE QUEUE

- CREATE REMOTE SERVICE BINDING

- CREATE ROLE

- CREATE ROUTE

- CREATE RULE

- CREATE SCHEMA

- CREATE SERVICE

- CREATE SYMMETRIC KEY

- CREATE SYNONYM

- CREATE TABLE

- CREATE TYPE

- CREATE VIEW

- CREATE XML SCHEMA COLLECTION

- DELETE

- EXECUTE

- INSERT

- REFERENCES



- SELECT

- SHOWPLAN

- SUBSCRIBE QUERY NOTIFICATIONS

- TAKE OWNERSHIP

- UPDATE

- VIEW DATABASE STATE

- VIEW DEFINITION

5.4 Droit au niveau serveur Les privilges au niveau serveur sattribuent de la mme manire que ceux de niveau base de

donnes, simplement, ils ne sont pas les mmes. En revanche, ces privilges ne sont pas attribus

un utilisateur, mais une connexion. Nous ne prsenterons donc pas les faons dattribuer ces droits

puisque elles sont les mmes que pour le niveau base de donnes, mais nous allons les citer :

- ADMINISTER BULK OPERATIONS

- ALTER ANY CONNECTION

- ALTER ANY CREDENTIAL

- ALTER ANY DATABASE

- ALTER ANY ENDPOINT

- ALTER ANY EVENT NOTIFICATION

- ALTER ANY LINKED SERVER

- ALTER ANY LOGIN

- ALTER RESOURCES

- ALTER SERVER STATE

- ALTER SETTINGS

- ALTER TRACE

- AUTHENTICATE SERVER

- CONNECT SQL

- CONTROL SERVER

- CREATE ANY DATABASE

- CREATE DDL EVENT NOTIFICATION

- CREATE ENDPOINT

- CREATE TRACE EVENT NOTIFICATION

- EXTERNAL ACCESS ASSEMBLY

- SHUTDOWN

- UNSAFE ASSEMBLY

- VIEW ANY DATABASE

- VIEW ANY DEFINITION

- VIEW SERVER STATE

6 Les rles On peut dire que les rles sont des sortes de groupements de droits. Il existe trois niveaux

dactions pour les rles : Server, Base de donnes et Application. Lutilit des rles rside dans le fait



quil est plus simple dattribuer des droits des rles puis dattribuer des rles des utilisateurs ou

des connexions plutt que dattribuer directement des droits ces derniers. Il faut donc retenir que

les rles sont des ensembles de droits qui portent un nom et quon peut les attribuer aux

utilisateurs.

Pour faciliter la gestion des droits, SQL Server propose des droits dits fixes. En effet, ils sont

prdfinis et donc non modifiables. Ils sont dfinis deux niveaux : Server et Base de donnes. En

revanche, les rles dfinis par lutilisateur de SQL Server atteignent deux niveaux diffrents : Base de

donnes et Application. Au final, un utilisateur de base de donnes peut avoir accs des droits de

quatre manires diffrentes : grce aux droits de la connexion quil utilise, grce aux rles fixes, grce

aux rles crs par lutilisateur et enfin grce aux droits qui lui ont t directement affects.

Note : Il existe un rle qui est public et qui est attribu tous les utilisateurs de base de donnes et toutes les

connexions. Ce rle ne peut pas tre enlev aucun des utilisateurs. En revanche, il peut tre modifi. Il faut simplement

prendre en compte que TOUS les utilisateurs auront les droits ajouts public.

6.1 Les rles prdfinis Rles serveur :

- Sysadmin : Administrateur du serveur.

- Serveradmin : Permet de configurer les paramtres niveau serveur.

- Setupadmin : Permet dexcuter certaines procdures stockes et dajouter des serveurs

lis.

- Securityadmin : Permet de grer les connexions serveur.

- Processadmin : Permet de grer les traitements au sein de SQL Server.

- Dbcreator : Permet de crer ou modifier des bases de donnes.

- Diskadmin : Permet de grer les fichiers sur le disque.

- Bulkadmin : Permet dexcuter linstruction BULK INSERT.

Rles base de donnes :

- Db_owner : Equivalent propritaire base de donnes.

- Db_accessadmin : Permet dajouter et supprimer des utilisateurs de base de donnes.

- Db_datareader : Permet dutiliser linstruction SELECT.

- Db_datawriter : Permet les instructions INSERT, UPDATE et DELETE.

- Db_ddladmin : Permet les oprations sur les objets de base de donnes.

- Db_securityadmin : Permet de grer les lments de scurit sur la base de donnes.

- Db_backupoperator : Permet lutilisation des backups.

- Db_denydatareader : Interdit linstruction SELECT.

- Db_denydatawriter : Interdit lcriture sur la base de donnes.

6.2 Les rles dfinis par lutilisateur Il est possible de dfinir ses propres rles afin de faciliter ladministration des droits dans SQL

Server. Logiquement, on crera un rle dit personnalis lorsque plusieurs utilisateurs doivent avoir

les mmes droits et que ces droits nexistent pas dans les rles prdfinis. Les rles peuvent tre

accords soit directement un utilisateur, soit un autre rle.



6.2.1 Avec SQL Server Management Studio

Pour crer un nouveau rle, procdez de la manire suivante. Dployez successivement le nud

de votre base de donnes puis le nud scurit. Affichez alors le menu contextuel du nud rles et

slectionnez Nouveau rle. Vous obtenez cette nouvelle fentre :

Il vous suffit alors de prciser le nom et le propritaire du rle. En revanche, vous ntes pas

oblig de prciser les autres informations de suite. Vous pouvez les modifier ultrieurement en vous

rendant dans les proprits du rle voulu.


Voici la syntaxe de cration dun rle personnalis :

Nous allons bien entendu crer un rle grce linstruction CREATE ROLE. Il est alors

ncessaire de prciser le nom du schma aprs cette instruction pour assurer son unicit au sein de

la base de donnes sur le serveur. On peut enfin prciser un propritaire grce la clause

AUTHORIZATION.

CREATE ROLE nomdurole

AUTHORIZATION propritaire



7 Conclusion Dans ce chapitre, vous avez appris mettre en place la scurit de vos bases de donnes au

travers des droits. Dautres formes de scurits sont possible, entre autres, lencryptions

transparente des donnes. Ce chapitre est dsormais termin. Si toutefois vous avez besoin de

prcisions sur le cours, ou bien sur les objets de bases nhsitez pas consulter les cours de

dveloppement de base de donnes, ou bien poster sur le forum de ce mme site

(http://forums.dotnet-france.com/forums/).

Documents

Gestion des utilisateurs, des groupes et des rôles dans SQL Server 2008