Chapter 7 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 7.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() or some other one-way hashing function and store the hash value.

To prevent password recovery using rainbow tables, do not use these functions on a plain password; instead, choose some string to be used as a salt, and use hash(hash(password)+salt) values.

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

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.

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 7.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 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. This includes the server's data directory containing the files that implement the privilege tables. To make FILE-privilege operations 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.

To limit the location in which files can be read and written, set the secure_file_priv system to a specific directory. See Section 6.1.5, “Server System Variables”.

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 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 9.12.3.2, “Using Symbolic Links for MyISAM Tables on Unix”.

Stored programs and views should be written using the security guidelines discussed in Section 23.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 14.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.

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 27.8.7.50, “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 6.1.8, “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_quote() 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 Choosing an API.

If the older ext/mysql extension must be used, then for escaping use the mysql_real_escape_string_quote() function and not mysql_escape_string() or addslashes() because only mysql_real_escape_string_quote() is character set-aware; the other functions can be “bypassed” when using (invalid) multibyte 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, 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.

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.

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 ALTER USER, 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. This includes the server's data directory containing the files that implement the privilege tables. As a security measure, the server will not overwrite existing files. As of MySQL 5.7.17, the FILE privilege is required to use the DATA DIRECTORY or INDEX DIRECTORY table option for the CREATE TABLE statement.

To limit the location in which files can be read and written, set the secure_file_priv system to a specific directory. See Section 6.1.5, “Server System Variables”.

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 PROCESS privilege also enables use of SHOW ENGINE.

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

The REFERENCES privilege is unused before MySQL 5.7.6. As of 5.7.6, creation of a foreign key constraint requires the REFERENCES privilege for the parent table.

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, SHOW SLAVE STATUS, and SHOW BINARY LOGS.

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 SHUTDOWN statement, the mysqladmin shutdown command, and the mysql_shutdown() C API function.

The SUPER privilege enables these operations and server behaviors:

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

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

When a trigger is activated (by a user who has privileges to execute INSERT, UPDATE, or DELETE statements for the table associated with the trigger), trigger execution requires that the user who defined the trigger still have the TRIGGER privilege.

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 authentication_string column can change an account's password, and then connect to the MySQL server using that account.

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

db: Database-level privileges

tables_priv: Table-level privileges

columns_priv: Column-level privileges

procs_priv: Stored procedure and function privileges

proxies_priv: Proxy-user privileges

Scope columns determine the scope of each row 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' applies to 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' applies 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 a table row grants; that is, which operations it permits to be performed. The server combines the information in the various grant tables to form a complete description of a user's privileges. Section 7.2.5, “Access Control, Stage 2: Request Verification”, describes the rules for 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 privileges granted in this table apply 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 the permitted operations. 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 (procedures and functions). A privilege granted at the routine level applies only to a single procedure or function.

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

Host, User: The proxy account; that is, the account that has the PROXY privilege for the proxied account.

Proxied_host, Proxied_user: The proxied account.

Grantor, Timestamp: Unused.

With_grant: Whether the proxy account can grant the PROXY privilege to other accounts.

Account name syntax 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 space or -), or a host_name string containing special characters or wildcard characters (such as . or %); 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 ("). For string-quoting and identifier-quoting guidelines, see Section 10.1.1, “String Literals”, and Section 10.2, “Schema Object Names”.

The user name and host name parts, if quoted, must be quoted separately. That is, write 'me'@'localhost', not 'me@localhost'; the latter is actually equivalent to '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.

For access-checking purposes, comparisons of User values are case sensitive. Comparisons of Host values are not case sensitive.

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.

The % and _ wildcard characters are permitted 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 IP wildcard values are permitted 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 does not perform matching on host names that start with digits and a dot. For example, if a host is named 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, a netmask can be given to indicate 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 range from 192.58.197.0 to 192.58.197.255.

A netmask typically begins with bits set to 1, followed by bits set to 0. Examples:

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. 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.%.

Your identity and whether you can verify your identity by supplying the correct password

Whether your account is locked or unlocked

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 Windows 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 data directory, including the mysql database containing the grant tables. For distributions that do not do this, you must initialize the data directory manually. For details, see Section 2.10, “Postinstallation Setup and Testing”.

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, initialize the data directory. After doing so and starting the server, test the initial privileges by executing this command:

shell> mysql -u root

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 with no password if you initialized MySQL using mysqld --initialize-secure to not create a password for the initial root account (see Section 2.10.1.1, “Initializing the Data Directory Manually Using mysqld”). That is a security risk, so setting the password for the root account is something you should do while you're setting up your other MySQL accounts. For instructions on setting the initial password, see Section 2.10.4, “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 5.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 7.1.2.4, “Password Hashing in MySQL”, and Section B.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 5.2.6, “Using Option Files”. Environment variables are listed in Section 5.9, “MySQL Program 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 7.3.5, “Assigning Account Passwords”.

If you have lost or forgotten the root password, see Section B.5.3.2, “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 7.3.5, “Assigning Account Passwords”, and Section 14.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.

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 9.12.5.2, “DNS Lookup Optimization and the Host Cache”.

Some permanent solutions are:

If mysql -u root works but mysql -h your_hostname -u root 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 a row with host 'pluto' in the user table, but your DNS tells MySQL that your host name is 'pluto.example.com', the row does not work. Try adding a row to the user table that contains the IP address of your host as the Host column value. (Alternatively, you could add a row 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 works but mysql -u user_name some_db does not, you have not granted access to the given user for the database named some_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 rows that have Host values containing wildcards (rows that contain '%' or '_' characters). A very common error is to insert a new row 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 a row with Host='localhost' and User=''. Because that row has a Host value 'localhost' that is more specific than '%', it is used in preference to the new row when connecting from localhost! The correct procedure is to insert a second row with Host='localhost' and User='some_user', or to delete the row with Host='localhost' and User=''. After deleting the row, remember to issue a FLUSH PRIVILEGES statement to reload the grant tables. See also Section 7.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 row 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 7.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 SHOW GRANTS statement 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 Section 28.5.3, “The DBUG Package”.

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. This means that anyone can attempt to connect to the server using any user name, so 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 32 characters long (16 characters before MySQL 5.7.8). Operating system user names may be of a different maximum length. For example, Unix user names typically are limited to eight characters.

To authenticate client connections for accounts that use MySQL native authentication (implemented by the mysql_native_password authentication plugin), the server uses passwords stored in the user table. These passwords are distinct from 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 some other plugin, the authentication method that the plugin implements may or may not use a password stored in the user table. In this case, it is possible that an external password is also used to authenticate to the MySQL server.

Passwords stored in the user table are encrypted using plugin-specific algorithms. For information about MySQL native password hashing, see Section 7.1.2.4, “Password Hashing in MySQL”.

If the user name and password contain only ASCII characters, it is possible to connect to the server regardless of character set settings. 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 11.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 account-management statements intended for creating accounts and establishing their privileges, such as CREATE USER and GRANT. These statements cause the server to make appropriate modifications to the underlying grant tables.

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

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

The 'finley'@'localhost' account is necessary if there is an anonymous-user account for localhost. Without the 'finley'@'localhost' account, that anonymous-user account takes precedence when finley connects from the local host and finley is treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the 'finley'@'%' account and thus comes earlier in the user table sort order. (user table sorting is discussed in Section 7.2.4, “Access Control, Stage 1: Connection Verification”.)

The 'admin'@'localhost' account has a password of admin_pass. 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 using GRANT statements.

The 'dummy'@'localhost' account has no password (which is insecure and not recommended). 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 using GRANT statements.

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, from any host in the example.com domain. This account has access from all machines in the domain due to use of the % wildcard character in the host part of the account name.

The number of queries an account can issue per hour

The number of updates 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 reset to zero by setting any of its limits again. Specify a limit value equal to the value currently assigned to the account.

Use the ALTER USER statement with an IDENTIFIED BY clause:

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

If you are not connected as an anonymous user, you can change your own password without naming your own account literally:

mysql> ALTER USER USER()
    -> IDENTIFIED BY 'mypass';

For these ALTER USER syntaxes, MySQL automatically hashes the password before storing it in the mysql.user table.

ALTER USER syntax for changing passwords is available as of MySQL 5.7.6.

Use SET PASSWORD with the PASSWORD() function:

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

If you are not connected as an anonymous user, you can change your own password by omitting the FOR clause:

mysql> SET PASSWORD = PASSWORD('mypass');

The PASSWORD() function hashes the password using the hashing method determined by the value of the old_passwords system variable value. If SET PASSWORD rejects the hashed password value returned by PASSWORD() as not being in the correct format, it may be necessary to change old_passwords to change the hashing method. See Section 14.7.1.7, “SET PASSWORD Syntax”.

Use SET PASSWORD without the PASSWORD() function:

For this syntax, the meaning differs in MySQL 5.7.6 and higher from earlier versions:

Use a GRANT USAGE statement at the global level (ON *.*) to change an account password without affecting the account's current privileges:

mysql> GRANT USAGE ON *.* TO 'jeffrey'@'localhost'
    -> IDENTIFIED BY 'mypass';

For this GRANT syntax, MySQL automatically hashes the password before storing it in the mysql.user table.

To change an account password from the command line, use the mysqladmin command:

shell> mysqladmin -u user_name -h host_name password "new_password"

The account for which this command sets the password is the one with a mysql.user table row that matches user_name in the User column and the client host from which you connect in the Host column.

For password changes made using mysqladmin, MySQL automatically hashes the password before storing it in the mysql.user table.

To establish a global policy that passwords have a lifetime of approximately six months, start the server with these lines in an option file:

[mysqld]
default_password_lifetime=180

To establish a global policy such that passwords never expire, set default_password_lifetime to 0:

[mysqld]
default_password_lifetime=0

default_password_lifetime can also be changed at runtime (this requires the SUPER privilege):

SET GLOBAL default_password_lifetime = 180;
SET GLOBAL default_password_lifetime = 0;

Require the password to be changed every 90 days:

ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

Disable password expiration:

ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;

Defer to the global expiration policy:

ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;

PASSWORD EXPIRE DEFAULT defers to the global exipration policy and in the mysql.user table sets the password_lifetime field to NULL for the named account.

The server checks whether the password has been manually expired and, if so, restricts the session.

Otherwise, the server checks whether the password is past its lifetime according to the automatic password expiration policy. If so, the server considers the password expired and restricts the session.

The client can reset the account password with ALTER USER or SET PASSWORD. This modifies the row of the mysql.user table associated with the current account, setting the password_expired column to 'N'. After the password has been reset, the server restores normal access for the session, as well as for subsequent connections that use the account.

It is possible to “reset” a password by setting it to its current value. As a matter of good policy, it is preferable to choose a different password.

The client can use SET statements, which is useful before MySQL 5.7.6 if SET PASSWORD must be used instead of ALTER USER and the account password has a hashing format that requires old_passwords to be set to a value different from its default.

Pass the MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS flag to mysql_options() prior to connecting:

arg = 1;
result = mysql_options(mysql,
                       MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS, &arg);

The mysql client enables MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS if invoked interactively or the --connect-expired-password option is given.

Pass the CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS flag to mysql_real_connect() at connection time:

mysql = mysql_real_connect(mysql,
                           host, user, password, "test",
                           port, unix_socket,
                           CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS);

If disconnect_on_expired_password is enabled (the default), the server disconnects the client with an ER_MUST_CHANGE_PASSWORD_LOGIN error.

If disconnect_on_expired_password is disabled, the server puts the client in sandbox mode.

If the account row specifies a plugin, the server invokes it to authenticate the user. If the server cannot find the plugin, an error occurs.

If the account row specifies no plugin name, the server authenticates the account using either the mysql_native_password or mysql_old_password plugin, depending on whether the password hash value in the Password column used native hashing or the older pre-4.1 hashing method. Clients must match the password in the Password column of the account row. As of MySQL 5.7.2, the server requires the plugin value to be nonempty, and as of 5.7.5, support for mysql_old_password is removed.

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 7.3.9, “Proxy Users”.

Plugins that perform native authentication that matches the password against the Password column of the account row. The mysql_native_password plugin implements authentication based on the native password hashing method. The mysql_old_password plugin implements native authentication based on the older (pre-4.1) password hashing method (and is deprecated and removed in MySQL 5.7.5). See Section 7.5.1.1, “The Native Authentication Plugin”, and Section 7.5.1.2, “The Old Native Authentication Plugin”. Native authentication using mysql_native_password is the default for new accounts, unless the default_authentication_plugin system variable is set otherwise.

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 7.5.1.4, “The SHA-256 Authentication Plugin”.

A plugin prevents all client connections to any account that uses it. Use cases for such a plugin includes accounts that must be able to execute stored programs and views with elevated privileges without exposing those privileges to ordinary users, and proxy accounts that should never permit direct login. See Section 7.5.1.7, “The No-Login 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 7.5.1.8, “The Cleartext Client-Side Authentication Plugin”.

A plugin that authenticates clients that connect from the local host through the Unix socket file. See Section 7.5.1.9, “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 7.5.1.10, “The Test Authentication Plugin”.

On the server host, install the library containing the appropriate server plugin, if necessary, so that the server can use it to authenticate client connections. Similarly, on each client host, install the library containing the appropriate 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 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.

The external user is a “proxy user” (a user who can impersonate or become known as another user).

The second user is a “proxied user” (a user whose identity can be taken on by a proxy user).

The plugin must support proxying, either for itself, or by the MySQL server on behalf of the plugin. In the latter case, server support may need to be enabled explicitly; see Server Support for Proxy User Mapping.

A proxy user account must be set up to be authenticated by the plugin. Use the CREATE USER statement to associate an account with an authentication plugin, or ALTER USER to change its plugin.

For a client connecting to the proxy account to be treated as a proxy user, the authentication plugin must return a user name different from the client user name, to indicate the user name for the proxied account.

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

When used with CREATE USER, these clauses specify the initial locking state for a new account. In the absence of either clause, the account is created in an unlocked state.

When used with ALTER USER, these clauses specify the new locking state for an existing account. In the absence of either clause, the account locking state remains unchanged.

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

Views defined with the SQL SECURITY DEFINER characteristic

Triggers and events

MySQL Enterprise Edition binary distributions are compiled using OpenSSL. It is not possible to use yaSSL with MySQL Enterprise Edition.

MySQL Community Edition binary distributions are compiled using yaSSL.

MySQL Community Edition source distributions can be compiled using either OpenSSL or yaSSL (see Section 7.4.2, “Building MySQL with Support for Secure Connections”).

OpenSSL supports a wider range of encryption ciphers from which to choose for the --ssl-cipher option. OpenSSL supports the --ssl-capath, --ssl-crl, and --ssl-crlpath options. See Section 7.4.5, “Command Options for Secure Connections”.

Accounts that authenticate using the sha256_password plugin can use RSA key files for secure password exchange over unencrypted connections. See Section 7.5.1.4, “The SHA-256 Authentication Plugin”.

The server can automatically generate missing SSL and RSA certificate and key files at startup. See Section 7.4.6.1, “Creating SSL and RSA Certificates and Keys using MySQL”.

OpenSSL supports more encryption modes for the AES_ENCRYPT() and AES_DECRYPT() functions. See Section 13.13, “Encryption and Compression Functions”

auto_generate_certs

sha256_password_auto_generate_rsa_keys

sha256_password_private_key_path

sha256_password_public_key_path

Rsa_public_key

MySQL Enterprise Edition binary distributions are compiled using OpenSSL. It is not possible to use yaSSL with MySQL Enterprise Edition.

MySQL Community Edition binary distributions are compiled using yaSSL.

MySQL Community Edition source distributions can be compiled using either OpenSSL or yaSSL.

When compiled using OpenSSL 1.0.1 or higher, MySQL supports the TLSv1, TLSv1.1, and TLSv1.2 protocols.

When compiled using the bundled version of yaSSL, MySQL supports the TLSv1 and TLSv1.1 protocols.

If the server and client are compiled using OpenSSL, TLSv1.2 is used if possible.

If either or both the server and client are compiled using yaSSL, TLSv1.1 is used if possible.

TLSv1.2 does not work with all ciphers that have a key size of 512 bits or less. To use this protocol with such a key, use --ssl-cipher to specify the cipher name explicitly:

AES128-SHA
AES128-SHA256
AES256-SHA
AES256-SHA256
CAMELLIA128-SHA
CAMELLIA256-SHA
DES-CBC3-SHA
DHE-RSA-AES256-SHA
RC4-MD5
RC4-SHA
SEED-SHA

For better security, use a certificate with an RSA key size of of 2048 bits or more.

The following ciphers are permanently restricted:

!DHE-DSS-DES-CBC3-SHA
!DHE-RSA-DES-CBC3-SHA
!ECDH-RSA-DES-CBC3-SHA
!ECDH-ECDSA-DES-CBC3-SHA
!ECDHE-RSA-DES-CBC3-SHA
!ECDHE-ECDSA-DES-CBC3-SHA

The following categories of ciphers are permanently restricted:

!aNULL
!eNULL
!EXPORT
!LOW
!MD5
!DES
!RC2
!RC4
!PSK
!SSLv3

Server-Side Configuration for Secure Connections

Client-Side Configuration for Secure Connections

C API Configuration for Secure Connections

--ssl

This option has different effects on the server and client sides.

For the MySQL server, this option specifies that the server permits but does not require secure connections. The option is enabled on the server side by default.

MySQL servers compiled using OpenSSL can generate missing certificate and key files automatically at startup. See Section 7.4.6.1, “Creating SSL and RSA Certificates and Keys using MySQL”.

The server performs certificate and key file autodiscovery. If --ssl is enabled (possibly along with --ssl-cipher) and other --ssl-xxx options are not given to configure secure connections explicitly, the server attempts to enable support for secure connections automatically at startup:

Client programs attempt to establish a secure connection by default whenever the server supports secure connections. The client-side --ssl option is used as follows:

If other --ssl-xxx options are given in the absence of --ssl, the client attempts to connect securely. If the server is configured to support secure connections, the connection attempt fails if a secure connection cannot be established. If the server is not configured for secure connections, the client falls back to an unencrypted connection.

As a recommended set of options to enable secure connections, use at least --ssl-cert and --ssl-key on the server side and --ssl-ca on the client side. See Section 7.4.4, “Configuring MySQL to Use Secure Connections”.

--ssl is implied by other --ssl-xxx options, as indicated in the descriptions for those options.

The --ssl option in negated form overrides other --ssl-xxx options and indicates that encryption should not be used. To do this, specify the option as --ssl=0 or a synonym (--skip-ssl, --disable-ssl). For example, you might have options specified in the [client] group of your option file to use secure connections by default when you invoke MySQL client programs. To use an unencrypted connection instead, invoke the client program with --ssl=0 on the command line to override the options in the option file.

To require use of secure connections by a MySQL account, use CREATE USER to create the account with at least a REQUIRE SSL clause, or use ALTER USER for an existing account to add a REQUIRE clause. Connections for the account will be rejected unless MySQL supports secure connections and the server and client have been started with the proper secure-connection options.

The REQUIRE clause permits other encryption-related options, which can be used to enforce stricter requirements than REQUIRE SSL. For additional details about which command options may or must be specified by clients that connect using accounts configured using the various REQUIRE options, see the description of REQUIRE in Section 14.7.1.2, “CREATE USER Syntax”.

--ssl-ca=file_name

The path to a file in PEM format that contains a list of trusted SSL certificate authorities. On the server side, this option implies --ssl.

If you use encryption when establishing a client connection, to tell the client not to authenticate the server certificate, specify neither --ssl-ca nor --ssl-capath. The server still verifies the client according to any applicable requirements established for the client account, and it still uses any --ssl-ca or --ssl-capath option values specified at server startup.

--ssl-capath=dir_name

The path to a directory that contains trusted SSL certificate authority certificates in PEM format. On the server side, this option implies --ssl.

If you use encryption when establishing a client connection, to tell the client not to authenticate the server certificate, specify neither --ssl-ca nor --ssl-capath. The server still verifies the client according to any applicable requirements established for the client account, and it still uses any --ssl-ca or --ssl-capath option values specified at server startup.

MySQL distributions compiled using OpenSSL support the --ssl-capath option (see Section 7.4.1, “OpenSSL Versus yaSSL”). Distributions compiled using yaSSL do not because yaSSL does not look in any directory and does not follow a chained certificate tree. yaSSL requires that all components of the CA certificate tree be contained within a single CA certificate tree and that each certificate in the file has a unique SubjectName value. To work around this yaSSL limitation, concatenate the individual certificate files comprising the certificate tree into a new file and specify that file as the value of the --ssl-ca option.

--ssl-cert=file_name

The name of the SSL certificate file in PEM format to use for establishing a secure connection. On the server side, this option implies --ssl.

--ssl-cipher=cipher_list

A list of permissible ciphers to use for connection encryption. If no cipher in the list is supported, encrypted connections will not work. On the server side, this option implies --ssl.

For greatest portability, cipher_list should be a list of one or more cipher names, separated by colons. This format is understood both by OpenSSL and yaSSL. Examples:

--ssl-cipher=AES128-SHA
--ssl-cipher=DHE-RSA-AES256-SHA:AES128-SHA

OpenSSL supports a more flexible syntax for specifying ciphers, as described in the OpenSSL documentation at https://www.openssl.org/docs/manmaster/man1/ciphers.html. yaSSL does not, so attempts to use that extended syntax fail for a MySQL distribution compiled using yaSSL.

For information about which encryption ciphers MySQL supports, see Section 7.4.3, “Secure Connection Protocols and Ciphers”.

--ssl-crl=file_name

The path to a file containing certificate revocation lists in PEM format. On the server side, this option implies --ssl.

If neither --ssl-crl nor --ssl-crlpath is given, no CRL checks are performed, even if the CA path contains certificate revocation lists.

MySQL distributions compiled using OpenSSL support the --ssl-crl option (see Section 7.4.1, “OpenSSL Versus yaSSL”). Distributions compiled using yaSSL do not because revocation lists do not work with yaSSL.

--ssl-crlpath=dir_name

The path to a directory that contains files containing certificate revocation lists in PEM format. On the server side, this option implies --ssl.

If neither --ssl-crl nor --ssl-crlpath is given, no CRL checks are performed, even if the CA path contains certificate revocation lists.

MySQL distributions compiled using OpenSSL support the --ssl-crlpath option (see Section 7.4.1, “OpenSSL Versus yaSSL”). Distributions compiled using yaSSL do not because revocation lists do not work with yaSSL.

--ssl-key=file_name

The name of the SSL key file in PEM format to use for establishing a secure connection. On the server side, this option implies --ssl.

If the key file is protected by a passphrase, the program prompts the user for the passphrase. The password must be given interactively; it cannot be stored in a file. If the passphrase is incorrect, the program continues as if it could not read the key.

For better security, use a certificate with an RSA key size of of 2048 bits or more.

--ssl-mode=mode

This option is available only for client programs, not the server. It specifies the security state of the connection to the server. The following option values are permitted:

Use of the --ssl-ca or --ssl-capath option implies --ssl-mode=VERIFY_CA, if --ssl-mode is not explicitly set otherwise.

If --ssl-mode is explicit, use of a value other than VERIFY_CA or VERIFY_IDENTITY with an explicit --ssl-ca or --ssl-capath option produces a warning that no verification of the server certificate will be done, despite CA certificate options being specified.

The --ssl-mode option was added in MySQL 5.7.11.

To require use of secure connections by a MySQL account, use CREATE USER to create the account with at least a REQUIRE SSL clause, or use ALTER USER for an existing account to add a REQUIRE clause. Connections for the account will be rejected unless MySQL supports secure connections and the server and client have been started with the proper secure-connection options.

The REQUIRE clause permits other encryption-related options, which can be used to enforce stricter requirements than REQUIRE SSL. For additional details about which command options may or must be specified by clients that connect using accounts configured using the various REQUIRE options, see the description of REQUIRE in Section 14.7.1.2, “CREATE USER Syntax”.

--ssl-verify-server-cert

This option is available only for client programs, not the server. It causes the client to check the server's Common Name value in the certificate that the server sends to the client. The client verifies that name against the host name the client uses for connecting to the server, and the connection fails if there is a mismatch. For encrypted connections, this option helps prevent man-in-the-middle attacks. Verification is disabled by default.

--tls-version=protocol_list

For client programs, the protocols permitted by the client for encrypted connections. The value is a comma-separated list containing one or more protocol names. The protocols that can be named for this option depend on the SSL library used to compile MySQL. For details, see Section 7.4.3, “Secure Connection Protocols and Ciphers”.

This option was added in MySQL 5.7.10.

On the server side, the tls_version system variable can be used instead.