4.8. The MySQL Access Privilege System

MySQL has an advanced but non-standard security and privilege system. The following discussion describes how it works.

4.8.1. What the Privilege System Does

The primary function of the MySQL privilege system is to authenticate a user who connects from a given host and to associate that user with privileges on a database such as SELECT, INSERT, UPDATE, and DELETE.

Additional functionality includes the capability to have anonymous users and to grant privileges for MySQL-specific functions such as LOAD DATA INFILE and administrative operations.

4.8.2. How the Privilege System Works

The MySQL privilege system ensures that all users may perform only the operations allowed to them. As a user, when you connect to a MySQL server, your identity is determined by the host from which you connect and the username you specify. When you issue requests after connecting, the system grants privileges according to your identity and what you want to do.

MySQL considers both your hostname and username in identifying you because there is little reason to assume that a given username belongs to the same person everywhere on the Internet. For example, the user joe who connects from office.example.com need not be the same person as the user joe who connects from home.example.com. MySQL handles this by allowing you to distinguish users on different hosts that happen to have the same name: You can grant one set of privileges for connections by joe from office.example.com, and a different set of privileges for connections by joe from home.example.com.

MySQL access control involves two stages when you run a client program that connects to the server:

• Stage 1: The server checks whether it should allow you to connect.

• Stage 2: Assuming that you can connect, the server checks each statement you issue to determine whether you have sufficient privileges to perform it. For example, if you try to select rows from a table in a database or drop a table from the database, the server verifies that you have the SELECT privilege for the table or the DROP privilege for the database.

If your privileges are changed (either by yourself or someone else) while you are connected, those changes do not necessarily take effect immediately for the next statement that you issue. See Section 4.8.7, "When Privilege Changes Take Effect," for details.

The server stores privilege information in the grant tables of the mysql database (that is, in the database named mysql). The MySQL server reads the contents of these tables into memory when it starts and re-reads them under the circumstances indicated in Section 4.8.7, "When Privilege Changes Take Effect." Access-control decisions are based on the in-memory copies of the grant tables.

Normally, you manipulate the contents of the grant tables indirectly by using statements such as GRANT and REVOKE to set up accounts and control the privileges available to each one. The discussion here describes the underlying structure of the grant tables and how the server uses their contents when interacting with clients.

The server uses the user, db, and host tables in the mysql database at both stages of access control. The columns in the user and db tables are shown here. The host table is similar to the db table but has a specialized use as described in Section 4.8.6, "Access Control, Stage 2: Request Verification."

Table Name	User	Db
Scope columns	Host	Host
	User	Db
	Password	User
Privilege columns	Select_priv	Select_priv
	Insert_priv	Insert_priv
	Update_priv	Update_priv
	Delete_priv	Delete_priv
	Index_priv	Index_priv
	Alter_priv	Alter_priv
	Create_priv	Create_priv
	Drop_priv	Drop_priv
	Grant_priv	Grant_priv
	Create_view_priv	Create_view_priv
	Show_view_priv	Show_view_priv
	Create_routine_priv	Create_routine_priv
	Alter_routine_priv	Alter_routine_priv
	Execute_priv	Execute_priv
	Create_tmp_table_priv	Create_tmp_table_priv
	Lock_tables_priv	Lock_tables_priv
	References_priv	References_priv
	Reload_priv
	Shutdown_priv
	Process_priv
	File_priv
	Show_db_priv
	Super_priv
	Repl_slave_priv
	Repl_client_priv
Security columns	ssl_type
	ssl_cipher
	x509_issuer
	x509_subject
Resource control columns	max_questions
	max_updates
	max_connections
	max_user_connections

Execute_priv was present in MySQL 5.0.0, but did not become operational until MySQL 5.0.3.

The Create_view_priv and Show_view_priv columns were added in MySQL 5.0.1.

The Create_routine_priv, Alter_routine_priv, and max_user_connections columns were added in MySQL 5.0.3.

During the second stage of access control, the server performs request verification to make sure that each client has sufficient privileges for each request that it issues. In addition to the user, db, and host grant tables, the server may also consult the tables_priv and columns_priv tables for requests that involve tables. The tables_priv and columns_priv tables provide finer privilege control at the table and column levels. They have the following columns:

Table Name	tables_priv	columns_priv
Scope columns	Host Host
	Db Db
	User	User
	Table_name Table_name
		Column_name
Privilege columns	Table_priv	Column_priv
	Column_priv
Other columns	Timestamp	Timestamp
	Grantor

The Timestamp and Grantor columns currently are unused and are discussed no further here.

For verification of requests that involve stored routines, the server may consult the procs_priv table. This table has the following columns:

Table Name	procs_priv
Scope columns	Host
	Db
	User
	Routine_name
	Routine_type
Privilege columns	Proc_priv
Other columns	Timestamp
	Grantor

The procs_priv table exists as of MySQL 5.0.3. The Routine_type column was added in MySQL 5.0.6. It is an ENUM column with values of 'FUNCTION' or 'PROCEDURE' to indicate the type of routine the row refers to. This column allows privileges to be granted separately for a function and a procedure with the same name.

The Timestamp and Grantor columns currently are unused and are discussed no further here.

Each grant table contains scope columns and privilege columns:

• Scope columns determine the scope of each row (entry) in the tables; that is, the context in which the row applies. For example, a user table row with Host and User values of 'thomas.loc.gov' and 'bob' would be used for authenticating connections made to the server from the host thomas.loc.gov by a client that specifies a username of bob. Similarly, a db table row with Host, User, and Db column values of 'thomas.loc.gov','bob', and 'reports' would be used when bob connects from the host thomas.loc.gov to access the reports database. The tables_priv and columns_priv tables contain scope columns indicating tables or table/column combinations to which each row applies. The procs_priv scope columns indicate the stored routine to which each row applies.

• Privilege columns indicate which privileges are granted by a table row; that is, what operations can be performed. The server combines the information in the various grant tables to form a complete description of a user's privileges. Section 4.8.6, "Access Control, Stage 2: Request Verification," describes the rules that are used to do this.

Scope columns contain strings. They are declared as shown here; the default value for each is the empty string:

Column Name	Type
Host	CHAR(60)
User	CHAR(16)
Password	CHAR(16)
Db	CHAR(64)
Table_name	CHAR(64)
Column_name	CHAR(64)
Routine_name	CHAR(64)

For access-checking purposes, comparisons of Host values are case insensitive. User, Password, Db, and Table_name values are case sensitive. Column_name and Routine_name values are case insensitive.

In the user, db, and host tables, each privilege is listed in a separate column that is declared as ENUM(N','Y') DEFAULT 'N'. In other words, each privilege can be disabled or enabled, with the default being disabled.

In the tables_priv, columns_priv, and procs_priv tables, the privilege columns are declared as SET columns. Values in these columns can contain any combination of the privileges controlled by the table:

Table Name	Column Name	Possible Set Elements
tables_priv	Table_priv	'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter', 'Create View', 'Show view'
tables_priv	Column_priv	'Select', 'Insert', 'Update', 'References'
columns_priv	Column_priv	'Select', 'Insert', 'Update', 'References'
procs_priv	Proc_priv	'Execute', 'Alter Routine', 'Grant'

Briefly, the server uses the grant tables in the following manner:

• The user table scope columns determine whether to reject or allow incoming connections. For allowed connections, any privileges granted in the user table indicate the user's global (superuser) privileges. Any privilege granted in this table applies to all databases on the server.

  Note: Because any global privilege is considered a privilege for all databases, any global privilege enables a user to see all database names with SHOW DATABASES or by examining the SCHEMATA table of INFORMATION_SCHEMA.

• The db table scope columns determine which users can access which databases from which hosts. The privilege columns determine which operations are allowed. A privilege granted at the database level applies to the database and to all its tables.

• The host table is used in conjunction with the db table when you want a given db table row to apply to several hosts. For example, if you want a user to be able to use a database from several hosts in your network, leave the Host value empty in the user's db table row, and then populate the host table with a row for each of those hosts. This mechanism is described more detail in Section 4.8.6, "Access Control, Stage 2: Request Verification."

  Note: The host table must be modified directly with statements such as INSERT, UPDATE, and DELETE. It is not affected by statements such as GRANT and REVOKE that modify the grant tables indirectly. Most MySQL installations need not use this table at all.

• The tables_priv and columns_priv tables are similar to the db table, but are more fine-grained: They apply at the table and column levels rather than at the database level. A privilege granted at the table level applies to the table and to all its columns. A privilege granted at the column level applies only to a specific column.

• The procs_priv table applies to stored routines. A privilege granted at the routine level applies only to a single routine.

Administrative privileges (such as RELOAD or SHUTDOWN) are specified only in the user table. The reason for this is that administrative operations are operations on the server itself and are not database-specific, so there is no reason to list these privileges in the other grant tables. In fact, to determine whether you can perform an administrative operation, the server need consult only the user table.

The FILE privilege also is specified only in the user table. It is not an administrative privilege as such, but your ability to read or write files on the server host is independent of the database you are accessing.

The mysqld server reads the contents of the grant tables into memory when it starts. You can tell it to re-read the tables by issuing a FLUSH PRIVILEGES statement or executing a mysqladmin flush-privileges or mysqladmin reload command. Changes to the grant tables take effect as indicated in Section 4.8.7, "When Privilege Changes Take Effect."

When you modify the contents of the grant tables, it is a good idea to make sure that your changes set up privileges the way you want. To check the privileges for a given account, use the SHOW GRANTS statement. For example, to determine the privileges that are granted to an account with Host and User values of pc84.example.com and bob, issue this statement:

SHOW GRANTS FOR  'bob'@'pc84.example.com';

For general advice on security issues, see Section 4.7, "General Security Issues." For additional help in diagnosing privilege-related problems, see Section 4.8.8, "Causes of Access denied Errors."

4.8.3. Privileges Provided by MySQL

Information about account privileges is stored in the user, db, host, tables_priv, columns_priv, and procs_priv tables in the mysql database. The MySQL server reads the contents of these tables into memory when it starts and re-reads them under the circumstances indicated in Section 4.8.7, "When Privilege Changes Take Effect." Access-control decisions are based on the in-memory copies of the grant tables.

The names used in the GRANT and REVOKE statements to refer to privileges are shown in the following table, along with the column name associated with each privilege in the grant tables and the context in which the privilege applies.

Privilege	Column	Context
CREATE	Create_priv	Databases, tables, or indexes
DROP	Drop_priv	Databases or tables
GRANT OPTION	Grant_priv	Databases, tables, or stored routines
REFERENCES	References_priv	Databases or tables
ALTER	Alter_priv	Tables
DELETE	Delete_priv	Tables
INDEX	Index_priv	Tables
INSERT	Insert_priv	Tables
SELECT	Select_priv	Tables
UPDATE	Update_priv	Tables
CREATE VIEW	Create_view_priv	Views
SHOW VIEW	Show_view_priv	Views
ALTER ROUTINE	Alter_routine_priv	Stored routines
CREATE ROUTINE	Create_routine_priv	Stored routines
EXECUTE	Execute_priv	Stored routines
FILE	File_priv	File access on server host
CREATE TEMPORARY TABLES	Create_tmp_table_priv	Server administration
LOCK TABLES	Lock_tables_priv	Server administration
CREATE USER	Create_user_priv	Server administration
PROCESS	Process_priv	Server administration
RELOAD	Reload_priv	Server administration
REPLICATION CLIENT	Repl_client_priv	Server administration
REPLICATION SLAVE	Repl_slave_priv	Server administration
SHOW DATABASES	Show_db_priv	Server administration
SHUTDOWN	Shutdown_priv	Server administration
SUPER	Super_priv	Server administration

Some releases of MySQL introduce changes to the structure of the grant tables to add new privileges or features. Whenever you update to a new version of MySQL, you should update your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities. See Section 4.6.2, "mysql_upgradeCheck Tables for MySQL Upgrade."

CREATE VIEW and SHOW VIEW were added in MySQL 5.0.1. CREATE USER, CREATE ROUTINE, and ALTER ROUTINE were added in MySQL 5.0.3. Although EXECUTE was present in MySQL 5.0.0, it did not become operational until MySQL 5.0.3.

To create or alter stored routines if binary logging is enabled, you may also need the SUPER privilege.

The CREATE and DROP privileges allow you to create new databases and tables, or to drop (remove) existing databases and tables. If you grant the DROP privilege for the mysql database to a user, that user can drop the database in which the MySQL access privileges are stored.

The SELECT, INSERT, UPDATE, and DELETE privileges allow you to perform operations on rows in existing tables in a database.

SELECT statements require the SELECT privilege only if they actually retrieve rows from a table. Some SELECT statements do not access tables and can be executed without permission for any database. For example, you can use the mysql client as a simple calculator to evaluate expressions that make no reference to tables:

SELECT 1+1;
SELECT PI()*2;

The INDEX privilege enables you to create or drop (remove) indexes. INDEX applies to existing tables. If you have the CREATE privilege for a table, you can include index definitions in the CREATE TABLE statement.

The ALTER privilege enables you to use ALTER TABLE to change the structure of or rename tables.

The CREATE ROUTINE privilege is needed for creating stored routines (functions and procedures). ALTER ROUTINE privilege is needed for altering or dropping stored routines, and EXECUTE is needed for executing stored routines.

The GRANT privilege enables you to give to other users those privileges that you yourself possess. It can be used for databases, tables, and stored routines.

The FILE privilege gives you permission to read and write files on the server host using the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements. A user who has the FILE privilege can read any file on the server host that is either world-readable or readable by the MySQL server. (This implies the user can read any file in any database directory, because the server can access any of those files.) The FILE privilege also enables the user to create new files in any directory where the MySQL server has write access. As a security measure, the server will not overwrite existing files.

The remaining privileges are used for administrative operations. Many of them can be performed by using the mysqladmin program or by issuing SQL statements. The following table shows which mysqladmin commands each administrative privilege enables you to execute:

Privilege	Commands Permitted to Privilege Holders
RELOAD	flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload
SHUTDOWN	Shutdown
PROCESS	Processlist
SUPER	Kill

The reload command tells the server to re-read the grant tables into memory. flush-privileges is a synonym for reload. The refresh command closes and reopens the log files and flushes all tables. The other flush-xxx commands perform functions similar to refresh, but are more specific and may be preferable in some instances. For example, if you want to flush just the log files, flush-logs is a better choice than refresh.

The shutdown command shuts down the server. There is no corresponding SQL statement.

The processlist command displays information about the threads executing within the server (that is, information about the statements being executed by clients). The kill command terminates server threads. You can always display or kill your own threads, but you need the PROCESS privilege to display threads initiated by other users and the SUPER privilege to kill them.

The CREATE TEMPORARY TABLES privilege enables the use of the keyword TEMPORARY in CREATE TABLE statements.

The LOCK TABLES privilege enables the use of explicit LOCK TABLES statements to lock tables for which you have the SELECT privilege. This includes the use of write locks, which prevents anyone else from reading the locked table.

The REPLICATION CLIENT privilege enables the use of SHOW MASTER STATUS and SHOW SLAVE STATUS.

The REPLICATION SLAVE privilege should be granted to accounts that are used by slave servers to connect to the current server as their master. Without this privilege, the slave cannot request updates that have been made to databases on the master server.

The SHOW DATABASES privilege allows the account to see database names by issuing the SHOW DATABASE statement. Accounts that do not have this privilege see only databases for which they have some privileges, and cannot use the statement at all if the server was started with the --skip-show-database option. Note that any global privilege is a privilege for the database.

It is a good idea to grant to an account only those privileges that it needs. You should exercise particular caution in granting the FILE and administrative privileges:

• The FILE privilege can be abused to read into a database table any files that the MySQL server can read on the server host. This includes all world-readable files and files in the server's data directory. The table can then be accessed using SELECT to transfer its contents to the client host.

• The GRANT privilege enables users to give their privileges to other users. Two users that have different privileges and with the GRANT privilege are able to combine privileges.

• The ALTER privilege may be used to subvert the privilege system by renaming tables.

• The SHUTDOWN privilege can be abused to deny service to other users entirely by terminating the server.

• The PROCESS privilege can be used to view the plain text of currently executing statements, including statements that set or change passwords.

• The SUPER privilege can be used to terminate other clients or change how the server operates.

• Privileges granted for the mysql database itself can be used to change passwords and other access privilege information. Passwords are stored encrypted, so a malicious user cannot simply read them to know the plain text password. However, a user with write access to the user table Password column can change an account's password, and then connect to the MySQL server using that account.

There are some things that you cannot do with the MySQL privilege system:

• You cannot explicitly specify that a given user should be denied access. That is, you cannot explicitly match a user and then refuse the connection.

• You cannot specify that a user has privileges to create or drop tables in a database but not to create or drop the database itself.

• A password applies globally to an account. You cannot associate a password with a specific object such as a database, table, or routine.

4.8.4. Connecting to the MySQL Server

MySQL client programs generally expect you to specify certain connection parameters when you want to access a MySQL server:

• The name of the host where the MySQL server is running

• Your username

• Your password

For example, the mysql client can be started as follows from a command-line prompt (indicated here by shell>):

shell> mysql -h host_name  -u user_name  -pyour_pass

Alternative forms of the -h, -u, and -p options are --host=host_name, --user=user_name, and --password=your_pass. Note that there is no space between -p or --password= and the password following it.

If you use a -p or --password option but do not specify the password value, the client program prompts you to enter the password. The password is not displayed as you enter it. This is more secure than giving the password on the command line. Any user on your system may be able to see a password specified on the command line by executing a command such as ps auxww. See Section 4.9.6, "Keeping Your Password Secure."

MySQL client programs use default values for any connection parameter option that you do not specify:

• The default hostname is localhost.

• The default username is ODBC on Windows and your Unix login name on Unix.

• No password is supplied if neither -p nor --password is given.

Thus, for a Unix user with a login name of joe, all of the following commands are equivalent:

shell> mysql -h localhost -u joe
shell> mysql -h localhost
shell> mysql -u joe
shell> mysql

Other MySQL clients behave similarly.

You can specify different default values to be used when you make a connection so that you need not enter them on the command line each time you invoke a client program. This can be done in a couple of ways:

• You can specify connection parameters in the [client] section of an option file. The relevant section of the file might look like this:

  [client]
  host=host_name
  user=user_name
  password=your_pass
  

  Section 3.3.2, "Using Option Files," discusses option files further.

• You can specify some connection parameters using environment variables. The host can be specified for mysql using MYSQL_HOST. The MySQL username can be specified using USER (this is for Windows and NetWare only). The password can be specified using MYSQL_PWD, although this is insecure; see Section 4.9.6, "Keeping Your Password Secure."

4.8.5. Access Control, Stage 1: Connection Verification

When you attempt to connect to a MySQL server, the server accepts or rejects the connection based on your identity and whether you can verify your identity by supplying the correct password. If not, the server denies access to you completely. Otherwise, the server accepts the connection, and then enters Stage 2 and waits for requests.

Your identity is based on two pieces of information:

• The client host from which you connect

• Your MySQL username

Identity checking is performed using the three user table scope columns (Host, User, and Password). The server accepts the connection only if the Host and User columns in some user table row match the client hostname and username and the client supplies the password specified in that row.

Host values in the user table may be specified as follows:

• A Host value may be a hostname or an IP number, or 'localhost' to indicate the local host.

• You can use the wildcard characters '%' and '_' in Host column values. These have the same meaning as for pattern-matching operations performed with the LIKE operator. For example, a Host value of '%' matches any hostname, whereas a value of %.mysql.com' matches any host in the mysql.com domain.

• For Host values specified as IP numbers, you can specify a netmask indicating how many address bits to use for the network number. For example:

  GRANT ALL PRIVILEGES ON db.* TO david@ '192.58.197.0/255.255.255.0';
  

  This allows david to connect from any client host having an IP number client_ip for which the following condition is true:

  client_ip & netmask = host_ip
  

  That is, for the GRANT statement just shown:

  client_ip & 255.255.255.0 = 192.58.197.0
  

  IP numbers that satisfy this condition and can connect to the MySQL server are those in the range from 192.58.197.0 to 192.58.197.255.

  Note: The netmask can only be used to tell the server to use 8, 16, 24, or 32 bits of the address. Examples:

  • 192.0.0.0/255.0.0.0: Anything on the 192 class A network

  • 192.168.0.0/255.255.0.0: Anything on the 192.168 class B network

  • 192.168.1.0/255.255.255.0: Anything on the 192.168.1 class C network

  • 192.168.1.1: Only this specific IP

  The following netmask (28 bits) will not work:

  192.168.0.1/255.255.255.240

• A blank Host value in a db table row means that its privileges should be combined with those in the row in the host table that matches the client hostname. The privileges are combined using an AND (intersection) operation, not OR (union). Section 4.8.6, "Access Control, Stage 2: Request Verification," discusses use of the host table further.

  A blank Host value in the other grant tables is the same as '%'.

Because you can use IP wildcard values in the Host column (for example, '144.155.166.%' to match every host on a subnet), someone could try to exploit this capability by naming a host 144.155.166.somewhere.com. To foil such attempts, MySQL disallows matching on host-names that start with digits and a dot. Thus, if you have a host named something like 1.2.foo.com, its name never matches the Host column of the grant tables. An IP wildcard value can match only IP numbers, not hostnames.

In the User column, wildcard characters are not allowed, but you can specify a blank value, which matches any name. If the user table row that matches an incoming connection has a blank username, the user is considered to be an anonymous user with no name, not a user with the name that the client actually specified. This means that a blank username is used for all further access checking for the duration of the connection (that is, during Stage 2).

The Password column can be blank. This is not a wildcard and does not mean that any password matches. It means that the user must connect without specifying a password.

Non-blank Password values in the user table represent encrypted passwords. MySQL does not store passwords in plaintext form for anyone to see. Rather, the password supplied by a user who is attempting to connect is encrypted (using the PASSWORD() function). The encrypted password then is used during the connection process when checking whether the password is correct. (This is done without the encrypted password ever traveling over the connection.) From MySQL's point of view, the encrypted password is the real password, so you should never give anyone access to it. In particular, do not give non-administrative users read access to tables in the mysql database.

MySQL 5.0 employs the stronger authentication method (first implemented in MySQL 4.1) that has better password protection during the connection process than in earlier versions. It is secure even if TCP/IP packets are sniffed or the mysql database is captured. Section 4.8.9, "Password Hashing as of MySQL 4.1," discusses password encryption further.

The following table shows how various combinations of Host and User values in the user table apply to incoming connections.

Host Value	User Value	Allowable Connections
'thomas.loc.gov'	'fred'	fred, connecting from thomas.loc.gov
'thomas.loc.gov'	''	Any user, connecting from thomas.loc.gov
'%'	'fred'	fred, connecting from any host
'%'	''	Any user, connecting from any host
'%.loc.gov'	'fred'	fred, connecting from any host in theloc.gov domain
'x.y.%'	'fred'	fred, connecting from x.y.net, x.y.com, x.y.edu, and so on (this is probably not useful)
'144.155.166.177'	'fred'	fred, connecting from the host with IP address 144.155.166.177
'144.155.166.%'	'fred'	fred, connecting from any host in the 144.155.166 class C subnet
'144.155.166.0/255.255.255.0	'fred'	Same as previous example

It is possible for the client hostname and username of an incoming connection to match more than one row in the user table. The preceding set of examples demonstrates this: Several of the entries shown match a connection from thomas.loc.gov by fred.

When multiple matches are possible, the server must determine which of them to use. It resolves this issue as follows:

• Whenever the server reads the user table into memory, it sorts the rows.

• When a client attempts to connect, the server looks through the rows in sorted order.

• The server uses the first row that matches the client hostname and username.

To see how this works, suppose that the user table looks like this:

+-----------+---------- +-
| Host      | User      | ...
+-----------+---------- +-
| %         | root      | ...
| %         | jeffrey   | ...
| localhost | root      | ...
| localhost |           | ...
+-----------+---------- +-

When the server reads the table into memory, it orders the rows with the most-specific Host values first. Literal hostnames and IP numbers are the most specific. The pattern '%' means "any host" and is least specific. Rows with the same Host value are ordered with the most-specific User values first (a blank User value means "any user" and is least specific). For the user table just shown, the result after sorting looks like this:

+-----------+---------- +-
| Host      | User      | ...
+-----------+---------- +-
| localhost | root      | ...
| localhost |           | ...
| %         | jeffrey   | ...
| %         | root      | ...
+-----------+---------- +-

When a client attempts to connect, the server looks through the sorted rows and uses the first match found. For a connection from localhost by jeffrey, two of the rows from the table match: the one with Host and User values of 'localhost' and '', and the one with values of '%' and 'jeffrey'. The 'localhost' row appears first in sorted order, so that is the one the server uses.

Here is another example. Suppose that the user table looks like this:

+----------------+---------- +-
| Host           | User      | ...
+----------------+---------- +-
| %              | jeffrey   | ...
| thomas.loc.gov |           | ...
+----------------+---------- +-

The sorted table looks like this:

+----------------+---------- +-
| Host           | User      | ...
+----------------+---------- +-
| thomas.loc.gov |           | ...
| %              | jeffrey   | ...
+----------------+---------- +-

A connection by jeffrey from thomas.loc.gov is matched by the first row, whereas a connection by jeffrey from whitehouse.gov is matched by the second.

It is a common misconception to think that, for a given username, all rows that explicitly name that user are used first when the server attempts to find a match for the connection. This is simply not true. The previous example illustrates this, where a connection from thomas.loc.gov by jeffrey is first matched not by the row containing 'jeffrey' as the User column value, but by the row with no username. As a result, jeffrey is authenticated as an anonymous user, even though he specified a username when connecting.

If you are able to connect to the server, but your privileges are not what you expect, you probably are being authenticated as some other account. To find out what account the server used to authenticate you, use the CURRENT_USER() function. It returns a value in user_name@host_name format that indicates the User and Host values from the matching user table row. Suppose that jeffrey connects and issues the following query:

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| @localhost     |
+----------------+

The result shown here indicates that the matching user table row had a blank User column value. In other words, the server is treating jeffrey as an anonymous user.

Another thing you can do to diagnose authentication problems is to print out the user table and sort it by hand to see where the first match is being made.

4.8.6. Access Control, Stage 2: Request Verification

After you establish a connection, the server enters Stage 2 of access control. For each request that you issue via that connection, the server determines what operation you want to perform, and then checks whether you have sufficient privileges to do so. This is where the privilege columns in the grant tables come into play. These privileges can come from any of the user, db, host, tables_priv, columns_priv, or procs_priv tables. (You may find it helpful to refer to Section 4.8.2, "How the Privilege System Works," which lists the columns present in each of the grant tables.)

The user table grants privileges that are assigned to you on a global basis and that apply no matter what the default database is. For example, if the user table grants you the DELETE privilege, you can delete rows from any table in any database on the server host! In other words, user table privileges are superuser privileges. It is wise to grant privileges in the user table only to superusers such as database administrators. For other users, you should leave all privileges in the user table set to 'N' and grant privileges at more specific levels only. You can grant privileges for particular databases, tables, columns, or routines.

The db and host tables grant database-specific privileges. Values in the scope columns of these tables can take the following forms:

• The wildcard characters '%' and '_' can be used in the Host and Db columns of either table. These have the same meaning as for pattern-matching operations performed with the LIKE operator. If you want to use either character literally when granting privileges, you must escape it with a backslash. For example, to include the underscore character ('_') as part of a database name, specify it as '\_' in the GRANT statement.

• A '%' Host value in the db table means "any host." A blank Host value in the db table means "consult the host table for further information" (a process that is described later in this section).

• A '%' or blank Host value in the host table means "any host."

• A '%' or blank Db value in either table means "any database."

• A blank User value in either table matches the anonymous user.

The server reads the db and host tables into memory and sorts them at the same time that it reads the user table. The server sorts the db table based on the Host, Db, and User scope columns, and sorts the host table based on the Host and Db scope columns. As with the user table, sorting puts the most-specific values first and least-specific values last, and when the server looks for matching entries, it uses the first match that it finds.

The tables_priv, columns_priv, and procs_priv tables grant table-specific, column-specific, and routine-specific privileges. Values in the scope columns of these tables can take the following forms:

• The wildcard characters '%' and '_' can be used in the Host column. These have the same meaning as for pattern-matching operations performed with the LIKE operator.

• A '%' or blank Host value means "any host."

• The Db, Table_name, and Column_name columns cannot contain wildcards or be blank.

The server sorts the tables_priv, columns_priv, and procs_priv tables based on the Host, Db, and User columns. This is similar to db table sorting, but simpler because only the Host column can contain wildcards.

The server uses the sorted tables to verify each request that it receives. For requests that require administrative privileges such as SHUTDOWN or RELOAD, the server checks only the user table row because that is the only table that specifies administrative privileges. The server grants access if the row allows the requested operation and denies access otherwise. For example, if you want to execute mysqladmin shutdown but your user table row doesn't grant the SHUTDOWN privilege to you, the server denies access without even checking the db or host tables. (They contain no Shutdown_priv column, so there is no need to do so.)

For database-related requests (INSERT, UPDATE, and so on), the server first checks the user's global (superuser) privileges by looking in the user table row. If the row allows the requested operation, access is granted. If the global privileges in the user table are insufficient, the server determines the user's database-specific privileges by checking the db and host tables:

After determining the database-specific privileges granted by the db and host table entries, the server adds them to the global privileges granted by the user table. If the result allows the requested operation, access is granted. Otherwise, the server successively checks the user's table and column privileges in the tables_priv and columns_priv tables, adds those to the user's privileges, and allows or denies access based on the result. For stored routine operations, the server uses the procs_priv table rather than tables_priv and columns_priv.

Expressed in boolean terms, the preceding description of how a user's privileges are calculated may be summarized like this:

global privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges
OR routine privileges

It may not be apparent why, if the global user row privileges are initially found to be insufficient for the requested operation, the server adds those privileges to the database, table, and column privileges later. The reason is that a request might require more than one type of privilege. For example, if you execute an INSERT INTO ... SELECT statement, you need both the INSERT and the SELECT privileges. Your privileges might be such that the user table row grants one privilege and the db table row grants the other. In this case, you have the necessary privileges to perform the request, but the server cannot tell that from either table by itself; the privileges granted by the entries in both tables must be combined.

The host table is not affected by the GRANT or REVOKE statements, so it is unused in most MySQL installations. If you modify it directly, you can use it for some specialized purposes, such as to maintain a list of secure servers. For example, at TcX, the host table contains a list of all machines on the local network. These are granted all privileges.

You can also use the host table to indicate hosts that are not secure. Suppose that you have a machine public.your.domain that is located in a public area that you do not consider secure. You can allow access to all hosts on your network except that machine by using host table entries like this:

+--------------------+---- +-
| Host               | Db | ...
+--------------------+---- +-
| public.your.domain | %  | ... (all privileges set to  ' N )
| %.your.domain      | %  | ... (all privileges set to  ' Y )
+--------------------+---- +-

Naturally, you should always test your changes to the grant tables (for example, by using SHOW GRANTS) to make sure that your access privileges are actually set up the way you think they are.

4.8.7. When Privilege Changes Take Effect

When mysqld starts, it reads all grant table contents into memory. The in-memory tables become effective for access control at that point.

When the server reloads the grant tables, privileges for existing client connections are affected as follows:

• Table and column privilege changes take effect with the client's next request.

• Database privilege changes take effect at the next USE db_name statement.

• Changes to global privileges and passwords take effect the next time the client connects.

If you modify the grant tables indirectly using statements such as GRANT, REVOKE, or SET PASSWORD, the server notices these changes and loads the grant tables into memory again immediately.

If you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE, your changes have no effect on privilege checking until you either restart the server or tell it to reload the tables. To reload the grant tables manually, issue a FLUSH PRIVILEGES statement or execute a mysqladmin flush-privileges or mysqladmin reload command.

If you change the grant tables directly but forget to reload them, your changes have no effect until you restart the server. This may leave you wondering why your changes do not seem to make any difference!

4.8.8. Causes of Access denied Errors

If you encounter problems when you try to connect to the MySQL server, the following items describe some courses of action you can take to correct the problem.

• Make sure that the server is running. If it is not running, you cannot connect to it. For example, if you attempt to connect to the server and see a message such as one of those following, one cause might be that the server is not running:

  shell> mysql
  ERROR 2003: Can't connect to MySQL server on 'host_name' (111)
  shell> mysql
  ERROR 2002: Can't connect to local MySQL server through socket
  '/tmp/mysql.sock' (111)
  

  It might also be that the server is running, but you are trying to connect using a TCP/IP port, named pipe, or Unix socket file different from the one on which the server is listening. To correct this when you invoke a client program, specify a --port option to indicate the proper port number, or a --socket option to indicate the proper named pipe or Unix socket file. To find out where the socket file is, you can use this command:

  shell> netstat -ln | grep mysql

• The grant tables must be properly set up so that the server can use them for access control. For some distribution types (such as binary distributions on Windows, or RPM distributions on Linux), the installation process initializes the mysql database containing the grant tables. For distributions that do not do this, you must initialize the grant tables manually by running the mysql_install_db script. For details, see Section 2.9.2, "Unix Post-Installation Procedures."

  One way to determine whether you need to initialize the grant tables is to look for a mysql directory under the data directory. (The data directory normally is named data or var and is located under your MySQL installation directory.) Make sure that you have a file named user.MYD in the mysql database directory. If you do not, execute the mysql_install_db script. After running this script and starting the server, test the initial privileges by executing this command:

  shell> mysql -u root test

  The server should let you connect without error.

• After a fresh installation, you should connect to the server and set up your users and their access permissions:

  shell> mysql -u root mysql

  The server should let you connect because the MySQL root user has no password initially. That is also a security risk, so setting the password for the root accounts is something you should do while you're setting up your other MySQL accounts. For instructions on setting the initial passwords, see Section 2.9.3, "Securing the Initial MySQL Accounts."

• If you have updated an existing MySQL installation to a newer version, did you run the mysql_upgrade script? If not, do so. The structure of the grant tables changes occasionally when new capabilities are added, so after an upgrade you should always make sure that your tables have the current structure. For instructions, see Section 4.6.2, "mysql_upgradeCheck Tables for MySQL Upgrade."

• If a client program receives the following error message when it tries to connect, it means that the server expects passwords in a newer format than the client is capable of generating:

  shell> mysql
  Client does not support authentication protocol requested
  by server; consider upgrading MySQL client
  

  For information on how to deal with this, see Section 4.8.9, "Password Hashing as of MySQL 4.1," and the "MySQL Language Reference."

• If you try to connect as root and get the following error, it means that you do not have a row in the user table with a User column value of 'root' and that mysqld cannot resolve the hostname for your client:

  Access denied for user ''@' unknown' to database mysql
  

  In this case, you must restart the server with the --skip-grant-tables option and edit your /etc/hosts file or \windows\hosts file to add an entry for your host.

• Remember that client programs use connection parameters specified in option files or environment variables. If a client program seems to be sending incorrect default connection parameters when you have not specified them on the command line, check your environment and any applicable option files. For example, if you get Access denied when you run a client without any options, make sure that you have not specified an old password in any of your option files!

  You can suppress the use of option files by a client program by invoking it with the --n-defaults option.For example

  shell> mysqladmin --no-defaults -u root version

  The option files that clients use are listed in Section 3.3.2, "Using Option Files."

• If you get the following error, it means that you are using an incorrect root password:

  shell> mysqladmin -u root -pxxxx ver
  Access denied for user ' root '@' localhost' (using password: YES)
  

  If the preceding error occurs even when you have not specified a password, it means that you have an incorrect password listed in some option file. Try the --no-defaults option as described in the previous item.

  For information on changing passwords, see Section 4.9.5, "Assigning Account Passwords."

  If you have lost or forgotten the root password, you can restart mysqld with --skip-grant-tables to change the password.

• If you change a password by using SET PASSWORD, INSERT, or UPDATE, you must encrypt the password using the PASSWORD() function. If you do not use PASSWORD() for these statements, the password will not work. For example, the following statement sets a password, but fails to encrypt it, so the user is not able to connect afterward:

  SET PASSWORD FOR 'abe '@'host_name' = 'eagle';

  Instead, set the password like this:

  SET PASSWORD FOR 'abe '@'host_name' = PASSWORD('eagle');

  The PASSWORD() function is unnecessary when you specify a password using the GRANT or (beginning with MySQL 5.0.2) CREATE USER statements, or the mysqladmin password command. Each of those automatically uses PASSWORD() to encrypt the password. See Section 4.9.5, "Assigning Account Passwords."

• localhost is a synonym for your local hostname, and is also the default host to which clients try to connect if you specify no host explicitly.

  To avoid this problem on such systems, you can use a --host=127.0.0.1 option to name the server host explicitly. This will make a TCP/IP connection to the local mysqld server. You can also use TCP/IP by specifying a --host option that uses the actual host-name of the local host. In this case, the hostname must be specified in a user table row on the server host, even though you are running the client program on the same host as the server.

• If you get an Access denied error when trying to connect to the database with mysql -u user_name, you may have a problem with the user table. Check this by executing mysql -u root mysql and issuing this SQL statement:

  SELECT * FROM user;

  The result should include a row with the Host and User columns matching your computer's hostname and your MySQL username.

• The Access denied error message tells you who you are trying to log in as, the client host from which you are trying to connect, and whether you were using a password. Normally, you should have one row in the user table that exactly matches the hostname and username that were given in the error message. For example, if you get an error message that contains using password: NO, it means that you tried to log in without a password.

• If the following error occurs when you try to connect from a host other than the one on which the MySQL server is running, it means that there is no row in the user table with a Host value that matches the client host:

  Host ... is not allowed to connect to this MySQL server

  You can fix this by setting up an account for the combination of client hostname and username that you are using when trying to connect.

  If you do not know the IP number or hostname of the machine from which you are connecting, you should put a row with '%' as the Host column value in the user table. After trying to connect from the client machine, use a SELECT USER() query to see how you really did connect. (Then change the '%' in the user table row to the actual host-name that shows up in the log. Otherwise, your system is left insecure because it allows connections from any host for the given username.)

  On Linux, another reason that this error might occur is that you are using a binary MySQL version that is compiled with a different version of the glibc library than the one you are using. In this case, you should either upgrade your operating system or glibc, or download a source distribution of MySQL version and compile it yourself. A source RPM is normally trivial to compile and install, so this is not a big problem.

• If you specify a hostname when trying to connect, but get an error message where the hostname is not shown or is an IP number, it means that the MySQL server got an error when trying to resolve the IP number of the client host to a name:

  shell> mysqladmin -u root -pxxxx -h some_hostname ver
  Access denied for user 'root'@'' (using password: YES)
  

  This indicates a DNS problem. To fix it, execute mysqladmin flush-hosts to reset the internal DNS hostname cache. See Section 6.5.6, "How MySQL Uses DNS."

  Some permanent solutions are:

  • Determine what is wrong with your DNS server and fix it.

  • Specify IP numbers rather than hostnames in the MySQL grant tables.

  • Put an entry for the client machine name in /etc/hosts or \windows\hosts.

  • Start mysqld with the --skip-name-resolve option.

  • Start mysqld with the --skip-host-cache option.

  • On Unix, if you are running the server and the client on the same machine, connect to localhost. Unix connections to localhost use a Unix socket file rather than TCP/IP.

  • On Windows, if you are running the server and the client on the same machine and the server supports named pipe connections, connect to the hostname . (period). Connections to . use a named pipe rather than TCP/IP.

• If mysql -u root test works but mysql -h your_hostname -u root test results in Access denied (where your_hostname is the actual hostname of the local host), you may not have the correct name for your host in the user table. A common problem here is that the Host value in the user table row specifies an unqualified hostname, but your system's name resolution routines return a fully qualified domain name (or vice versa). For example, if you have an entry with host 'tcx' in the user table, but your DNS tells MySQL that your hostname is 'tcx.subnet.se', the entry does not work. Try adding an entry to the user table that contains the IP number of your host as the Host column value. (Alternatively, you could add an entry to the user table with a Host value that contains a wildcard; for example, 'tcx.%'. However, use of hostnames ending with '%' is insecure and is not recommended!)

• If mysql -u user_name test works but mysql -u user_name other_db_name does not, you have not granted database access for other_db_name to the given user.

• If mysql -u user_name works when executed on the server host, but mysql -h host_name -u user_name does not work when executed on a remote client host, you have not enabled access to the server for the given username from the remote host.

• If you cannot figure out why you get Access denied, remove from the user table all entries that have Host values containing wildcards (entries that contain '%' or '_'). A very common error is to insert a new entry with Host= '%' and User= 'some_user', thinking that this allows you to specify localhost to connect from the same machine. The reason that this does not work is that the default privileges include an entry with Host= 'local-host' and User= ''. Because that entry has a Host value 'localhost' that is more specific than '%', it is used in preference to the new entry when connecting from localhost! The correct procedure is to insert a second entry with Host= 'localhost' and User= 'some_user', or to delete the entry with Host= 'localhost' and User= ''. After deleting the entry, remember to issue a FLUSH PRIVILEGES statement to reload the grant tables.

• If you get the following error, you may have a problem with the db or host table:

  Access to database denied

  If the entry selected from the db table has an empty value in the Host column, make sure that there are one or more corresponding entries in the host table specifying which hosts the db table entry applies to.

• If you are able to connect to the MySQL server, but get an Access denied message whenever you issue a SELECT ... INTO OUTFILE or LOAD DATA INFILE statement, your entry in the user table does not have the FILE privilege enabled.

• If you change the grant tables directly (for example, by using INSERT, UPDATE, or DELETE statements) and your changes seem to be ignored, remember that you must execute a FLUSH PRIVILEGES statement or a mysqladmin flush-privileges command to cause the server to re-read the privilege tables. Otherwise, your changes have no effect until the next time the server is restarted. Remember that after you change the root password with an UPDATE command, you won't need to specify the new password until after you flush the privileges, because the server won't know you've changed the password yet!

• If your privileges seem to have changed in the middle of a session, it may be that a MySQL administrator has changed them. Reloading the grant tables affects new client connections, but it also affects existing connections as indicated in Section 4.8.7, "When Privilege Changes Take Effect."

• If you have access problems with a Perl, PHP, Python, or ODBC program, try to connect to the server with mysql -u user_name db_name or mysql -u user_name -pyour_pass db_name. If you are able to connect using the mysql client, the problem lies with your program, not with the access privileges. (There is no space between -p and the password; you can also use the --password=your_pass syntax to specify the password. If you use the -p --password option with no password value, MySQL prompts you for the password.)

• For testing, start the mysqld server with the --skip-grant-tables option. Then you can change the MySQL grant tables and use the mysqlaccess script to check whether your modifications have the desired effect. When you are satisfied with your changes, execute mysqladmin flush-privileges to tell the mysqld server to start using the new grant tables. (Reloading the grant tables overrides the --skip-grant-tables option. This enables you to tell the server to begin using the grant tables again without stopping and restarting it.)

• If everything else fails, start the mysqld server with a debugging option (for example, --debug=d,general,query). This prints host and user information about attempted connections, as well as information about each command issued.

• If you have any other problems with the MySQL grant tables and feel you must post the problem to the mailing list, always provide a dump of the MySQL grant tables. You can dump the tables with the mysqldump mysql command. To file a bug report, see the instructions in Section 1.8, "How to Report Bugs or Problems." In some cases, you may need to restart mysqld with --skip-grant-tables to run mysqldump.

4.8.9. Password Hashing as of MySQL 4.1

MySQL user accounts are listed in the user table of the mysql database. Each MySQL account is assigned a password, although what is stored in the Password column of the user table is not the plaintext version of the password, but a hash value computed from it. Password hash values are computed by the PASSWORD() function.

MySQL uses passwords in two phases of client/server communication:

• When a client attempts to connect to the server, there is an initial authentication step in which the client must present a password that has a hash value matching the hash value stored in the user table for the account that the client wants to use.

• After the client connects, it can (if it has sufficient privileges) set or change the password hashes for accounts listed in the user table. The client can do this by using the PASSWORD() function to generate a password hash, or by using the GRANT or SET PASSWORD statements.

In other words, the server uses hash values during authentication when a client first attempts to connect. The server generates hash values if a connected client invokes the PASSWORD() function or uses a GRANT or SET PASSWORD statement to set or change a password.

The password-hashing mechanism was updated in MySQL 4.1 to provide better security and to reduce the risk of passwords being intercepted. However, this new mechanism is understood only by MySQL 4.1 (and newer) servers and clients, which can result in some compatibility problems. A 4.1 or newer client can connect to a pre-4.1 server, because the client understands both the old and new password-hashing mechanisms. However, a pre-4.1 client that attempts to connect to a 4.1 or newer server may run into difficulties. For example, a 3.23 mysql client that attempts to connect to a 5.0 server may fail with the following error message:

shell> mysql -h localhost -u root
Client does not support authentication protocol requested
by server; consider upgrading MySQL client

Another common example of this phenomenon occurs for attempts to use the older PHP mysql extension after upgrading to MySQL 4.1 or newer.

The following discussion describes the differences between the old and new password mechanisms, and what you should do if you upgrade your server but need to maintain backward compatibility with pre-4.1 clients. This information is of particular importance to PHP programmers migrating MySQL databases from version 4.0 or lower to version 4.1 or higher.

Note: This discussion contrasts 4.1 behavior with pre-4.1 behavior, but the 4.1 behavior described here actually begins with 4.1.1. MySQL 4.1.0 is an "odd" release because it has a slightly different mechanism than that implemented in 4.1.1 and up. Differences between 4.1.0 and more recent versions are described further in "MySQL 3.23, 4.0, 4.1 Reference Manual."

Prior to MySQL 4.1, password hashes computed by the PASSWORD() function are 16 bytes long. Such hashes look like this:

mysql> SELECT PASSWORD('mypass');
+--------------------  +
| PASSWORD('  mypass ) |
+--------------------  +
| 6f8c114b58f2ce9e     |
+--------------------  +

The Password column of the user table (in which these hashes are stored) also is 16 bytes long before MySQL 4.1.

As of MySQL 4.1, the PASSWORD() function has been modified to produce a longer 41-byte hash value:

mysql> SELECT PASSWORD('mypass');
+-------------------------------------------+
| PASSWORD('mypass' )                       |
+-------------------------------------------+
| *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
+-------------------------------------------+

Accordingly, the Password column in the user table also must be 41 bytes long to store these values:

• If you perform a new installation of MySQL 5.0, the Password column is made 41 bytes long automatically.

• Upgrading from MySQL 4.1 (4.1.1 or later in the 4.1 series) to MySQL 5.0 should not give rise to any issues in this regard because both versions use the same password hashing mechanism. If you wish to upgrade an older release of MySQL to version 5.0, you should upgrade to version 4.1 first, and then upgrade the 4.1 installation to 5.0.

A widened Password column can store password hashes in both the old and new formats. The format of any given password hash value can be determined two ways:

• The obvious difference is the length (16 bytes versus 41 bytes).

• A second difference is that password hashes in the new format always begin with a '*' character, whereas passwords in the old format never do.

The longer password hash format has better cryptographic properties, and client authentication based on long hashes is more secure than that based on the older short hashes.

The differences between short and long password hashes are relevant both for how the server uses passwords during authentication and for how it generates password hashes for connected clients that perform password-changing operations.

The way in which the server uses password hashes during authentication is affected by the width of the Password column:

• If the column is short, only short-hash authentication is used.

• If the column is long, it can hold either short or long hashes, and the server can use either format:

  • Pre-4.1 clients can connect, although because they know only about the old hashing mechanism, they can authenticate only using accounts that have short hashes.

  • 4.1 and later clients can authenticate using accounts that have short or long hashes.

Even for short-hash accounts, the authentication process is actually a bit more secure for 4.1 and later clients than for older clients. In terms of security, the gradient from least to most secure is:

• Pre-4.1 client authenticating with short password hash

• 4.1 or later client authenticating with short password hash

• 4.1 or later client authenticating with long password hash

The way in which the server generates password hashes for connected clients is affected by the width of the Password column and by the --old-passwords option. A 4.1 or later server generates long hashes only if certain conditions are met: The Password column must be wide enough to hold long values and the --old-passwords option must not be given. These conditions apply as follows:

• The Password column must be wide enough to hold long hashes (41 bytes). If the column has not been updated and still has the pre-4.1 width of 16 bytes, the server notices that long hashes cannot fit into it and generates only short hashes when a client performs password-changing operations using PASSWORD(), GRANT, or SET PASSWORD. This is the behavior that occurs if you have upgraded to 4.1 but have not yet run the mysql_ fix_privilege_tables script to widen the Password column.

• If the Password column is wide, it can store either short or long password hashes. In this case, PASSWORD(), GRANT, and SET PASSWORD generate long hashes unless the server was started with the --old-passwords option. That option forces the server to generate short password hashes instead.

The purpose of the --old-passwords option is to enable you to maintain backward compatibility with pre-4.1 clients under circumstances where the server would otherwise generate long password hashes. The option doesn't affect authentication (4.1 and later clients can still use accounts that have long password hashes), but it does prevent creation of a long password hash in the user table as the result of a password-changing operation. Were that to occur, the account no longer could be used by pre-4.1 clients. Without the --old-passwords option, the following undesirable scenario is possible:

• An old client connects to an account that has a short password hash.

• The client changes its own password. Without --old-passwords, this results in the account having a long password hash.

• The next time the old client attempts to connect to the account, it cannot, because the account has a long password hash that requires the new hashing mechanism during authentication. (Once an account has a long password hash in the user table, only 4.1 and later clients can authenticate for it, because pre-4.1 clients do not understand long hashes.)

This scenario illustrates that, if you must support older pre-4.1 clients, it is dangerous to run a 4.1 or newer server without using the --old-passwords option. By running the server with --old-passwords, password-changing operations do not generate long password hashes and thus do not cause accounts to become inaccessible to older clients. (Those clients cannot inadvertently lock themselves out by changing their password and ending up with a long password hash.)

The downside of the --old-passwords option is that any passwords you create or change use short hashes, even for 4.1 clients. Thus, you lose the additional security provided by long password hashes. If you want to create an account that has a long hash (for example, for use by 4.1 clients), you must do so while running the server without --old-passwords.

The following scenarios are possible for running a 4.1 or later server:

Scenario 1: Short Password column in user table:

• Only short hashes can be stored in the Password column.

• The server uses only short hashes during client authentication.

• For connected clients, password hash-generating operations involving PASSWORD(), GRANT, or SET PASSWORD use short hashes exclusively. Any change to an account's password results in that account having a short password hash.

• The --old-passwords option can be used but is superfluous because with a short Password column, the server generates only short password hashes anyway.

Scenario 2: Long Password column; server not started with --old-passwords option:

• Short or long hashes can be stored in the Password column.

• 4.1 and later clients can authenticate using accounts that have short or long hashes.

• Pre-4.1 clients can authenticate only using accounts that have short hashes.

• For connected clients, password hash-generating operations involving PASSWORD(),GRANT, or SET PASSWORD use long hashes exclusively. A change to an account's password results in that account having a long password hash.

As indicated earlier, a danger in this scenario is that it is possible for accounts that have a short password hash to become inaccessible to pre-4.1 clients. A change to such an account's password made via GRANT, PASSWORD(), or SET PASSWORD results in the account being given a long password hash. From that point on, no pre-4.1 client can authenticate to that account until the client upgrades to 4.1.

To deal with this problem, you can change a password in a special way. For example, normally you use SET PASSWORD as follows to change an account password:

SET PASSWORD FOR 'some_user '@' some_host' = PASSWORD('mypass');

To change the password but create a short hash, use the OLD_PASSWORD() function instead:

SET PASSWORD FOR ' some_user '@' some_host' = OLD_PASSWORD(' mypass ');

OLD_PASSWORD() is useful for situations in which you explicitly want to generate a short hash.

Scenario 3: Long Password column; 4.1 or newer server started with --old-passwords option:

• Short or long hashes can be stored in the Password column.

• 4.1 and later clients can authenticate for accounts that have short or long hashes (but note that it is possible to create long hashes only when the server is started without --old-passwords).

• Pre-4.1 clients can authenticate only for accounts that have short hashes.

• For connected clients, password hash-generating operations involving PASSWORD(), GRANT, or SET PASSWORD use short hashes exclusively. Any change to an account's password results in that account having a short password hash.

In this scenario, you cannot create accounts that have long password hashes, because the --old-passwords option prevents generation of long hashes. Also, if you create an account with a long hash before using the --old-passwords option, changing the account's password while --old-passwords is in effect results in the account being given a short password, causing it to lose the security benefits of a longer hash.

The disadvantages for these scenarios may be summarized as follows:

In scenario 1, you cannot take advantage of longer hashes that provide more secure authentication.

In scenario 2, accounts with short hashes become inaccessible to pre-4.1 clients if you change their passwords without explicitly using OLD_PASSWORD().

In scenario 3, --old-passwords prevents accounts with short hashes from becoming inaccessible, but password-changing operations cause accounts with long hashes to revert to short hashes, and you cannot change them back to long hashes while --old-passwords is in effect.

4.8.9.1. Implications of Password Hashing Changes for Application Programs

An upgrade to MySQL version 4.1 or later can cause compatibility issues for applications that use PASSWORD() to generate passwords for their own purposes. Applications really should not do this, because PASSWORD() should be used only to manage passwords for MySQL accounts. But some applications use PASSWORD() for their own purposes anyway.

If you upgrade to 4.1 or later from a pre-4.1 version of MySQL and run the server under conditions where it generates long password hashes, an application using PASSWORD() for its own passwords breaks. The recommended course of action in such cases is to modify the application to use another function, such as SHA1() or MD5(), to produce hashed values. If that is not possible, you can use the OLD_PASSWORD() function, which is provided for generating short hashes in the old format. However, you should note that OLD_PASSWORD() may one day no longer be supported.

If the server is running under circumstances where it generates short hashes, OLD_PASSWORD() is available but is equivalent to PASSWORD().