◆ Process_priv — This right gives a user the ability to view and kill all running processes and threads.
3537-4 AppD.f.qc 12/15/00 15:26 Page 442
442
Part V: Appendixes
◆ Reload_priv — Most of the privileges granted by this column are not covered in the course of this book. This privilege is most often used with the mysqladmin utility to perform flush commands. See the MySQL online
manual for more details.
◆ Shutdown_priv — Allows the user to shut down the daemon using
mysqladmin shutdown.
db table
For database-specific permissions, the db table is where you will be doing most of your work. The following is a list of columns from the db table:
mysql> show columns from db;
+-----------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------+
| Host | char(60) | | PRI | | |
| Db | char(32) | | PRI | | |
| User | char(16) | | PRI | | |
| Select_priv | enum(‘N’,’Y’) | | | N | |
| Insert_priv | enum(‘N’,’Y’) | | | N | |
| Update_priv | enum(‘N’,’Y’) | | | N | |
| Delete_priv | enum(‘N’,’Y’) | | | N | |
| Create_priv | enum(‘N’,’Y’) | | | N | |
| Drop_priv | enum(‘N’,’Y’) | | | N | |
| Grant_priv | enum(‘N’,’Y’) | | | N | |
| References_priv | enum(‘N’,’Y’) | | | N | |
| Index_priv | enum(‘N’,’Y’) | | | N | |
| Alter_priv | enum(‘N’,’Y’) | | | N | |
+-----------------+---------------+------+-----+---------+-------+
13 rows in set (0.01 sec)
mysql>
This works like the user table, except that permissions granted here will only work for the database specified in the db column.
tables_priv and columns_priv
These two tables look pretty similar, and to save a bit of space, I’ll only show the tables_priv table.
mysql> show columns from tables_priv;
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
3537-4 AppD.f.qc 12/15/00 15:26 Page 443
Appendix D: MySQL User Administration
443
+-------------+---------------+------+-----+---------+-------+
| Host | char(60) | | PRI | | |
| Db | char(60) | | PRI | | |
| User | char(16) | | PRI | | |
| Table_name | char(60) | | PRI | | |
| Grantor | char(77) | | MUL | | |
| Timestamp | timestamp(14) | YES | | NULL | |
| Table_priv |set(‘Select’,’Insert’,’Update’, | |
| | ‘Delete’,’Create’,’Drop’,’Grant’, | |
| | ‘References’,’Index’,’Alter’) | |
| | | | | | |
| Column_priv | set(‘Select’,’Insert’, | | |
| | ‘Update’,’References’) | | |
+-------------+---------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
For users who only get access to a table or set of tables within a database, the exact rights will be stored in this table. Note the use of the set column type for table_priv and column_priv tables. All of the rights available to a specific user will be crammed into these two cells.
NOTE
At a couple of points in the course of this book, we advised against using the set column type. In fact the db table is a good example of where set makes sense. There are few potential values for the column and the number of potential values is not likely to change.
Grant and Revoke Statements
Since the tables discussed above are regular MySQL tables, you can alter them with the SQL statements you are already familiar with. But consider the nightmare that would be. If you wanted to grant a new user table-level access, you would first need to insert a row into the user database with an SQL statement that looked like this: INSERT INTO user (Host, User, Password, Select_priv, Insert_priv,
Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv,
Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv) VALUES (‘localhost’, ‘juan’, ‘password’,
‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’,
‘N’)
3537-4 AppD.f.qc 12/15/00 15:26 Page 444
444
Part V: Appendixes
Then you’d need to grant specific rights with another insert statement to another table.
If you are thinking you could script these functions with a Web front end, that is definitely a possibility. But you’d want to be very careful, because the script would have the equivalent of root access to the database, which could be very unsafe.
Happily, the MySQL has some built-in statements that make user administration a whole lot easier. Knowing the grant and revoke statements will save you from having to send individual queries.
Grant