High Performance MySQL [Electronic resources] نسخه متنی

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
privilegejust 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 solutionlet's call it a workaround,
because that's what it isis 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.