Lors de la restauration de base MySQL contenant des Views, on rencontre des problèmes de droits d'accès sur certaines tables (en l'occurrence, les views). Le problème vient de ce que la view incorpore les droits de l'utilisatrice (au sens MySQL) qui l'a créée (ou d'une utilisatrice n'existant que sur le système d'origine).
Les manifestations du problème sont plus ou moins explicites. Dans le meilleur des cas, s'affichera un message d'erreur : “#1449 - The user specified as a definer ('<user>'@'<host>') does not exist”. Mais on pourra aussi avoir un message plus ambiguë tel que “invalid table(s) or column(s) or fucntion(s) or definer/invoker of view lack rights to use them”. Pourtant, l'erreur mysql 1449 est sans ambiguïté : The user specified as a definer ('%s'@'%s') does not exist.
Pour éviter le problème, il faut :
L'avantage de cette technique est qu'on peut l'appliquer même si la base a été exportée sans aucune précaution. Ce sera le cas si la personne qui a exporté la base n'a pas connaissance de la spécificité des views.
La nouveauté réside dans la suppression des lignes de déclaration de droits qui ont la forme suivante :
/*!50013 DEFINER=`projects`@`localhost` SQL SECURITY DEFINER */
sous linux la commande sed fait ça très bien :
$ sed '/^\/\*\!50013/d' backup.sql > backup_without_50013.sql
L'opération complète, en ligne de commande, sous Linux :
$ mysqldump -uuser1 -ppassword1 database1 > backup.sql $ sed '/^\/\*\!50013/d' backup.sql > backup_without_50013.sql $ mysql -u user2 -ppassword2 -D database2 < backup_without_50013.sql
en PHP, on peut le faire comme ça :
$contents = file_get_contents('backups/backup.sql');
$contents = preg_replace('@\/\*\!50013 DEFINER.*?\*\/@' , '' , $contents);
file_put_contents('backups/backup.sql' , $contents);
Cela règle le problème dans la majorité des cas (simples). Mais il suffira que :
pour le problème resurgisse.
Le problème vient des droits sur les views, tels qu'ils sont gérés depuis MySQL 5.0.16. En l'absence d'indication contraire, une vue hérite des droits de l'utilisatrice qui l'a créée, sur le système où cette création a eu lieu. Lors du transport de la base sur un autre système, il sera fréquent que l'utilisatrice n'existe pas sur cet autre système. On demande alors à utiliser une vue avec les droits d'une utilisatrice inexistante…
Dans la majorité des cas simples, ce n'est pas le comportement souhaité. On voudrait que la vue soit utilisée avec les droits de l'utilisatrice qui s'en sert. Cette situation est prévue, mais il faut que la vue soit créée avec les droits appropriés “SQL SECURITY INVOKER” (au lieu “SQL SECURITY DEFINER” qui est la déclaration par défaut).
Pour s'affranchir des droit de DEFINER, il suffit de fixer SQL SECURITY à INVOKER. Dès lors, même l'utilisatrice DEFINER est inconnue de MySQL, la view fonctionnera puisque s'exécutant avec les droit de l'utilisatrice qui s'en sert.
Dans l'idéal, on souhaiterait pouvoir effectuer l'opération de changement de droits, directement dans la base, sans avoir à manipuler le fichier de sauvegarde.
Malheureusement (et heureusement !), les droits sur les views ne sont pas enregistrés dans la base contenant les vues mais dans la base information_schema manipulée par MySQL. Même l'utilisatrice super ne peut pas modifier directement le contenus des lignes de la table ad hoc (en l'occurrence information_schema.views).
Le panneau de contrôle Webmin propose un éditeur de views permettant de modifier directement les attributs de sécurité des vues attachées à une base de données. Il suffit de sélectionner la base puis de sélectionner chaque vue. L'interface de modifier le “DEFINER” (désigné owner) et les droits avec lesquels s'exécute la view, DEFINER OU INVOKER (désignés owner et user).
Cette technique est opérante lorsque le nombre de bases et de views concernées est limité.
Lorsqu'on utilise l'option “Drop all tables” de la commande “Drop” database, seules les tables “ordinaires” sont supprimées, les views ne le sont pas. On doit lancer un “DROP” spécifique à leur encontre. La requête SQL suivante devrait fournir le code de la requête à formuler :
SELECT CONCAT("DROP VIEW ",table_name,";") FROM information_schema.views WHERE table_schema='Your_Database_Name'
Dans phpMyAdmin, l'effacement des tables portant des views entraîne la suppression des views.
Dans Virtualmin, les utilisatrices créées au sein d'un serveur virtuel voient leur login de base de données postfixé par le domaine, à l'aide d'un point : user.dom. Cette syntaxe n'est pas acceptée par l'interface le renommage du DEFINER ! C'est bien dommage car il est impératif de créer des utilisatrices spécifiques à chaque base de donnée, au sein d'un serveur vituel ; ne serait-ce que pour limiter les risques de sécurité inhérents à l'inscription de leurs identifiants dans les fichiers de configuration des CMS.
Une astuce consiste à utiliser l'identifiant de l'utilisatrice contrôlant le serveur virtuel (utilisatrice Unix) dont le login n'est pas préfixé.
Lorsqu'on doit traiter de nombreuses views et/ou bases ou si l'on ne dispose pas d'une interface traitant les vues, il devient indispensable d'automatiser les transformations à appliquer sur la base.
Pour changer les droits d'une view existante, on doit nécessairement passer par la requête ALTER VIEW. Le problème est que sa syntaxe ne permet pas de modifier uniquement les droits. Il faut “re-déclarer” la view complète. C'est peu pratique mais faisable si on a soi-même créé la vue. Ça l'est moins si l'on ne sait rien de cette view et/ou s'il en existe beaucoup.
La seule solution est de demander l'information à MySQL et, tant qu'à faire, de bâtir la requête qui effectuera les changements souhaités :
SELECT CONCAT("ALTER SQL SECURITY INVOKER VIEW ",table_name," AS ", view_definition,";")
FROM information_schema.views WHERE table_schema='Your_Database_Name';
On récupère ainsi la déclaration des views sous une forme appropriée pour lancer une requête de mise à jour.
L'opération peut se faire en ligne de commande, en injectant le texte le requête :
$ echo "texte_de_requête" | mysql -uuser -p > alterationRequest.sql $ mysql -uuser -p Your_Database_Name < alterationRequest.sql
On peut aussi vouloir profiter des possibilité d'utilisation d'une view avec les droits d'une utilisatrice particulière, différente celle(s) qui l'utilise(nt), à un moment.
Les commandes et requêtes précédentes ne demandent qu'une légère adaptation…
Modification du fichier de sauvegarde :
sed -ri s/DEFINER=`[^`]+`@`%`/DEFINER=The_User_You_Want/g backup.sql
Intervention directe sur la base de données :
SELECT CONCAT("ALTER DEFINER=`The_User_You_Want`@`The_Host_You_Want` VIEW ",table_name," AS ", view_definition,";")
FROM information_schema.views WHERE table_schema='Your_Database_Name'