Learning MySQL

The GRANT OPTION Privilege

The GRANT OPTION privilege allows a user to pass on any privileges she has to other users. Consider an example, which we’ve run when connected to the monitor as the root user:

mysql> GRANT ALL ON music.* TO 'hugh'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql>  GRANT GRANT OPTION ON music.* TO 'hugh'@'localhost';
Query OK, 0 rows affected (0.00 sec)

This creates a MySQL user hugh (with no password!) and allows him to pass on his privileges for the music database to other users. Since the GRANT OPTION is given at the database level (to music.*), hugh can pass on his privileges on that database, or on any of the tables or columns in that database. GRANT OPTION always allows a user to pass on his privileges at the level which they’re given, or any lower level, and it also allows him to pass on any future privileges he’s given. We explain this hierarchy more in the next section.

Let’s test our new privilege using the user hugh. Quit the monitor, and then reconnect as the MySQL user hugh:

$ mysql --user=hugh

Now, let’s give our privileges to another user:

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 ...