Note: The following portion of documentation comes from my answer to an old discussion of Keynote, a cool product that does trouble-ticket tracking for IT departments. I wrote this post to the Keynote support group regarding MySQL grant table permissions, and how to use them effectively. It is badly in need of updating, as I believe MySQL has added a field or two to the grant tables since this time, but it serves as a decent introduction and troubleshooting document for grant table issues. I used Keynote to track my troubles until I discovered Bugzilla, which gave me a whole new set of troubles to work on : )
From matt_barnson@singletrac.com Wed Jul 7 09:00:07 1999
Date: Mon, 1 Mar 1999 21:37:04 -0700
From: Matthew Barnson matt_barnson@singletrac.com
To: keystone-users@homeport.org
Subject: [keystone-users] Grant Tables FAQ
[The following text is in the "iso-8859-1" character set]
[Your display is set for the "US-ASCII" character set]
[Some characters may be displayed incorrectly]
Maybe we can include this rambling message in the Keystone FAQ? It gets
asked a lot, and the only option current listed in the FAQ is
"--skip-grant-tables".
Really, you can't go wrong by reading section 6 of the MySQL manual, at
http://www.mysql.com/Manual/manual.html. I am sure their description is
better than mine.
MySQL runs fine without permissions set up correctly if you run the mysql
daemon with the "--skip-grant-tables" option. Running this way denies
access to nobody. Unfortunately, unless you've got yourself firewalled it
also opens the potential for abuse if someone knows you're running it.
Additionally, the default permissions for MySQL allow anyone at localhost
access to the database if the database name begins with "test_" or is named
"test" (i.e. "test_keystone"). You can change the name of your database in
the keystone.conf file ($sys_dbname). This is the way I am doing it for
some of my databases, and it works fine.
The methods described below assume you're running MySQL on the same box as
your webserver, and that you don't mind if your $sys_dbuser for Keystone has
superuser access. See near the bottom of this message for a description of
what each field does.
Method #1:
1. cd /var/lib
#location where you'll want to run /usr/bin/mysql_install_db shell
script from to get it to work.
2. ln -s mysql data
# soft links the "mysql" directory to "data", which is what
mysql_install_db expects. Alternately, you can edit mysql_install_db and
change all the "./data" references to "./mysql".
3. Edit /usr/bin/mysql_install_db with your favorite text editor (vi,
emacs, jot, pico, etc.)
A) Copy the "INSERT INTO db VALUES
('%','test\_%','','Y','Y','Y','Y','Y','Y');" and paste it immediately after
itself. Chage the 'test\_%' value to 'keystone', or the value of
$sys_dbname in keystone.conf.
B) If you are running your keystone database with any user, you'll need to
copy the "INSERT INTO user VALUES
('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');" line after
itself and change 'root' to the name of the keystone database user
($sys_dbuser) in keystone.conf.
# adds entries to the script to create grant tables for specific
hosts and users. The user you set up has super-user access ($sys_dbuser) --
you may or may not want this. The layout of mysql_install_db is really very
uncomplicated.
4. /usr/bin/mysqladmin shutdown
# ya gotta shut it down before you can reinstall the grant tables!
5. rm -i /var/lib/mysql/mysql/*.IS?' and answer 'Y' to the deletion
questions.
# nuke your current grant tables. This WILL NOT delete any other
databases than your grant tables.
6. /usr/bin/mysql_install_db
# run the script you just edited to install your new grant tables.
7. mysqladmin -u root password (new_password)
# change the root MySQL password, or else anyone on localhost can
login to MySQL as root and make changes. You can skip this step if you want
keystone to connect as root with no password.
8. mysqladmin -u (webserver_user_name) password (new_password)
# change the password of the $sys_dbuser. Note that you will need
to change the password in the keystone.conf file as well in $sys_dbpasswd,
and if your permissions are set up incorrectly anybody can type the URL to
your keystone.conf file and get the password. Not that this will help them
much if your permissions are set to @localhost.
Method #2: easier, but a pain reproducing if you have to delete your grant
tables. This is the "recommended" method for altering grant tables in
MySQL. I don't use it because I like the other way :)
shell> mysql --user=root keystone
mysql> GRANT
SELECT,INSERT,UPDATE,DELETE,INDEX,ALTER,CREATE,DROP,RELOAD,SHUTDOWN,PROCESS,
FILE,
ON keystone.*
TO <$sys_dbuser name>@localhost
IDENTIFIED BY '(password)'
WITH GRANT OPTION;
OR
mysql> GRANT ALL PRIVELEGES
ON keystone.*
TO <$sys_dbuser name>@localhost
IDENTIFIED BY '(password)'
WITH GRANT OPTION;
# this grants the required permissions to the keystone ($sys_dbuser)
account defined in keystone.conf. However, if you are runnning many
different MySQL-based apps, as we are, it's generally better to edit the
mysql_install_db script to be able to quickly reproduce your permissions
structure again. Note that the FILE privelege and WITH GRANT OPTION may not
be in your best interest to include.
GRANT TABLE FIELDS EXPLANATION:
Quick syntax summary: "%" in MySQL is a wildcard. I.E., if you are
defining your DB table and in the 'host' field and enter '%', that means
that any host can access that database. Of course, that host must also have
a valid db user in order to do anything useful. 'db'=name of database. In
our case, it should be "keystone". "user" should be your "$sys_dbuser"
defined in keystone.conf. Note that you CANNOT add or change a password by
using the "INSERT INTO db (X)" command -- you must change it with the mysql
-u command as defined above. Passwords are stored encrypted in the MySQL
database, and if you try to enter it directly into the table they will not
match.
TABLE: USER. Everything after "password" is a privelege granted (Y/N).
This table controls individual user global access rights.
'host','user','password','select','insert','update','delete','index','alter'
,'create','drop','grant','reload','shutdown','process','file'
TABLE: DB. This controls access of USERS to databases.
'host','db','user','select','insert','update','delete','index','alter','crea
te','drop','grant'
TABLE: HOST. This controls which HOSTS are allowed what global access
rights. Note that the HOST table, USER table, and DB table are very closely
connected -- if an authorized USER attempts an SQL request from an
unauthorized HOST, she's denied. If a request from an authorized HOST is
not an authorized USER, it is denied. If a globally authorized USER does
not have rights to a certain DB, she's denied. Get the picture?
'host','db','select','insert','update','delete','index','alter','create','dr
op','grant'
You should now have a working knowledge of MySQL grant tables. If there is
anything I've left out of this answer that you feel is pertinent, or if my
instructions don't work for you, please let me know and I'll re-post this
letter again, corrected. I threw it together one night out of exasperation
for all the newbies who don't know squat about MySQL yet, so it is almost
guaranteed to have errors.
Once again, you can't go wrong by reading section 6 of the MySQL manual. It
is more detailed than I!
http://www.mysql.com/Manual/manual.html.
----------------------------------------------------------------------------
10/12/2000
Matthew sent in some mail with updated contact information:
NEW CONTACT INFORMATION:
------------------------
Matthew P. Barnson
Manager, Systems Administration
Excite@Home Business Applications
mbarnson@excitehome.net
(801)234-8300