Managing Users and Privileges - Learning MySQL - page 307

mysql> GRANT ALL ON music.* TO 'selina'@'localhost';
Query OK, 0 rows affected (0.00 sec)
This passes on all privileges to a new user, selina (with no password). It doesn't pass
on the GRANT OPTION privilege, but you can do this if you want to:
mysql> GRANT GRANT OPTION ON music.* TO 'selina'@'localhost';
Query OK, 0 rows affected (0.00 sec)
Now selina can do the same things hugh can on the music database.
You can also pass on the GRANT OPTION privilege in a single SQL statement that also
grants other privileges. Here's an example using an alternative syntax:
mysql> GRANT ALL ON music.* to 'lucy'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
This has exactly the same effect as the previous two-step example that created the user
selina .
As discussed previously, users can pass on privileges at the same or lower levels. Con-
sider an example that's executed when we're connected as hugh :
mysql> GRANT ALL ON music.artist TO 'rose'@'localhost';
Query OK, 0 rows affected (0.00 sec)
Since hugh has all privileges for all tables in the music database, he can pass all privileges
for only the artist table to a new user, rose .
Be careful with GRANT OPTION ; users with this privilege can share other privileges in ways
you may not anticipate. We discuss this further later in this chapter in “More Security
Tips.”
How Privileges Interact
In the previous section, we explained how the GRANT OPTION privilege is used to pass
privileges to other users and how it allows privileges at lower levels in the privilege
hierarchy to be granted. In this section, we explore the privilege hierarchy further and
explain how MySQL allows or denies access to resources.
Figure 9-1 shows an example of the MySQL privilege hierarchy. There are four levels;
reading from highest to lowest, these are global, database, table, and column. In Fig-
ure 9-1, the global level contains the MySQL server system and three databases: music ,
university , and flight . Each database contains tables; the figure shows the tables in
the music database. Each table in turn contains columns, and the figure shows the
columns in the artist table.
When you grant privileges at a level, those privileges are available at that and all lower
levels. In Figure 9-1, if you grant privileges at the global level, those privileges are avail-
able for MySQL server functions and throughout the databases, tables, and columns.
For example, if you have the UPDATE privilege at the global level, you can execute the