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