Wikilivres

Microsoft SQL Server/Gestion des utilisateurs

< Microsoft SQL Server
Bases de données Bases de données
Variables Variables
  1. Introduction
  2. Bases de données
  3. Gestion des utilisateurs
  4. Variables
  5. Tables
  6. Procédures stockées
  7. Fonctions
  8. Bases de données spatiales
  9. Performances
  10. Importer et exporter
  11. SugarCRM
Annexes
  1. Mots réservés
  2. Problèmes connus

[ Modifier le sommaire ]

Bases de données Bases de données
Variables Variables

Introduction

modifier

On distingue les comptes de connexions au serveur, de ceux des utilisateurs stockés dans chaque base et qui en contiennent les permissions.

On peut assigner aux premiers des "Rôles du serveur" et aux seconds des "Rôles" propres à leur base de données.

Plusieurs comptes utilisateurs peuvent utiliser la même connexion au serveur.

SSMS

modifier

Connexions

modifier

Pour chaque connexion, il y a deux types d’authentification possibles : par le compte Windows ou par un login propre à SQL Server (en en spécifiant le mot de passe).

Le premier menu au démarrage de SSMS utilise d'ailleurs une connexion existante, et peut en créer d'autres avec des droits plus limités sur certaines bases.

Toutefois en production il est plutôt prévu que l'application ne passe pas par SSMS ou la même connexion que son administrateur, mais se connecte avec d'autres droits, directement au serveur SQL par le port 1433 en TCP, par exemple par l'intermédiaire des pilotes de langages tiers comme PHP ou Java.

Pour créer une autre connexion :

  • Menu "Sécurité" du serveur (en bas à gauche pas défaut, ne pas prendre le menu "Sécurité" d'une base).
  • Connexions.
  • Clic droit, "Nouvelle connexion...".
  • Dans "Général", indiquer le nom du compte et la base par défaut.
  • Dans "Rôles du serveur", cocher les droits sur le serveur entier.
  • Dans "Mappage de l'utilisateur", cocher les bases de données à rendre accessibles, avec leur utilisateur et schéma associés.

Connections en cours

modifier

Un clic droit sur une base permet d'afficher l'option Moniteur d'activité. Ce menu affiche en temps réel les connexions et performances de la base. Il est donc possible d'y forcer l'arrêt des connexions, par exemple avant de supprimer une base (car son utilisation empêche toute suppression).

Schémas

modifier

Les schémas (de sécurité) sont visibles dans le menu "Sécurité" de chaque base de données. Ils permettent de gérer les permissions des utilisateurs pour des groupes de tables, vues ou codes stockés.

Par défaut, SQL Server en propose une dizaine tels que dbo et sys que l'on retrouve souvent en préfixe des tables dans le code SQL, ou encore db_accessadmin.

Pour modifier leurs autorisations, faire un clic droit puis "Propriétés".

Utilisateurs

modifier

Les utilisateurs sont visibles dans le menu "Sécurité" de chaque base de données.

Lors de leur création, on peut leur affecter une connexion et un schéma par défaut, ainsi que des clés d'authentification.

Au niveau de chaque table, on peut ensuite définir quels utilisateurs ou rôles auront le droit d'insérer, de mettre à jour, de supprimer ou de sélectionner les enregistrements.

Rôles

modifier
 
Rôles d'une base et du serveur.

Les rôles sont visibles dans le menu "Sécurité" de chaque base de données.

Par défaut, SQL Server en propose une dizaine tels que public.

Lors de la création d'un nouveau rôle, il faut choisir s'il appartient à la base ou à l'application, puis à quel schéma il est rattaché.

SQL

modifier

LOGIN

modifier

Les connexions et utilisateurs peuvent aussi être gérés en SQL : 

 CREATE LOGIN Connexion1 WITH PASSWORD = 'test' 
 USE MaBase;

Inventaire des connexions : 

SELECT loginname FROM master.dbo.syslogins

La liste des connexions en cours est listable par la commande sp_who[1].

SCHEMA

modifier

Une base de données peut contenir plusieurs schémas, qui peuvent eux-mêmes contenir plusieurs tables ou vues, afin de leur appliquer les mêmes permissions.

  • create schema[2]
  • alter schema
  • drop schema
  • alter authorization[3]

Exemple : 

CREATE SCHEMA schema1 AUTHORIZATION Utilisateur1  
    CREATE TABLE Table1 (ID int)
    DENY SELECT ON SCHEMA::schema1 TO Utilisateur2
    GRANT SELECT ON SCHEMA::schema1 TO Utilisateur3;
GO

USER

modifier 
 CREATE USER Utilisateur1 FOR LOGIN Connexion1;
 exec sp_addrolemember 'public', 'Utilisateur1' --ajoute l'utilisateur comme membre du rôle "public"

Inventaire des utilisateurs de la base courante : 

 SELECT name FROM sys.database_principals where (type='S' or type = 'U')

Utilisateur courant : 

 SELECT CURRENT_USER;

ROLE

modifier

Microsoft recommande toutefois d'utiliser ALTER ROLE au lieu de sp_addrolemember[4] :

  • CREATE ROLE[5], ex : CREATE ROLE Role1 AUTHORIZATION db_securityadmin.
  • ALTER ROLE[6], ex : ALTER ROLE Role1 ADD MEMBER Utilisateur1.
  • DROP ROLE.

Pour réactiver une connexion : 

 ALTER LOGIN Connexion1 ENABLE

Pour la débloquer : 

 ALTER LOGIN Connexion1 WITH PASSWORD = 'test' UNLOCK

Références

modifier
  1. ↑ https://msdn.microsoft.com/fr-fr/library/ms174313%28v=SQL.120%29.aspx
  2. ↑ https://msdn.microsoft.com/fr-fr/library/ms189462.aspx
  3. ↑ https://msdn.microsoft.com/en-us/library/ms187359.aspx
  4. ↑ https://msdn.microsoft.com/en-us/library/ms187750.aspx
  5. ↑ https://msdn.microsoft.com/en-us/library/ms187936.aspx
  6. ↑ https://msdn.microsoft.com/en-us/library/ms189775.aspx
Récupérée de « https://fr.wikibooks.org/w/index.php?title=Microsoft_SQL_Server/Gestion_des_utilisateurs&oldid=720599 »