Learning Mysql

mysql> GRANT ALL ON music TO 'selina'@'localhost';
Query OK, 0 rows affected (0.00 sec)
This passes on all privileges to anew 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 canon 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. Consider 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 anew 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 Figure, 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 available 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

Download 4.24 Mb.

Share with your friends: