Securing the Database - Database Design Manual: Using MySQL for Windows - page 138

To see what has happened to the user system table after these GRANT and REVOKE
commands, issue the following query in the graphical client:

SELECT *
FROM user
WHERE user = 'Mary'

Notice that although the user table is a system table, it is still a table that you can apply
any query to, as long as you have the access rights. Although we revoked the ALL PRIVI-
LEGES right, there is still an entry for Mary in the user table, as we can see by looking at
Figure 12.6.
You will see from the small selection of privilege columns in Figure 12.6 that these
columns have now all been set to N. If you have this on your screen, scroll across and you
will see that all of the other columns have been set to N as well. Mary has therefore lost her
global rights to everything. If this is the case, how does the system know that she has access
to the user table to do SELECT queries?

The tables_priv Table

Access rights to a specific table are stored within the tables_priv table. This table has slightly
different properties from the user table. Figure 12.7 describes this table, which you may like
to compare with the user table shown in Figure 12.1.
You will notice that instead of having columns specifically for each privilege, this table
has a column where you can explicitly set which keywords will work on the specified table:
tables_priv . If we SELECT everything from this table now, the results are as shown in
Figure 12.8. Notice that Mary has an entry that allows her to view (SELECT) the user table.
It also shows you who granted that access, in the grantor column, useful when you are
tracking down a problem with access.
What will happen if we allow Mary some further access to the user table, say allowing her
to insert a new user? The following script will allow this:

GRANT INSERT on mysql.user
TO Mary@localhost

Figure 12.6 Mary's new entry in the user table.