diff options
Diffstat (limited to 'docs/sgml/database.sgml')
| -rw-r--r-- | docs/sgml/database.sgml | 897 |
1 files changed, 393 insertions, 504 deletions
diff --git a/docs/sgml/database.sgml b/docs/sgml/database.sgml index 848555520..4cba46a48 100644 --- a/docs/sgml/database.sgml +++ b/docs/sgml/database.sgml @@ -1,553 +1,441 @@ <!-- <!DOCTYPE appendix PUBLIC "-//OASIS//DTD DocBook V4.1//EN"> --> - <appendix id="database"> + <title>The Bugzilla Database</title> -<title>The Bugzilla Database</title> -<note> -<para> - This document really needs to be updated with more fleshed out information about primary keys, interrelationships, and maybe some nifty tables to document dependencies. Any takers? - </para> + <note> + <para>This document really needs to be updated with more fleshed out + information about primary keys, interrelationships, and maybe some nifty + tables to document dependencies. Any takers?</para> </note> + <section id="dbschema"> <title>Database Schema Chart</title> + <para> <mediaobject> - <imageobject> - <imagedata fileref="../images/dbschema.jpg" format="JPG" /> - </imageobject> - - <textobject> - <phrase>Database Relationships</phrase> - </textobject> - - <caption> - <para>Bugzilla database relationships chart</para> - </caption> + <imageobject> + <imagedata fileref="../images/dbschema.jpg" format="JPG" /> + </imageobject> + + <textobject> + <phrase>Database Relationships</phrase> + </textobject> + + <caption> + <para>Bugzilla database relationships chart</para> + </caption> </mediaobject> </para> </section> <section id="dbdoc"> -<title>MySQL Bugzilla Database Introduction</title> - <para> - This information comes straight from my life. I was forced to learn how - Bugzilla organizes database because of nitpicky requests from users for tiny - changes in wording, rather than having people re-educate themselves or - figure out how to work our procedures around the tool. It sucks, but it can - and will happen to you, so learn how the schema works and deal with it when it - comes. - </para> - - <para> - So, here you are with your brand-new installation of Bugzilla. You've got - MySQL set up, Apache working right, Perl DBI and DBD talking to the database - flawlessly. Maybe you've even entered a few test bugs to make sure email's - working; people seem to be notified of new bugs and changes, and you can - enter and edit bugs to your heart's content. Perhaps you've gone through the - trouble of setting up a gateway for people to submit bugs to your database via - email, have had a few people test it, and received rave reviews from your beta - testers. - </para> - <para> - What's the next thing you do? Outline a training strategy for your - development team, of course, and bring them up to speed on the new tool you've - labored over for hours. - </para> - <para> - Your first training session starts off very well! You have a captive - audience which seems enraptured by the efficiency embodied in this thing called - "Bugzilla". You are caught up describing the nifty features, how people can - save favorite queries in the database, set them up as headers and footers on - their pages, customize their layouts, generate reports, track status with - greater efficiency than ever before, leap tall buildings with a single bound - and rescue Jane from the clutches of Certain Death! - </para> - <para> - But Certain Death speaks up -- a tiny voice, from the dark corners of the - conference room. "I have a concern," the voice hisses from the darkness, - "about the use of the word 'verified'. - </para> - <para> - The room, previously filled with happy chatter, lapses into reverential - silence as Certain Death (better known as the Vice President of Software - Engineering) continues. "You see, for two years we've used the word 'verified' - to indicate that a developer or quality assurance engineer has confirmed that, - in fact, a bug is valid. I don't want to lose two years of training to a - new software product. You need to change the bug status of 'verified' to - 'approved' as soon as possible. To avoid confusion, of course." - </para> - <para> - Oh no! Terror strikes your heart, as you find yourself mumbling "yes, yes, I - don't think that would be a problem," You review the changes with Certain - Death, and continue to jabber on, "no, it's not too big a change. I mean, we - have the source code, right? You know, 'Use the Source, Luke' and all that... - no problem," All the while you quiver inside like a beached jellyfish bubbling, - burbling, and boiling on a hot Jamaican sand dune... - </para> - <para> - Thus begins your adventure into the heart of Bugzilla. You've been forced - to learn about non-portable enum() fields, varchar columns, and tinyint - definitions. The Adventure Awaits You! - </para> + <title>MySQL Bugzilla Database Introduction</title> + + <para>This information comes straight from my life. I was forced to learn + how Bugzilla organizes database because of nitpicky requests from users + for tiny changes in wording, rather than having people re-educate + themselves or figure out how to work our procedures around the tool. It + sucks, but it can and will happen to you, so learn how the schema works + and deal with it when it comes.</para> + + <para>So, here you are with your brand-new installation of Bugzilla. + You've got MySQL set up, Apache working right, Perl DBI and DBD talking + to the database flawlessly. Maybe you've even entered a few test bugs to + make sure email's working; people seem to be notified of new bugs and + changes, and you can enter and edit bugs to your heart's content. Perhaps + you've gone through the trouble of setting up a gateway for people to + submit bugs to your database via email, have had a few people test it, + and received rave reviews from your beta testers.</para> + + <para>What's the next thing you do? Outline a training strategy for your + development team, of course, and bring them up to speed on the new tool + you've labored over for hours.</para> + + <para>Your first training session starts off very well! You have a + captive audience which seems enraptured by the efficiency embodied in + this thing called "Bugzilla". You are caught up describing the nifty + features, how people can save favorite queries in the database, set them + up as headers and footers on their pages, customize their layouts, + generate reports, track status with greater efficiency than ever before, + leap tall buildings with a single bound and rescue Jane from the clutches + of Certain Death!</para> + + <para>But Certain Death speaks up -- a tiny voice, from the dark corners + of the conference room. "I have a concern," the voice hisses from the + darkness, "about the use of the word 'verified'.</para> + + <para>The room, previously filled with happy chatter, lapses into + reverential silence as Certain Death (better known as the Vice President + of Software Engineering) continues. "You see, for two years we've used + the word 'verified' to indicate that a developer or quality assurance + engineer has confirmed that, in fact, a bug is valid. I don't want to + lose two years of training to a new software product. You need to change + the bug status of 'verified' to 'approved' as soon as possible. To avoid + confusion, of course."</para> + + <para>Oh no! Terror strikes your heart, as you find yourself mumbling + "yes, yes, I don't think that would be a problem," You review the changes + with Certain Death, and continue to jabber on, "no, it's not too big a + change. I mean, we have the source code, right? You know, 'Use the + Source, Luke' and all that... no problem," All the while you quiver + inside like a beached jellyfish bubbling, burbling, and boiling on a hot + Jamaican sand dune...</para> + + <para>Thus begins your adventure into the heart of Bugzilla. You've been + forced to learn about non-portable enum() fields, varchar columns, and + tinyint definitions. The Adventure Awaits You!</para> <section> <title>Bugzilla Database Basics</title> - <para> - If you were like me, at this point you're totally clueless - about the internals of MySQL, and if it weren't for this - executive order from the Vice President you couldn't care less - about the difference between a <quote>bigint</quote> and a - <quote>tinyint</quote> entry in MySQL. I recommend you refer - to the MySQL documentation, available at <ulink url="http://www.mysql.com/doc.html">MySQL.com</ulink>. Below are the basics you need to know about the Bugzilla database. Check the chart above for more details. - </para> - <para><orderedlist> - <listitem> - <para> - To connect to your database: - </para> - <para> - <prompt>bash#</prompt><command>mysql</command><parameter>-u root</parameter> - </para> - <para> - If this works without asking you for a password, - <emphasis>shame on you</emphasis>! You should have - locked your security down like the installation - instructions told you to. You can find details on - locking down your database in the Bugzilla FAQ in this - directory (under "Security"), or more robust security - generalities in the MySQL searchable documentation at - http://www.mysql.com/php/manual.php3?section=Privilege_system . - </para> - </listitem> - - <listitem> - <para>You should now be at a prompt that looks like - this:</para> - <para><prompt>mysql></prompt></para> - <para>At the prompt, if <quote>bugs</quote> is the name - you chose in the<filename>localconfig</filename> file - for your Bugzilla database, type:</para> - <para><prompt>mysql</prompt><command>use bugs;</command></para> - <note> - <para>Don't forget the <quote>;</quote> at the end of - each line, or you'll be kicking yourself later.</para> - </note> - </listitem> - </orderedlist> - </para> - <section> - <title>Bugzilla Database Tables</title> - <para> Imagine your MySQL database as a series of - spreadsheets, and you won't be too far off. If you use this - command:</para> - <para><prompt>mysql></prompt><command>show tables from bugs;</command></para> - <para>you'll be able to see all the - <quote>spreadsheets</quote> (tables) in your database. It - is similar to a file system, only faster and more robust for - certain types of operations.</para> - <para>From the command issued above, ou should have some - output that looks like this: - <programlisting> -+-------------------+ -| Tables in bugs | -+-------------------+ -| attachments | -| bugs | -| bugs_activity | -| cc | -| components | -| dependencies | -| fielddefs | -| groups | -| keyworddefs | -| keywords | -| logincookies | -| longdescs | -| milestones | -| namedqueries | -| products | -| profiles | -| profiles_activity | -| shadowlog | -| tokens | -| versions | -| votes | -| watch | -+-------------------+ - </programlisting></para> -<literallayout> - - Here's an overview of what each table does. Most columns in each table have -descriptive names that make it fairly trivial to figure out their jobs. - -attachments: This table stores all attachments to bugs. It tends to be your -largest table, yet also generally has the fewest entries because file -attachments are so (relatively) large. - -bugs: This is the core of your system. The bugs table stores most of the -current information about a bug, with the exception of the info stored in the -other tables. - -bugs_activity: This stores information regarding what changes are made to bugs -when -- a history file. - -cc: This tiny table simply stores all the CC information for any bug which has -any entries in the CC field of the bug. Note that, like most other tables in -Bugzilla, it does not refer to users by their user names, but by their unique -userid, stored as a primary key in the profiles table. - -components: This stores the programs and components (or products and -components, in newer Bugzilla parlance) for Bugzilla. Curiously, the "program" -(product) field is the full name of the product, rather than some other unique -identifier, like bug_id and user_id are elsewhere in the database. - -dependencies: Stores data about those cool dependency trees. - -fielddefs: A nifty table that defines other tables. For instance, when you -submit a form that changes the value of "AssignedTo" this table allows -translation to the actual field name "assigned_to" for entry into MySQL. - -groups: defines bitmasks for groups. A bitmask is a number that can uniquely -identify group memberships. For instance, say the group that is allowed to -tweak parameters is assigned a value of "1", the group that is allowed to edit -users is assigned a "2", and the group that is allowed to create new groups is -assigned the bitmask of "4". By uniquely combining the group bitmasks (much -like the chmod command in UNIX,) you can identify a user is allowed to tweak -parameters and create groups, but not edit users, by giving him a bitmask of -"5", or a user allowed to edit users and create groups, but not tweak -parameters, by giving him a bitmask of "6" Simple, huh? - If this makes no sense to you, try this at the mysql prompt: -mysql> select * from groups; - You'll see the list, it makes much more sense that way. - -keyworddefs: Definitions of keywords to be used - -keywords: Unlike what you'd think, this table holds which keywords are -associated with which bug id's. - -logincookies: This stores every login cookie ever assigned to you for every -machine you've ever logged into Bugzilla from. Curiously, it never does any -housecleaning -- I see cookies in this file I've not used for months. However, -since Bugzilla never expires your cookie (for convenience' sake), it makes -sense. - -longdescs: The meat of bugzilla -- here is where all user comments are stored! -You've only got 2^24 bytes per comment (it's a mediumtext field), so speak -sparingly -- that's only the amount of space the Old Testament from the Bible -would take (uncompressed, 16 megabytes). Each comment is keyed to the -bug_id to which it's attached, so the order is necessarily chronological, for -comments are played back in the order in which they are received. - -milestones: Interesting that milestones are associated with a specific product -in this table, but Bugzilla does not yet support differing milestones by -product through the standard configuration interfaces. - -namedqueries: This is where everybody stores their "custom queries". Very -cool feature; it beats the tar out of having to bookmark each cool query you -construct. - -products: What products you have, whether new bug entries are allowed for the -product, what milestone you're working toward on that product, votes, etc. It -will be nice when the components table supports these same features, so you -could close a particular component for bug entry without having to close an -entire product... - -profiles: Ahh, so you were wondering where your precious user information was -stored? Here it is! With the passwords in plain text for all to see! (but -sshh... don't tell your users!) - -profiles_activity: Need to know who did what when to who's profile? This'll -tell you, it's a pretty complete history. - -shadowlog: I could be mistaken here, but I believe this table tells you when -your shadow database is updated and what commands were used to update it. We -don't use a shadow database at our site yet, so it's pretty empty for us. - -versions: Version information for every product - -votes: Who voted for what when - -watch: Who (according to userid) is watching who's bugs (according to their -userid). - - -=== -THE DETAILS -=== - - Ahh, so you're wondering just what to do with the information above? At the -mysql prompt, you can view any information about the columns in a table with -this command (where "table" is the name of the table you wish to view): -mysql> show columns from table; + <para>If you were like me, at this point you're totally clueless about + the internals of MySQL, and if it weren't for this executive order from + the Vice President you couldn't care less about the difference between + a + <quote>bigint</quote> - You can also view all the data in a table with this command: + and a + <quote>tinyint</quote> -mysql> select * from table; + entry in MySQL. I recommend you refer to the MySQL documentation, + available at + <ulink url="http://www.mysql.com/doc.html">MySQL.com</ulink> - -- note: this is a very bad idea to do on, for instance, the "bugs" table if -you have 50,000 bugs. You'll be sitting there a while until you ctrl-c or -50,000 bugs play across your screen. + . Below are the basics you need to know about the Bugzilla database. + Check the chart above for more details.</para> - You can limit the display from above a little with the command, where -"column" is the name of the column for which you wish to restrict information: - -mysql> select * from table where (column = "some info"); - - -- or the reverse of this - -mysql> select * from table where (column != "some info"); + <para> + <orderedlist> + <listitem> + <para>To connect to your database:</para> - Let's take our example from the introduction, and assume you need to change -the word "verified" to "approved" in the resolution field. We know from the -above information that the resolution is likely to be stored in the "bugs" -table. Note we'll need to change a little perl code as well as this database -change, but I won't plunge into that in this document. Let's verify the -information is stored in the "bugs" table: + <para> + <prompt>bash#</prompt> -mysql> show columns from bugs + <command>mysql</command> - (exceedingly long output truncated here) -| bug_status| enum('UNCONFIRMED','NEW','ASSIGNED','REOPENED','RESOLVED','VERIFIED','CLOSED')||MUL | UNCONFIRMED|| + <parameter>-u root</parameter> + </para> - Sorry about that long line. We see from this that the "bug status" column is -an "enum field", which is a MySQL peculiarity where a string type field can -only have certain types of entries. While I think this is very cool, it's not -standard SQL. Anyway, we need to add the possible enum field entry -'APPROVED' by altering the "bugs" table. + <para>If this works without asking you for a password, + <emphasis>shame on you</emphasis> -mysql> ALTER table bugs CHANGE bug_status bug_status - -> enum("UNCONFIRMED", "NEW", "ASSIGNED", "REOPENED", "RESOLVED", - -> "VERIFIED", "APPROVED", "CLOSED") not null; + ! You should have locked your security down like the installation + instructions told you to. You can find details on locking down + your database in the Bugzilla FAQ in this directory (under + "Security"), or more robust security generalities in the MySQL + searchable documentation at + http://www.mysql.com/php/manual.php3?section=Privilege_system + .</para> + </listitem> - (note we can take three lines or more -- whatever you put in before the -semicolon is evaluated as a single expression) + <listitem> + <para>You should now be at a prompt that looks like this:</para> -Now if you do this: + <para> + <prompt>mysql></prompt> + </para> -mysql> show columns from bugs; + <para>At the prompt, if + <quote>bugs</quote> - you'll see that the bug_status field has an extra "APPROVED" enum that's -available! Cool thing, too, is that this is reflected on your query page as -well -- you can query by the new status. But how's it fit into the existing -scheme of things? - Looks like you need to go back and look for instances of the word "verified" -in the perl code for Bugzilla -- wherever you find "verified", change it to -"approved" and you're in business (make sure that's a case-insensitive search). -Although you can query by the enum field, you can't give something a status -of "APPROVED" until you make the perl changes. Note that this change I -mentioned can also be done by editing checksetup.pl, which automates a lot of -this. But you need to know this stuff anyway, right? + is the name you chose in the + <filename>localconfig</filename> - I hope this database tutorial has been useful for you. If you have comments -to add, questions, concerns, etc. please direct them to -mbarnson@excitehome.net. Please direct flames to /dev/null :) Have a nice -day! + file for your Bugzilla database, type:</para> + <para> + <prompt>mysql</prompt> + <command>use bugs;</command> + </para> -=== -LINKS -=== + <note> + <para>Don't forget the + <quote>;</quote> -Great MySQL tutorial site: -http://www.devshed.com/Server_Side/MySQL/ + at the end of each line, or you'll be kicking yourself + later.</para> + </note> + </listitem> + </orderedlist> + </para> - </literallayout> + <section> + <title>Bugzilla Database Tables</title> + + <para>Imagine your MySQL database as a series of spreadsheets, and + you won't be too far off. If you use this command:</para> + + <para> + <prompt>mysql></prompt> + + <command>show tables from bugs;</command> + </para> + + <para>you'll be able to see all the + <quote>spreadsheets</quote> + + (tables) in your database. It is similar to a file system, only + faster and more robust for certain types of operations.</para> + + <para>From the command issued above, ou should have some output that + looks like this: + <programlisting>+-------------------+ | Tables in bugs | + +-------------------+ | attachments | | bugs | | bugs_activity | | cc + | | components | | dependencies | | fielddefs | | groups | | + keyworddefs | | keywords | | logincookies | | longdescs | | + milestones | | namedqueries | | products | | profiles | | + profiles_activity | | shadowlog | | tokens | | versions | | votes | | + watch | +-------------------+</programlisting> + </para> + + <literallayout>Here's an overview of what each table does. Most + columns in each table have descriptive names that make it fairly + trivial to figure out their jobs. attachments: This table stores all + attachments to bugs. It tends to be your largest table, yet also + generally has the fewest entries because file attachments are so + (relatively) large. bugs: This is the core of your system. The bugs + table stores most of the current information about a bug, with the + exception of the info stored in the other tables. bugs_activity: This + stores information regarding what changes are made to bugs when -- a + history file. cc: This tiny table simply stores all the CC + information for any bug which has any entries in the CC field of the + bug. Note that, like most other tables in Bugzilla, it does not refer + to users by their user names, but by their unique userid, stored as a + primary key in the profiles table. components: This stores the + programs and components (or products and components, in newer + Bugzilla parlance) for Bugzilla. Curiously, the "program" (product) + field is the full name of the product, rather than some other unique + identifier, like bug_id and user_id are elsewhere in the database. + dependencies: Stores data about those cool dependency trees. + fielddefs: A nifty table that defines other tables. For instance, + when you submit a form that changes the value of "AssignedTo" this + table allows translation to the actual field name "assigned_to" for + entry into MySQL. groups: defines bitmasks for groups. A bitmask is a + number that can uniquely identify group memberships. For instance, + say the group that is allowed to tweak parameters is assigned a value + of "1", the group that is allowed to edit users is assigned a "2", + and the group that is allowed to create new groups is assigned the + bitmask of "4". By uniquely combining the group bitmasks (much like + the chmod command in UNIX,) you can identify a user is allowed to + tweak parameters and create groups, but not edit users, by giving him + a bitmask of "5", or a user allowed to edit users and create groups, + but not tweak parameters, by giving him a bitmask of "6" Simple, huh? + If this makes no sense to you, try this at the mysql prompt: + mysql> select * from groups; You'll see the list, it makes much + more sense that way. keyworddefs: Definitions of keywords to be used + keywords: Unlike what you'd think, this table holds which keywords + are associated with which bug id's. logincookies: This stores every + login cookie ever assigned to you for every machine you've ever + logged into Bugzilla from. Curiously, it never does any housecleaning + -- I see cookies in this file I've not used for months. However, + since Bugzilla never expires your cookie (for convenience' sake), it + makes sense. longdescs: The meat of bugzilla -- here is where all + user comments are stored! You've only got 2^24 bytes per comment + (it's a mediumtext field), so speak sparingly -- that's only the + amount of space the Old Testament from the Bible would take + (uncompressed, 16 megabytes). Each comment is keyed to the bug_id to + which it's attached, so the order is necessarily chronological, for + comments are played back in the order in which they are received. + milestones: Interesting that milestones are associated with a + specific product in this table, but Bugzilla does not yet support + differing milestones by product through the standard configuration + interfaces. namedqueries: This is where everybody stores their + "custom queries". Very cool feature; it beats the tar out of having + to bookmark each cool query you construct. products: What products + you have, whether new bug entries are allowed for the product, what + milestone you're working toward on that product, votes, etc. It will + be nice when the components table supports these same features, so + you could close a particular component for bug entry without having + to close an entire product... profiles: Ahh, so you were wondering + where your precious user information was stored? Here it is! With the + passwords in plain text for all to see! (but sshh... don't tell your + users!) profiles_activity: Need to know who did what when to who's + profile? This'll tell you, it's a pretty complete history. shadowlog: + I could be mistaken here, but I believe this table tells you when + your shadow database is updated and what commands were used to update + it. We don't use a shadow database at our site yet, so it's pretty + empty for us. versions: Version information for every product votes: + Who voted for what when watch: Who (according to userid) is watching + who's bugs (according to their userid). === THE DETAILS === Ahh, so + you're wondering just what to do with the information above? At the + mysql prompt, you can view any information about the columns in a + table with this command (where "table" is the name of the table you + wish to view): mysql> show columns from table; You can also view + all the data in a table with this command: mysql> select * from + table; -- note: this is a very bad idea to do on, for instance, the + "bugs" table if you have 50,000 bugs. You'll be sitting there a while + until you ctrl-c or 50,000 bugs play across your screen. You can + limit the display from above a little with the command, where + "column" is the name of the column for which you wish to restrict + information: mysql> select * from table where (column = "some + info"); -- or the reverse of this mysql> select * from table where + (column != "some info"); Let's take our example from the + introduction, and assume you need to change the word "verified" to + "approved" in the resolution field. We know from the above + information that the resolution is likely to be stored in the "bugs" + table. Note we'll need to change a little perl code as well as this + database change, but I won't plunge into that in this document. Let's + verify the information is stored in the "bugs" table: mysql> show + columns from bugs (exceedingly long output truncated here) | + bug_status| + enum('UNCONFIRMED','NEW','ASSIGNED','REOPENED','RESOLVED','VERIFIED','CLOSED')||MUL + | UNCONFIRMED|| Sorry about that long line. We see from this that the + "bug status" column is an "enum field", which is a MySQL peculiarity + where a string type field can only have certain types of entries. + While I think this is very cool, it's not standard SQL. Anyway, we + need to add the possible enum field entry 'APPROVED' by altering the + "bugs" table. mysql> ALTER table bugs CHANGE bug_status bug_status + -> enum("UNCONFIRMED", "NEW", "ASSIGNED", "REOPENED", "RESOLVED", + -> "VERIFIED", "APPROVED", "CLOSED") not null; (note we can take + three lines or more -- whatever you put in before the semicolon is + evaluated as a single expression) Now if you do this: mysql> show + columns from bugs; you'll see that the bug_status field has an extra + "APPROVED" enum that's available! Cool thing, too, is that this is + reflected on your query page as well -- you can query by the new + status. But how's it fit into the existing scheme of things? Looks + like you need to go back and look for instances of the word + "verified" in the perl code for Bugzilla -- wherever you find + "verified", change it to "approved" and you're in business (make sure + that's a case-insensitive search). Although you can query by the enum + field, you can't give something a status of "APPROVED" until you make + the perl changes. Note that this change I mentioned can also be done + by editing checksetup.pl, which automates a lot of this. But you need + to know this stuff anyway, right? I hope this database tutorial has + been useful for you. If you have comments to add, questions, + concerns, etc. please direct them to mbarnson@excitehome.net. Please + direct flames to /dev/null :) Have a nice day! === LINKS === Great + MySQL tutorial site: + http://www.devshed.com/Server_Side/MySQL/</literallayout> </section> </section> </section> <section id="granttables"> <title>MySQL Permissions & Grant Tables</title> - + <note> - <para>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.</para> - <para> - 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. - </para> + <para>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.</para> + + <para>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.</para> </note> - - <literallayout> -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 PRIVILEGES - 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. - </literallayout> + <literallayout>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 PRIVILEGES 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.</literallayout> </section> - </appendix> <!-- Keep this comment at the end of the file @@ -570,3 +458,4 @@ sgml-shorttag:t sgml-tag-region-if-active:t End: --> + |