Dear Russ,
I'll tell you what you need to know if you can get me a job in your company...Ha! Ha! What the heck, I'm feeling a streak of altruism coming over me.
I hope you don't feel I'm being patronizing but I am assuming that you are an absolute beginner to MySQL privilege tables, as such, I will start my explanation from square one. If you already know this stuff then sorry for the lengthy explanation!! As a systems administrator, I assume you have access to the MySQL server and it's associated folders and files.
The folder that contains all the databases is called 'data' (by default). The privilege tables (the tables that store the user names and passwords for the different kinds of permission....e.g., for super user, "middle management", "worker drone"...my own terminology, but I think you understand what I mean), are found in the database called 'mysql', in the 'data' folder. By the way, the folder 'data' is a sub-folder of a folder also called 'mysql'....not very original!! So, the entire structure is \mysql\data\mysql\privilege_tables.
The privilege tables that exist in the 'mysql' database are:
(1) user;
(2) db;
(3) host;
(4) tables_priv; and,
(5) columns_priv
The 'user', 'db'and 'table_priv' are probably the most important of the five privilege tables. Most or all of the following fields exist in these 3 privilege tables: 1) Host, 2) User, 3) Password, 4) Select_priv, 5) Insert_priv, 6) Update_priv, 7) Delete_priv, 8) Create_priv, 9) Drop_priv, 10) Reload_priv, 11) Shutdown_priv, 12) Process_priv, 13) File_priv, 14) Grant_priv, 15) Reference_priv, 16) Index_priv,17) Alter_priv, 18) Db (short for database but I'm sure you realized that....this field found only in 'db' and 'table_priv' tables), 19) Table_name (this field only found in 'table_priv' table)
Let's assume we have 4 users: Russ (coincidentally, the sys admin), Jon and Phoebe (in middle management), Alex (the company drone/slave). All usernames and passwords (for Russ, Jon, Phoebe and yes, even our worker bee, Alex) are entered in the 'user' table. To make Russ the superuser, he will have a 'Y' (for "yes") entered in all the above fields of the 'user' table (you can, of course, create different hierachy of superusers by entering 'N' for certain fields of user table). On the other hand, Jon, Phoebe and Alex will have 'N' in all the above fields in the 'user' table.
The person who has a username and password entered into the 'db' can have access and control over one or more databases (and therefore, all the tables in a particular database). Say, for example, Jon has access to the databases," customer_DB" and "product_DB", while Phoebe only has access to DB1. You would enter Jon's username (the Password field does not exist in this table) twice in this table and under the field 'Db', enter customer_DB and product_DB. If Phoebe, also has access to the database product_DB and all the tables in it, then her username is associated with the respective database.....an abridged version of the 'db' table looks like this:
+----------------------------------+---------------------- +
| Db | User |
+---------------------------------- +---------------------- +
| customer_DB | jsmith_mm |
| product_DB | jsmith_mm |
| product_DB | pcates_mm |
+---------------------------------- +---------------------- +
Like the 'user' table, Phoebe's and Jon's privileges for each database can be set according to whether the privilege field (for example, Insert_priv) has a 'Y' or 'N'.
Since we don't want poor ol' Alex to be overwhelmed with too much power...I mean, privileges....his user name will only be enter in the table_priv table. I hear his specialty is footwear so he will have access to only the "footwear" table in the product_DB database.
+----------------------------------+---------------------- +---------------------------------- +
| Db | User | Table_name |
+---------------------------------- +---------------------- +---------------------------------- +
| product_DB | alex_drone | footwear |
+---------------------------------- +---------------------- +---------------------------------- +
I hate it when someone tells me to look in the manual for info at these discussion forums because I can never seem to make out what the manual is trying to say. But I find myself, at this point, having to tell you that if you need more details about the other privilege tables ('host' and 'column_priv') and the privilege fields, then please look in the manual. Most of what I've told you here is found in the manual that comes with the MySQL server.....it's not a hardcopy but an html file that should be in a folder called Docs (mysql\Docs).
I hope this was helpful....if there is anything else you wish to know (I don't guarantee I will have the answers), please feel free to write to me at chongallen@hotmail.com.
Later,
Allen.
P.S. Don't forget to put in a good word with your people in HR...I really need a job....why do you think I have all this time to write such a lengthy thread. Ha! Ha!
