Working with MySQL Security

MySQL security isn't as involved as SQL Server, Oracle, or DB2 security. MySQL doesn't support roles, and it has a limited range of security options that can be set. You'll take a brief look at them here—please consult the MySQL online documentation for complete and more recent information.

MySQL user rights are stored in a series of security data tables known as grant tables, which are automatically created when MySQL is installed.

There are a total of six grant tables: user, tables_priv, columns_priv, db, func, and host. Each table serves a different purpose and can be manually updated in order to grant user privileges or add and remove users. You should consult the MySQL documentation for a description of each grant table.

It's unlikely that somebody would want to modify these tables by hand. If you don't believe me, here's an INSERT command that adds a new user named Alice@localhost with the password simplepass:

USE mysql
INSERT INTO user VALUES('localhost','Alice',PASSWORD('simplepass'),'Y','Y',
       'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
FLUSH PRIVILEGES;

This adds a new user named Alice@localhost to the database system.

The 'Y' fields in this statement relate to the available privileges, but rather than learn what each one stands for, in just a moment you'll learn how to set the same privileges with a simpler method: using the GRANT command. Before you move on, if you look at this INSERT command, you can clearly see that passwords aren't stored in clear text. Instead, the password is encrypted with the PASSWORD function and stored in an encrypted form. Each time the user attempts to log in, the entered password is encrypted again using the same algorithm, and the result is compared to the value in the database. This way, somebody gaining access to the database (or even database administrators) can't obtain the password by reading the user table (the encryption process used by PASSWORD isn't reversible).

Note that the FLUSH PRIVILEGES command is required after any change to the grant tables because although these are loaded when MySQL starts, they aren't reloaded automatically when security information changes.

Okay, so it's pretty obvious that you won't be adding users by inserting records into the user table. The alternative to this is, as we said before, the GRANT command, which is much simpler. GRANT is primarily used to accord privileges to users, but if the user getting the privileges doesn't exist, the user is automatically created.

The syntax of GRANT is similar to what you've seen with SQL Server or Oracle. One interesting feature is that it also allows you to set the user's password:

GRANT <privilege type> ON <resource> TO <username> [IDENTIFIED BY <passwd>];

A FLUSH PRIVILEGES call is still required, even when granting privileges using GRANT or revoking privileges with REVOKE. This tells MySQL to read again security information from its data tables.

In order to accord SELECT permissions for all the tables in the InstantUniversity database to Alice@localhost, you would type the following:

GRANT SELECT ON InstantUniversity.*
TO Alice@localhost IDENTIFIED BY 'simplepassword';
FLUSH PRIVILEGES;

MySQL also supports the WITH GRANT option, which allows the grantees to pass along the rights they receive:

GRANT SELECT ON InstantUniversity.*
TO Alice@localhost IDENTIFIED BY 'simplepassword' WITH GRANT OPTION;

Note the notation used to express all the tables in the test database: InstantUniversity.*. You can also grant privileges on all databases and all their tables using the *.* wildcard. To grant all possible privileges (including to grant privileges to other users) to Alice@localhost , you use the following command:

GRANT ALL ON *.* TO Alice@localhost;

The following command gives INSERT privileges to the Friend table of the Test database:

GRANT INSERT ON InstantUniversity.Student TO Alice@localhost;

To revoke user rights, you use—as you've probably guessed already—the REVOKE command. REVOKE works just like GRANT and has a similar syntax, except you use FROM instead of TO. The following example revokes from Alice@localhost the rights to drop the Friend table in Test database:

REVOKE DROP ON InstantUniversity.Student FROM Alice@localhost;

To revoke from Alice@localhost the rights to drop any objects from InstantUniversity, you use this command:

REVOKE DROP ON InstantUniversity.* FROM Alice@localhost;

If you want to revoke the rights to drop any database and any object from any database, you use the following command:

REVOKE DROP ON *.* FROM Alice@localhost;

Although GRANT can be used to create user accounts, REVOKE doesn't delete user accounts. The only way to remove MySQL accounts is manually delete the user from the user table. Here's an example:

DELETE FROM mysql.user WHERE user='Alice' AND host='localhost';

Notice that you prefixed the user table with the database in which it resides. This is required only if mysql isn't the currently selected database.

Finally, if you want to see which privileges a particular user has in the system, you use the SHOW GRANTS FOR command. Here's an example:

SHOW GRANTS FOR Alice@localhost;