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.)
Structure and Contents of the MySQL Grant Tables
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.
Table 12.1. Grant Table Scope-of-Access Columns 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
Table 12.2. Grant Table Privilege Columns 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
Table 12.3. Grant Table SSL and Resource Management Columns (user Table Only) 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 Structure
The 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:
The tables_priv and columns_priv tables were introduced in MySQL 3.22.11 (at the same time as the GRANT statement). If you have an older version of MySQL, your mysql database will have only the user, db, and host tables.
The SSL columns in the user table were added in MySQL 4.0.0.
The resource-management columns in the user table were added in MySQL 4.0.2.
Several privilege columns were added to the user table in MySQL 4.0.2—Create_tmp_table_priv, Execute_priv, Lock_tables_priv, Repl_client_priv, Repl_slave_priv, Show_db_priv, and Super_priv. In MySQL 4.0.4, the Create_tmp_table_priv and Lock_tables_priv privileges were added to the db and hosts tables as well.
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:
Show_db_priv is set to the existing Select_priv value.
Execute_priv and Super_priv are set to the existing Process_priv value. Repl_client_priv and Repl_slave_priv are set to the existing File_priv value.
Create_tmp_table_priv and Lock_tables_priv are set to 'Y'.
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.
Grant Table Scope-of-Access Columns
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.
Grant Table Privilege Columns
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.
Administrative Privileges
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.
Database and Table Privileges
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.
Privilege Column Storage Structure
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.
Grant Table SSL-Related Columns
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.
Grant Table Resource Management 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.
How the Server Controls Client Access
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.
Scope Column Contents
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).
Table 12.4. Case Sensitivity in Grant Table Scope Columns Column Case Sensitive
Host No
User Yes
Password Yes
Db Yes
Table_name Yes
Column_name No
How Passwords Are Stored in the user Table
The 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.)
Query Access Verification
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.
Scope Column Matching Order
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.%
%
A Privilege Puzzle
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.
Grant Table Risks to Avoid
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 mysql database tables gives that user a global GRANT OPTION privilege. (After all, if the user can modify the tables directly, that's pretty much equivalent to being able to issue any GRANT statement you can think of.)
Be careful with the GRANT OPTION privilege. Two users with different privileges that both have the GRANT OPTION privilege can make each other's access rights more powerful.
The FILE privilege is particularly dangerous; don't grant it lightly. The following is an example of something a user with the FILE privilege can do:
CREATE TABLE etc_passwd (pwd_entry TEXT);
LOAD DATA INFILE '/etc/passwd' INTO TABLE etc_passwd;
After executing those statements, the user has access to contents of your server host's password file just by issuing a SELECT:
SELECT * FROM etc_passwd;
The name of any publicly readable file on the server host can be substituted for /etc/passwd in the LOAD DATA statement. If the user has connected from a remote host, the effect is that granting the FILE privilege gives that user network access to potentially a large portion of your server host's file system.
The FILE privilege also can be exploited to compromise databases on systems that aren't set up with sufficiently restrictive data directory permissions. This is a reason why you should set the data directory contents to be readable only by the server. If files corresponding to database tables are world readable, not only can any user with an account on the server host read them, but any client user with the FILE privilege can connect over the network and read them, too! The following procedure demonstrates how:
Create a table with a LONGBLOB column:
USE test;
CREATE TABLE tmp (b LONGBLOB);
Use the table to read in the contents of each of the files that correspond to the table you want to steal. For example, if a user has a MyISAM table named x in a database other_db, the table is represented by three files, x.frm, x.MYD, and x.MYI. You can read those files and copy them into corresponding files in the test database as follows:
LOAD DATA INFILE './other_db/x.frm' INTO TABLE tmp
FIELDS ESCAPED BY '' LINES TERMINATED BY '';
SELECT * FROM tmp INTO OUTFILE 'x.frm'
FIELDS ESCAPED BY '' LINES TERMINATED BY '';
DELETE FROM tmp;
LOAD DATA INFILE './other_db/x.MYD' INTO TABLE tmp
FIELDS ESCAPED BY '' LINES TERMINATED BY '';
SELECT * FROM tmp INTO OUTFILE 'x.MYD'
FIELDS ESCAPED BY '' LINES TERMINATED BY '';
DELETE FROM tmp;
LOAD DATA INFILE './other_db/x.MYI' INTO TABLE tmp
FIELDS ESCAPED BY '' LINES TERMINATED BY '';
SELECT * FROM tmp INTO OUTFILE 'x.MYI'
FIELDS ESCAPED BY '' LINES TERMINATED BY '';
After executing those statements, the test database directory also will contain files named x.frm, x.MYD, and x.MYI. In other words, the test database will contain a table x that is a stolen duplicate of the table in the other_db database.
To avoid having someone attack your users' tables in the same way, set the permissions on your data directory contents according to the instructions given earlier in this chapter in the "Securing Your MySQL Installation" section. As an additional measure, you can also use the --skip-show-database option when you start the server to limit users from using SHOW DATABASES and from using SHOW TABLES for databases to which they have no access. This helps prevent users from finding out about databases and tables they shouldn't be accessing.
The dangers of the FILE privilege are amplified if you run the MySQL server as root. That's inadvisable in the first place, and is particularly so when combined with FILE. Because root can create files anywhere in the file system, a user with the FILE privilege can do so as well, even a user who has connected from a remote host. The server won't create a file that already exists, but it's sometimes possible to create new files that will alter the operation of the server host or compromise its security. For example, if any of the /etc/resolv.conf, /etc/hosts.equiv, /etc/hosts.lpd, or /etc/sudoers files do not exist, a user who can use the MySQL server to create them can drastically change the way your server host behaves. To avoid these problems, don't run mysqld as root. (See the "Running the Server Using an Unprivileged Login Account" section in Chapter 11.)
The PROCESS privilege should be granted only to trusted MySQL accounts. With PROCESS, a user can use SHOW PROCESSLIST to see the text of queries being executed by the server. This allows a user to snoop on other users and possibly see information that should remain private.
Don't give people the RELOAD privilege who don't need it. RELOAD allows a user to issue FLUSH and RESET statements, which can be abused in several ways:
If you have configured the server to perform update or binary update logging using a numbered sequence of files, each FLUSH LOGS statement creates a new update or binary update log file. A user with the RELOAD privilege who performs many log flushing operations can cause the server to create large numbers of files.
A user with the RELOAD privilege can defeat the resource management mechanism by reloading the grant tables with FLUSH PRIVILEGES or with FLUSH USER_RESOURCES. Both statements reset all resource management counters to zero.
FLUSH TABLES can be used repeatedly to cause the server to flush its open-table cache, which degrades performance by preventing the server from taking advantage of the cache. RESET QUERY CACHE has the same effect on performance by negating the benefits of the query cache.
RESET MASTER LOGS causes a replication master server to delete all of its binary update logs whether or not they are still in use, which removes the information necessary to maintain replication integrity.
The ALTER privilege can be used in ways you may not intend. Suppose you want user1 to be able to access table1 but not table2. A user with the ALTER privilege may be able to subvert your intent by using ALTER TABLE to rename table2 to table1.
Setting Up MySQL Accounts Without GRANT Statements
If you have a version of MySQL older than 3.22.11, you can't use the GRANT (or REVOKE) statements to manage MySQL accounts and access privileges. However, you can modify the contents of the grant tables directly using statements like INSERT. It's easier to do that if you understand how the GRANT statement modifies the grant tables because you'll know what kind of INSERT statements correspond to various GRANT statements.
GRANT acts to modify the grant tables as follows:
When you issue a GRANT statement, you specify a username and hostname and possibly a password. A user table entry is created for the user, and these values are recorded in the User, Host, and Password columns of the entry. In addition, if you specify global privileges in the GRANT statement, those privileges are recorded in the privilege columns of the user table entry.
If you specify database-level privileges in the GRANT statement, the username and hostname are recorded in the User and Host columns of a db table entry. The database you granted privileges for is recorded in the Db column, and the privileges you granted are recorded in the privilege columns.
For table-level and column-level privileges, the effects are similar. Entries are created in the tables_priv and columns_priv tables to record the username, hostname, and database, as well as the table or table and column as necessary. The privileges granted are recorded in the privilege columns.
If you keep the preceding description in mind, you should be able to do anything GRANT does without using GRANT itself. But remember that when you modify the grant tables directly, you'll need to tell the server to reload the grant tables or it won't notice your changes. You can force a reload by executing a mysqladmin reload command.[2] If you forget to do that, you'll be wondering why the server isn't doing what you want.
[2] The mysqladmin flush-privileges command and the FLUSH PRIVILEGES statement also tell the server to reload the grant tables, but of course if the reason you're not using GRANT is that your server is too old to support it, it's also too old to support mysqladmin flush-privileges or FLUSH PRIVILEGES.
The following GRANT statement uses ON *.* to specify global privileges. It creates an account for a superuser who has all privileges, including the ability to grant privileges to other users:
GRANT ALL ON *.* TO 'ethel'@'localhost' IDENTIFIED BY 'coffee'
WITH GRANT OPTION;
The statement will create an entry for ethel@localhost in the user table. It also will turn on all the privileges there because that's where superuser (global) privileges are stored. To do the same thing with INSERT, the statement is as follows:
INSERT INTO user VALUES('localhost','ethel',PASSWORD('coffee'),
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
That's one ugly INSERT statement! You may even find that it doesn't work, depending on your version of MySQL. This statement assumes that the user table has 14 privilege columns, which is the number present at the point when GRANT was implemented. Because the structure of the grant tables has changed on occasion, you may have a different number. Use SHOW COLUMNS to find out just what privilege columns your user table contains, and adjust the INSERT statement accordingly. Note too that although the GRANT statement encrypts the password for you, INSERT does not; it's necessary to use the PASSWORD() function to encrypt passwords in your INSERT statements.
The following GRANT statement creates another account with superuser status, but for only a single privilege:
GRANT RELOAD ON *.* TO 'flush'@'localhost' IDENTIFIED BY 'flushpass';
You may remember this statement from Chapter 11 where we created an account for log file maintenance purposes that has privileges for flushing the server logs. The equivalent INSERT statement for this GRANT statement is a bit simpler than for the preceding one, so it's easier to list the column names and specify only the one privilege column. Each of the other privilege columns will be set to its default value ('N'):
INSERT INTO user (Host,User,Password,Reload_priv)
VALUES('localhost','flush',PASSWORD('flushpass'),'Y');
Database-level privileges are granted with an ON db_name.* clause rather than ON *.*:
GRANT ALL ON sampdb.* TO 'boris'@'localhost' IDENTIFIED BY 'ruby';
These privileges are not global, so they won't be stored in the user table. However, to duplicate the effect of this GRANT statement, it's necessary to create an entry in the user table so that the user can connect. This means that a user table entry is needed along with a db table entry that records the database-level privileges:
INSERT INTO user (Host,User,Password)
VALUES('localhost','boris',PASSWORD('ruby'));
INSERT INTO db VALUES('localhost','sampdb','boris',
'Y','Y','Y','Y','Y','Y','N','Y','Y','Y');
The 'N' value in the second statement is for the GRANT OPTION privilege. Setting the column to 'Y' instead would duplicate the effect of a database-level GRANT statement that has WITH GRANT OPTION at the end.
To set table-level or column-level privileges, use INSERT statements for the tables_priv or columns_priv tables. Of course, if you don't have the GRANT statement, you won't have those two tables, either, because they appeared in MySQL at the same time. If you do have the tables and want to manipulate them manually for some reason, be aware that you don't enable privileges using individual columns. You set either the tables_priv.Table_priv or columns_priv.Column_priv column to a SET value consisting of the privileges you want to enable. For example, to enable SELECT and INSERT privileges for a table, you'd set the Table_priv column to a value of 'Select,Insert' in the relevant tables_priv entry.
If you want to modify privileges for a MySQL account that already exists, use UPDATE rather than INSERT. This is true whether you are adding or revoking privileges.
To remove an account entirely, use DELETE to remove entries from each grant table in which the account appears. For example, to remove an account for mike@%.snake.net, issue the following statements:
DELETE FROM user WHERE User = 'mike' AND Host = '%.snake.net';
DELETE FROM db WHERE User = 'mike' AND Host = '%.snake.net';
DELETE FROM host WHERE User = 'mike' AND Host = '%.snake.net';
If you also happen to have the tables_priv or columns_priv tables, issue the following statements as well:
DELETE FROM tables_priv WHERE User = 'mike' AND Host = '%.snake.net';
DELETE FROM columns_priv WHERE User = 'mike' AND Host = '%.snake.net';
If you prefer to avoid issuing queries that modify the grant tables directly, you may want to take a look at the mysqlaccess and mysql_setpermissions scripts that come with the MySQL distribution.
No comments:
Post a Comment