4.4.1.1. Database creation

Your database server is useless to you unless it actually has databases to serve. Using mysqladmin, you can create new databases:

mysqladmin -p create DATABASENAME

4.4.1.2. Database destruction

mysqladmin -p drop DATABASENAME

4.4.1.4. Server status

Uptime: 395  Threads: 1  Questions: 14  Slow queries: 0  
Opens: 10 Flush tables: 1  Open tables: 6

The values you see in the mysqladmin status output have the following meanings:

The mysqladmin status command also provides values for memory in use and maximum memory used if MySQL was compiled with the --with-debug option.

If you are looking for some more general, static information, then mysqladmin version is the command you are looking for. It provides the following output:

bin/mysqladmin  Ver 7.8 Distrib 3.22.17, for sun-solaris2.6 on sparc
TCX Datakonsult AB, by Monty

Server version          3.22.17
Protocol version        10
Connection              Localhost via Unix socket
Unix socket             /tmp/mysql.sock
Uptime:                 23 min 58 sec

Threads: 1  Questions: 15  Slow queries: 0  Opens: 10  Flush tables: 1  Open tables: 6

The last line of information is, of course, identical to the information you saw from mysqladmin status. The rest of the display is entirely new.

Two other commands, mysqladmin variables and mysqladmin extended-status, offer more information.

+----+------+-----------+------+-----------+------+-------+------+
| Id | User | Host      | db   | Command   | Time | State | Info |
+----+------+-----------+------+-----------+------+-------+------+
| 920| joe  | client.com|mydata| Sleep     | 0    |       |      |
| 939| root | localhost |      | Processes | 0    |       |      |
+----+------+-----------+------+-----------+------+-------+------+

This output tells you exactly what each thread is doing. The values in the display have the following meaning:

MySQL supports one more human-readable log. When you start MySQL with the -- log-update option, MySQL will create a file with the name HOSTNAME.# where HOSTNAME is the name of the server machine and # is a unique number. This log will hold all changes to database tables. The log appears as SQL so that the operations can be replicated in another database server.

4.4.1.5. Server shutdown

The following command will perform a clean shutdown of the MySQL database server:

mysqladmin -p shutdown

This command is the most orderly way to shut down the server. If you started MySQL with safe_mysqld and try using some other method for shutting down the server, safe_mysqld will just start up a new instance of the server. One can also shut down the server safely with the traditional Unix kill command. But avoid using the drastic kill-9 command.

4.4.1.6. Command line options for mysqladmin

The mysqladmin utility is a very rich tool with a handful of command line options. Its general format is

mysqladmin OPTIONS COMMAND1 COMMAND2 ... COMMANDn

In other words, you can issue multiple commands at one time with the mysqladmin utility. Just for grins, you could do

mysqladmin -p create silly drop silly

This command will both create and drop the database "silly" in one shot. The following is a list of commands you can send to mysqladmin:

In addition to the commands it supports, it also supports the following options:

4.4.3.1. The user table

Table 4-1. The User Table

Select_priv

The ability to perform SELECT statements

Insert_priv

The ability to perform INSERT statements

Update_priv

The ability to perform UPDATE statements

Delete_priv

The ability to perform DELETE statements

Create_priv

The ability to perform CREATE statements or to create databases

Drop_priv

The ability to perform DROP statements or to drop databases

Reload_priv

The ability to reload access information via mysqladmin reload

Shutdown_priv

The ability to shutdown the server via mysqladmin shutdown

Process_priv

The ability to manage server processes

File_priv

The ability to read and write files using commands like SELECT INTO OUTFILE and LOAD DATA INFILE

Grant_priv

The ability to grant your privileges to others

Index_priv

The ability to create or drop indices

Alter_priv

The ability to perform the ALTER TABLE statement

INSERT INTO user (Host, User, Password, Select_priv, 
                  Insert_priv, Update_priv, Delete_priv)
VALUES ('%', 'bob', password('mypass'), 'Y', 'Y', 'Y','Y')
INSERT INTO user (Host, User, Password, Select_priv)
VALUES ('athens.imaginary.com', 'jane', '', 'Y')
INSERT INTO user(Host, User, Password)
VALUES ('%', 'nobody', '')
INSERT INTO user (Host, User, Password, Select_priv,
                  Insert_priv, Update_priv, Delete_priv)
VALUES ('athens.imaginary.com', 'nobody', 
        password('thispass'), 'Y', 'Y', 'Y', 'Y')

The first user we created, "bob," can come from any host and can SELECT, INSERT, UPDATE, and DELETE records. The second user, "jane," can connect from "athens.imaginary.com." has no password, and can only execute SELECT statements. The third user is "nobody" from any host. This user is able to do absolutely nothing. The final user is "nobody" from "athens.imaginary.com" and can SELECT, INSERT, UPDATE, and DELETE records like "bob."

So how does MySQL do matching? Perhaps you noticed above that a given name could actually match several records. For example, "nobody@athens.imaginary.com" matches "nobody@%" and "nobody@athens.imaginary.com." Before checking the user table, MySQL sorts the data in the following manner:

1. MySQL first matches hosts that do not contain wildcards followed by hosts with wildcards. Empty Host fields are treated like they contain "%."

2. When hosts are the same, users without wildcards are checked before users with wildcards. As with Host, an empty User field is treated as if it contains "%."

3. The first match encountered is the only match considered.

In the earlier example, the user would be verified against "nobody" from "athens.imaginary.com" because "athens.imaginary.com" is sorted before "%." Because hosts are sorted before users, the values of any host from which you are connecting will take precedence over any specific privileges you might have. For example, if the user table contains the following entries:

If "jane" connects from "athens.imaginary.com," the privileges associated with "athens.imaginary.com" are the privileges that MySQL will use.

4.4.3.2. The db table

Table 4-1. The db Table

The most effective use of the user table is to create entries for each user in the user table with no permissions. This scheme enables a user to connect to the server, but do nothing else. The exception would be anyone who should be a server administrator. Everyone else should gain their permissions from the db table. Every user does have to appear in the user table, or they will not be allowed to connect to the database.

The same rules for user and host matching on the User and Host columns from the user table applies to this table -- with a bit of a twist. A blank Host field will prompt MySQL to look for an entry matching the user's host in the host table. If no such match is found, MySQL denies the operation. If an entry is found, MySQL calculates the permission as the intersection of those found in the db and host entries. In other words, both entries must have a `Y' in them or access is denied.

4.4.3.3. The host table

Table 4-1. The Host Table

4.4.3.5. The stages of access control

You have had a look at the players in MySQL security. Now we need to put the players together and demonstrate how they are applied to real situations. MySQL divides access control into two stages. The first stage is connection. You must be able to connect to the server before you can do anything else.

Connection involves two checks. First, MySQL checks to see that the user name and host under which you are connecting has a corresponding entry in the user table. Matching an entry for you is based on the rules for matching we discussed earlier. If MySQL fails to find a match, your connection request is denied. If it finds a match and that match has a nonblank Password field entry, you must specify and match that password. Failure to match the password results in the denial of your connection request.

Once connected, the MySQL server enters the request verification stage. At this point, any specific requests you make are matched against your privileges. MySQL may take these privileges from any of the user, db, host, tables_priv, or columns_priv tables. If there is a match with the user table and the user table has a positive permission, then the operation is immediately allowed. Otherwise, MySQL looks for matches in the following tables in the following order:

1. db

2. tables_priv

3. columns_priv

If the db table has a positive entry, the operation is allowed and no further checking occurs. If the entry is negative, then MySQL checks with all matching tables_priv entries. If, for example, the operation is a SELECT that joins two tables, then the user must have positive entries for both tables in that database in the tables_priv table. If one or more of the entries is negative or nonexistent, then MySQL will perform the same logic for all of the columns in the columns_priv table.

4.4.3.6. The mysqlaccess utility

Access-rights
for USER 'nobody', from HOST 'isp.com', to DB 'mydata'
        +-----------------+---+ +-----------------+---+
        | Select_priv     | Y | | Drop_priv       | N |
        | Insert_priv     | Y | | Reload_priv     | N |
        | Update_priv     | Y | | Shutdown_priv   | N |
        | Delete_priv     | Y | | Process_priv    | N |
        | Create_priv     | N | | File_priv       | N |
        +-----------------+---+ +-----------------+---+
BEWARE:  Everybody can access your DB as user 'nobody'
      :  from host 'isp.com' WITHOUT supplying a password.
      :  Be very careful about it!!

The following rules are used:
 db    : 'isp.com','mydata','nobody','Y','Y','Y','Y','N',
'N','N','N','N','N'
 host  : 'Not processed: host-field is not empty in db-
table.'
 user  : '%','nobody','','N','N','N','N','N','N','N','N','N'

As you can see, even if you understand MySQL security fully, mysqlaccess can be a valuable tool in auditing your server's security.

4.4.3.7. Making changes