Learning MySQL

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. The privilege hierarchy

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 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 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 UPDATE statement on any table or column in any database. If you grant privileges for only the music database, the privileges are available for just it and its tables and columns. Privileges never propagate up the hierarchy; ...