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() 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 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”.

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 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 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 5.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 8.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 20.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.3, “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. (A patched server could in fact reply with a file-transfer request to any statement, not just LOAD DATA LOCAL, so a more fundamental issue is that clients should not connect to untrusted servers.)

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 statement against the SQL server). In this environment, the client with respect to the MySQL server actually is the Web server, not a remote program being run by users who connect to the Web server.

On the server side:

On the client side:

Enable strict SQL mode to tell the server to be more restrictive of what data values it accepts. See Section 5.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() 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() 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) 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 the ALTER TABLE statement 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.

However, 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. For more information, see Section 13.1.17.3, “CREATE TEMPORARY TABLE Syntax”.

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

The CREATE VIEW privilege enables use of the CREATE VIEW statement.

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.5.54, 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 5.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 6.3.7, “Proxy Users”.

The REFERENCES privilege is unused before MySQL 5.5.41. As of 5.5.41, creation of a foreign key constraint requires at least one of the SELECT, INSERT, UPDATE, DELETE, or REFERENCES privileges 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 the SHOW MASTER STATUS and SHOW SLAVE STATUS statements. In MySQL 5.5.25 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 SELECT privilege is also needed for tables or views being used with EXPLAIN, including any underlying tables of views.

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 the SHOW CREATE VIEW statement. This privilege is also needed for views being used with EXPLAIN.

The SHUTDOWN privilege enables use of the mysqladmin shutdown command and the mysql_shutdown() C API function. There is no corresponding SQL statement.

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 20.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 naming specific account privileges. SHOW GRANTS displays USAGE to indicate that an account has no privileges at a privilege level.

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: User accounts, global privileges, and other non-privilege columns

db: Database-level privileges

host: Obsolete

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 'h1.example.net' and 'bob' applies to authenticating connections made to the server from the host h1.example.net by a client that specifies a user name of bob. Similarly, a db table row with Host, User, and Db column values of 'h1.example.net', 'bob' and 'reports' applies when bob connects from the host h1.example.net 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 6.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 host table is used in conjunction with the db table when you want a given db table row to apply to several hosts. For example, if you want a user to be able to use a database from several hosts in your network, leave the Host value empty in the user's db table row, then populate the host table with a row for each of those hosts. This mechanism is described more detail in Section 6.2.5, “Access Control, Stage 2: Request Verification”.

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 9.1.1, “String Literals”, and Section 9.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. '198.51.100.%' matches any host in the 198.51.100 class C network.

Because IP wildcard values are permitted in host values (for example, '198.51.100.%' to match every host on a subnet), someone could try to exploit this capability by naming a host 198.51.100.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'@'198.51.100.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 = 198.51.100.0

IP addresses that satisfy this condition range from 198.51.100.0 to 198.51.100.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 198.51.100.2, that will match an account host value of 198.51.100.2 but not 198.051.100.2. Similarly, it will match an account host pattern like 198.51.100.% but not 198.051.100.%.

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 in the db table 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 of either table. These have the same meaning as for pattern-matching operations performed with the LIKE operator. If you want to use either character literally when granting privileges, you must escape it with a backslash. For example, to include the underscore character (_) as part of a database name, specify it as \_ in the GRANT statement.

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

A '%' or blank Host value in the host table means “any host.”

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

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 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 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.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 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 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 4.2.6, “Using Option Files”. Environment variables are listed in Section 4.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 6.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 6.3.5, “Assigning Account Passwords”, and Section 13.7.1.1, “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 8.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 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 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 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 you get the following error, you may have a problem with the db or host table:

Access to database denied

If the row selected from the db table has an empty value in the Host column, make sure that there are one or more corresponding rows in the host table specifying which hosts the db table row applies to. This problem occurs infrequently because the host table is rarely used.

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 24.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.6, “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 16 characters long. 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 6.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 10.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. 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 6.2.4, “Access Control, Stage 1: Connection Verification”.)

The 'admin'@'localhost' 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.

If the server cannot find the plugin, an error occurs and the connection attempt is rejected. Otherwise, if the account row specifies a plugin, the server invokes it to authenticate the user.

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.

Choice of authentication methods.  Pluggable authentication makes it easy for DBAs to choose and change the authentication method used for individual MySQL accounts.

External authentication.  Pluggable authentication makes it possible for clients to connect to the MySQL server with credentials appropriate for authentication methods that store credentials elsewhere than in the mysql.user system 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 (the proxied user). While the connection lasts, the proxy user is treated, for purposes of access control, as having the privileges of the proxied user. In effect, one user impersonates another. For more information, see Section 6.3.7, “Proxy Users”.

Available Authentication Plugins

Authentication Plugin Usage

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 and privileges can be assumed by a proxy user).

Requirements for Proxy User Support

Granting the Proxy Privilege

Default Proxy Users

Default Proxy User and Anonymous User Conflicts

Proxy User System Variables

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

Views defined with the SQL SECURITY DEFINER characteristic

Triggers and events

Server-Side Configuration for Encrypted Connections

Client-Side Configuration for Encrypted Connections

--ssl

For the MySQL server, this option specifies that the server permits but does not require encrypted connections.

For MySQL client programs, this option permits but does not require the client to connect to the server using encryption. Therefore, this option is not sufficient in itself to cause an encrypted connection to be used. For example, if you specify this option for a client program but the server has not been configured to support encrypted connections, the client falls back to an unencrypted connection.

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

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

The --ssl option in negated form indicates that encryption should not be used and overrides other --ssl-xxx options. 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 encrypted 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 encrypted connections by a MySQL account, use a GRANT statement for the account that includes a REQUIRE SSL clause. Connection attempts by clients that use the account will be rejected unless MySQL supports encrypted connections and an encrypted connection can be established.

The REQUIRE clause permits other encryption-related options, which can be used to enforce security requirements stricter 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 13.7.1.3, “GRANT Syntax”.

--ssl-ca=file_name

The path name of the Certificate Authority (CA) certificate file in PEM format. This option implies --ssl.

To tell the client not to authenticate the server certificate when establishing an encrypted connection to the server, 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 on the server side.

--ssl-capath=dir_name

The path name of the directory that contains trusted SSL certificate authority (CA) certificate files in PEM format. This option implies --ssl.

To tell the client not to authenticate the server certificate when establishing an encrypted connection to the server, 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 on the server side.

MySQL distributions compiled using OpenSSL support the --ssl-capath option (see Section 6.4.4, “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 path name of the SSL public key certificate file in PEM format. On the client side, this is the client public key certificate. On the server side, this is the server public key certificate. This option implies --ssl.

--ssl-cipher=cipher_list

The list of permitted ciphers for connection encryption. If no cipher in the list is supported, encrypted connections will not work. 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 6.4.6, “Encrypted Connection Protocols and Ciphers”.

--ssl-key=file_name

The path name of the SSL private key file in PEM format. On the client side, this is the client private key. On the server side, this is the server private key. This option implies --ssl.

If the MySQL distribution was compiled using OpenSSL and 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. If the MySQL distribution was built using yaSSL and the key file is protected by a passphrase, an error occurs.

--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 --ssl-mode option was added in MySQL 5.5.49.

--ssl-verify-server-cert

This option is available only for client programs, not the server. It causes the client to perform host name identify verification by checking the server's Common Name identity in the certificate that the server sends to the client. The client verifies the Common 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. Host name identity verification is disabled by default.

Example 1: Creating SSL Files from the Command Line on Unix

Example 2: Creating SSL Files Using a Script on Unix

Example 3: Creating SSL Files on Windows

MySQL Enterprise Edition binary distributions are compiled using yaSSL.

MySQL Community Edition binary distributions are compiled using yaSSL.

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

MySQL Enterprise Edition binary distributions are compiled using yaSSL.

MySQL Community Edition binary distributions are compiled using yaSSL.

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

Information within <AUDIT_RECORD> elements written in the old format using attributes is written in the new format using subelements.

The new format includes more information in <AUDIT_RECORD> elements. Every element includes a RECORD_ID value providing a unique identifier. The TIMESTAMP value includes time zone information. Query records include HOST, IP, OS_LOGIN, and USER information, as well as COMMAND_CLASS and STATUS_CODE values.