Learning Mysql

...| max_connections | max_user_connections |
...+-----------------+----------------------+
...| 0 | 0 |
...+-----------------+----------------------+
1 row inset sec)
You can see that the password is encrypted using the PASSWORD function, and that all privileges are
N
except for the four simple privileges we’ve granted. If you create a user with no global privileges—because the privileges you grant are fora database,
tables, or columns—you’ll find that all privileges in the user table are set to
N
The user table is used to authenticate connections, as well as store global privileges. If a connection’s parameters—its username, password, and host—don’t match an entry in the user table, then the user isn’t authenticated and it doesn’t matter what privileges are available in the other four tables. If the parameters do match, then the user is allowed access to the MySQL server, and her privileges area combination of those in the five privilege tables. There’s no requirement for an exact match between the parameters and the user table for authentication because a blank username allows anonymous access from a host, and the hostname column can contain wildcards.
The db Table
When you grant privileges fora particular database, they are stored in the db table of the mysql database. The table is similar to the user table but stores privilege values for
Host
, Db, and User combinations. Consider what happens when you grant 'bob'@'localhost'
privileges for the music database:
mysql> GRANT SELECT, INSERT, DELETE on music.*
-> TO 'bob'@'localhost';
Query OK, 0 rows affected (0.00 sec)
You’ll now see these privileges in the db table:
mysql> SELECT * FROM db WHERE User = 'bob';
+-----------+-------+------+-------------+-------------+-------------+...
| Host | Db | User | Select_priv | Insert_priv | Update_priv |...
+-----------+-------+------+-------------+-------------+-------------+...
| localhost | music | bob | Y | Y | N |...
+-----------+-------+------+-------------+-------------+-------------+...
...+-------------+-------------+-----------+------------+-----------------+...
...| Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv |...
...+-------------+-------------+-----------+------------+-----------------+...
...| Y | N | N | N | N |...
...+-------------+-------------+-----------+------------+-----------------+...
...+------------+------------+-----------------------+------------------+...
...| Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv |...
...+------------+------------+-----------------------+------------------+...
...| N | N | N | N |...
...+------------+------------+-----------------------+------------------+...
...+------------------+----------------+---------------------+...
...| Create_view_priv | Show_view_priv | Create_routine_priv |...
...+------------------+----------------+---------------------+...

Download 4.24 Mb.

Share with your friends: