MySQL has an advanced but non-standard security/privilege system. This section describes how it works.
4.4.1 What the Privilege System Does
The primary function of the MySQL privilege system is to authenticate a user connecting from a given host, and to associate that user with privileges on a database such as SELECT, INSERT, UPDATE, and DELETE.
Additional functionality includes the ability to have an anonymous user and to grant privileges for MySQL-specific functions such as LOAD DATA INFILE and administrative operations.
4.4.2 How the Privilege System Works
The MySQL privilege system ensures that all users may perform only the operations allowed to them. As a user, when you connect to a MySQL server, your identity is determined by the host from which you connect and the username you specify. The system grants privileges according to your identity and what you want to do.
MySQL considers both your hostname and username in identifying you because there is little reason to assume that a given username belongs to the same person everywhere on the Internet. For example, the user joe who connects from office.com need not be the same person as the user joe who connects from elsewhere.com. MySQL handles this by allowing you to distinguish users on different hosts that happen to have the same name: You can grant joe one set of privileges for connections from office.com, and a different set of privileges for connections from elsewhere.com.
MySQL access control involves two stages:
If your privileges are changed (either by yourself or someone else) while you are connected, those changes will not necessarily take effect immediately for the next statement you issue. See Section 4.4.7, "When Privilege Changes Take Effect," for details.
The server stores privilege information in the grant tables of the mysql database (that is, in the database named mysql). The MySQL server reads the contents of these tables into memory when it starts and re-reads them under the circumstances indicated in Section 4.4.7, "When Privilege Changes Take Effect." Access-control decisions are based on the in-memory copies of the grant tables.
Normally, you manipulate the contents of the grant tables indirectly by using the GRANT and REVOKE statements to set up accounts and control the privileges available to each one. The discussion here describes the underlying structure of the grant tables and how the server uses their contents when interacting with clients.
The server uses the user, db, and host tables in the mysql database at both stages of access control. The columns in these grant tables are shown here:
During the second stage of access control (request verification), the server may, if the request involves tables, additionally consult the tables_priv and columns_priv tables that provide finer control at the table and column levels. The columns in these tables are shown here:
The Timestamp and Grantor columns currently are unused and are discussed no further here.
Each grant table contains scope columns and privilege columns:
Scope columns contain strings. They are declared as shown here; the default value for each is the empty string:
Before MySQL 3.23, the Db column is CHAR(32) in some tables and CHAR(60) in others.
For access-checking purposes, comparisons of Host values are case-insensitive. User, Password, Db, and Table_name values are case-sensitive. Column_name values are case-insensitive in MySQL 3.22.12 or later.
In the user, db, and host tables, each privilege is listed in a separate column that is declared as ENUM('N','Y') DEFAULT 'N'. In other words, each privilege can be disabled or enabled, with the default being disabled.
In the tables_priv and columns_priv tables, the privilege columns are declared as SET columns. Values in these columns can contain any combination of the privileges controlled by the table:
Briefly, the server uses the grant tables as follows:
Administrative privileges (such as RELOAD or SHUTDOWN) are specified only in the user table. This is because administrative operations are operations on the server itself and are not database-specific, so there is no reason to list these privileges in the other grant tables. In fact, to determine whether you can perform an administrative operation, the server need consult only the user table.
The FILE privilege also is specified only in the user table. It is not an administrative privilege as such, but your ability to read or write files on the server host is independent of the database you are accessing.
The mysqld server reads the contents of the grant tables into memory when it starts. You can tell it to re-read the tables by issuing a FLUSH PRIVILEGES statement or executing a mysqladmin flush-privileges or mysqladmin reload command. Changes to the grant tables take effect as indicated in Section 4.4.7, "When Privilege Changes Take Effect."
When you modify the contents of the grant tables, it is a good idea to make sure that your changes set up privileges the way you want. One way to check the privileges for a given account is to use the SHOW GRANTS statement. For example, to determine the privileges that are granted to an account with Host and User values of pc84.example.com and bob, issue this statement:
mysql> SHOW GRANTS FOR 'bob'@'pc84.example.com';
A useful diagnostic tool is the mysqlaccess script, which Yves Carlier has provided for the MySQL distribution. Invoke mysqlaccess with the --help option to find out how it works. Note that mysqlaccess checks access using only the user, db, and host tables. It does not check table or column privileges specified in the tables_priv or columns_priv tables.
For additional help in diagnosing privilege-related problems, see Section 4.4.8, "Causes of Access denied Errors." For general advice on security issues, see Section 4.3, "General Security Issues."
blog comments powered by Disqus