The MySQL security system is flexible. It allows you to set up user access privileges in many different ways. Normally, you do this by using the GRANT and REVOKE statements, which modify on your behalf the grant tables that control client access. However, you may have an older version of MySQL that does not support these statements (they were not functional prior to MySQL 3.22.11), or you may find that user privileges don't seem to be working the way you want. For such situations, it's helpful to understand the structure of the MySQL grant tables and how the server uses them to determine access permissions. Such an understanding allows you to add, remove, or modify user privileges by modifying the grant tables directly. It also allows you to diagnose privilege problems when you examine the tables.
I assume that you've read the "Managing MySQL User Accounts" section in Chapter 11 and that you understand how the GRANT and REVOKE statements work. GRANT and REVOKE provide a convenient way for you to set up MySQL user accounts and associate privileges with them, but they are just a front end. All the real action takes place in the MySQL grant tables. (In fact, the "Setting Up MySQL Accounts Without GRANT Statements" section later in this chapter discusses how to modify the grant tables directly to achieve the same results that you get by issuing GRANT statements.)
Access to MySQL databases by clients who connect to the server over the network is controlled by the contents of the grant tables. These tables are located in the mysql database and are initialized during the process of installing MySQL on a machine for the first time (as described in Appendix A, "Obtaining and Installing Software," for example). These tables are named user, db, tables_priv, columns_priv, and host. They are used as follows:
The user table lists accounts for users that may connect to the server, their passwords, and which global (superuser) privileges each user has, if any. It's important to recognize that any privileges that are enabled in the user table are global privileges that apply to all databases. For example, if you enable the DELETE privilege in a user table entry, the account associated with the entry can delete records from any table in any database. Think carefully before you do this.
Because of the superuser nature of privileges specified in the user table, it's generally best to leave all the privileges turned off for entries in this table and list more specific privileges in other tables that are more restrictive. There are two types of exceptions to this principle:
Superusers, such as root and other administrative accounts, need global privileges to operate the server. These accounts tend to be few.
A few specific global privileges usually can be granted safely. These pertain to creating temporary tables, locking tables, and being able to use the SHOW DATABASES statement. Most installations probably will grant these, but others where tighter control is necessary will not.
The user table also has columns for SSL options that pertain to the establishment of secure connections with SSL and columns for resource management that can be used to prevent a given account from monopolizing the server.
The db table lists which accounts have privileges for which databases. If you grant a privilege here, it applies to all tables in a database.
The tables_priv table specifies table-level privileges. A privilege specified here applies to all columns in a table.
The columns_priv table specifies column-level privileges. A privilege specified here applies to a particular column in a table.
The host table is used in combination with the db table to control database access privileges to particular hosts at a finer level than is possible with the db table alone. This table is unaffected by the GRANT and REVOKE statements, so it's likely that you will never use it at all.
The structure of each grant table is shown in Tables 12.1, 12.2 and 12.3, broken down by type of column. All grant tables contain two primary kinds of columns?scope-of-access columns that determine when an entry applies and privilege columns that determine which privileges an entry grants. The privilege columns can be subdivided further into columns for administrative operations and those that are related to database and table operations. The user table has additional columns for SSL connections and resource management; these are present only in the user table because they apply globally. Some of the grant tables contain other miscellaneous columns, but they don't concern us here because they have no bearing on account management.
Scope-of-Access Columns | ||||
---|---|---|---|---|
user Table | db Table | tables_priv Table | columns_priv Table | host Table |
Host | Host | Host | Host | Host |
User | User | User | User | |
Password | Db | Db | Db | Db |
Table_name | Table_name | |||
Column_name |
Administrative Privilege Columns | ||
user Table | db Table | host Table |
Create_tmp_table_priv | Create_tmp_table_priv | Create_tmp_table_priv |
Execute_priv | ||
File_priv | ||
Grant_priv | Grant_priv | Grant_priv |
Lock_tables_priv | Lock_tables_priv | Lock_tables_priv |
Process_priv | ||
Reload_priv | ||
Repl_client_priv | ||
Repl_slave_priv | ||
Show_db_priv | ||
Shutdown_priv | ||
Super_priv | ||
Database/Table Privilege Columns | ||
user Table | db Table | host Table |
Alter_priv | Alter_priv | Alter_priv |
Create_priv | Create_priv | Create_priv |
Delete_priv | Delete_priv | Delete_priv |
Drop_priv | Drop_priv | Drop_priv |
Index_priv | Index_priv | Index_priv |
Insert_priv | Insert_priv | Insert_priv |
References_priv | References_priv | References_priv |
Select_priv | Select_priv | Select_priv |
Update_priv | Update_priv | Update_priv |
tables_priv Table | columns_priv Table | |
Table_priv | Column_priv |
SSL Columns | Resource Management Columns |
---|---|
ssl_type | max_connections |
ssl_cipher | max_questions |
x509_issuer | max_updates |
x509_subject |
The grant table system includes tables_priv and columns_priv tables for setting up table-specific and column-specific privileges. However, there is no analogous rows_priv table because MySQL doesn't provide record-level privileges. For example, you cannot restrict a user's access to just those rows in a table that contain a particular value in some column. If you need this capability, you must provide it using application programming. (One way to perform advisory record-level locking is to use the GET_LOCK() function described in Appendix C, "Operator and Function Reference.")
Dealing with Changes to Grant Table StructureThe structure of the grant tables has changed occasionally over time, so if you examine the tables in your server's mysql database, you may find that certain tables or columns are not present:
If your grant table structure doesn't match what you expect based on the description just given, you can bring the tables up to date by running the mysql_fix_privilege_tables script. It needs to connect to the local server as the MySQL root user, so invoke it with the appropriate password: % mysql_fix_privilege_tables root-password If you upgrade to MySQL 4.0.2 or later from an earlier version, the new privilege columns added to the user table by the mysql_fix_privilege_tables script are initialized for existing non-anonymous user table entries as follows:
It's actually best to upgrade to 4.0.4 or later if possible rather than to 4.0.2 or 4.0.3. The lack of the Create_tmp_table_priv and Lock_tables_priv privileges in the db table for those two releases results in the inability to grant them on a database-specific basis and causes some administrative difficulties. |
The grant table scope columns are used to determine which rows to use when a given account attempts to perform a given operation. Each grant table entry contains Host and User columns to indicate that the entry applies to connections from a given host by a particular user. For example, a user table record with localhost and bill in the Host and User columns would be used for connections from the local host by bill, but not for connections by betty. (The host table is an exception; it's used in a special way that we won't get into just yet.) The other tables contain additional scope columns. The db table contains a Db column to indicate which database the entry applies to. Similarly, rows in the tables_priv and columns_priv tables contain scope fields that further narrow their scope to a particular table in a database or column in a table.
The grant tables also contain privilege columns. These indicate which privileges are held by the user who matches the values listed in the scope columns. The privileges supported by MySQL are shown in the following lists, which show the administrative privileges and the privileges that control database and table access. Each list uses the privilege names that are used for the GRANT statement. For the most part, these privilege names bear an obvious resemblance to the names of privilege columns in the user, db, and host tables. For example, the SELECT privilege corresponds to the Select_priv column.
The following privileges apply to administrative operations that control the operation of the server or a user's ability to grant privileges:
CREATE TEMPORARY TABLES
Allows you to create temporary tables with the CREATE TEMPORARY TABLE statement.
EXECUTE
Allows you to execute stored procedures. This privilege is currently unimplemented. It will come into effect when stored procedures are implemented in a future version of MySQL (currently scheduled for MySQL 5).
FILE
Allows you to tell the server to read or write files on the server host. To keep the use of this privilege within certain bounds, the server takes certain precautions:
You can access only files that are world-readable, and thus likely not to be considered protected in any way.
Any file that you want to write must not already exist. This prevents you from coercing the server into overwriting important files, such as /etc/passwd or database files in a database belonging to someone else. (If this constraint were not enforced, you could completely replace the contents of the grant tables in the mysql database, for example.)
Despite these precautions, this privilege should not be granted without just cause; it can be extremely dangerous, as discussed in the "Grant Table Risks to Avoid" section later in this chapter. If you do grant the FILE privilege, be sure not to run the server as the UNIX root user, because root can create new files anywhere in the file system. By running the server from an ordinary login account, the server can create files only in directories accessible to that account.
GRANT OPTION
Allows you to grant other users the privileges you have yourself, including the GRANT OPTION privilege.
LOCK TABLES
Allows you to lock tables by issuing explicit LOCK TABLES statements. This privilege applies only to tables for which you also have the SELECT privilege, but allows you to place read or write locks, not just read locks. The privilege does not apply to locks that are acquired implicitly on your behalf by the server during the process of query execution. Such locks are set and released automatically regardless of your LOCK TABLES privilege setting.
PROCESS
The MySQL server is multi-threaded such that each client connection is serviced by a separate thread. These threads can be thought of as processes running within the server. Prior to MySQL 4.0.2, the PROCESS privilege allows you to view information about threads that are currently executing using the SHOW PROCESSLIST statement or the mysqladmin processlist command. It also allows you to kill threads with the KILL statement or the mysqladmin kill command. (You can always see or kill your own threads, even without the PROCESS privilege. What this privilege adds is the global ability to see or kill any thread, even those associated with other users.) The PROCESS privilege also allows mysqladmin debug, and it overrides any max_connections setting when connecting to the server, so that you can access the connection slot that the server reserves for administrative connections even when all the regular slots are taken.
In MySQL 4.0.2 or later, PROCESS controls only the ability to see threads; the ability to kill them is controlled by the SUPER privilege, as is the ability to use mysqladmin debug and the reserved connection slot.
RELOAD
Allows you to perform a variety of server administration operations. This privilege gives you the ability to issue statements such as FLUSH and RESET. It also lets you perform the following mysqladmin commands: reload, refresh, flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, and flush-threads.
REPLICATION CLIENT
Allows you to inquire about the location of master and slave servers.
REPLICATION SLAVE
Allows a client to connect to a master server and request slave server updates. It's granted to accounts used by slave servers. Prior to MySQL 4.0.2, replication slaves are set up using the FILE privilege instead.
SHOW DATABASES
Controls the ability to use the SHOW DATABASES statement.
SHUTDOWN
Allows you to shut down the server with mysqladmin shutdown.
SUPER
Allows you to kill server threads, use mysqladmin debug, and access the reserved connection slot, as discussed in the description for the PROCESS privilege. Other statements allowed by this privilege are CHANGE MASTER, PURGE MASTER LOGS, and SET for modifying global server variables and the global transaction isolation level. SUPER also allows you to perform DES decryption based on the keys stored in the DES key file.
The following privileges apply to operations on databases and tables:
ALTER
Allows you to use the ALTER TABLE statement, although you may also need additional privileges, depending on what you want to do with the table.
CREATE
Allows you to create databases and tables. This privilege does not allow you to create indexes on a table, except those declared initially in the CREATE TABLE statement.
DELETE
Allows you to remove existing records from tables.
DROP
Allows you to drop databases and tables. This privilege does not allow you to drop indexes.
INDEX
Allows you to create or drop indexes from tables.
INSERT
Allows you to insert new records in tables.
REFERENCES
This is currently unused. Eventually it may be used to define who can set up foreign key constraints.
SELECT
Allows you to retrieve data from tables using SELECT statements. This privilege is unnecessary for SELECT statements such as SELECT NOW() or SELECT 4/2 that do nothing more than evaluate expressions and involve no tables.
UPDATE
Allows you to modify existing records in tables.
Some operations require a combination of privileges. For example, REPLACE may implicitly cause a DELETE followed by an INSERT, so it requires both the DELETE and INSERT privileges.
In the user, db, and host tables, each privilege is specified as a separate column. These columns are all declared to have a type of ENUM('N','Y'), with a default value of 'N' (off). For example, the Select_priv column is defined as follows:
Select_priv ENUM('N','Y') NOT NULL DEFAULT 'N'
Privileges in the tables_priv and columns_priv tables are represented by a SET, which allows any combination of privileges to be stored in a single column. The Table_priv column in the tables_priv table is defined as follows:
SET('Select','Insert','Update','Delete','Create','Drop', 'Grant','References','Index','Alter')
The Column_priv column in the columns_priv table is defined as follows:
SET('Select','Insert','Update','References')
The reason there are fewer column privileges than table privileges is that fewer operations make sense at the column level. For example, you can delete a row from a table to remove it, but you can't delete individual columns of a row.
The tables_priv and columns_priv tables are newer than the other three, which is why they use the more efficient SET representation. (It's possible that the user, db, and host tables may be reorganized in the future to represent privileges by SET columns as well.)
The user table contains several administrative privilege columns that are not present in any of the other grant tables, such as File_priv, Process_priv, Reload_priv, and Shutdown_priv. Such privileges are present only in the user table because they are global privileges that are not associated with any particular database or table. For example, it doesn't make sense to allow or not allow a user to shut down the server based on what the current database is.
Several columns in the user table apply to authentication of secure connections over SSL. The primary column is ssl_type, which indicates whether and what type of secure connection is required. It is represented as an ENUM with four possible values:
'NONE' indicates that secure connections are not required. This is the default value; it's used when you set up an account but do not specify any REQUIRE clause or when you specify REQUIRE NONE explicitly.
'ANY' indicates that a secure connection is required, but that it can be any kind of secure connection; it's a kind of "generic" requirement. The column is set to this value when you specify REQUIRE SSL in a GRANT statement.
'X509' indicates that a secure connection is required and that the client must supply a valid X509 certificate. The contents of the certificate are not otherwise relevant. The column is set to this value when you specify REQUIRE X509.
'SPECIFIED' indicates that the secure connection must meet specific requirements. The column is set to this value when you specify any combination of ISSUER, SUBJECT, or CIPHER values in the REQUIRE clause.
For all ssl_type values except 'SPECIFIED', the server ignores the values in the other SSL-related columns when validating client connection attempts. For 'SPECIFIED', the server checks the other columns and, for any that have non-empty values, the client must supply matching information. These columns are:
ssl_cipher
If non-empty, this column indicates the cipher method that the client must use when connecting.
x509_issuer
If non-empty, this column indicates the issuer value that must be found in the X509 certificate presented by the client.
x509_subject
If non-empty, this column indicates the subject value that must be found in the X509 certificate presented by the client.
ssl_cipher, x509_issuer, and x509_subject all are represented in the user table as BLOB columns.
The following columns in the user table allow you to limit the extent to which any given MySQL account can consume server resources:
max_connections
The number of times per hour the account can connect to the server.
max_questions
The number of queries per hour the account can issue.
max_updates
Like max_questions, but applies more specifically to queries that modify data.
For each of these columns, a value of zero means "no limit."
If the server restarts, the current counters are reset to zero. A reset also occurs if you reload the grant tables or issue a FLUSH USER_RESOURCES statement.
There are two stages of client access control when you use MySQL. The first stage occurs when you attempt to connect to the server. The server looks at the user table to see if it can find an entry that matches the host you're connecting from, your name, and the password you supplied. If there is no match, you can't connect. If there is a match and your user table is recent enough to include the SSL or resource management columns, the server also checks those columns:
If you've exceeded your connections-per-hour limit, the connection is rejected.
If the user table entry indicates that secure connections are required, the server determines whether the credentials you supply match those required in the SSL-related columns. If not, the connection is rejected.
If everything checks out okay, the server establishes the connection and you proceed to the second stage. For secure connections, encryption is used.
In the second stage, for each query you issue, the server checks the grant tables to see whether or not you have sufficient privileges to perform the query. (If the resource management columns are present in the user table, the server also checks your queries-per-hour and updates-per-hour limits. It does this prior to checking your access privileges?after all, if you've exceeded those limits, there is little point in checking your privileges.) The second stage continues until you disconnect from the server.
The following discussion describes in some detail the rules that the MySQL server uses to match grant table entries to incoming client connection requests and to queries. This includes the types of values that are legal in the grant table scope columns, how privilege values from different grant tables are combined, and the order in which table entries within a given grant table are searched.
Each scope column is governed by rules that define what kinds of values are legal and how the server interprets those values. Some of the scope columns require literal values, but most of them allow wildcard or other special values.
Host
A Host column value can be a hostname or an IP number. The value localhost means the local host. It matches if you connect using a host value of localhost or 127.0.0.1 or if you connect using a named pipe on Windows NT-based systems using a '.' host value. However, localhost does not match if you connect using the host's actual name or IP number. Suppose the name of the local host is cobra.snake.net and there are two entries for a user named bob in the user table, one with a Host value of localhost and the other with a value of cobra.snake.net. The entry with localhost will match if bob connects either of the following ways, on either UNIX or Windows:
% mysql -p -u bob -h localhost % mysql -p -u bob -h 127.0.0.1
In addition, on Windows, the entry matches if bob connects like this:
C:\> mysql -p -u bob -h .
The localhost connection will use a UNIX socket on UNIX, and TCP/IP on Windows. The 127.0.0.1 connection will use TCP/IP on both platforms. The '.' connection will use a named pipe on Windows.
The entry with a Host value of cobra.snake.net will match if bob connects from cobra.snake.net using the server's name or IP number. In both cases, the connection will use TCP/IP.
You can also specify Host values using wildcards. The '%' and '_' SQL pattern characters can be used and have the same meaning as when you use the LIKE operator in a query. (Regular expressions of the type used with REGEXP are not allowed.) The SQL pattern characters work both for names and for IP numbers. For example, %.kitebird.com matches any host in the kitebird.com domain, and %.edu matches any host at any educational institution. Similarly, 192.168.% matches any host in the 192.168 class B subnet, whereas 192.168.3.% matches any host in the 192.168.3 class C subnet.
A Host value of % matches any host at all, and can be used to allow a user to connect from anywhere. With one exception, a blank Host value in a grant table is the same as %. (The exception is the db table, for which a blank Host value means "check the host table for further information." This process is described in the "Query Access Verification" section later in this chapter.)
As of MySQL 3.23, you can also specify a network number with a netmask indicating which bits of the client IP address must match the network number. For example, 192.168.128.0/255.255.255.0 specifies a 24-bit network number and matches any client host for which the first 24 bits of its IP address have a value equal to 192.168.128.
User
Usernames must be either literal values or blank (empty). A blank value matches any name and thus means "anonymous." Otherwise, the value matches exactly the name specified. In particular, % as a User value does not mean blank; instead, it matches a user with a literal name of %, which is probably not what you want.
When an incoming connection is verified against the user table, if the first matching entry contains a blank User value, the client is considered to be an anonymous user.
Password
Password values are either blank (empty) or non-blank, and wildcards are not allowed. A blank password doesn't mean that any password matches; it means that the user must specify no password.
Passwords are stored as encrypted values, not literal text. If you store a literal password in the Password column, the user will not be able to connect! The GRANT statement and the mysqladmin password command encrypt the password for you automatically, but if you use statements such as INSERT, REPLACE, UPDATE, or SET PASSWORD to modify the grant tables directly, be sure to specify the password using PASSWORD('new_password') rather than just 'new_password'.
Db
In the db and host tables, Db values can be specified literally or by using the '%' or '_' SQL pattern characters to specify a wildcard. A value of % or blank matches any database. In the columns_priv and tables_priv tables, Db values must be literal database names and match exactly the name specified; patterns and empty values are not allowed.
Table_name, Column_name
Values in these columns must be literal table or column names and match exactly the name specified; patterns and empty values are not allowed.
Some scope columns are treated by the server as case sensitive, whereas others are not, as summarized in Table 12.4. Note in particular that Db and Table_name values are always treated as case sensitive, even though treatment of database and table names in queries depends on the case sensitivity of the file system on which the server runs (typically case sensitive under UNIX, and not case sensitive under Windows).
Column | Case Sensitive |
---|---|
Host | No |
User | Yes |
Password | Yes |
Db | Yes |
Table_name | Yes |
Column_name | No |
How Passwords Are Stored in the user TableThe MySQL server encrypts passwords with the PASSWORD() function before storing them in the user table, to prevent them from being exposed as plain text even to users who have read access to the table. It seems to be a common assumption that PASSWORD() implements the same kind of encryption as is used for UNIX passwords, but it doesn't. The two kinds of encryption are similar in that both are one-way and not reversible, but MySQL doesn't use the same encryption algorithm that UNIX does. This means that even if you use your UNIX password as your MySQL password, you shouldn't expect the encrypted password strings to match. If you want to perform UNIX encryption for an application, use the CRYPT() function rather than PASSWORD(). (If you're curious about what other encryption options are available for use in your applications, see the "Security-Related Functions" section in Appendix C.) |
Each time you issue a query, the server determines whether you've exceeded your resource limits and, if not, checks whether you have sufficient privileges to execute the query. The resource limits are given by the max_questions and max_updates values stored in the user table, if your version of MySQL is recent enough to have those columns. The server examines your access privileges by checking, in order, the user, db, tables_priv, and columns_priv tables, until it either determines you have proper access or it has searched all the tables in vain. More specifically:
The server checks the user table entry that matched when you connected initially, to see what global privileges you have. If you have any and they are sufficient for the query, the server executes it.
If your global privileges are insufficient, the server looks for an entry for you in the db table. If it finds one, it adds the privileges in that entry to your global privileges. If the result is sufficient for the query, the server executes it.
If the combination of your global and database-level privileges is insufficient, the server keeps looking, first in the tables_priv table and then in the columns_priv table.
If you don't have permission after all the tables have been checked, the server rejects your attempt to execute the query.
In boolean terms, the privileges in the grant tables are combined by the server as follows:
user OR db OR tables_priv OR columns_priv
I see that you're wondering why the preceding description refers to only four grant tables when there are five grant tables. Okay, you caught me. The server really checks access permissions like this:
user OR (db AND host) OR tables_priv OR columns_priv
I showed the simpler expression first because the more complex expression will never come into play for most MySQL installations. That's because the host table that appears in the more complex expression is not affected at all by the GRANT and REVOKE statements. It's affected only if you manipulate it directly with INSERT, UPDATE, and so forth. This means that if you adopt the usual administrative policy of managing user accounts with GRANT and REVOKE, your host table will never be used and you can forget about it entirely.
However, if you do want to use the host table, here's how it works:
When the server checks for database-level privileges, it looks at the db table entry for the client. If the Host column value is blank, it means "Look in the host table to find out which hosts can access the database."
To check the host table, the server looks for entries with the same Db column value as the entry from the db table. If no host table entry matches the client host, no database-level privileges are granted. If an entry does have a Host column value that matches the host from which the client is connecting, the db table entry and the host table entry are combined to produce the client's database-level privileges.
However, the privileges are combined using a logical AND, which means that the client doesn't have a given privilege unless it's present in both the db table and host table entries. In this way, you can grant a basic set of privileges in the db table entry, and then selectively disable them for particular hosts using host table entries. For example, you might allow access to a database from all hosts in your domain, but turn off database privileges for hosts that are located in less secure areas.
The preceding description no doubt makes access checking sound like a rather complicated process, especially when you consider that the server checks privileges for every single query that clients issue. However, the process is quite fast because the server doesn't actually look up information from the grant tables for every query. Instead, it reads the contents of the tables into memory when it starts up and then verifies queries using the in-memory copies. This gives a performance boost to access-checking operations, but has a rather important side effect: If you change the contents of the grant tables directly, the server won't notice the privilege change.
For example, if you add a new MySQL user by using an INSERT statement to add a new record to the user table, the user named in the entry won't be able to connect to the server. This is something that often confuses new administrators (and sometimes more-experienced ones!), but the solution is quite simple. Tell the server to reload the contents of the grant tables after you change them. You can do this by issuing a FLUSH PRIVILEGES statement or by executing mysqladmin flush-privileges or mysqladmin reload.
There is no need to tell the server to reload the grant tables when you use GRANT, REVOKE, or SET PASSWORD to set up or modify a user's account. The server maps those statements onto operations that modify the grant tables and then refreshes the in-memory copies of the tables automatically.
The MySQL server sorts entries in the grant tables in a particular way and then tries to match incoming connections by looking through the entries in order. The first match found determines the entry that is used. It's important to understand the sorting order that MySQL uses, especially for the user table. This seems to trip up a lot of people in their attempts to understand MySQL security.
When the server reads the contents of the user table, it sorts entries according to the values in the Host and User columns. The Host column is dominant, so entries with the same Host value are sorted together and then ordered according to the User value. However, sorting is not lexical, or rather, it's only partially so. The principle to keep in mind is that literal values are preferred over patterns, and more-specific patterns are preferred over less-specific patterns. This means that if you're connecting from boa.snake.net and there are entries with Host values of boa.snake.net and %.snake.net, the first entry will be preferred. Similarly, %.snake.net is preferred over %.net, which in turn is preferred over %. Matching for IP numbers works that way, too. For a client connecting from a host with an IP number of 192.168.3.14, entries with the following Host values all match, but are preferred in the order shown:
192.168.3.14 192.168.3.% 192.168.% 192.% %
This section describes a particular scenario that demonstrates why it's useful to understand the order in which the server sorts user table entries when validating connection attempts. It also shows how to solve a problem that seems to be fairly common with new MySQL installations, at least judged by the frequency with which it comes up on the MySQL mailing list?a MySQL administrator sets up a new installation, including the default root and anonymous-user entries in the user table. A good administrator will assign passwords for the root accounts, but it's common to leave the anonymous users as is, with no passwords. Now, suppose the administrator wants to set up a new account for a user who will be connecting from several different hosts. The easiest way to allow this is by creating the account with % as the host part of the account name in the GRANT statement, so that the user can connect from anywhere:
GRANT ALL ON sampdb.* TO 'fred'@'%' IDENTIFIED BY 'cocoa';
The intent here is to grant the user fred all privileges for the sampdb database and allow him to connect from any host he likes. Unfortunately, the probable result is that fred will be able to connect from any host except the server host itself! For example, when fred connects from boa.snake.net, the attempt succeeds:
% mysql -p -u fred -h cobra.snake.net sampdb Enter password: cocoa mysql>
But if fred logs in on the server host cobra.snake.net and tries to connect, the attempt fails, even though fred supplies his password correctly:
% mysql -p -u fred sampdb Enter password: cocoa ERROR 1045: Access denied for user: 'fred@localhost' (Using password: YES)
This situation occurs if your user table contains the default anonymous-user entries (the entries with blank usernames). These entries are created by the mysql_install_db initialization script under UNIX and are present in the pre-initialized user table included with Windows distributions. The reason the connection fails is that when the server attempts to validate fred, one of the anonymous-user entries takes precedence over fred's entry in the matching order. The anonymous-user entry requires the user to connect with no password (not the password cocoa), so a password mismatch results.
Why does this happen? To understand what's going on, it's necessary to consider both how MySQL's grant tables are set up initially and how the server uses user table entries to validate client connections. For example, under UNIX, when you run the mysql_install_db script on cobra.snake.net to initialize the grant tables, the resulting user table contains rows with Host and User values that look like this:[1]
[1] A detailed description of the initial user table entries is given in the "Securing a New MySQL Installation" section in Chapter 11.
+-----------------+------+ | Host | User | +-----------------+------+ | localhost | root | | cobra.snake.net | root | | localhost | | | cobra.snake.net | | +-----------------+------+
The first two entries allow root to connect to the server on the local host by specifying either localhost or the host's actual name. The second two entries allow users to connect anonymously to the local server. After the administrator sets up the account for fred with the GRANT statement shown earlier, the user table contains the following entries:
+-----------------+------+ | Host | User | +-----------------+------+ | localhost | root | | cobra.snake.net | root | | localhost | | | cobra.snake.net | | | % | fred | +-----------------+------+
But the order of the entries as shown is not the order the server uses when validating connection requests. Instead, it sorts entries by host first and then by user within host, putting more-specific values first and less-specific values last:
+-----------------+------+ | Host | User | +-----------------+------+ | localhost | root | | localhost | | | cobra.snake.net | root | | cobra.snake.net | | | % | fred | +-----------------+------+
The two entries with localhost in the Host column sort together, with the entry for root first because that's a more specific username than the blank value. The entries with cobra.snake.net sort together in a similar way. Furthermore, all four of these entries have a literal Host value without any wildcard characters, so they all sort ahead of the entry for fred, which does use a wildcard character in its Host value. In particular, both of the anonymous user entries take precedence over fred's entry in the sorting order.
The result is that when fred attempts to connect from the local host, one of the entries with a blank username matches before the entry containing % in the Host column. The blank password in the anonymous user entry doesn't match fred's password of cocoa, so the connection fails. One implication of this phenomenon is that it is possible for fred to connect from the local host if he specifies no password. But then he will be validated as an anonymous user and won't have the privileges associated with the fred@% account.
What all this means is that although it's very convenient to use wildcards when you set up an account for a user who will connect from multiple hosts, the user may have problems connecting from the local host due to the anonymous entries in the user table.
What is the solution to this problem? Actually, there are two. First, you can set up another account for fred that explicitly lists localhost as the host value:
GRANT ALL ON sampdb.* TO 'fred'@'localhost' IDENTIFIED BY 'cocoa';
If you do that, the entries in the user table will sort as follows:
+-----------------+------+ | Host | User | +-----------------+------+ | localhost | fred | | localhost | root | | localhost | | | cobra.snake.net | root | | cobra.snake.net | | | % | fred | +-----------------+------+
Now when fred connects from the local host, the entry with localhost and fred will match ahead of the anonymous user entries. When he connects from any other host, the entry with % and fred will match. The downside of having two entries for fred is that whenever you want to make a privilege or password change for him, you'll have to make the change twice.
The second solution is to delete the anonymous entries from the user table entirely. To do this, you cannot use REVOKE, because that only revokes privileges; it won't remove account entries from the user table. It's necessary to use DELETE instead:
% mysql -u root mysql mysql> DELETE FROM user WHERE User = ''; mysql> FLUSH PRIVILEGES;
The sort order of the remaining entries becomes:
+-----------------+------+ | Host | User | +-----------------+------+ | localhost | root | | cobra.snake.net | root | | % | fred | +-----------------+------+
Now when fred attempts to connect from the local host, he'll succeed, because there won't be any user table entries that will match ahead of his.
In general, I recommend that if you want to make your life easier as an administrator, you should delete the anonymous-user entries that are present in the initial grant tables. (In my view, these entries are generally not very useful, and they tend to cause more problems than they're worth.)
The puzzle presented in this section addresses a specific situation, but contains a more general lesson. If privileges for a given account don't work the way you expect, look in the grant tables to see if there's some entry containing Host values that are more specific than the entry for the user in question and that will match connection attempts by that user. If so, that may explain the problem. You may need to make the user's entry more specific or add another entry to cover the more specific case.
This section describes some precautions to observe when you grant privileges and the attendant risks of unwise choices.
Avoid creating anonymous user accounts. Even if they don't have privileges to cause damage directly, allowing a user to connect still may provide access to that user to look around and gather information, such as what databases and tables you have.
Find accounts that have no passwords and either remove them or assign passwords. To find such accounts, use the following query in the mysql database:
mysql> SELECT Host, User FROM user WHERE Password = '';
If it's not necessary, don't use patterns in hostname specifiers when setting up accounts. Broadening the range of hosts from which a given user can connect also broadens the range from which an imposter claiming to be that user can try to break in.
Grant superuser privileges sparingly. That is, don't enable privileges in user table entries. Those privileges are global and allow the user to affect the operation of your server or to access any table in any database. Instead, use the other grant tables to restrict user privileges to particular databases, tables, or columns.
Don't grant privileges for the mysql database because it contains the grant tables. A user with privileges on that database may be able to modify its tables to acquire privileges on any other database as well. In effect, granting privileges that allow a user to modify the my