SQL Server La gestion des utilisateurs 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 Version 1.0 Date Auteur 18/03/2015 Guy Verghote 1/5 SQL Server La gestion des utilisateurs 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 Ajouter un objet Appliquer l'int. ref. Tous les droits Définition grant alter on schema::nomSchema to nomUtilisateur grant references on schema::nomSchema to nomUtilisateur 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 Version 1.0 Date Auteur 18/03/2015 Guy Verghote 2/5 SQL Server La gestion des utilisateurs 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 Supprimer un rôle dans la base de données en cours 'nomRole' 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 Version 1.0 Date Auteur 18/03/2015 Guy Verghote 3/5 SQL Server La gestion des utilisateurs Il existe des rôles standards qui disposent déjà de permissions Db_owner Db_accessadmin Exécute les activités de tous les rôles 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 Version 1.0 Date Auteur 18/03/2015 Guy Verghote 4/5 SQL Server La gestion des utilisateurs 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) Version 1.0 Date Auteur 18/03/2015 Guy Verghote 5/5