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.