Flaz's Hotline

Cette page est en lecture seule. Vous pouvez afficher le texte source, mais ne pourrez pas le modifier. Contactez votre administrateur si vous pensez qu'il s'agit d'une erreur.
====== Bases MySQL avec des Views ======
===== Problème lors du déplacement de la base =====
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é : [[http://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html#error_er_no_such_user|The user specified as a definer ('%s'@'%s') does not exist]].

==== Principe ====


Pour éviter le problème, il faut :
  - exporter la base en format textuel (typiquement mysqldump ou le format par défaut de Virtualmin)
  - supprimer les lignes contenant les droits d'utilisation des vues
  - restaurer le fichier modifié avec les droits d'un l'utilisateur exploitant la base, une fois déplacée

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.

==== Suppression ====
La nouveauté réside dans la suppression des lignes de déclaration de droits qui ont la forme suivante :
<code>/*!50013 DEFINER=`projects`@`localhost` SQL SECURITY DEFINER */</code>

sous linux la commande //sed// fait ça très bien :
<code>$ sed '/^\/\*\!50013/d' backup.sql > backup_without_50013.sql</code>

L'opération complète, en ligne de commande, sous Linux :
<code>
$ 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
</code>

en PHP, on peut le faire comme ça :
<code>
$contents = file_get_contents('backups/backup.sql');
$contents = preg_replace('@\/\*\!50013 DEFINER.*?\*\/@' , '' , $contents);
file_put_contents('backups/backup.sql' , $contents);
</code>


==== Discussion ====
Cela règle le problème dans la majorité des cas (simples). Mais il suffira que :
  * la base soit de nouveau déplacée
  * l'utilisatrice ayant restauré la base soit supprimée ou voie ses droits modifiés (plusieurs utilisatrices sur une même base)
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).

===== Modification des droits sur la base =====
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//).

==== Utiliser Virtualmin-Webmin ====
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é.

== Remarque 1 ==
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 :
<code>
SELECT CONCAT("DROP VIEW ",table_name,";") FROM information_schema.views WHERE table_schema='Your_Database_Name'
</code>

Dans phpMyAdmin, l'effacement des tables portant des views entraîne la suppression des views.

== Remarque 2 ==
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é.


==== Passer des requêtes SQL ====
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 [[http://dev.mysql.com/doc/refman/5.0/en/alter-view.html|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 :

<code>
SELECT CONCAT("ALTER SQL SECURITY INVOKER VIEW ",table_name," AS ", view_definition,";") 
FROM information_schema.views WHERE table_schema='Your_Database_Name';
</code>

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 :
<code>
$ echo "texte_de_requête" | mysql -uuser -p > alterationRequest.sql
$ mysql -uuser -p Your_Database_Name < alterationRequest.sql
</code>

==== Changer le nom de l'utilisatrice portant les droits ====
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 :
<code>
sed -ri s/DEFINER=`[^`]+`@`%`/DEFINER=The_User_You_Want/g backup.sql
</code>

Intervention directe sur la base de données :
<code>
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'
</code>

===== Références =====

  * [[http://dba.stackexchange.com/questions/4129/modify-definer-on-many-views]|Modify definers on many views]]
  * Une [[http://www.apprendre-php.com/tutoriels/tutoriel-28-mysql-5-0-les-vues.html|article]] (en français) clair, précis et concis exposant, exemples à l'appui, le principe, les commandes et les contextes d'utilisation des views.
  * La commande [[http://dev.mysql.com/doc/refman/5.0/en/create-view.html|CREATE VIEW]].
  * La commande [[http://dev.mysql.com/doc/refman/5.0/en/alter-view.html|ALTER VIEW]].
  * La commande [[http://dev.mysql.com/doc/refman/5.0/en/drop-view.html|DROP VIEW]].
  * Les informations sur les views conservées dans la [[http://dev.mysql.com/doc/refman/5.1/en/views-table.html|table views]] de la base système //information_schema//.
  * Un post exposant le [[http://www.binarytides.com/blog/mysql-view-definer-and-sql-security/|retraitement des déclarations de sécurité sur un fichier]] de sauvegarde au format mysqldump.