<<

. 99
( 132 .)



>>





Other Utilities
Please check the /bin directory for other utilities that come with MySQL. If you are
using 3.23 or 4.x you will want to look at myisamchk and myisampack. The first
repairs corrupted tables and the second ensures that tables are set up as efficiently
as possible. These utilities only work with the MyISAM tables. If you are using 3.22
or newer you will need to use of the isamchk utility, which operates on the ISAM
tables used in this version of MySQL. And, of course, there™s MySQL Control Center
(MySQLCC) for those who prefer it.
Appendix E

MySQL User
Administration
THIS to work with MySQL™s permissions tables, which con-
APPENDIX TEACHES YOU
trol permissions in MySQL.
Administration of any relational database management system (RDBMS) requires
some work. Each system presents its own unique methods for administration, and
you can expect difficulties when it comes to tasks like adding and deleting user
accounts, backing up, and assuring security. Administering MySQL isn™t especially
difficult, but it can be a bit bewildering at first.
This book focuses on applications development, not server administration. Thus,
extensive details on administration are beyond the scope of this book. If you are
responsible for the backup and security of your server, you should delve deep into
the MySQL online manual at http://www.mysql.com/documentation/.
For the purposes of this book, and we hope also for you, the application devel-
oper, it is enough to know a bit about user administration and the methods for
assigning rights for users.



grant Tables
MySQL user rights are stored in a series of tables that are automatically created
with the MySQL installation. These tables are kept in a database called mysql. If
you start up the MySQL daemon (with mysqld or, better, mysqld_safe) and the
MySQL monitor (with mysql), and run the query show databases just after instal-
lation, you see two databases, test and mysql.
Running the show tables query on the mysql database lists the tables that store
user permissions.

mysql> use mysql

Database changed
mysql> show tables;
+-----------------+
| Tables in mysql |
+-----------------+
| columns_priv |
597
| db |
598 Part V: Appendixes

| func |
| host |
| tables_priv |
| user |
+-----------------+
6 rows in set (0.00 sec)

Each of these tables corresponds to a level of access control. You can create any
number of users, and users can be allowed access from any number of hosts. For
each user/host combination you can grant access to an entire database, to specific
tables within a database, or to a number of columns within a table. Additionally,
these tables grant administrative privileges. Users can be given permission to add
and drop databases or permission to grant other users permissions.
In practice you want to grant no more permissions than necessary. You want to
protect your data from the overzealous and the incompetent. The best way to do
that with MySQL is to use the proper grant table when assigning rights, keeping the
following in mind:

— Rights are granted in a hierarchical way.

— Rights granted in the user table are universal. If a user is granted drop
privileges in the user table, that user is able to drop any table in any
database in that MySQL installation.
— The db table grants privileges on a database-specific basis. Using this
table, you can grant rights for an entire database.
— For any one table or set of tables, make use of the tables_priv table.

— Finally, the columns_priv table enables you to grant rights on specific
columns within a table. If you don™t need to grant rights to an entire
table, see that rights are assigned in the columns_priv table.

Recent releases of MySQL make use of a couple of very convenient commands
that make creating users and assigning rights fairly easy. We™ll discuss these com-
mands after a brief look at the user, db, tables_priv, and columns_priv tables.


Remember, though, that managing user privileges manually is generally a
bad idea. As in administering operating systems, it™s better to define groups
and manage rights through those groups. Even if you have a group of one,
that™s the better way to go. Why? Because there™s less chance of someone
being forgotten and retaining privileges he or she should not have.
Appendix E: MySQL User Administration 599


The user table
Every user who needs to get at MySQL must be listed in the user table. Rights might
be granted elsewhere, but without a listing here the user is refused a connection to
the database server. Here is the listing of columns in the user table:

mysql> show columns from user;
----------------- -------------------- ------ ----- ------- -----
| Field | Type | Null | Key |Default|Extra|
----------------- -------------------- ------ ----- ------- -----
| Host | varchar(60) binary | | PRI | | |
| User | varchar(16) binary | | PRI | | |
| Password | varchar(16) binary | | | | |
| 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 | |
| Reload_priv | enum(˜N™,™Y™) | | |N | |
| Shutdown_priv | enum(˜N™,™Y™) | | |N | |
| Process_priv | enum(˜N™,™Y™) | | |N | |
| File_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 | |
| Show_db_priv | enum(˜N™,™Y™) | | |N | |
| Super_priv | enum(˜N™,™Y™) | | |N | |
| Create_tmp | enum(˜N™,™Y™) | | |N | |
| _table_priv | | | | | |
| Lock_tables_priv| enum(˜N™,™Y™) | | |N | |
| Execute_priv | enum(˜N™,™Y™) | | |N | |
| Repl_slave_priv | enum(˜N™,™Y™) | | |N | |
| Repl_client_priv| enum(˜N™,™Y™) | | |N | |
| ssl_type | enum(˜™,™ANY™ | | | | |
| | ,™X509™ | | | | |
| | ,™SPECIFIED™) | | | | |
| ssl_cipher | blob | | | | |
| x509_issuer | blob | | | | |
| x509_subject | blob | | | | |
| max_questions | int(11) unsigned | | |0 | |
| max_updates | int(11) unsigned | | |0 | |
| max_connections | int(11) unsigned | | |0 | |
----------------- -------------------- ------ ----- ------- -----
600 Part V: Appendixes

As you have seen, the PHP mysql_connect() function takes three arguments:
username, host, and password. In the preceding listing you can see the corre-
sponding field names. MySQL identifies a user by the combination of username and
host. For instance, user jay can have a different set of rights for each host that he
uses to connect to MySQL. If you or your PHP scripts are accessing MySQL from the
local machine, you usually assign a host of localhost.
The other columns are intuitively named. As you can see, most of the columns
allow only Y or N as column values. As we mentioned earlier, any of these rights
that are set to Y are granted to every table of every database. Most of the columns™
names correspond to SQL statements (such as delete, create, and so forth).
The user table also contains a set of columns that grant administrative rights.
These columns are covered in detail in the “GRANT and REVOKE Syntax” section of
the MySQL documentation. The following is a brief explanation of the meanings of
some of these columns. If you are security-minded, grant these rights sparingly.

— File_priv ” If granted, this privilege allows the database server to read
and write files from the file system. You most often use it when loading
a file into a database table.
— Grant_priv ” A user with this right is able to assign his or her privileges
to other users.
— Process_priv ” This right gives a user the ability to view and kill all run-
ning processes and threads.
— Reload_priv ” Most of the privileges granted by this column are not cov-
ered 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.


The db table
For database-specific permissions, the db table is where you do 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) binary | |PRI| | |
| Db | char(64) binary | |PRI| | |
| User | char(16) binary | |PRI| | |
| Select_priv | enum(˜N™,™Y™) | | |N | |
| Insert_priv | enum(˜N™,™Y™) | | |N | |
Appendix E: MySQL User Administration 601

| 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 | |
| Create_tmp_table_priv| enum(˜N™,™Y™) | | |N | |
| Lock_tables_priv | enum(˜N™,™Y™) | | |N | |
---------------------- ----------------- ------ --- ------- -----

The db table works like the user table, except that permissions granted here work
only 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, we show only the
tables_priv table.

mysql> show columns from tables_priv;
------------- ----------------- ------ ----- --------- -------
| Field | Type | Null | Key | Default | Extra |
------------- ----------------- ------ ----- --------- -------
| Host | char(60) binary | | PRI | | |
| Db | char(64) binary | | PRI | | |
| User | char(16) binary | | PRI | | |
| Table_name | char(60) binary | | 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™) | | | | |
------------- ----------------- ------ ----- --------- -------
602 Part V: Appendixes

For users who get access only to a table or set of tables within a database, the
exact rights are 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 are
crammed into these two cells.


At a couple of points in this book we advise against using the set column
type. In fact, the db table is a good example of a place where using set
makes sense. The column has few potential values, and the number of
potential values is not likely to change.




grant and revoke Statements
Because the preceding tables 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 like the following:

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™)

Then you™d need to grant specific rights with another insert statement to
another table.
If you are thinking you can script these functions with a Web front end, that is
definitely a possibility. But you want to be very careful, because the script would
have the equivalent of root access to the database, which could be very unsafe.

<<

. 99
( 132 .)



>>