Synonyme pour WITH GRANT OPTION
USAGE peut être utilisé lorsque vous voulez créer un utilisateur sans aucun droit.
Les droits de CREATE TEMPORARY TABLES, EXECUTE, LOCK TABLES, REPLICATION ... ,
SHOW DATABASES et SUPER sont nouveaux en version 4.0.2. Pour utiliser ces droits après
mise à jour en 4.0.2, vous devez exécuter le script mysql_fix_privilege_tables.
Dans les anciennes versions de MySQL, le droit de PROCESS donnait les mêmes droits que le
nouveau droit SUPER.
Vous pouvez donner des droits globaux en utilisant la syntaxe ON *.*. Vous pouvez donner
des droits de base en utilisant la syntaxe ON nom_base.*. Si vous spécifiez ON * et que
vous avez une base de données qui est déjà sélectionnée, vous allez donner des droits pour la
base de données courante. Attention : si vous spécifiez ON * et que vous n'avez pas de
base courante, vous allez affecter les droits au niveau du serveur !
Les droits EXECUTION, FILE, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION
SLAVE, SHOW DATABASES, SHUTDOWN et SUPER sont des droits d'administration, qui
ne peuvent être donnés que globalement (avec la syntaxe ON *.*).
Les autres droits peuvent être donnés globalement ou à des niveaux plus spécifiques. Les
seuls droits priv_type que vous pouvez donner au niveau d'une table sont SELECT, INSERT,
UPDATE, DELETE, CREATE, DROP, GRANT OPTION, INDEX et ALTER.
Les seuls droits priv_type que vous pouvez donner au niveau d'une colonne (avec la clause
column_list) sont SELECT, INSERT et UPDATE. GRANT ALL assigne des droits que vous
possédez au niveau où vous le possédez. Par exemple, si vous utilisez GRANT ALL ON
db_name.*, qui est un droit de niveau de base de données, aucun des droits globaux,
comme FILE ne sera donné.
MySQL vous permet de donner des droits au niveau d'une base de données, même si la base
de données n'existe pas, pour vous aider à préparer l'utilisation de la base de données.
54
Actuellement, MySQL ne vous permet pas de créer des droits pour une table si la table
n'existe pas.
MySQL ne supprime pas les droits lorsqu'un utilisateur efface une table ou une base.
NB : les caractères joker ‘_’ et ‘%’ sont autorisés lors de la spécification de noms dans la commande GRANT. Cela signifie que si vous voulez utiliser par exemple le caractère littéral
‘_’ comme nom de base, vous devez le spécifier sous la forme ‘\_’ dans la commande
GRANT, pour éviter à l'utilisateur d'accéder à d'autres bases, dont le nom pourrait
correspondre au masque d'expression régulière ainsi créé. Utilisez plutôt GRANT ... ON
`foo\_bar`.* TO ... .
Afin de permettre l'identification des utilisateurs depuis des hôtes arbitraires, MySQL supporte
la spécification du nom d'utilisateur nom_utilisateur sous la forme user@host. Si vous
voulez spécifier un nom d'utilisateur user qui contient des caractères spéciaux tels que ‘-’, ou
une chaîne d'hôte host qui contient des caractères joker (comme ‘%’), vous pouvez placer le
nom de l'utilisateur ou de l'hôte entre guillemets (par exemple, 'test-utilisateur'@'test-
nomdhote' ).
Vous pouvez spécifier des caractères jokers dans le nom d'hôte. Par exemple,
user@'%.loc.gov' fait correspondre l'utilisateur user de n'importe quel hôte du domaine
loc.gov, et user@'144.155.166.%' fait correspondre l'utilisateur user à n'importe quelle
adresse de la classe C 144.155.166.
La forme simple de user est synonyme de user@"%" .
MySQL ne supporte pas de caractères joker dans les noms d'utilisateur. Les utilisateurs
anonymes sont définis par l'insertion de ligne avec User='' dans la table mysql.user, ou en
créant un utilisateur avec un nom vide, grâce à la commande GRANT.
GRANT ALL ON test.* TO ''@'localhost' ...
Attention : si vous autorisez des utilisateurs anonymes à se connecter à votre serveur, vous
devriez aussi donner ces droits à tous les utilisateurs locaux user@localhost, car sinon, la
ligne dans la table mysql.user sera utilisée lorsque l'utilisateur se connectera au serveur
MySQL depuis la machine locale ! (Ce compte est créé durant l'installation de MySQL.)
Vous pouvez vérifier si cela s'applique à vous en exécutant la requête suivante :
SELECT Host,User FROM mysql.user WHERE User='';
Si vous voulez effacer les utilisateurs anonymes d'un serveur, utilisez ces commandes :
DELETE FROM mysql.user WHERE Host='localhost' AND User='';
FLUSH PRIVILEGES;
Actuellement, la commande GRANT supporte uniquement les noms d'hôte, colonne, table et
bases de données d'au plus 60 caractères. Un nom d'utilisateur peut être d'au plus 16
caractères.
Les droits pour les tables et colonnes sont combinés par OU logique, avec les quatre niveaux
de droits. Par exemple, si la table mysql.user spécifie qu'un utilisateur a un droit global de
SELECT, ce droit ne pourra pas être annulé au niveau base, table ou colonne. Les droits d'une
colonne sont calculés comme ceci :
droit global
OR (droit de base de données ET droit d'hôte)
55
OR droit de table
OR droit de colonne
Dans la plupart des cas, vous donnez des droits à un utilisateur en utilisant un seul des
niveaux de droits ci-dessus, ce qui fait que la vie n'est pas aussi compliquée.
Si vous donnez des droits à une paire utilisateur/hôte qui n'existe pas dans la table
mysql.user, une ligne sera créée et restera disponible jusqu'à son effacement avec la
commande DELETE. En d'autres termes, GRANT crée une ligne dans la table user, mais
REVOKE ne la supprime pas. Vous devez le faire explicitement avec la commande DELETE.
Avec MySQL version 3.22.12 ou plus récent, si un nouvel utilisateur est créé, ou si vous avez
les droits de GRANT globaux, le mot de passe sera configuré avec le mot de passe spécifié
avec la clause IDENTIFIED BY, si elle est fournie. Si l'utilisateur a déjà un mot de passe, il
sera remplacé par ce nouveau.
Attention : si vous créez un nouvel utilisateur, mais ne spécifiez pas de clause IDENTIFIED
BY, l'utilisateur n'aura pas de mot de passe. Ce n'est pas sécuritaire.
Les mots de passe peuvent aussi être modifiés avec la commande SET PASSWORD. Si vous
ne voulez pas transmettre le mot de passe en texte clair, vous pouvez immédiatement utiliser
l'option PASSWORD suivie du mot de passe déjà chiffré avec la fonction PASSWORD() ou
l'API C make_scrambled_password(char *to, const char *password).
Si vous donnez les droits de base, une ligne sera ajoutée dans la table mysql.db. Lorsque les
droits sur cette base seront supprimés avec la commande REVOKE, cette ligne disparaîtra.
Si un utilisateur n'a pas de droit sur une table, elle ne sera pas affichée lorsqu'il demandera la
liste des tables avec la commande SHOW TABLES. Si un utilisateur n'a pas de droit dans une
base, le nom de la base ne sera pas affiché par SHOW DATABASES à moins que l'utilisateur
n'ait un droit de SHOW DATABASES.
La clause WITH GRANT OPTION donne à l'utilisateur le droit de donner les droits qu'il
possède à d'autres utilisateurs. La plus grande prudence est recommandée pour cette
commande, car il permettra à terme à deux utilisateurs de combiner les droits dont ils
disposent.
Vous ne pouvez pas donner un droit que vous ne possédez pas le droit de GRANT OPTION
ne vous donne le droit que de donner les droits que vous possédez.
Sachez que si vous donnez à quelqu'un le droit de GRANT OPTION, tous les droits que
possède cet utilisateur seront distribuables. Supposez que vous donnez à un utilisateur le
droit d' INSERT dans une base de données. Si vous donnez le droit de SELECT sur une base,
et spécifiez l'option WITH GRANT OPTION, l'utilisateur peut distribuer non seulement son
droit de SELECT, mais aussi son droit de INSERT. Si vous donnez ensuite le droit de
UPDATE, il pourra alors distribuer INSERT, SELECT et UPDATE.
Il est recommandé de ne pas donner de droits de ALTER à un utilisateur normal. Si vous le
faites, l'utilisateur pourra essayer de contourner le système de droits en renommant des
tables.
MAX_QUERIES_PER_HOUR #, MAX_UPDATES_PER_HOUR # et MAX_CONNECTIONS_
PER_HOUR # sont nouveaux en MySQL 4.0.2. Ces deux options limitent le nombre de
requêtes et de modifications qu'un utilisateur peut réclamer dans une heure. Si # vaut 0
(valeur par défaut), alors cela signifie qu'il n'y a pas de limitations pour cet utilisateur.
MySQL peut vérifier les attributs X509 en plus des éléments d'identifications habituels,
comme le nom d'utilisateur et le mot de passe. Pour spécifier des options SSL pour un compte
MySQL, utilisez la clause REQUIRE de la commande GRANT.
56
Programmations SGBD
Oracle dispose d’un langage appelé PL/SQL pour compiler des procédures et des fonctions sur
le serveur. Ces procédures et fonctions peuvent être appelées directement en SQL. Quand
elles sont écrites correctement, elles permettent en général un gain de performances non
négligeable, en plus d’être pratiques et agréables à utiliser. En gros, les procédures et
fonctions sont un excellent moyen d’apporter une couche d’intelligence supplémentaire à
votre serveur de bases de données, en lui permettant d’exécuter des actions complexes sans
avoir recours à des scripts extérieurs. On économise donc le protocole de communication
entre bases de données et application. Et bien… C’est possible aussi sous MySQL !
Mini rappel : Procédure, ou fonction ?
Si vous hésitez entre créer une procédure stockée ou une fonction, rappelez-vous bien que la
seule différence entre les deux est qu’une fonction va chercher un résultat (quitte à passer
par des tonnes d’étapes intermédiaires), alors qu’une procédure va faire une action. En gros,
si vous voulez avoir une valeur de retour, il vous faut une fonction. Dans le cas contraire,
préférez une procédure.
Dans quel cas les utiliser, et comment ?
Vous pouvez vous tourner vers les procédures stockées (le terme étant souvent utilisé aussi
pour les fonctions) partout où vous exécutez des traitements de calculs lourds et/ou sur de
gros volumes de données. L’avantage énorme est que vous n’aurez pas à rapatrier des
resultsets de grande taille, pour les traiter en PHP (par exemple), puis les insérer en base :
tout se fera directement en une seule requête très simple, qui appellera la
fonction/procédure.
Les fonctions MySQL que vous allez définir s’utilisent exactement comme les fonctions
prédéfinies (bien que celles-ci soient en général écrites en C et compilées avec le serveur…
c’est faisable aussi pour un gain maximal en performances MySQL, mais ceci est une autre
histoire), par exemple AVG (qui calcule une moyenne sur les valeurs d’un champ). Sans AVG
(syntaxe: SELECT AVG(champ) FROM table), il faudrait récupérer les résultats concernés,
les ajouter, puis les diviser par leur nombre : (1+5+6)/3 = 4. AVG fait ça toute seule et
renvoie directement 4. Bien évidemment, il n’est pas bien grave d’avoir à récupérer 3 lignes.
Mais avec 20.000 enregistrements, c’est différent, et les performances seront affectées,
notamment en raison de l’utilisation de RAM nécessaire à l’exécution du script.
Il peut être aussi très intéressant d’utiliser des procédures et fonctions sur le serveur de
bases de données quand plusieurs applications frontend dans des langages différents peuvent
avoir à réaliser les mêmes actions: plutôt que d’écrire (et maintenir…) les actions communes
en plusieurs langages, autant déporter leur exécution sur le serveur SQL, et demander aux
clients de seulement interagir avec les fonctions stockées.
57
Application concrète
Imaginons un site de vente en ligne. Chaque jour est généré un rapport, enregistré en base,
qui, en fonction du détail des ventes de la journée, calcule des indicateurs comme le chiffre
d’affaires global et le panier moyen. On aurait donc une table « commandes » avec un
champ « montant » et un champ « date ». En PHP, sans procédure stockée, il faut :
• Envoyer une requête qui prend les ventes de la journée passée
• Récupérer dans un tableau le détail des transactions
• Faire les calculs nécessaires (nombre de lignes, moyenne des montants, total des montants)
• Stocker ces résultats en base
Au bas mot, cela représente une vingtaine de lignes de PHP, avec deux communications
depuis/vers la base de données, une boucle qui parse le tableau, des variables temporaires…
Dans cette situation, c’est d’une procédure stockée que nous avons besoin. On ne récupère
pas les infos (pas de valeur retournée), mais on les stocke en base. Dans le cas contraire, on
aurait créé une fonction.
La procédure stockée en question, que nous appellerons « genere_rapport », va s’occuper
de tout cela pour nous. Voici son code :
DELIMITER //;
CREATE PROCEDURE genere_rapport()
BEGIN
DECLARE nb_commandes INTEGER(5);
DECLARE panier,chiffre_affaires FLOAT;
SELECT COUNT(*),AVG(montant),SUM(montant) INTO
nb_commandes,panier,chiffre_affaires FROM commandes LIMIT 1;
INSERT INTO rapports (nb_com, panier_moyen, ca_total) VALUES
(nb_commandes, panier, chiffre_affaires);
END//
DELIMITER ;
MySQL devrait répondre « Query OK, 0 rows affected (0.01 sec) » pour signaler que la
procédure stockée a bien été créée.
Première remarque, on change le délimiteur de fin de commande MySQL. La création d’une
procédure/fonction doit se faire en une seule instruction MySQL, même si la procédure
stockée comporte plusieurs instructions à exécuter. J’avoue que ça surprend au début, mais
c’est un coup à prendre. Pensez bien à remettre le délimiteur normal (le point-virgule) après
la création de votre procédure stockée.
Ensuite, on lance la création de la procédure. On déclare d’abord les variables dont on aura
besoin pour stocker les données (même si ici, on aurait pu directement faire le SELECT dans
une sous-requête de l’INSERT, mais ce n’est pas le but) : La liste des types disponibles est la
même que les types des champs. On utilise ensuite SELECT INTO avec le nom de nos
variables pour dire à MySQL dans quelle variable stocker quelle valeur, variables qu’on utilise
ensuite dans une requête INSERT classique pour stocker le rapport.
Pour appeler la procédure, on fera CALL genere_rapport(); . Pour une fonction, ça sera
SELECT nom_de_la_fonction(); .
58
Exemple de procédure stockée :
-- AJOUTER UN AMI
DELIMITER |
CREATE PROCEDURE Ami_Insert(
IN param_idami int,
IN param_numeroadh int,
IN param_numeroadhami int,
IN param_dateami datetime)
BEGIN
INSERT INTO ami(NUMEROADH, NUMEROADHAMI)
VALUES(param_numeroadh, param_numeroadhami);
END|
DELIMITER ;
-- Vérification
CALL Ami_Insert(0, 1, 4, '');
-- METTRE A JOUR UN AMI
DELIMITER |
CREATE PROCEDURE Ami_Update(
IN param_idami int,
IN param_numeroadh int,
IN param_numeroadhami int,
IN param_dateami datetime)
BEGIN
UPDATE AMI
SET NUMEROADH = param_numeroadh,
NUMEROADHAMI = param_numeroadhami,
DATEAMI = param_dateami
WHERE IDAMI = param_idami;
END|
DELIMITER ;
-- Vérification
CALL Ami_Update(1, 2, 1, '2011-08-11');
-- SUPPRIMER UN AMI
DELIMITER |
CREATE PROCEDURE Ami_Delete(
IN param_idami int,
IN param_numeroadh int,
IN param_numeroadhami int,
IN param_dateami datetime)
BEGIN
DELETE FROM AMI
WHERE IDAMI = param_idami;
END|
DELIMITER ;
-- Vérification
CALL Ami_Delete(4, 0, 0, '');
59
-- LISTE DES AMIS POUR UN NUMERO ADH DONNEE
DELIMITER |
CREATE PROCEDURE Ami_Rechercher(
IN param_numeroadh int)
BEGIN
SELECT *
FROM AMI
WHERE NUMEROADH = param_numeroadh OR NUMEROADHAMI = param_numeroadh
ORDER BY DATEAMI DESC;
END|
DELIMITER ;
-- Vérification
CALL Ami_Rechercher(1);
-- AFFICHER LA LISTE DES AMI
DELIMITER |
CREATE PROCEDURE Liste_Ami()
BEGIN
SELECT *
FROM AMI
ORDER BY DATEAMI DESC;
END|
DELIMITER ;
-- Vérification
CALL Liste_Ami();
-- RECHERCHER UN AMI PAR SON IDENTIFIANT
DELIMITER |
CREATE PROCEDURE Ami_RechercherAmi(
IN param_idami int)
BEGIN
SELECT *
FROM AMI
WHERE IDAMI = param_idami;
END|
DELIMITER ;
-- Vérification
CALL Ami_RechercherAmi(1);
60
