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
+
+
+