WampServer

Gestion des utilisateurs MySql

La gestion des utilisateurs permet :
- de mettre en place un contrôle d'accès, c'est-à-dire déterminer si un utilisateur a le droit de se connecter à un serveur MySQL,
- si l'utilisateur a le droit de se connecter, déterminer ce qu'il peut y faire.

Le système de contrôle d'accès mis en place par MySQL s'inspire de la norme SQL, mais est spécifique. Il lui manque la gestion des rôles mais ce contrôle distingue les utilisateurs selon l'endroit d'où ils se connectent.
Il existe deux phases du contrôle d'accès :
- la connexion au serveur,
- la vérification des privilèges.
L'ensemble des informations relatives à la gestion des utilisateurs est stocké de manière relationnelle, en l'occurrence dans une base de données dédiée, nommée tout simplement mysql.

La base de données mysql est l'une des deux bases créées automatiquement lors de l'installation de MySQL (l'autre étant information_schema). S'il est fortement déconseillé d'y faire la moindre modification de structure, nous pouvons y ajouter, modifier et supprimer des données, de deux manières différentes :
- en utilisant les ordres DCL (Data Control Language) de MySQL, comme : Create User, Rename User, Drop User, Set Password, Grant, Revoke. C'est la méthode conseillée dans la plupart des cas.
- en modifiant directement le contenu des tables avec les ordres « classiques » (Insert, Update, Delete). Cette méthode, très délicate, qui suppose de très bien connaître le système, est fortement déconseillée.

Pour vous connecter à un serveur MySQL, vous devez disposer d'un nom d'utilisateur, et le cas échéant du mot de passe associé. La liste des utilisateurs autorisés, ainsi que leur éventuel mot de passe, correspond à la table user de la base mysql.

Notion de compte utilisateur

Si la plupart des SGBD caractérisent un compte par un nom d'utilisateur (souvent appelé login), MySQL prend en considération un paramètre supplémentaire : le nom (ou l'adresse IP) de la machine *depuis* laquelle l'utilisateur essaie de se connecter (appelée « hôte »). Un compte utilisateur est donc l'association entre un nom d'utilisateur et l'hôte de cet utilisateur.
Ainsi, vous pouvez par exemple laisser root@localhost (le super administrateur travaillant directement sur le serveur) se connecter, et refuser root@provider.com (le super administrateur travaillant de chez lui, avec sa connexion Internet... ou un imposteur mal intentionné), ou bien le déclasser en utilisateur standard.

Créer un compte nommé

Nota : les commandes ci-dessous sont effectuées à partir de la « console » MySql (Sous Wampserver, Icône, Clic gauche, MySql, Console MySql où il vous sera demandé d'entrer le mot de passe éventuellement défini pour root@localhost. (1)
Nota : Les utilisateurs peuvent aussi être ajoutés, modifiés, supprimés via PhpMyAdmin, par l'option Privilèges.

Commencez par créer un compte pour accéder au serveur en local :
Création d'un compte sans mot de passe : CREATE USER 'moi'@'localhost' ;
Ou de manière moins formelle si les noms ne comportent ni espace, ni caractère spécial, ni joker: CREATE USER moi@localhost ;

Si vous souhaitez accéder à MySQL depuis un autre ordinateur, il vous faut créer un autre compte. Si vous vous baladez d'un poste à l'autre, cela pourrait devenir vite laborieux ! Heureusement, vous pouvez utiliser le joker "%" selon le degré d'ouverture que vous estimez sûr :

Exemples d'hôtes (notez les apostrophes obligatoires pour utiliser le joker %)

Instruction de création de compte ; L'utilisateur moi pourra se connecter depuis...

Create User moi@localhost ; Le serveur MySQL lui-même
Create User moi@monordi ; L'ordinateur appelé monordi
Create User moi@192.168.1.123 ; L'ordinateur dont l'IP est 192.168.1.123
Create User moi@'l92.l68.%' ; N'importe quel ordinateur dont l'IP est de la classe 192.168
Create User moi@'%.microapp.com' ; N'importe quel ordinateur du domaine microapp.com
Create User moi@'%' ; N'importe quel ordinateur

Notez que la table user est la liste des comptes utilisateurs autorisés à se connecter, et qu'il n'existe pas de « liste noire » des comptes interdits. De même, les comptes ne peuvent pas être suspendus, désactivés ou verrouillés, mais seulement détruits (ce qui n'empêche nullement leur recréation).

Pièges de vocabulaire

ATTENTION Le vocabulaire du contrôle d'accès n'est pas toujours limpide. Voici quelques précisions utiles :
-- Le mot-clé user ne doit pas vous induire en erreur : ce qui est créé n'est pas un utilisateur repéré par son simple nom, mais un compte caractérisé par le couple nom/hôte. De la même manière, la documentation MySQL parle le plus souvent d'un utilisateur là où il serait moins ambigu d'utiliser le terme de compte.
-- Le terme «hôte », combiné avec le nom d'utilisateur, désigne l'ordinateur de l'utilisateur, alors que dans un autre contexte il s'agirait plutôt de l'ordinateur qui héberge le serveur MySQL. Supposez que l'utilisateur toto souhaite se connecter avec le client texte, depuis un ordinateur sous Windows nommé client, à un serveur MySQL nommé serveur. Il utilisera la commande suivante :
C: \> mysql --user=toto --host=serveur. Il sera connecté comme étant toto@ciient.
-- Enfin, il ne faut pas confondre le compte avec la session. L'utilisateur ouvre une session en se connectant, et la referme en utilisant la commande Quit. Toutes les informations propres à la session (comme les variables de session ou les tables temporaires) sont alors effacées, tandis que les informations relatives au compte (comme le mot de passe ou les privilèges) persistent.

Les comptes anonymes

Pour le nom d'utilisateur, vous ne pouvez pas utiliser de joker. Il vous est par contre possible de créer des comptes d'utilisateurs anonymes, en indiquant une chaîne vide comme nom d'utilisateur :
Création de deux comptes d'utilisateurs anonymes, l'un en local, l'autre depuis n'importe quel poste
CREATE USER ''@localhost, ''@'%' ,
Un compte anonyme permet de se connecter sans nom d'utilisateur, mais aussi avec un nom inconnu. Ainsi, avec les comptes créés ci-dessus, les deux connexions suivantes deviennent possibles :
Lancement du client texte sous Windows, avec une connexion anonyme en local
C:\> mysql --host=localhost
Idem, avec un nom d'utilisateur inexistant, qui sera considéré comme utilisateur anonyme
C:\> mysql --user=nimportequi --host=localhost
Notez que si aucun nom d'utilisateur n'est fourni, MySQL en attribuera automatiquement un : sous Linux, ce sera le nom de l'utilisateur Linux en cours, et sous Windows, ce sera toujours ODBC.

Limites sur les noms d'utilisateurs

ATTENTION La colonne user de la table user est un Char (16) , c'est-à-dire que le nom d'utilisateur est limité à 16 caractères et que les espaces finaux ne comptent pas : 'moi'@localhost et 'moi... '@localhost représentent le même compte ; par contre, 'moi'@localhost est un utilisateur différent.
En installation standard, cette colonne est sensible à la casse : moi et MOI sont deux utilisateurs différents.

Les mots de passe

Les comptes créés comportent une faille de sécurité béante : ils ne sont protégés par aucun mot de passe. N'importe qui peut donc les utiliser et profiter de leurs privilèges.

Affecter ou supprimer un mot de passe

Pour affecter un mot de passe à un compte déjà existant (ou changer le mot de passe d'un compte), il faut utiliser l'instruction Set Password :
Attribution d'un mot de passe avec SET PASSWORD et la fonction de cryptage PASSWORD()
SET PASSWORD FOR moi@localhost = PASSWORD('secret') ;
Il est préférable d'attribuer le mot de passe dès la création du compte :
Création d'un compte protégé par mot de passe
CREATE USER alibaba@'%' IDENTIFIED BY 'sesame' ;
Si vous souhaitez supprimer un mot de passe afin d'ôter la protection du compte, il suffit d'utiliser Set Password en fournissant une chaîne vide à la place du mot de passe :
Attribution puis suppression d'un mot de passe pour le compte anonyme local
SET PASSWORD FOR ''@localhost = PASSWORD('chut') ; SET PASSWORD FOR ''@localhost = '' ;
N'importe quel utilisateur nommé peut changer ou supprimer son propre mot de passe, auquel cas la clause For est facultative :

Changement puis suppression du mot de passe du compte avec lequel vous êtes connecté (probablement root@localhost)
SET PASSWORD = PASSWORD('rosebud') , SET PASSWORD = '' ;
À l'inverse, un utilisateur anonyme ne peut pas changer le mot de passe de son compte.
Pour changer le mot de passe d'un autre compte que celui avec lequel vous êtes connecté, il faut disposer du privilège Update sur la base mysql. Comme root@localhost dispose de tous les privilèges, vous n'avez pas de problème à attribuer des mots de passe.

Le cryptage des mots de passe

MySQL ne stocke pas les mots de passe en clair, mais sous forme cryptée, selon son propre algorithme de cryptage. La fonction de cryptage est déterministe mais irréversible. Autrement dit, le même mot de passe clair donnera toujours le même mot de passe crypté, mais il est impossible de retrouver le mot de passe clair à partir du mot de passe crypté.

En fait, MySQL ne connaît pas le mot de passe des utilisateurs, mais seulement sa version cryptée !
Les mots de passe cryptés sont, depuis MySQL 4.1, toujours constitués d'un astérisque suivi de 40 chiffres hexadécimaux. Ainsi, pour MySQL le mot de passe de moi@localhost n'est pas secret mais *14E65567ABDB5135DOCFD9A7083032C179A49EE7. Quand l'utilisateur tente de se connecter, MySQL crypte le mot de passe fourni et compare le résultat avec le mot de passe crypté stocké dans la table User. Si l'utilisateur a fourni secret, le cryptage donne à
nouveau *14E65567ABDB5135DOCFD9A7083032C179A49EE7 et la connexion est acceptée. Sinon, elle est refusée.
Si l'utilisateur perd son mot de passe, il n'existe aucun moyen de le retrouver. Il est par contre facile de lui en attribuer un nouveau.

Pour attribuer un mot de passe, vous pouvez toujours soit crypter un mot de passe en clair, soit fournir directement la version cryptée, mais avec des syntaxes différentes selon que vous utilisez Set Password ou Create User. Ainsi, les quatre instructions suivantes donnent le mot de passe secret à l'utilisateur lui@localhost :
Avec la fonction de cryptage PASSWORD()
SET PASSWORD FOR lui@localhost = PASSWORD('secret')
En fournissant la version cryptée
SET PASSWORD FOR lui@localhost = '*14E65567ABDB5135DOCFD9A70B3032C179A49EE7';
La fonction PASSWORD() est implicite à la clause IDENTIFIED BY CREATE USER lui@localhost IDENTIFIED BY 'secret' ;
Le mot-clé PASSWORD dans la clause IDENTIFIED BY permet de fournir directement la version cryptée
CREATE USER lui@localhost IDENTIFIED BY PASSWORD '*14E65567ABDB5135DOCFD9A7OB3032C179A49EE7' ;
Ces syntaxes peuvent être trompeuses, car la fonction Password( ) crypte, tandis que le mot-clé Password indique au contraire qu'il est inutile de crypter.
Si l'administrateur peut attribuer un mot de passe déjà crypté à un utilisateur, l'utilisateur qui tente de se connecter ne peut par contre jamais indiquer qu'il fournit un mot de passe déjà crypté. S'il essaie malgré tout de se connecter avec le mot de passe crypté, celui-ci sera à nouveau crypté, et ne correspondra plus à celui qui est stocké dans la table User. Connaître un mot de passe crypté ne permet donc pas de se connecter à la place de l'utilisateur.

Ambiguïté des comptes

Les différents comptes créés coexistent, éventuellement avec des mots de passe ou des privilèges différents. Du fait des jokers et des connexions anonymes, il est fréquent qu'une tentative de connexion puisse correspondre à plusieurs comptes.

La préférence par spécificité

Voyez par exemple les comptes créés à ce jour :
Lecture de la table User
SELECT User, Host, Password FROM mysql.User ;

Résultat (vous pouvez avoir quelques comptes supplémentaires selon les options choisies lors de la configuration)
User Host Password
Root localhost
alibaba % *EE33294C97C42FFF14EA73AB81E428A6FC7B9A8B
Lui localhost *14E65567ABD85135DOCFD9A7083032C179A49EE7
%
Moi localhost *14E65567ABDB5135DOCFD9A70B3032C179A49EE7
Moi monordi
Moi 192.168.1.123
Moi 192.168.%
Moi %.microapp.com
Moi %
localhost
Si moi tente de se connecter en local, cela correspond non seulement à moi@localhost, mais aussi à moi@'%', ''@localhost, ''@'%'. Or, certains de ces comptes sont protégés par un mot de passe et d'autres non. Comment MySQL choisit-il ?
-- MySQL choisit d'abord l'hôte exprimé de la façon la plus spécifique (donc localhost de préférence à %).
-- Il choisit ensuite le nom d'utilisateur le plus spécifique (donc moi plutôt qu'un compte anonyme).
En l'occurrence, ce sera donc bien moi@localhost qui l'emportera ; moi devra donc utiliser son mot de passe.

Que se passe-t-il si alibaba tente de se connecter en local avec son mot de passe ?
C:\>mysql --user=alibaba --password=sesame --host=localhost
ERROR 1045 (28000): Access denied for user 'alibaba'8'localhost' (using password : YES)
En effet, en appliquant les règles ci-dessus, MySQL préfère un compte anonyme sur un hôte précis à un compte nommé sur un hôte générique. C'est donc ''@localhost, sans mot de passe, qui l'emporte sur alibaba@'%'.

Les « privilèges » accordés aux utilisateurs.

On parle de privilèges et non pas de droits, octroyès (GRANT) par un administrateur, qui peuvent être révoqués (REVOKE) à tout moment. Cet administrateur ne peut exercer ce rôle d'octroi de privilèges que parce qu'il dispose lui-même des privilèges idoines qui lui ont été octroyés par le "superadministrateur" root@localhost.

Les privilèges globaux

Les privilèges globaux sont associés à un compte, sans considération de base de données ni de table.
Privilège ; Description
Create user ; Permet de créer, de supprimer et de renommer (Rename User) des utilisateurs et également de révoquer tous les privilèges d'un utilisateur (Revoke All), mais pas de les rétablir
File ; Permet d'utiliser les commandes Select... Into Outfile et Load Data Infile
etc.

Octroi du privilège CREATE USER au niveau global
GRANT CREATE USER ON *.* TO alibaba@'%' ;

Les privilèges liés à des objets

Les autres privilèges sont liés à des objets : bases de données, tables, colonnes, routines, vues.

Privilège ; Description Objets privilégiés
Select ; Permet de lire les données des Base, table, vue, colonnes d'une table avec une requête colonne
Select Update ; Permet de modifier les données des colonnes d'une table (requête Update)
Insert ; Permet d'ajouter des lignes à une table (requête Insert)
Delete ; Permet de supprimer des lignes d'une Base, table, vue table (Delete, Truncate)
etc.

La hiérarchie des autorisations

Un privilège change de portée selon le niveau auquel il est accordé. MySQL définit quatre niveaux de privilège :
- niveau global (privilèges renseignés dans la table user de la base mysql) ;
- niveau base de données (table db);
- niveau table (table tables_priv) ou routine (procs_priv)
- niveau colonne (table columns_priv)

Prenons l'exemple du privilège Select :
Syntaxe et portée de GRANT selon le niveau de privilège
Niveau ; Exemple ; Portée
- Global ; Grant Select On *.* To alibaba@'%' ; Toutes les colonnes de toutes les tables de toutes les bases
- Base ; Grant Select On Bibli.* To alibaba@'%' ; Toutes les colonnes de toutes les tables de la base Bibli
- Table ; Grant Select On Bibli.Lecteurs To alibaba@'%' ; Toutes les colonnes de la table Lecteurs de la base Bibli
- Colonne ; Grant Select (Nom, Prenom) On Bibli.Lecteurs To alibaba@'%' ; Les colonnes Nom et Prenom de la table Lecteurs de la base Bibli

Les différents niveaux de privilège sont amenés à entrer en conflit. Que se passe-t-il si, par exemple, alibaba@'%' dispose du privilège Select sur la table Lecteurs, mais pas sur les colonnes Nom et Prenom de cette table ? MySQL donne toujours la préférence à l'autorisation. Autrement dit, il suffit de posséder un privilège à n'importe quel niveau pour pouvoir l'exercer sur ce niveau et tous les niveaux inférieurs.

Nous venons de voir la création des comptes utilisateurs et l'octroi des privilèges via la console MySql. Les ajouts, suppressions, modifications des comptes utilisateurs ainsi que l'octroi, la révocation ou la modification des privilèges peuvent être effectués par PhpMyadmin via l'option Privilèges.

Dans la plupart des cas et jusqu'à maintenant, vous avez implicitement utilisé le compte de « super administrateur root » (root@localhost), avec ou sans mot de passe selon les options choisies lors de la configuration. Ce « super administrateur » possède tous les privilèges possibles sur les bases de données, même la suppression totale des tables et bases quelles qu'elles soient, c'est pourquoi il faut éviter, même en utilisation locale et sauf cas particuliers, de se connecter à MySql en tant que root@localhost. Il faut créer un ou plusieurs comptes utilisateurs, avec mot de passe, et ne possédant que les privilèges strictement nécessaires, par exemple seulement : SELECT, INSERT, UPDATE, DELETE

On ne peut pas, en une seule fois, créer un utilisateur et lui donner des privilèges sur une base de données.
Il faut d'abord créer l'utilisateur (Page d'accueil PhpMyadmin puis lien Privilèges, Puis Ajouter un utilisateur) en ne lui donnant aucun privilège global. Ensuite, on pourra choisir sur quelle base et quels privilèges ou uniquement une ou plusieurs tables ou même sur un ou plusieurs champs d'une ou plusieurs tables.

--------------------

(1) Remarques sur les jeux de caractères entre le mode console MySql (Client texte) et le mode PhpMyAdmin (Client graphique) :

Par défaut, le client texte est configuré comme utilisant latin1. Or, sous Windows, le client texte est une application en ligne de commande, qui utilise un jeu de caractères cp850 des versions occidentales de Windows.
Le fait que le client texte se déclare, à tort, comme Latini auprès de MySQL entraîne une erreur d'interprétation des caractères accentués ou spéciaux (ceux qui vous viennent de la base mais aussi ceux que vous frappez). En corrigeant les variables système afin qu'elles interprètent correctement le texte envoyé par MySQL, vous devriez corriger le problème :

SET NAMES cp850 ;

Doit-on faire un Set Names à chaque nouvelle session ? En théorie, il suffit de modifier l'option default-character-set de la section CLIENT du fichier my.ini. Toutefois, le cp850 n'étant pas un jeu compilé, tenter de l'utiliser comme option de départ empêche le client de démarrer.
Il ne vous reste donc plus qu'à trouver une distribution de MySQL avec cp850 compilé. Le caractère compilé ou non d'un jeu de caractère se voit avec la commande Show Collation.

Les introducteurs

Une autre possibilité, surtout utilisée à titre de test ou pour résoudre certains cas difficiles, est d'indiquer à MySQL quel est le jeu de caractères d'une chaîne littérale, en la faisant précéder d'un introducteur :

SELECT latinl'été', utf8'été', cp850'été' ;

Une chaîne précédée d'un introducteur est envoyée telle quelle à MySQL, quelles que soient les variables @@character_set_client et @@character_set_connexion. MySQL l'interprète ensuite selon le jeu indiqué par l'introducteur, puis le renvoie. Vous ne verrez "été" écrit correctement que pour le jeu de caractère sous lequel vous êtes ; les autres cas vous donneront des affichages du genre Ä©tÄ© ou ?t? ou +®t+® ou ,t,

Très intérressant et très bien expliquer, je te remercie infiniment pour ton dévouement.

ml

Franchement c'est la première fois que je vois un tel topo, aussi clair et tellement utile.
Encore MERCI et espérons que nombreux seront les lecteurs.
Merci encore pour ton support de (très) longue date Otomatic.