Un bout de DBA MySQL, En long et en large

Tout ce qu’il faut savoir sur les routines

La syntaxe SQL pour créer une procédure stockée ou une fonction est simple. Cependant, il y a plusieurs choses à savoir pour créer et utiliser une routine. Tout d’abord, il faut connaitre les trois nouveaux privilèges liés aux routines:

• Create routine pour créer des routines (procédure stockée ou fonction)
• Alter routine pour modifier ou supprimer une routine. Ce privilège est automatiquement donné au créateur de la routine.
• Execute pour les exécuter. Ce privilège est également donné automatiquement au créateur.

Si l’option automatic_sp_privilèges est à 0, Alter routine et Execute ne seront pas automatiquement attribués au créateur. Il faut aussi savoir que ces 3 privilèges ne sont pas les seuls éléments de sécurité. Avec les procédures stockées, la caractéristique SQL SECURITY peut être utilisée pour définir si c’est les privilèges du créateur ou de celui qui l’exécute qui doivent être utilisés. La valeur par défaut est definer.

Le privilège SUPER doit être donné aux utilisateurs qui crée et exécute des routines sur des serveurs ayant le binary log activé, en plus des privilèges Create routine et alter routine. Vous conviendrez que donner un tel privilège n’est pas toujours souhaitable, donc MySQL s’est doté de l’option log_bin_trust_routine_creators (désactivée par défaut) pour pallier ce “problème”.

On pourrait se demander pourquoi cette option est désactivée par défaut. La réponse est simple: par souci de sécurité. Lorsque le binlog est activé, la création de routine doit absolument avoir DETERMINISTIC, NO SQL ou READ SQL DATA dans sa déclaration.

• Les caractéristiques DETERMINISTIC et NOT DETERMINISTIC indiquent si la routine produit toujours le même résultat pour la même entrée. Pour la déclarer DETERMINISTIC, vous devez le spécifier explicitement. Le défaut est NOT DETERMINISTIC ce qui signifie que la routine peut retourner des résultats différents pour la même entrée.
• Les caractéristiques CONTAINS SQL, NO SQL, READS SQL DATA et MODIFIES SQL DATA indiquent si la routine sélectionne ou modifie des données. Le défaut est CONTAINS SQL si aucune caractéristique n’est spécifiée.

L’option log_bin_trust_routine_creators permet d’outre passer cette sécurité. Si elle est activée, le créateur d’une fonction peut déclarer qu’elle est not deterministic. C’est pourquoi il est conseillé d’activer cette option seulement si vous avez une confiance entière aux usagers qui peuvent créer des routines. Une telle routine peut causer d’importants problèmes:

• Rendre les Slaves différents du master, puisqu’il n’y a aucune garantie que le résultat sera le même
• Les données restaurées peuvent être différentes des données originales

Comme vous devez vous douter, les routines sont stockées directement dans le serveur. Pour se faire, MySQL utilise 3 nouvelles tables: information_schema.routine, mysql.proc et mysql.proc_priv. Si vous upgradez un serveur à partir d’une version inférieure à MySQL 5, il faut s’assurer que ces tables sont présentes. Vous pouvez utiliser le script mysql_upgrade pour les ajouter.

Pour conclure, vous devez aussi savoir que le code qui constitue une routine est stocké différemment du code utilisé pour la créer. Les commentaires sont supprimés, c’est pourquoi il est conseillé de toujours conserver une copie du code de votre routine dans un fichier texte à l’extérieur du serveur.

Bien sur, il y a beaucoup d’autres choses à savoir, mais je crois que ça fait le tour de ce qui est essentiel!