NAME MysqlTool - Web-based administration tool for mysql SYNOPSIS use MysqlTool; &MysqlTool::handler(); DESCRIPTION MysqlTool provides a graphical alternative to the mysql command line program and is *very* useful for managing one or more mysql server installations. SETUP As of version 0.90, explicit definition of $MysqlTool::servers is no longer required. MysqlTool can be run in two different modes. MysqlTool as a 'single user' application -- In 'single user' mode, connection information is preset and stored in a configuration file. In single user mode, the webserver MysqlTool runs under is responsible for authentication, which usually means access is restricted by means of an htaccess file. Anyone who has access to this 'single user' MysqlTool installation has access to all of the databases defined in the configuration file. See "SINGLE USER MODE" for details. MysqlTool as a 'multi user' app (default) -- In this mode a user provides connection information via a form interface when first connecting to MysqlTool. This information is hopefully sent over an SSL-encrypted link. MysqlTool will then use Crypt::Blowfish to encrypt this database connection information and store the crypted information in a cookie. In multi user mode, many different users may be connected to the same instance of mysqltool using many different databases at the same time. See "MULTI USER MODE" for details. First step - install the modules gunzip MysqlTool-x.xx.tar.gz tar -xvf MysqlTool-x.xx.tar cd MysqlTool-x.xx perl Makefile.PL make make test make install (You will need to be root for the "make install", and perl Makefile.PL will fail unless the modules CGI, DBI, DBD::mysql and Crypt::Blowfish are installed.) Step 2 - edit and install mysqltool.conf (See "MYSQTOOL.CONF" for details about the syntax of mysqltool.conf. For basic installations, you shouldn't need to edit mysqltool.conf at all.) Move the included default configuration file htdocs/mysqltool.conf to a safe directory that is not under your webserver document root. We recommend putting mysqltool.conf in the same directory as your web server configuration files. mv htdocs/mysqltool.conf {apache_root}/conf/ Change the owner of mysqltool.conf to the root if running under mod_perl, or to the user the webserver runs as if you're running MysqlTool as a standalone CGI. chown root.root {apache_root}/conf/mysqltool.conf or, if your webserver runs as nobody chown nobody {apache_root}/conf/mysqltool.conf Next, change the permissions so only the owner can read the file. chmod 600 {apache_root}/conf/mysqltool.conf Step 3 - edit index.cgi & install htdocs/ Open htdocs/index.cgi with your favorite editor and make sure the line require '{apache_root}/conf/mysqltool.conf'; points to where you put your mysqltool.conf file. Copy the entire htdocs directory (index.cgi & images/) to somewhere within your webserver's document root. cp -R htdocs {apache_document_root}/htdocs/mysqltool You should now have a working installation of MysqlTool. Follow the pointers in the "SEE ALSO" if you have problems and need help. SINGLE USER MODE The variable %MysqlTool::servers in mysqltool.conf must be defined and $MysqlTool::mode must equal 'SINGLE USER' for single user mode to work. $MysqlTool::mode = 'SINGLE USER'; $MysqlTool::servers Variable MysqlTool makes the connection to mysql based on the properties you specify in the $MysqlTool::servers variable. If you specify an admin username and password MysqlTool will be able to automatically find databases that the specified mysql server stores, and will display functions available to users with server scope privileges. Example: $MysqlTool::servers{1}->{'server'} = 'localhost'; $MysqlTool::servers{1}->{'port'} = 3306; $MysqlTool::servers{1}->{'admin_user'} = 'root'; $MysqlTool::servers{1}->{'admin_password'} = ''; $MysqlTool::servers{2}->{'server'} = 'example.com'; $MysqlTool::servers{2}->{'port'} = 3306; $MysqlTool::servers{2}->{'admin_user'} = 'root'; $MysqlTool::servers{2}->{'admin_password'} = 'password'; If you don't want to connect as a user with server scope privileges then you must define specific databases, database usernames and database passwords. Example: $MysqlTool::servers{1}->{'server'} = 'localhost'; $MysqlTool::servers{1}->{'port'} = 3306; $MysqlTool::servers{1}->{'databases'}->{1}->{'db'} = 'Historical_League'; $MysqlTool::servers{1}->{'databases'}->{1}->{'username'} = 'hist_league'; $MysqlTool::servers{1}->{'databases'}->{1}->{'password'} = 'password'; $MysqlTool::servers{1}->{'databases'}->{2}->{'db'} = 'Northwind'; $MysqlTool::servers{1}->{'databases'}->{2}->{'username'} = 'northwind'; $MysqlTool::servers{1}->{'databases'}->{2}->{'password'} = 'password'; Single User Mode Security Considerations Unless the webserver is only accessable from behind a very secure firewall, you should setup an 'htaccess' file to limit access to your private installation of MysqlTool. See "NOTES" below for a sample .htaccess file. MULTI USER MODE (Note: MysqlTool's mutli-user mode is a work in progress. We would greatly appreciate advice and feedback about this initial implementation.) $MysqlTool::mode = 'MULTI USER'; Multi-user mode means that connection information is provided by the client with each request. The database connection information is passed in the clear for the initial connection (wich should be over an encrypted SSL channel). MysqlTool then uses $MysqlTool::private_key to encode the database connection parameters, the client's IP, and a timeout value. This ciphertext is then passed back to the client and stored as a cookie named mysqltool. A cookie with a past expiration or incorrect IP address embedded within is considered invalid and ignored. Here's an explanation from Eric Smith, an originator of the idea -- "The only way that a stolen cookie is useful is if the attacker also has the secret key. And an attacker with the secret key but without a valid cookie can't do anything. The attacker has to obtain both the cookie and the secret key, thus must attack both the client and the server, in order to compromise the system." MysqlTool::private_key When MysqlTool is installed (when you run perl Makefile.PL), a 448-bit key for Crypt::Blowfish is automatically generated and stored in mysqltool.conf as $MysqlTool::private_key. You may modify this key at any time, although it must be at least eight characters. When you modify the private_key, existing sessions become invalidated and users must resupply their connection parameters. Is storing a cookie on the client side secure .. ? We think so, but we would sure love to have someone audit our Crypt::Blowfish usage. The big question is -- does it matter that a good deal of the ciphertext is known plaintext? Or do issues with known plaintext not matter because the key is so large? Is our key generation code random enough? Should we be using a larger subset of characters when generating keys? How do I limit access to only certain mysql servers? Un-comment and define %MysqlTool::allowed_servers in mysqltool.conf and restart apache. The login page will display a popup menu with the servers you defined. Attempts to login to other servers will fail. Please see the multi-user mode code in MysqlTool.pm (lines 800 to 830, and 760 to 790) for more details. MYSQTOOL.CONF TODO - document the configurable variables. NOTES Running under mod_perl We recommend running MysqlTool under mod_perl. To do so, add the following lines to your mod_perl server configuration file and restart the web server. PerlRequire {apache_root}/conf/mysqltool.conf Options ExecCGI SetHandler perl-script PerlHandler MysqlTool Also note -- just like with any other mod_perl program, you will need to restart Apache every time you make a change to mysqltool.conf. Sample .htaccess file AuthType Basic AuthName "authentication required" AuthUserFile {apache_root}/conf/mysqltool.htpasswd AuthGroupFile /dev/null require valid-user To create the file {apache_root}/conf/mysqltool.htpasswd, use Apache's 'htpasswd' command: {apache_root}/bin/htpasswd -c {apache_root}/conf/mysqltool.htpasswd [username] For more information about htaccess files, check out the article titled 'Using .htaccess Files with Apache' at: http://apachetoday.com/news_story.php3?ltsn=2000-07-19-002-01-NW-LF-SW SEE ALSO The MysqlTool project homepage: http://dajoba.com/projects/mysqltool/ The MysqlTool mailing list: mysqltool at lists dot dajoba dot com http://lists.dajoba.com/m/listinfo/mysqltool/ Mysql documentation: http://mysql.com/doc/ TODO - importing and exporting data - query builder - some sort of contextual help system - a (better?) multi-user system that saves connection parameters on the server end - a better SQL statement parsing system - document mysqltool.conf (configurable) variables - and, as always, find more testers, err, users AUTHROS / ACKNOWLEDGMENTS MysqlTool was created by Joe Ingersoll . Tweaks, testing and documentation by Abe Ingersoll . Thanks to: Eric Smith , for multi-user idea. John Van Essen , for bug fixes. Mathieu Longtin , for bug report & suggestions. Bill Gerrard , for suggestions & bug reports. Ray Zimmerman , for bug report. Andy Baio , for the early critique. Joerg Ungethuem , bugfix. COPYRIGHT Copyright 2001 Dajoba, LLC This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.