MySQL has a sophisticated multiuser access-control system that can allow or prevent each user from having access to a particular database or individual tables. You can even restrict the types of operations the user is able to perform on a table.
Authenticating with MySQL
When you connect to a MySQL server, you provide a username and password to gain access. MySQL authenticates you using not only these login details, but also the hostname or IP address you are connecting from.
Not only do you need to supply a valid username and password for the database you want to gain access to, but you also need to satisfy MySQL that you are connecting from an allowed host for that user. If you try to connect with a username and password that are not valid for the host you are connecting from, access will be denied, even if the login information works from another host.
It is also possible for two users who have the same username but are connecting from two different hosts to use separate passwords and have separate privileges. Therefore, you should try to think of a MySQL user as a username plus a host when dealing with user permissions, rather than just the username. In other words, consider the access requirements for chris@localhost or firstname.lastname@example.org, not for user chris.
MySQL includes a number of individual privileges that determine the type of actions that a user can perform. For example, a user with the SELECT privilege may query a table, but the same user also requires the INSERT, DELETE, and UPDATE privileges to perform data manipulation on the same table.
A privilege can be granted to a userremember, this means a user plus a hostat the table level. Therefore, two users with access to the same database can be given permission to use only the tables they require access to.
Table 18.1 lists the privileges that can be assigned. User permissions are set using the GRANT and REVOKE commands, which are discussed later in this chapter.
The MySQL Privilege Tables
Whether access is granted to MySQL is determined by data in the mysql database, which contains a number of tables that control user privileges.
Data from the user table is used for authentication. The Host, User, and Password columns contain the values used to validate a connection attempt. The Password column is encrypted, so you cannot view a user's actual password by querying this table.
The db database contains the default permissions that a user has for each database. The Db, User, and Host columns are followed by a series of privilege columns, such as Select_priv and Alter_priv. Those columns contain either a Y or an N value to indicate whether that privilege has been granted.
Individual table privileges are stored in the tables_priv table, using the Db, User, Host, and Table_name columns to identify the database table and user. The Table_priv column contains the name of the privilege granted. Whereas db contains one row per user and database, there is one row in tables_priv for each table-level privilege assigned.