10.3 Grant and Revoke

The recommended way to change privileges in MySQL is to use the GRANT and REVOKE commands. They provide a simple syntax for making most changes without needing to understand the underlying grant tables and their various matching rules.

There's nothing to prevent you from using normal INSERT, UPDATE, and DELETE queries to manipulate the grant tables directly. In fact, many long-time MySQL users still find it easier to do so. But as MySQL continues to evolve, it is likely that the grant tables will change. Columns may be added, renamed, or removed (it has happened before). There may even be additional tables involved in the process at some point. By sticking to the GRANT and REVOKE commands, you can insulate yourself from those changes. It is also very easy to make very bad mistakes when modifying the table directly. The GRANT and REVOKE commands will continue to be the recommended way of managing privileges.

If you do decide to manipulate the grant tables by hand rather than using the GRANT and REVOKE commands, you must tell MySQL that you've done so by issuing a FLUSH PRIVILEGES command. MySQL caches the information contained in the grant tables so that it doesn't have to go through the expensive process of reading and interpreting them each time it needs to check a privilege. As a result, any changes you make with an INSERT or other generic command will go unnoticed until the server is restarted or a FLUSH PRIVILEGES is executed.

10.3.1 Grant Mechanics

With an understanding of the layout of the grant tables, let's walk through some examples to see exactly how the tables are affected. We'll create a fictional organization, widgets.example.com, and see what kind of access various individuals within that organization might require. Each example is intended to demonstrate how you might use various GRANT commands to set up real-world permissions.

10.3.1.1 System administrator account

In most large organizations, you have two important administrators. The system administrator manages the "physical" server including the operating system, Unix login accounts, etc., and the database administrator concentrates on the database server.

You may want to restrict the access of the root account to the database, for various reasons. You can accomplish this by issuing the following command:

mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'root'@'localhost';

10.3.1.2 Database administrator account

When more than one DBA has access to MySQL, it's a good idea to give each one a separate account rather than having them share the root account. This setup provides greater accountability, and you don't have to give out the root password if you'd rather not. widgets.example.com has two database administrators; let's call them Raymond and Diana.

To give the user raymond full privileges on the server when connecting from any host, a GRANT command like this does the trick:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'raymond'@'%' IDENTIFIED BY '27skuw!'

    -> WITH GRANT OPTION;

Behind the scenes, that command adds a record to the user table:

mysql> SELECT * FROM user WHERE User = 'raymond' \G

*************************** 1. row ***************************

           Host: %

           User: raymond

       Password: 11417e201753de4b

    Select_priv: Y

    Insert_priv: Y

    Update_priv: Y

    Delete_priv: Y

    Create_priv: Y

      Drop_priv: Y

    Reload_priv: Y

  Shutdown_priv: Y

   Process_priv: Y

      File_priv: Y

     Grant_priv: Y

References_priv: Y

     Index_priv: Y

     Alter_priv: Y

You might decide that while Raymond travels around the world and needs to be able to get access from anywhere,^[2] Diana needs access from only the office, in which case you would execute a command like this one:

^[2] Obviously, opening up MySQL from anywhere in the world is a really bad idea, and Raymond should come up with a better way to connect to the server.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'diana'@'%.widgets.example.dom' IDENTIFIED BY 

    -> 'yu-gi-oh' WITH GRANT OPTION;

This would limit Diana's access such that she connects only if she is coming from a machine within the widgets.example.com domain, which hopefully corresponds to a trusted machine. For even higher security, it might make sense to change the %.widgets.example.com clause to use an IP address or IP network, specifying the office Diana works in, perhaps, or possibly only her workstation.

Of course, Diana has the ability to alter her own privileges, but there's not a lot you can do about that.

10.3.1.3 Average employee account

The average widgets.example.com employee is a customer service representative, entering orders taken over the phone, updating existing orders, etc. Tera, a customer service representative, logs into a custom application that passes her username and password through to the MySQL server for any activity. The command to create Tera's account might look like this:

mysql> GRANT INSERT,UPDATE PRIVILEGES ON widgets.orders

    -> TO 'tera'@'%.widgets.example.com'

    -> IDENTIFIED BY 'rachel!94';

Tera can provide her username and password to the application, and she can add new orders or update existing orders, but she can't go back and delete entries, etc. In this configuration, every employee of widgets.example.com that needs to enter an order into the system has her own individual database access. Instead of a shared "application account," each employee's transactions are logged under her own username, and each employee has only the privileges she needs to enter or work with orders.

Notice the lack of a WITH GRANT OPTION clause. There's no need to give Tera the ability to assign privileges to anyone else.

10.3.1.4 Logging, write-only access

It is common to use MySQL as the backend for logging various types of data. Whether you have Apache recording every request in MySQL or you're keeping track of when your doorbell rings, logging is a write-only application that probably needs to write to only a single database or table.

To set up write-only access for logging, you might use a command like this:

mysql> GRANT INSERT ON logs.* TO 'logger'@'%.widgets.example.com' 

    -> IDENTIFIED BY 'blah0halb';

This command adds a record to the user table, of course:

mysql> SELECT * FROM user WHERE User = 'logger' \G

*************************** 1. row ***************************

           Host: %.widgets.example.com

           User: logger

       Password: 2d502d346553f4f3

    Select_priv: N

    Insert_priv: N

    Update_priv: N

    Delete_priv: N

    Create_priv: N

      Drop_priv: N

    Reload_priv: N

  Shutdown_priv: N

   Process_priv: N

      File_priv: N

     Grant_priv: N

References_priv: N

     Index_priv: N

     Alter_priv: N

However, this command grants no privileges. The only purpose of the record here is to allow the user to connect from any host and to provide a password.

Because we specified a privilege that applies to a specific database, the interesting bits were added to the db table:

mysql> SELECT * FROM db WHERE User = 'logger' \G

*************************** 1. row ***************************

           Host: %.widgets.example.com

             Db: logs

           User: logger

    Select_priv: N

    Insert_priv: Y

    Update_priv: N

    Delete_priv: N

    Create_priv: N

      Drop_priv: N

     Grant_priv: N

References_priv: N

     Index_priv: N

     Alter_priv: N

As expected, the only privilege granted by this record is the insert privilege—just what we wanted.

10.3.1.5 Operations and monitoring

There are times when you want to give someone (a network operations center) or some thing (monitoring software) access to your MySQL server to check its health, kill long-running queries, or even shut down the server. Let's say that the widgets.example.com network operations center has a staff that works 24/7 monitoring various processes and services, including the health of the MySQL server.

The Network Operation Center's (NOC) user account needs to be able to connect, issue the KILL and SHOW commands, and shut down the server. Further, because this ability is very powerful, it has to be limited to a single host, so that even if the password is somehow compromised, the unauthorized user would have to be in the NOC do anything.

This statement accomplishes that:

mysql> GRANT PROCESS, SHUTDOWN on *.* 

    -> TO 'noc'@'monitorserver.noc.widgets.example.com' 

    -> IDENTIFIED BY 'q!w@e#r$t%';

The result is in a new user row:

mysql> SELECT * FROM user WHERE User = 'noc' \G

*************************** 1. row ***************************

           Host: monitorserver.noc.widgets.example.com

           User: noc

       Password: 7abf52ce38207ca0

    Select_priv: N

    Insert_priv: N

    Update_priv: N

    Delete_priv: N

    Create_priv: N

      Drop_priv: N

    Reload_priv: N

  Shutdown_priv: Y

   Process_priv: Y

      File_priv: N

     Grant_priv: N

References_priv: N

     Index_priv: N

     Alter_priv: N

10.3.2 Common Problems and Limitations

MySQL doesn't always act the way you expect it to. Often this is because the flexibility of its privilege system leads you to expect it to act in a more sophisticated way than it is designed to act. Let's take a look at a couple of common ways MySQL can demonstrate unexpected behavior.

10.3.2.1 Can't revoke specific privileges

One day you decide that raymond shouldn't have read access to the payroll database. He currently has all privileges. So you try to take away his select privilege for that database:

mysql> REVOKE SELECT ON payroll.* FROM raymond;

ERROR 1141: There is no such grant defined for user 'raymond' on host '%'

What? Raymond is a DBA and has all privileges, doesn't he? Let's check:

mysql> SHOW GRANTS FOR raymond \G

*************************** 1. row ***************************

Grants for raymond@%: GRANT ALL PRIVILEGES ON *.* TO 'raymond'@'%'

IDENTIFIED BY PASSWORD '11417e201753de4b' WITH GRANT OPTION

Sure enough, he has every privilege. What's the problem?

MySQL isn't as smart is it appears to be. It provides a way to grant privileges, through the user and host and other tables shown earlier, but it doesn't have a parallel system to deny privileges. It doesn't have a system for granting all access except for certain specific items (like the hosts.allow and hosts.deny files familiar to Unix system administrators). Essentially, you can't deny a more specific privilege than you have granted to a given user.

The solution to this problem is rather ugly. You have to remove all the user's privileges, then specifically grant those you want to keep. This gets messy because you need a GRANT command for every database except payroll.

mysql> GRANT ALL PRIVILEGES ON db1.* TO raymond WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON db2.* TO raymond WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON db3.* TO raymond WITH GRANT OPTION;

And so on. This example illustrates the class of problems that we'll look at next.

10.3.2.2 Host and database matching can't exclude matches

The previous example would have been a lot easier if you could write something like this:

mysql> GRANT ALL PRIVILEGES ON *.* EXCEPT payroll.* TO raymond;

But MySQL can't do that. Similarly, if you want to restrict access from just one host (insecure.example.com), there's no way to do it. You can't do this:

mysql> GRANT ALL PRIVILEGES ON *.* TO raymond@"%"

    -> EXCEPT raymond@insecure.example.com;

Neither of these work because MySQL was designed to make it easy to grant privileges but not to deny privileges. From MySQL's point of view, you deny a privilege by never granting it in the first place. The result is a system that makes it easy to build inclusive rules but makes it impossible to build exclusive rules.

If you want to allow raymond to connect from any host except insecure.example.com, you have to either block that host at the network level or add a record with a bogus password to the user table for raymond@insecure.example.com. In the latter case, Raymond can connect but authentication will always fail.

10.3.2.3 Privileges don't vanish when objects do

It should be noted that there is one serious design flaw in the way MySQL handles privileges. That problem is that there is no GRANT clean-up when database objects are removed.

For example, let's say you've done the following:

mysql> GRANT ALL PRIVILEGES ON my_db.* TO raymond;

You later run the following command:

$ mysqladmin drop my_db

In a well-designed privileges system, that GRANT would find itself destroyed as part of the dropping of the databases it referenced.^[3] With MySQL, however, the privileges remain in the db table.

^[3] At the very least, there would be a configuration option to permit the destruction to happen.

At first glance, you may think to yourself, "Why do I care? Since my_db is dropped, there's nothing there to see." But what if a couple months or years later, you create a new database called my_db? Do you still want Raymond to have access to the new table? Do you even remember that he has access to it?

The solution—let's call it a workaround, because that's what it is—is for the admin, when dropping a database or table, to scour and directly access the appropriate privileges tables. In the my_db example, if you drop the my_db table, you might want to do something like this:

mysql> DELETE FROM db where Db='my_db';

mysql> DELETE FROM tables_priv where Db='my_db';

mysql> DELETE FROM columns_priv where Db='my_db';

mysql> FLUSH PRIVILEGES;

In some cases, it might be possible to do this using the REVOKE command multiple times for each user that may have been granted privileges, but it's probably much faster and more secure to access the privileges tables as just shown, and be sure to make a clean sweep across them. Likewise, if you dropped only a particular table in my_db, say, my_db.my_table, you might do this:

mysql> DELETE FROM tables_priv where Db='my_db' AND Table_name='my_table';

mysql> DELETE FROM columns_priv where Db='my_db' AND Table_name='my_table';

mysql> FLUSH PRIVILEGES;

Obviously, no DELETE is needed against the db table because it isn't a database-wide privilege that needs to be revoked.

In some cases, you might find this useful. For example, if you're dropping a table just to reload it again from backup, it's much more convenient not to have to worry about revoking and regranting privileges.^[4]

^[4] An argument can be made that if you're restoring from a backup and leaving the existing privileges in place, you're not necessarily restoring to the backed-up state and might be leaving any security holes that were created afterwards still in place.

In an ideal world, this would be an option to commands like ALTER TABLE or DROP DATABASE, to allow the system to hunt down and destroy granted privileges automatically. Alternatively, MySQL could default to a theoretically "secure" methodology of destroying stale privileges but offer the option to leave the privileges intact.