Chapitre 3 : Connexions et Sécurité (PDF)

Connexions et sécurité 1. Introduction SQL Server propose divers niveaux de protection des données qui lui sont confiées. Il s’agit tout d’abord de protéger l’accès au serveur. Ensuite, en fonction de son profil, l’utilisateur aura ou non accès à certaines bases de données. Enfin, à l’intérieur d’u...

Connexions et sécurité 1. Introduction SQL Server propose divers niveaux de protection des données qui lui sont confiées. Il s’agit tout d’abord de protéger l’accès au serveur. Ensuite, en fonction de son profil, l’utilisateur aura ou non accès à certaines bases de données. Enfin, à l’intérieur d’une base de données, il n’aura pas forcément le loisir d’accéder et de modifier les informations comme il le souhaite. 2. Noms de connexion et noms d’utilisateurs Il est préférable de donner à un utilisateur, qui souhaite avoir accès à des données stockées dans une base gérée par SQL Server, un compte sur le serveur ou sur le domaine, selon la configuration du serveur Windows. SQL Server ne travaillera qu’à partir de la validation d’ouverture de session effectuée par Windows Server. On peut aussi inclure un groupe Windows comme compte de connexion dans SQL Server : tous les membres du groupe auront accès à SQL Server, avec les droits de leur groupe d’appartenance, sans avoir de nom de connexion personnel. 2.1. Noms de connexion Lors de l’installation de SQL Server des noms de connexions prédéfinis sont créés : ▪ sa : l’administrateur système qui a tous les droits sur le système et ses bases de données ▪ BUILTIN/Administrateurs : le compte correspondant au groupe Administrateurs Windows Les noms de connexion sont stockés dans la table syslogins de la base master. Pour créer un nouveau nom de connexion avec SQL Entreprise Manager, il faudrait ouvrir le dossier Sécurité, puis sélectionner Connexions et choisir Nouvelle Connexion. Avec Transact-SQL, la création d’un compte de connexion n’entraîne pas la création du ou des noms d’utilisateur associés. Il existe deux procédure pour ajouter un compte de connexion selon qu’il s’agit d’un compte SQL Server pour une authentification SQL Server (sp_addlogin) ou d’un compte Windows Server pour une authentification Windows (sp_grantlogin). Exemple : sp_addlogin ‘user1’, ‘user1pass’, ‘bd1’, NULL crée un utilisateur appelé user1, ayant comme mot de passe user1pass et comme base par défaut bd1. Il utilisera la langue par défaut du serveur. sp_grantlogin ‘SIEGE/user1’ accorde un accès à l’utilisateur Windows appelé user1 du domaine SIEGE A noter que, dans la procédure sp_grantlogin, il n’est pas possible de préciser une base ou une langue par défaut. La connexion héritera donc de master comme base de Prof. C.EL AMRANI Administration SQL Server 2 Connexions et sécurité données par défaut et de la langue d’installation du serveur comme langue par défaut. On peut cependant modifier la base par défaut avec la procédure sp_defaultdb et la langue par défaut avec sp_defaultlanguage. Exemple : sp_defaultdb ‘SIEGE/user1’,‘Gestion’ accorde à l’utilisateur appelé SIEGE/user1 la base Gestion par défaut. sp_defaultlanguage ‘SIEGE/user1’,‘English’ accorde à l’utilisateur appelé SIEGE/user1 la langue anglaise par défaut. 2.2. Noms d’utilisateurs 2.2.1. Création des noms Les noms d’utilisateurs sont stockés dans la table sysusers présente dans toutes les bases de données. Ils sont généralement identiques aux comptes de connexions, sauf pour dbo qui est mis en correspondance avec le compte propriétaire de la base. Pour créer les noms d’utilisateurs avec SQL Entreprise Manager , il faudrait choisir Nouvel utilisateur de base de données dans le dossier Utilisateurs qui se trouve dans le dossier d’une base de données particulière. Il est impératif d’avoir au préalable créé un nom de connexion. Avec le Transact-SQL, une fois sp_addlogin et sp_grantlogin exécutées, il ne reste qu’à créer les noms d’utilisateurs avec sp_grantdbaccess. Exemple : sp_grantdbaccess ‘user1’ (ou : sp_adduser ‘user1’) cette instruction créé l’utilisateur user1 lié au nom d’accès user1 2.2.2. Cas du compte Guest Le compte guest permet d’accéder à une base de données sans avoir de nom d’utilisateur. Il suffit de créer un compte appelé guest, sans correspondance avec un nom de connexion, pour que tout utilisateur accédant à SQL Server puisse utiliser la base de donnée. On peut créer ce compte avec Transact-SQL : sp_grantdbaccess guest 2.3. Suppression des noms Comme tout objet SQL, on peut supprimer un nom de connexion et un nom d’utilisateur. Avec SQL Entreprise Manager, la suppression des noms de connexion et des noms d’utilisateurs associés peut se faire en une seule opération. En effet, il suffit de sélectionner l’option Supprimer dans le dossier Sécurité/Connexion. Avec Transact-SQL, la procédure sp_droplogin supprime un nom de connexion, par exemple : sp_droplogin user1 supprime le compte de connexion user1 Prof. C.EL AMRANI Administration SQL Server 3 Connexions et sécurité Le nom de connexion ne doit pas être en cours d’utilisation, ni propriétaire, ni utilisateur d’une base de données. La procédure sp_revokedbaccess supprime un nom d’utilisateur, par exemple : sp_revokedbaccess user1 supprime le nom d’utilisateur user1 3. Les rôles Il existe deux types principaux de rôles : les rôles de serveur et les rôles de bases de données. 3.1. Rôles de serveur Ces rôles sont qualifiés de fixes car il n’est possible ni de les supprimer, ni de les modifier, ni d’en ajouter. Leur but est d’accorder à certains utilisateurs des droits d’administration limités du serveur. Il existe 8 rôles de serveur : Rôle de serveur Description sysadmin Administrateurs système. Ses membres ont tous les droits sur le système et les bases de données. securityadmin Administrateurs de la sécurité. Ses membres gèrent la sécurité d’accès à SQL Server. serverdadmin Administrateurs du serveur. Ses membres gèrent les paramètres de SQL Server. setupadmin Administrateurs de l’installation et de la configuration. Ses membres gèrent les serveurs liés et les procédures stockées d’auto-démarrage. processadmin Administrateurs des processus. Ses membres ont le droit de supprimer un processus. diskadmin Administrateurs des disques. Ses membres ont le droit de gérer les unités de sauvegarde et les fichiers de bases de données. dbcreator Créateur de bases de données. Ses membres ont le droit de créer et de modifier les bases de données. bulkadmin Administrateurs d’insertions en bloc. Ses membres ont le droit de procéder à des insertions en bloc par bcp ou BULK INSERT. Il est possible d’affecter un compte de connexion à un ou plusieurs rôles de serveur lors de sa création ou modification, dans la boîte de dialogue Propriétés de la connexion. Il est possible aussi de le faire depuis la fenêtre Propriétés du rôle du serveur. Avec Transact-SQL, pour affecter un rôle de serveur à un compte de connexion, on écrit par exemple : sp_addsrvrolemember ‘user1’, ‘diskadmin’ Afin de supprimer un rôle de serveur à un compte de connexion, on écrit par exemple : sp_dropsrvrolemember ‘user1’, ‘diskadmin’ Prof. C.EL AMRANI Administration SQL Server 4 Connexions et sécurité Si l’on n’indique pas de nom de rôle, la connexion est supprimée de tous les rôles auxquels elle appartient. 3.2. Rôles de base de données Les utilisateurs d’une base de données peuvent être regroupés pour faciliter l’administration de la sécurité. Il existe 10 rôles de base de données prédéfinis : Rôle de base de données Description db_owner Ses membres ont tous les droits sur les objets de la base. db_accessadmin Ses membres peuvent gérer la sécurité d’accès à la base en ajoutant des utilisateurs et/ou des rôles. db_backupoperator Ses membres ont le droit de faire les sauvegardes de la base de données. db_datareader Ses membres ont le droit de lire le contenu de toutes les tables de la base. db_datawriter Ses membres ont le droit de modifier, supprimer, insérer des données dans toutes les tables de la base (UPDATE, DELETE et INSERT). db_ddladmin Ses membres peuvent ajouter, supprimer, modifier les objets de la base (instructions DDL : CREATE, ALTER, DROP). db_securityadmin Ses membres gèrent les rôles et les autorisations sur les objets et les instructions de la base de données. db_denydatareader Ses membres ne peuvent lire les données d’aucune table de la base, même s’ils présentent des droits explicites de lecture. db_denydatawriter Ses membres ne peuvent modifier, supprimer, insérer les données d’aucune table de la base, même s’ils présentent des droits explicites. public Rôle auquel appartiennent tous les utilisateurs de la base. Personne ne peut être enlevé de ce rôle. En dehors de public, un utilisateur peut appartenir à un ou plusieurs rôles. 3.2.1. Créer un rôle Il est possible d’ajouter de nouveaux rôles de base de données avec SQL Entreprise Manager. Dans le dossier Rôles d’une base de données, il suffit de choisir Nouveau rôle de base de données. On peut immédiatement ajouter des utilisateurs. Avec Transact-SQL, il faut d’abord créer le rôle avec sp_addrole, puis remplir le rôle avec sp_addrolemember. Exemple : Prof. C.EL AMRANI Administration SQL Server 5 Connexions et sécurité sp_addrole gestion sp_addrolemember gestion, user1 sp_addrolemember db_datareader, user1 La suppression d’un utilisateur d’un rôle se fait avec la procédure : sp_droprolemember Exemple : sp_droprolemember gestion, user1 3.2.2. Suppression d’un rôle Pour supprimer un rôle avec SQL Entreprise Manager ou avec Transact-SQL, il faut d’abord en enlever les membres. Avec SQL Entreprise Manager, sélectionner le rôle à supprimer, puis choisir Supprimer. Avec Transact-SQL, il faudrait utiliser la procédure sp_droprole. Exemple : sp_droprole gestion 4. L’accès au système Il existe deux modes d’authentification d’une connexion : l’authentification SQL et l’authentification Windows. 4.1. Authentification SQL Dans ce mode appelé mixte, l’utilisateur se fait tout d’abord identifier par Windows, puis par SQL Server. La connexion peut être sans ou avec confiance. Le schéma d’une connexion sans confiance est simple : 1. L’utilisateur ouvre une session Windows, 2. Il ouvre ensuite une session sous SQL Server en donnant un nom de connexion et un mot de passe valides , 3. SQL Server vérifie la paire nom de connexion-mot de passe et autorise ou non l’accès. L’authentification SQL n’est possible qu’en mode mixte, pour lequel l’accès peut être établi avec ou sans confiance. Le déroulement du mode mixte dépend des renseignements transmis dans la boîte de connexion : ▪ Si L’utilisateur tape des informations dans la boîte de connexion SQL Server, ce dernier vérifie la paire nom de connexion-mot de passe dans sa table sysxlogins. SQL Server est alors en mode sans confiance. ▪ Si l’utilisateur laisse la boîte de connexion vierge de toute information, SQL Server passe implicitement en mode avec confiance et demande le nom de l’utilisateur à Windows Server. Prof. C.EL AMRANI Administration SQL Server 6 Connexions et sécurité 4.2. Authentification Windows En mode dit intégré, SQL Server récupère les informations de la base de comptes de Windows Server pour autoriser ou interdire l’accès d’un compte Windows. Le schéma d’une connexion avec confiance est la suivante : 1. L’utilisateur ouvre une session sous Windows Server, 2. Il ouvre une session sous SQL Server sans indiquer de nom ou de mot de passe dans la boîte de connexion, 3. SQL Server demande à Windows de lui fournir le nom de l’utilisateur, 4. SQL Server ouvre une session avec le nom de connexion correspondant au nom d’utilisateur Windows. 4.3. Changement de mode Le changement de mode se fait depuis SQL Server Manager. Il faudrait choisir Propriétés du serveur, et cliquer ensuite l’onglet Sécurité. Dans la zone Sécurité, on retrouve les deux modes déjà décrits : ▪ SQL Server et Windows : mode mixte, ▪ Uniquement Windows : mode intégré, ▪ Niveau d’audit : permet de suivre dans le journal certains événements de Windows Server, c'est-à-dire les accès réussis ou échoués. Ceci est intéressant pour tenter de suivre les agissements d’un « pirate ». 4.4. L’accès depuis un client Un client peut se connecter de diverses façons à SQL Server : ▪ Sous intranet, la connexion d’un client à un serveur SQL Server présuppose l’existence de la DB-Lib, d’ODBC et de son pilote pour SQL Server ou d’OLE-DB et de son fournisseur pour SQL Server. ▪ Avec Internet, Les Active Server Pages (ASP), fournis avec Internet Information Server (IIS), permettent de se connecter à SQL Server en s’appuyant sur OLE-DB. 5. Autorisations d’objet et autorisations d’instruction 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 auquel il appartient (par défaut, public) n’ait lui-même des droits. Il existe deux types d’autorisation : d’instruction et d’objet. 5.1. Autorisations d’instruction Les autorisations d’instruction agissent essentiellement sur les bases de données. Elles peuvent être stipulées sur les commandes suivantes : ▪ CREATE DATABASE ▪ CREATE TABLE ▪ CREATE VIEW ▪ CREATE PROCEDURE ▪ CREATE FUNCTION ▪ CREATE DEFAULT ▪ CREATE RULE ▪ BACKUP DATABASE Prof. C.EL AMRANI Administration SQL Server 7 Connexions et sécurité ▪ BACKUP LOG Seul l’administrateur système peut transmettre le droit de créer des bases de données. Pour donner des autorisations d’instruction aux utilisateurs d’une base de données, il faut choisir Propriétés de la base de données, et cliquer sur l’onglet Autorisations. La colonne Créer une BdD n’apparaît que dans la base master ; c’est-à-dire qu’un utilisateur ne peut avoir le droit de créer une base de données que s’il est membre de master. Il est possible de donner et d’enlever des droits avec Transact-SQL et les opérations GRANT, DENY et REVOKE. Si l’on utilise le mot clé ALL, toutes les autorisations d’instruction sont ajoutées, enlevées ou révoquées. Si l’on donne un droit à Public, tous les utilisateurs de la base sont concernés par cette attribution. Exemple : GRANT CREATE TABLE, CREATE VIEW TO user1 donne à user1 le droit de créer des tables et des vues DENY CREATE PROCEDURE TO user1 enlève à user1 le droit de créer des procédures REVOKE ALL TO user1 révoque tous les droits de user1 5.2. Autorisations d’objet Les autorisations d’objet s’appliquent à la manipulation des objets. Elles donnent, à des utilisateurs ou à des rôles de la base de données, des droits d’utilisation des tables, vues et procédures. On peut attribuer et visualiser les autorisations d’objet par rôle, par utilisateur ou par objet. Dans SQL Entreprise Manager, pour visualiser les autorisations d’objet par rôle ou utilisateur, sélectionner le serveur, la base de données puis le dossier Rôle ou Utilisateurs. Sélectionner le rôle ou l’utilisateur souhaité, puis choisir Propriétés et cliquer sur le bouton Autorisations. Cliquer sur le bouton OK pour valider les droits définis. Pour affecter des autorisations d’objet par objet, avec SQL Server Manager, sélectionner le serveur, la base de données puis le dossier contenant l’objet souhaité (Tables, Vues, Procédures stockées, Fonctions). Sélectionner l’objet puis cliquer sur le bouton Autorisations. Il suffira de sélectionner les autorisations à accorder ou à supprimer, et de cliquer sur le bouton OK. La syntaxe, pour donner des autorisations d’objet par Transact-SQL, est presque identique à celle des autorisations d’instruction. Elle permet de donner ou de supprimer des droits sur les objets. Exemple : GRANT SELECT, UPDATE ON Articles TO user1 Prof. C.EL AMRANI Administration SQL Server 8 Connexions et sécurité donne à user1 le droit de lire et de modifier les enregistrements de la table Articles. DENY DELETE, INSERT ON Articles TO user1 enlève à user1 le droit d’insérer et de supprimer des enregistrements de la table Articles. 5.3. Attribution des autorisations aux rôles Lorsqu’on donne des autorisations à un rôle, tous ses membres en profitent : c’est l’avantage du rôle. Généralement, il est préférable de commencer par donner (ou enlever) des droits à Public, puis aux rôles, et enfin aux utilisateurs. De cette façon, si un nouvel utilisateur est ajouté à la base, il possède immédiatement des autorisations implicites dues à Public et à ses rôles d’appartenance. Il ne reste alors qu’à lui donner ou enlever des autorisations particulières. 5.4. Transmettre les autorisations Avec SQL Server on peut «transmettre le droit de transmettre le droit». Par exemple : GRANT INSERT ON Articles TO user1 WITH GRANT OPTION donne à user1 le droit d’insertion dans la table Articles et lui permet de transmettre ce droit à d’autres utilisateurs de la base. L’option WITH GRANT OPTION ne peut ni être donnée à un rôle ni être utilisée avec des autorisations d’instruction. 5.5. Autorisations par défaut En dehors des autorisations qu’il est possible de donner à un utilisateur ou à un rôle, chaque rôle prédéfini possède un ensemble d’autorisations qu’il est impossible de modifier et qui peuvent passer par-dessus celles que l’on peut attribuer. Prof. C.EL AMRANI Administration SQL Server