1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
<HTML
><HEAD
><TITLE
>MySQL Permissions & Grant Tables</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.61
"><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="Useful Patches and Utilities for Bugzilla"
HREF="patches.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="patches.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"
><P
></P
><TABLE
CLASS="note"
WIDTH="100%"
BORDER="0"
><TR
><TD
WIDTH="25"
ALIGN="CENTER"
VALIGN="TOP"
><IMG
SRC="../images/note.gif"
HSPACE="5"
ALT="Note"></TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
><P
>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 : ) Although it is of limited use, it
still has SOME use, thus it's still included.</P
><P
> Please note, however, that I was a relatively new user to
MySQL at the time. Some of my suggestions, particularly in
how to set up security, showed a terrible lack of
security-related database experience.
</P
></TD
></TR
></TABLE
></DIV
><P
CLASS="literallayout"
><br>
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>
</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="patches.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"
>Useful Patches and Utilities for Bugzilla</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>