Chapter 6. Security

Do not ever give anyone (except MySQL root accounts) access to the user table in the mysql database! This is critical.

Learn how the MySQL access privilege system works (see Section 6.2, “The MySQL Access Privilege System”). Use the GRANT and REVOKE statements to control access to MySQL. Do not grant more privileges than necessary. Never grant privileges to all hosts.

Checklist:

Do not store cleartext passwords in your database. If your computer becomes compromised, the intruder can take the full list of passwords and use them. Instead, use SHA2(), SHA1(), MD5(), or some other one-way hashing function and store the hash value.

Do not choose passwords from dictionaries. Special programs exist to break passwords. Even passwords like “xfish98” are very bad. Much better is “duag98” which contains the same word “fish” but typed one key to the left on a standard QWERTY keyboard. Another method is to use a password that is taken from the first characters of each word in a sentence (for example, “Four score and seven years ago” results in a password of “Fsasya”). The password is easy to remember and type, but difficult to guess for someone who does not know the sentence. In this case, you can additionally substitute digits for the number words to obtain the phrase “4 score and 7 years ago”, yielding the password “4sa7ya” which is even more difficult to guess.

Invest in a firewall. This protects you from at least 50% of all types of exploits in any software. Put MySQL behind the firewall or in a demilitarized zone (DMZ).

Checklist:

Applications that access MySQL should not trust any data entered by users, and should be written using proper defensive programming techniques. See Section 6.1.7, “Client Programming Security Guidelines”.

Do not transmit plain (unencrypted) data over the Internet. This information is accessible to everyone who has the time and ability to intercept it and use it for their own purposes. Instead, use an encrypted protocol such as SSL or SSH. MySQL supports internal SSL connections. Another technique is to use SSH port-forwarding to create an encrypted (and compressed) tunnel for the communication.

Learn to use the tcpdump and strings utilities. In most cases, you can check whether MySQL data streams are unencrypted by issuing a command like the following:

shell> tcpdump -l -i eth0 -w - src or dst port 3306 | strings

This works under Linux and should work with small modifications under other systems.

Require all MySQL accounts to have a password. A client program does not necessarily know the identity of the person running it. It is common for client/server applications that the user can specify any user name to the client program. For example, anyone can use the mysql program to connect as any other person simply by invoking it as mysql -u other_user db_name if other_user has no password. If all accounts have a password, connecting using another user's account becomes much more difficult.

For a discussion of methods for setting passwords, see Section 6.3.5, “Assigning Account Passwords”.

Never run the MySQL server as the Unix root user. This is extremely dangerous, because any user with the FILE privilege is able to cause the server to create files as root (for example, ~root/.bashrc). To prevent this, mysqld refuses to run as root unless that is specified explicitly using the --user=root option.

mysqld can (and should) be run as an ordinary, unprivileged user instead. You can create a separate Unix account named mysql to make everything even more secure. Use this account only for administering MySQL. To start mysqld as a different Unix user, add a user option that specifies the user name in the [mysqld] group of the my.cnf option file where you specify server options. For example:

[mysqld]
user=mysql

This causes the server to start as the designated user whether you start it manually or by using mysqld_safe or mysql.server. For more details, see Section 6.1.5, “How to Run MySQL as a Normal User”.

Running mysqld as a Unix user other than root does not mean that you need to change the root user name in the user table. User names for MySQL accounts have nothing to do with user names for Unix accounts.

Do not permit the use of symlinks to tables. (This capability can be disabled with the --skip-symbolic-links option.) This is especially important if you run mysqld as root, because anyone that has write access to the server's data directory then could delete any file in the system! See Section 8.11.3.1.2, “Using Symbolic Links for MyISAM Tables on Unix”.

Make sure that the only Unix user account with read or write privileges in the database directories is the account that is used for running mysqld.

Do not grant the PROCESS or SUPER privilege to nonadministrative users. The output of mysqladmin processlist and SHOW PROCESSLIST shows the text of any statements currently being executed, so any user who is permitted to see the server process list might be able to see statements issued by other users such as UPDATE user SET password=PASSWORD('not_secure').

mysqld reserves an extra connection for users who have the SUPER privilege, so that a MySQL root user can log in and check server activity even if all normal connections are in use.

The SUPER privilege can be used to terminate client connections, change server operation by changing the value of system variables, and control replication servers.

Do not grant the FILE privilege to nonadministrative users. Any user that has this privilege can write a file anywhere in the file system with the privileges of the mysqld daemon. To make this a bit safer, files generated with SELECT ... INTO OUTFILE do not overwrite existing files and are writable by everyone.

The FILE privilege may also be used to read any file that is world-readable or accessible to the Unix user that the server runs as. With this privilege, you can read any file into a database table. This could be abused, for example, by using LOAD DATA to load /etc/passwd into a table, which then can be displayed with SELECT.

Stored programs and views should be written using the security guidelines discussed in Section 18.6, “Access Control for Stored Programs and Views”.

If you do not trust your DNS, you should use IP addresses rather than host names in the grant tables. In any case, you should be very careful about creating grant table entries using host name values that contain wildcards.

If you want to restrict the number of connections permitted to a single account, you can do so by setting the max_user_connections variable in mysqld. The GRANT statement also supports resource control options for limiting the extent of server use permitted to an account. See Section 13.7.1.4, “GRANT Syntax”.

If the plugin directory is writable by the server, it may be possible for a user to write executable code to a file in the directory using SELECT ... INTO DUMPFILE. This can be prevented by making plugin_dir read only to the server or by setting --secure-file-priv to a directory where SELECT writes can be made safely.

The transfer of the file from the client host to the server host is initiated by the MySQL server. In theory, a patched server could be built that would tell the client program to transfer a file of the server's choosing rather than the file named by the client in the LOAD DATA statement. Such a server could access any file on the client host to which the client user has read access.

In a Web environment where the clients are connecting from a Web server, a user could use LOAD DATA LOCAL to read any files that the Web server process has read access to (assuming that a user could run any command against the SQL server). In this environment, the client with respect to the MySQL server actually is the Web server, not the remote program being run by the user who connects to the Web server.

By default, all MySQL clients and libraries in binary distributions are compiled with the -DENABLED_LOCAL_INFILE=1 option, to be compatible with MySQL 3.23.48 and before.

If you build MySQL from source but do not invoke CMake with the -DENABLED_LOCAL_INFILE=1 option, LOAD DATA LOCAL cannot be used by any client unless it is written explicitly to invoke mysql_options(... MYSQL_OPT_LOCAL_INFILE, 0). See Section 21.9.3.49, “mysql_options()”.

You can disable all LOAD DATA LOCAL statements from the server side by starting mysqld with the --local-infile=0 option.

For the mysql command-line client, enable LOAD DATA LOCAL by specifying the --local-infile[=1] option, or disable it with the --local-infile=0 option. For mysqlimport, local data file loading is off by default; enable it with the --local or -L option. In any case, successful use of a local load operation requires that the server permits it.

If you use LOAD DATA LOCAL in Perl scripts or other programs that read the [client] group from option files, you can add the local-infile=1 option to that group. However, to keep this from causing problems for programs that do not understand local-infile, specify it using the loose- prefix:

[client]
loose-local-infile=1

If LOAD DATA LOCAL is disabled, either in the server or the client, a client that attempts to issue such a statement receives the following error message:

ERROR 1148: The used command is not allowed with this MySQL version

Enable strict SQL mode to tell the server to be more restrictive of what data values it accepts. See Section 5.1.7, “Server SQL Modes”.

Try to enter single and double quotation marks (“'” and “"”) in all of your Web forms. If you get any kind of MySQL error, investigate the problem right away.

Try to modify dynamic URLs by adding %22 (“"”), %23 (“#”), and %27 (“'”) to them.

Try to modify data types in dynamic URLs from numeric to character types using the characters shown in the previous examples. Your application should be safe against these and similar attacks.

Try to enter characters, spaces, and special symbols rather than numbers in numeric fields. Your application should remove them before passing them to MySQL or else generate an error. Passing unchecked values to MySQL is very dangerous!

Check the size of data before passing it to MySQL.

Have your application connect to the database using a user name different from the one you use for administrative purposes. Do not give your applications any access privileges they do not need.

MySQL C API: Use the mysql_real_escape_string() API call.

MySQL++: Use the escape and quote modifiers for query streams.

PHP: Use either the mysqli or pdo_mysql extensions, and not the older ext/mysql extension. The preferred API's support the improved MySQL authentication protocol and passwords, as well as prepared statements with placeholders. See also Section 21.10.1.3, “Choosing an API”.

If the older ext/mysql extension must be used, then for escaping use the mysql_real_escape_string() function and not mysql_escape_string() or addslashes() because only mysql_real_escape_string() is character set-aware; the other functions can be “bypassed” when using (invalid) multi-byte character sets.

Perl DBI: Use placeholders or the quote() method.

Ruby DBI: Use placeholders or the quote() method.

Java JDBC: Use a PreparedStatement object and placeholders.

Administrative privileges enable users to manage operation of the MySQL server. These privileges are global because they are not specific to a particular database.

Database privileges apply to a database and to all objects within it. These privileges can be granted for specific databases, or globally so that they apply to all databases.

Privileges for database objects such as tables, indexes, views, and stored routines can be granted for specific objects within a database, for all objects of a given type within a database (for example, all tables in a database), or globally for all objects of a given type in all databases).

The ALL or ALL PRIVILEGES privilege specifier is shorthand. It stands for “all privileges available at a given privilege level” (except GRANT OPTION). For example, granting ALL at the global or table level grants all global privileges or all table-level privileges.

The ALTER privilege enables use of ALTER TABLE to change the structure of tables. ALTER TABLE also requires the CREATE and INSERT privileges. Renaming a table requires ALTER and DROP on the old table, ALTER, CREATE, and INSERT on the new table.

The ALTER ROUTINE privilege is needed to alter or drop stored routines (procedures and functions).

The CREATE privilege enables creation of new databases and tables.

The CREATE ROUTINE privilege is needed to create stored routines (procedures and functions).

The CREATE TABLESPACE privilege is needed to create, alter, or drop tablespaces and log file groups.

The CREATE TEMPORARY TABLES privilege enables the creation of temporary tables using the CREATE TEMPORARY TABLE statement.

As of MySQL 5.6.3, after a session has created a temporary table, the server performs no further privilege checks on the table. The creating session can perform any operation on the table, such as DROP TABLE, INSERT, UPDATE, or SELECT.

One implication of this behavior is that a session can manipulate its temporary tables even if the current user has no privilege to create them. Suppose that the current user does not have the CREATE TEMPORARY TABLES privilege but is able to execute a DEFINER-context stored procedure that executes with the privileges of a user who does have CREATE TEMPORARY TABLES and that creates a temporary table. While the procedure executes, the session uses the privileges of the defining user. After the procedure returns, the effective privileges revert to those of the current user, which can still see the temporary table and perform any operation on it.

Before MySQL 5.6.3, other operations on a temporary table, such as INSERT, UPDATE, or SELECT, require additional privileges for those operations for the database containing the temporary table, or for the nontemporary table of the same name.

To keep privileges for temporary and nontemporary tables separate, a common workaround for this situation is to create a database dedicated to the use of temporary tables. Then for that database, a user can be granted the CREATE TEMPORARY TABLES privilege, along with any other privileges required for temporary table operations done by that user.

The CREATE USER privilege enables use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.

The CREATE VIEW privilege enables use of CREATE VIEW.

The DELETE privilege enables rows to be deleted from tables in a database.

The DROP privilege enables you to drop (remove) existing databases, tables, and views. The DROP privilege is required in order to use the statement ALTER TABLE ... DROP PARTITION on a partitioned table. The DROP privilege is also required for TRUNCATE TABLE. 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 EVENT privilege is required to create, alter, drop, or see events for the Event Scheduler.

The EXECUTE privilege is required to execute stored routines (procedures and functions).

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 and the LOAD_FILE() function. 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 GRANT OPTION privilege enables you to give to other users or remove from other users those privileges that you yourself possess.

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 INSERT privilege enables rows to be inserted into tables in a database. INSERT is also required for the ANALYZE TABLE, OPTIMIZE TABLE, and REPAIR TABLE table-maintenance 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 other sessions from reading the locked table.

The PROCESS privilege pertains to display of information about the threads executing within the server (that is, information about the statements being executed by sessions). The privilege enables use of SHOW PROCESSLIST or mysqladmin processlist to see threads belonging to other accounts; you can always see your own threads.

The PROXY privilege enables a user to impersonate or become known as another user. See Section 6.3.7, “Proxy Users”.

The REFERENCES privilege currently is unused.

The RELOAD privilege enables use of the FLUSH statement. It also enables mysqladmin commands that are equivalent to FLUSH operations: flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, and reload.

The reload command tells the server to reload 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 REPLICATION CLIENT privilege enables the use of SHOW MASTER STATUS and SHOW SLAVE STATUS. In MySQL 5.6.6 and later, it also enables the use of the SHOW BINARY LOGS statement.

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 SELECT privilege enables you to select rows from 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 SELECT as a simple calculator to evaluate expressions that make no reference to tables:

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

The SELECT privilege is also needed for other statements that read column values. For example, SELECT is needed for columns referenced on the right hand side of col_name=expr assignment in UPDATE statements or for columns named in the WHERE clause of DELETE or UPDATE statements.

The SHOW DATABASES privilege enables 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.

The SHOW VIEW privilege enables use of SHOW CREATE VIEW.

The SHUTDOWN privilege enables use of the mysqladmin shutdown command. There is no corresponding SQL statement.

The SUPER privilege enables an account to use CHANGE MASTER TO, KILL or mysqladmin kill to kill threads belonging to other accounts (you can always kill your own threads), PURGE BINARY LOGS, configuration changes using SET GLOBAL to modify global system variables, the mysqladmin debug command, enabling or disabling logging, performing updates even if the read_only system variable is enabled, starting and stopping replication on slave servers, specification of any account in the DEFINER attribute of stored programs and views, and enables you to connect (once) even if the connection limit controlled by the max_connections system variable is reached.

To create or alter stored functions if binary logging is enabled, you may also need the SUPER privilege, as described in Section 18.7, “Binary Logging of Stored Programs”.

The TRIGGER privilege enables trigger operations. You must have this privilege for a table to create, drop, or execute triggers for that table.

The UPDATE privilege enables rows to be updated in tables in a database.

The USAGE privilege specifier stands for “no privileges.” It is used at the global level with GRANT to modify account attributes such as resource limits or SSL characteristics without affecting existing account 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 OPTION privilege enables users to give their privileges to other users. Two users that have different privileges and with the GRANT OPTION 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 sessions 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.

user: Contains user accounts, global privileges, and other non-privilege columns.

db: Contains database-level privileges.

host: Obsolete. New MySQL installations no longer create this table as of MySQL 5.6.7.

tables_priv: Contains table-level privileges.

columns_priv: Contains column-level privileges.

procs_priv: Contains stored procedure and function privileges.

proxies_priv: Contains proxy-user privileges.

event: Contains information about Event Scheduler events: See Section 18.4, “Using the Event Scheduler”.

func: Contains information about user-defined functions: See Section 22.3, “Adding New Functions to MySQL”.

help_xxx: These tables are used for server-side help: See Section 5.1.10, “Server-Side Help”.

plugin: Contains information about server plugins: See Section 5.1.8.1, “Installing and Uninstalling Plugins”, and Section 22.2, “The MySQL Plugin API”.

proc: Contains information about stored procedures and functions: See Section 18.2, “Using Stored Routines (Procedures and Functions)”.

servers: Used by the FEDERATED storage engine: See Section 14.9.2.2, “Creating a FEDERATED Table Using CREATE SERVER”.

time_zone_xxx: These tables contain time zone information: See Section 10.6, “MySQL Server Time Zone Support”.

Tables with _log in their name are used for logging: See Section 5.2, “MySQL Server Logs”.

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 user name 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 6.2.5, “Access Control, Stage 2: Request Verification”, describes the rules that are used to do this.

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

The db table scope columns determine which users can access which databases from which hosts. The privilege columns determine which operations are permitted. A privilege granted at the database level applies to the database and to all objects in the database, such as tables and stored programs.

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.

The proxies_priv table indicates which users can act as proxies for other users and whether proxy users can grant the PROXY privilege to other users.

Host, User: These columns indicate the user account that has the PROXY privilege for the proxied account.

Proxied_host, Proxied_user: These columns indicate the account of the proxied user.

Grantor: Currently unused.

Timestamp: Currently unused.

With_grant: This column indicates whether the proxy account can grant the PROXY privilege to other accounts.

Syntax for account names is 'user_name'@'host_name'.

An account name consisting only of a user name is equivalent to 'user_name'@'%'. For example, 'me' is equivalent to 'me'@'%'.

The user name and host name need not be quoted if they are legal as unquoted identifiers. Quotes are necessary to specify a user_name string containing special characters (such as “-”), or a host_name string containing special characters or wildcard characters (such as “%”); for example, 'test-user'@'%.com'.

Quote user names and host names as identifiers or as strings, using either backticks (“`”), single quotation marks (“'”), or double quotation marks (“"”).

The user name and host name parts, if quoted, must be quoted separately. That is, write 'me'@'localhost', not 'me@localhost'; the latter is interpreted as 'me@localhost'@'%'.

A reference to the CURRENT_USER or CURRENT_USER() function is equivalent to specifying the current client's user name and host name literally.

The user table contains one row for each account. The User and Host columns store the user name and host name. This table also indicates which global privileges the account has.

Other grant tables indicate privileges an account has for databases and objects within databases. These tables have User and Host columns to store the account name. Each row in these tables associates with the account in the user table that has the same User and Host values.

A host value can be a host name or an IP address (IPv4 or IPv6). The name 'localhost' indicates the local host. The IP address '127.0.0.1' indicates the IPv4 loopback interface. The IP address '::1' indicates the IPv6 loopback interface.

You can use the wildcard characters “%” and “_” in host name or IP address values. These have the same meaning as for pattern-matching operations performed with the LIKE operator. For example, a host value of '%' matches any host name, whereas a value of '%.mysql.com' matches any host in the mysql.com domain. '192.168.1.%' matches any host in the 192.168.1 class C network.

Because you can use IP wildcard values in host values (for example, '192.168.1.%' to match every host on a subnet), someone could try to exploit this capability by naming a host 192.168.1.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.example.com, its name never matches the host part of account names. An IP wildcard value can match only IP addresses, not host names.

For a host value specified as an IPv4 address, you can specify a netmask indicating how many address bits to use for the network number. Netmask notation cannot be used for IPv6 addresses.

The syntax is host_ip/netmask. For example:

CREATE USER 'david'@'192.58.197.0/255.255.255.0';

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

client_ip & netmask = host_ip

That is, for the CREATE USER statement just shown:

client_ip & 255.255.255.0 = 192.58.197.0

IP addresses 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.

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

The following netmask will not work because it masks 28 bits, and 28 is not a multiple of 8:

192.168.0.1/255.255.255.240

Suppose that a host on the local network has a fully qualified name of host1.example.com. If DNS returns name lookups for this host as host1.example.com, use that name in account host values. But if DNS returns just host1, use host1 instead.

If DNS returns the IP address for a given host as 192.168.1.2, that will match an account host value of 192.168.1.2 but not 192.168.01.2. Similarly, it will match an account host pattern like 192.168.1.% but not 192.168.01.%.

The client host from which you connect

Your MySQL user name

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 host name and user name.

A blank User value matches the anonymous user. A nonblank value matches literally; there are no wildcards in user names.

The wildcard characters “%” and “_” can be used in the Host and Db columns. 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 '%' or blank Host value means “any host.”

A '%' or blank Db value means “any database.”

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, Column_name, and Routine_name columns cannot contain wildcards or be blank.

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

Database privilege changes take effect the next time the client executes a USE db_name statement.

Global privileges and passwords are unaffected for a connected client. These changes take effect only for subsequent connections.

Make sure that the server is running. If it is not, clients cannot connect to it. For example, if an attempt to connect to the server fails with 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 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

Make sure that the server has not been configured to ignore network connections or (if you are attempting to connect remotely) that it has not been configured to listen only locally on its network interfaces. If the server was started with --skip-networking, it will not accept TCP/IP connections at all. If the server was started with --bind-address=127.0.0.1, it will listen for TCP/IP connections only locally on the loopback interface and will not accept remote connections.

Check to make sure that there is no firewall blocking access to MySQL. Your firewall may be configured on the basis of the application being executed, or the port number used by MySQL for communication (3306 by default). Under Linux or Unix, check your IP tables (or similar) configuration to ensure that the port has not been blocked. Under Windows, applications such as ZoneAlarm or the Windows XP personal firewall may need to be configured not to block the MySQL port.

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.10.1, “Unix Postinstallation Procedures”.

To determine whether you need to initialize the grant tables, 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 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.10.2, “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.4.7, “mysql_upgrade — Check and Upgrade MySQL Tables”.

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 6.1.2.4, “Password Hashing in MySQL”, and Section C.5.2.4, “Client does not support authentication protocol”.

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 any applicable option files and your environment. 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 --no-defaults option. For example:

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

The option files that clients use are listed in Section 4.2.3.3, “Using Option Files”. Environment variables are listed in Section 2.12, “Environment Variables”.

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 6.3.5, “Assigning Account Passwords”.

If you have lost or forgotten the root password, see Section C.5.4.1, “How to Reset the Root 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 assigns 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 CREATE USER or GRANT statements or the mysqladmin password command. Each of those automatically uses PASSWORD() to encrypt the password. See Section 6.3.5, “Assigning Account Passwords”, and Section 13.7.1.2, “CREATE USER Syntax”.

localhost is a synonym for your local host name, 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 host name 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.

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 host name and user name 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 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 client's host name and your MySQL user name.

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 host name and user name that you are using when trying to connect.

If you do not know the IP address or host name 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 permits connections from any host for the given user name.

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 host name when trying to connect, but get an error message where the host name is not shown or is an IP address, it means that the MySQL server got an error when trying to resolve the IP address of the client host to a name:

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

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 host name for your client:

Access denied for user ''@'unknown'

These errors indicate a DNS problem. To fix it, execute mysqladmin flush-hosts to reset the internal DNS host cache. See Section 8.11.5.2, “DNS Lookup Optimization and the Host Cache”.

Some permanent solutions are:

If mysql -u root test works but mysql -h your_hostname -u root test results in Access denied (where your_hostname is the actual host name 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 host name, 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 'pluto' in the user table, but your DNS tells MySQL that your host name is 'pluto.example.com', the entry does not work. Try adding an entry to the user table that contains the IP address 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, 'pluto.%'. However, use of Host values ending with “%” is insecure and is not recommended!)

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

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 user name 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 '_' characters). A very common error is to insert a new entry with Host='%' and User='some_user', thinking that this enables 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='localhost' 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. See also Section 6.2.4, “Access Control, Stage 1: Connection Verification”.

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 reload 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 statement, you will not need to specify the new password until after you flush the privileges, because the server will not 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 6.2.6, “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 or --password option with no password value, MySQL prompts you for the password.)

For testing purposes, 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 reload the privileges. This enables you to begin using the new grant table contents without stopping and restarting the server.

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. See MySQL Internals: Porting to Other Systems.

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 at Section 1.7, “How to Report Bugs or Problems”. In some cases, you may need to restart mysqld with --skip-grant-tables to run mysqldump.

User names, as used by MySQL for authentication purposes, have nothing to do with user names (login names) as used by Windows or Unix. On Unix, most MySQL clients by default try to log in using the current Unix user name as the MySQL user name, but that is for convenience only. The default can be overridden easily, because client programs permit any user name to be specified with a -u or --user option. Because this means that anyone can attempt to connect to the server using any user name, you cannot make a database secure in any way unless all MySQL accounts have passwords. Anyone who specifies a user name for an account that has no password is able to connect successfully to the server.

MySQL user names can be up to 16 characters long. Operating system user names, because they are completely unrelated to MySQL user names, may be of a different maximum length. For example, Unix user names typically are limited to eight characters.

The server uses MySQL passwords stored in the user table to authenticate client connections using MySQL native authentication (against passwords stored in the mysql.user table). These passwords have nothing to do with passwords for logging in to your operating system. There is no necessary connection between the “external” password you use to log in to a Windows or Unix machine and the password you use to access the MySQL server on that machine.

If the server authenticates a client using a plugin, the authentication method that the plugin implements may or may not use the password in the user table. In this case, it is possible that an external password is also used to authenticate to the MySQL server.

MySQL encrypts passwords stored in the user table using its own algorithm. This encryption is the same as that implemented by the PASSWORD() SQL function but differs from that used during the Unix login process. Unix password encryption is the same as that implemented by the ENCRYPT() SQL function. See the descriptions of the PASSWORD() and ENCRYPT() functions in Section 12.13, “Encryption and Compression Functions”.

From version 4.1 on, MySQL employs a stronger authentication method 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. (In earlier versions, even though passwords are stored in encrypted form in the user table, knowledge of the encrypted password value could be used to connect to the MySQL server.) Section 6.1.2.4, “Password Hashing in MySQL”, discusses password encryption further.

It is possible to connect to the server regardless of character set settings if the user name and password contain only ASCII characters. To connect when the user name or password contain non-ASCII characters, the client should call the mysql_options() C API function with the MYSQL_SET_CHARSET_NAME option and appropriate character set name as arguments. This causes authentication to take place using the specified character set. Otherwise, authentication will fail unless the server default character set is the same as the encoding in the authentication defaults.

Standard MySQL client programs support a --default-character-set option that causes mysql_options() to be called as just described. In addition, character set autodetection is supported as described in Section 10.1.4, “Connection Character Sets and Collations”. For programs that use a connector that is not based on the C API, the connector may provide an equivalent to mysql_options() that can be used instead. Check the connector documentation.

The preceding notes do not apply for ucs2, utf16, and utf32, which are not permitted as client character sets.

By using statements intended for creating accounts, such as CREATE USER or GRANT. These statements cause the server to make appropriate modifications to the grant tables.

By manipulating the MySQL grant tables directly with statements such as INSERT, UPDATE, or DELETE.

Two of the accounts have a user name of monty and a password of some_pass. Both accounts are superuser accounts with full privileges to do anything. The 'monty'@'localhost' account can be used only when connecting from the local host. The 'monty'@'%' account uses the '%' wildcard for the host part, so it can be used to connect from any host.

It is necessary to have both accounts for monty to be able to connect from anywhere as monty. Without the localhost account, the anonymous-user account for localhost that is created by mysql_install_db would take precedence when monty connects from the local host. As a result, monty would be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the 'monty'@'%' account and thus comes earlier in the user table sort order. (user table sorting is discussed in Section 6.2.4, “Access Control, Stage 1: Connection Verification”.)

The 'admin'@'localhost' account has no password. This account can be used only by admin to connect from the local host. It is granted the RELOAD and PROCESS administrative privileges. These privileges enable the admin user to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-xxx commands, as well as mysqladmin processlist . No privileges are granted for accessing any databases. You could add such privileges later by issuing other GRANT statements.

The 'dummy'@'localhost' account has no password. This account can be used only to connect from the local host. No privileges are granted. It is assumed that you will grant specific privileges to the account later.

The first account can access the bankaccount database, but only from the local host.

The second account can access the expenses database, but only from the host host47.example.com.

The third account can access the customer database, but only from the host server.domain.

The number of queries that an account can issue per hour

The number of updates that an account can issue per hour

The number of times an account can connect to the server per hour

The number of simultaneous connections to the server by an account

To reset the current counts to zero for all accounts, issue a FLUSH USER_RESOURCES statement. The counts also can be reset by reloading the grant tables (for example, with a FLUSH PRIVILEGES statement or a mysqladmin reload command).

The counts for an individual account can be set to zero by re-granting it any of its limits. To do this, use GRANT USAGE as described earlier and specify a limit value equal to the value that the account currently has.

When you assign a password using CREATE USER, GRANT with an IDENTIFIED BY clause, or the mysqladmin password command, they encrypt the password for you. Specify the literal plaintext password:

mysql> CREATE USER 'jeffrey'@'localhost'
    -> IDENTIFIED BY 'mypass';

For CREATE USER or GRANT, you can avoid sending the plaintext password if you know the hash value that PASSWORD() would return for the password. Specify the hash value preceded by the keyword PASSWORD:

mysql> CREATE USER 'jeffrey'@'localhost'
    -> IDENTIFIED BY PASSWORD '*90E462C37378CED12064BB3388827D2BA3A9B689';

When you assign an account a nonempty password using SET PASSWORD, you must use the PASSWORD() function to encrypt the password, otherwise the password is stored as plaintext. Suppose that you assign a password like this:

mysql> SET PASSWORD FOR
    -> 'jeffrey'@'localhost' = 'mypass';

The result is that the literal value 'mypass' is stored as the password in the user table, not the encrypted value. When jeffrey attempts to connect to the server using this password, the value is encrypted and compared to the value stored in the user table. However, the stored value is the literal string 'mypass', so the comparison fails and the server rejects the connection with an Access denied error.

To establish a password for a new account, provide a value for the Password column:

mysql> INSERT INTO mysql.user (Host,User,Password)
    -> VALUES('localhost','jeffrey',PASSWORD('mypass'));
mysql> FLUSH PRIVILEGES;

To change the password for an existing account, use UPDATE to set the Password column value:

mysql> UPDATE mysql.user SET Password = PASSWORD('bagel')
    -> WHERE Host = 'localhost' AND User = 'francis';
mysql> FLUSH PRIVILEGES;

The server determines from the account row which authentication plugin applies for the client:

The plugin returns a status to the server indicating whether the user is permitted to connect.

External authentication: Pluggable authentication makes it possible for clients to connect to the MySQL server with credentials that are appropriate for authentication methods other than native authentication based on passwords stored in the mysql.user table. For example, plugins can be created to use external authentication methods such as PAM, Windows login IDs, LDAP, or Kerberos.

Proxy users: If a user is permitted to connect, an authentication plugin can return to the server a user name different from the name of the connecting user, to indicate that the connecting user is a proxy for another user. While the connection lasts, the proxy user is treated, for purposes of access control, as having the privileges of a different user. In effect, one user impersonates another. For more information, see Section 6.3.7, “Proxy Users”.

Plugins that perform native authentication that matches the password against the Password column of the account row. See Section 6.3.6.1, “The Native Authentication Plugins”. Native authentication is the default for accounts that have no plugin named explicitly in their account row.

A plugin that performs authentication using SHA-256 password hashing. This plugin matches the password against the authentication_string column of the account row. This is stronger encryption than that available with native authentication. See Section 6.3.6.2, “The SHA-256 Authentication Plugin”.

A client-side plugin that sends the password to the server without hashing or encryption. This plugin can be used by server-side plugins that require access to the password exactly as provided by the client user. See Section 6.3.6.3, “The Cleartext Client-Side Authentication Plugin”.

A plugin that authenticates clients that connect from the local host through the Unix socket file. See Section 6.3.6.4, “The Socket Peer-Credential Authentication Plugin”.

A test plugin that authenticates using MySQL native authentication. This plugin is intended for testing and development purposes, and as an example of how to write an authentication plugin. See Section 6.3.6.5, “The Test Authentication Plugin”.

On the server host, install the appropriate library containing the server plugin, if necessary, so that the server can use it to authenticate client connections. Similarly, on each client host, install the appropriate library containing the client plugin for use by client programs.

Create MySQL accounts that specify use of the plugin for authentication.

When a client connects, the server plugin tells the client program which client plugin to use for authentication.

The server-side plugin name is test_plugin_server.

The client-side plugin name is auth_test_plugin.

Both plugins are located in the shared library object file named auth_test_plugin.so in the plugin directory (the directory named by the plugin_dir system variable). The file name suffix might differ on your system.

When a connecting client should be treated as a proxy user, the plugin must return a different name, to indicate the proxied user name.

A proxy user account must be set up to be authenticated by the plugin. Use the CREATE USER or GRANT statement to associate an account with a plugin.

A proxy user account must have the PROXY privilege for the proxied account. Use the GRANT statement for this.

By proxied_user for itself: The value of USER() must exactly match CURRENT_USER() and proxied_user, for both the user name and host name parts of the account name.

By a user that has GRANT PROXY ... WITH GRANT OPTION for proxied_user.

proxy_user: This value is NULL if proxying is not used. Otherwise, it indicates the proxy user account. For example, if a client authenticates through the default proxy account, this variable will be set as follows:

mysql> SELECT @@proxy_user;
+--------------+
| @@proxy_user |
+--------------+
| ''@''        |
+--------------+

external_user: Sometimes the authentication plugin may use an external user to authenticate to the MySQL server. For example, when using Windows native authentication, a plugin that authenticates using the windows API does not need the login ID passed to it. However, it still uses an Windows user ID to authenticate. The plugin may return this external user ID (or the first 512 UTF-8 bytes of it) to the server using the external_user read-only session variable. If the plugin does not set this variable, its value is NULL.

Stored routines (procedures and functions) defined with the SQL SECURITY DEFINER characteristic

Views defined with the SQL SECURITY DEFINER characteristic

Triggers and events