Menu:
secure-client-programming:: Client Programming Security Guidelines
This section describes general security issues to be aware of and what you can do to make your MySQL installation more secure against attack or misuse. For information specifically about the access control system that MySQL uses for setting up user accounts and checking database access, see *note postinstallation::.
For answers to some questions that are often asked about MySQL Server security issues, see *note faqs-security::.
File: manual.info.tmp, Node: security-guidelines, Next: password-security, Prev: general-security-issues, Up: general-security-issues
Anyone using MySQL on a computer connected to the Internet should read this section to avoid the most common security mistakes.
In discussing security, it is necessary to consider fully protecting the entire server host (not just the MySQL server) against all types of applicable attacks: eavesdropping, altering, playback, and denial of service. We do not cover all aspects of availability and fault tolerance here.
MySQL uses security based on Access Control Lists (ACLs) for all connections, queries, and other operations that users can attempt to perform. There is also support for SSL-encrypted connections between MySQL clients and servers. Many of the concepts discussed here are not specific to MySQL at all; the same general ideas apply to almost all applications.
When running MySQL, follow these guidelines:
Do not ever give anyone (except MySQL 'root' accounts) access to the 'user' table in the 'mysql' system database! This is critical.
Learn how the MySQL access privilege system works (see note access-control::). Use the note 'GRANT': grant. and *note 'REVOKE': revoke. statements to control access to MySQL. Do not grant more privileges than necessary. Never grant privileges to all hosts.
Checklist:
* Try 'mysql -u root'. If you are able to connect successfully
to the server without being asked for a password, anyone can
connect to your MySQL server as the MySQL 'root' user with
full privileges! Review the MySQL installation instructions,
paying particular attention to the information about setting a
'root' password. See *note default-privileges::.
* Use the *note 'SHOW GRANTS': show-grants. statement to check
which accounts have access to what. Then use the *note
'REVOKE': revoke. statement to remove those privileges that
are not necessary.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.
Assume that all passwords will be subject to automated cracking attempts using lists of known passwords, and also to targeted guessing using publicly available information about you, such as social media posts. Do not choose passwords that consist of easily cracked or guessed items such as a dictionary word, proper name, sports team name, acronym, or commonly known phrase, particularly if they are relevant to you. The use of upper case letters, number substitutions and additions, and special characters does not help if these are used in predictable ways. Also do not choose any password you have seen used as an example anywhere, or a variation on it, even if it was presented as an example of a strong password.
Instead, choose passwords that are as long and as unpredictable as possible. That does not mean the combination needs to be a random string of characters that is difficult to remember and reproduce, although this is a good approach if you have, for example, password manager software that can generate and fill such passwords and store them securely. A passphrase containing multiple words is easy to create, remember, and reproduce, and is much more secure than a typical user-selected password consisting of a single modified word or a predictable sequence of characters. To create a secure passphrase, ensure that the words and other items in it are not a known phrase or quotation, do not occur in a predictable order, and preferably have no previous relationship to each other at all.
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:
* Try to scan your ports from the Internet using a tool such as
'nmap'. MySQL uses port 3306 by default. This port should
not be accessible from untrusted hosts. As a simple way to
check whether your MySQL port is open, try the following
command from some remote machine, where SERVER_HOST is the
host name or IP address of the host on which your MySQL server
runs:
$> telnet SERVER_HOST 3306
If 'telnet' hangs or the connection is refused, the port is
blocked, which is how you want it to be. If you get a
connection and some garbage characters, the port is open, and
should be closed on your firewall or router, unless you really
have a good reason to keep it open.Applications that access MySQL should not trust any data entered by users, and should be written using proper defensive programming techniques. See *note secure-client-programming::.
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:
$> tcpdump -l -i eth0 -w - src or dst port 3306 | stringsThis works under Linux and should work with small modifications under other systems.
Warning:
If you do not see cleartext data, this does not always mean that the information actually is encrypted. If you need high security, consult with a security expert.
File: manual.info.tmp, Node: password-security, Next: security-against-attack, Prev: security-guidelines, Up: general-security-issues
Menu:
password-hashing:: Password Hashing in MySQL
Passwords occur in several contexts within MySQL. The following sections provide guidelines that enable end users and administrators to keep these passwords secure and avoid exposing them. There is also a discussion of how MySQL uses password hashing internally and of a plugin that you can use to enforce stricter passwords.
File: manual.info.tmp, Node: password-security-user, Next: password-security-admin, Prev: password-security, Up: password-security
6.1.2.1 End-User Guidelines for Password Security .................................................
MySQL users should use the following guidelines to keep passwords secure.
When you run a client program to connect to the MySQL server, it is inadvisable to specify your password in a way that exposes it to discovery by other users. The methods you can use to specify your password when you run client programs are listed here, along with an assessment of the risks of each method. In short, the safest methods are to have the client program prompt for the password or to specify the password in a properly protected option file.
Use the note 'mysql_config_editor': mysql-config-editor. utility, which enables you to store authentication credentials in an encrypted login path file named '.mylogin.cnf'. The file can be read later by MySQL client programs to obtain authentication credentials for connecting to MySQL Server. See note mysql-config-editor::.
Use a '--password=PASSWORD' or '-pPASSWORD' option on the command line. For example:
$> mysql -u francis -pfrank DB_NAMEWarning:
This is convenient but insecure. On some systems, your password becomes visible to system status programs such as 'ps' that may be invoked by other users to display command lines. MySQL clients typically overwrite the command-line password argument with zeros during their initialization sequence. However, there is still a brief interval during which the value is visible. Also, on some systems this overwriting strategy is ineffective and the password remains visible to 'ps'. (SystemV Unix systems and perhaps others are subject to this problem.)
If your operating environment is set up to display your current command in the title bar of your terminal window, the password remains visible as long as the command is running, even if the command has scrolled out of view in the window content area.
Use the '--password' or '-p' option on the command line with no password value specified. In this case, the client program solicits the password interactively:
$> mysql -u francis -p DB_NAME
Enter password: ********The '*' characters indicate where you enter your password. The password is not displayed as you enter it.
It is more secure to enter your password this way than to specify it on the command line because it is not visible to other users. However, this method of entering a password is suitable only for programs that you run interactively. If you want to invoke a client from a script that runs noninteractively, there is no opportunity to enter the password from the keyboard. On some systems, you may even find that the first line of your script is read and interpreted (incorrectly) as your password.
Store your password in an option file. For example, on Unix, you can list your password in the '[client]' section of the '.my.cnf' file in your home directory:
[client]
password=PASSWORDTo keep the password safe, the file should not be accessible to anyone but yourself. To ensure this, set the file access mode to '400' or '600'. For example:
$> chmod 600 .my.cnfTo name from the command line a specific option file containing the password, use the '--defaults-file=FILE_NAME' option, where 'file_name' is the full path name to the file. For example:
$> mysql --defaults-file=/home/francis/mysql-opts*note option-files::, discusses option files in more detail.
Store your password in the 'MYSQL_PWD' environment variable. See *note environment-variables::.
This method of specifying your MySQL password must be considered extremely insecure and should not be used. Some versions of 'ps' include an option to display the environment of running processes. On some systems, if you set 'MYSQL_PWD', your password is exposed to any other user who runs 'ps'. Even on systems without such a version of 'ps', it is unwise to assume that there are no other methods by which users can examine process environments.
On Unix, the note 'mysql': mysql. client writes a record of executed statements to a history file (see note mysql-logging::). By default, this file is named '.mysql_history' and is created in your home directory. Passwords can be written as plain text in SQL statements such as note 'CREATE USER': create-user. and note 'ALTER USER': alter-user, so if you use these statements, they are logged in the history file. To keep this file safe, use a restrictive access mode, the same way as described earlier for the '.my.cnf' file.
If your command interpreter is configured to maintain a history, any file in which the commands are saved contains MySQL passwords entered on the command line. For example, 'bash' uses '~/.bash_history'. Any such file should have a restrictive access mode.
File: manual.info.tmp, Node: password-security-admin, Next: password-logging, Prev: password-security-user, Up: password-security
6.1.2.2 Administrator Guidelines for Password Security ......................................................
Database administrators should use the following guidelines to keep passwords secure.
MySQL stores passwords for user accounts in the 'mysql.user' system table. Access to this table should never be granted to any nonadministrative accounts.
Account passwords can be expired so that users must reset them. See note password-management::, and note expired-password-handling::.
The 'validate_password' plugin can be used to enforce a policy on acceptable password. See *note validate-password::.
A user who has access to modify the plugin directory (the value of the 'plugin_dir' system variable) or the 'my.cnf' file that specifies the plugin directory location can replace plugins and modify the capabilities provided by plugins, including authentication plugins.
Files such as log files to which passwords might be written should be protected. See *note password-logging::.
File: manual.info.tmp, Node: password-logging, Next: password-hashing, Prev: password-security-admin, Up: password-security
6.1.2.3 Passwords and Logging .............................
Passwords can be written as plain text in SQL statements such as note 'CREATE USER': create-user, note 'GRANT': grant, *note 'SET PASSWORD': set-password, and statements that invoke the 'PASSWORD()' function. If such statements are logged by the MySQL server as written, passwords in them become visible to anyone with access to the logs.
Statement logging avoids writing passwords as cleartext for the following statements:
CREATE USER ... IDENTIFIED BY ...
ALTER USER ... IDENTIFIED BY ...
GRANT ... IDENTIFIED BY ...
SET PASSWORD ...
SLAVE START ... PASSWORD = ...
CREATE SERVER ... OPTIONS(... PASSWORD ...)
ALTER SERVER ... OPTIONS(... PASSWORD ...)Passwords in those statements are rewritten to not appear literally in statement text written to the general query log, slow query log, and binary log. Rewriting does not apply to other statements. In particular, note 'INSERT': insert. or note 'UPDATE': update. statements for the 'mysql.user' system table that refer to literal passwords are logged as is, so you should avoid such statements. (Direct modification of grant tables is discouraged, anyway.)
For the general query log, password rewriting can be suppressed by starting the server with the '--log-raw' option. For security reasons, this option is not recommended for production use. For diagnostic purposes, it may be useful to see the exact text of statements as received by the server.
Contents of the audit log file produced by the audit log plugin are not encrypted. For security reasons, this file should be written to a directory accessible only to the MySQL server and users with a legitimate reason to view the log. See *note audit-log-security::.
Statements received by the server may be rewritten if a query rewrite plugin is installed (see Query Rewrite Plugins (https://dev.mysql.com/doc/extending-mysql/5.7/en/plugin-types.html#query-rewrite-plugin-type)). In this case, the '--log-raw' option affects statement logging as follows:
Without '--log-raw', the server logs the statement returned by the query rewrite plugin. This may differ from the statement as received.
With '--log-raw', the server logs the original statement as received.
An implication of password rewriting is that statements that cannot be parsed (due, for example, to syntax errors) are not written to the general query log because they cannot be known to be password free. Use cases that require logging of all statements including those with errors should use the '--log-raw' option, bearing in mind that this also bypasses password rewriting.
Password rewriting occurs only when plain text passwords are expected. For statements with syntax that expect a password hash value, no rewriting occurs. If a plain text password is supplied erroneously for such syntax, the password is logged as given, without rewriting. For example, the following statement is logged as shown because a password hash value is expected:
CREATE USER 'user1'@'localhost' IDENTIFIED BY PASSWORD 'not-so-secret';To guard log files against unwarranted exposure, locate them in a directory that restricts access to the server and the database administrator. If the server logs to tables in the 'mysql' database, grant access to those tables only to the database administrator.
Replicas store the password for the replication source in the source info repository, which can be either a file or a table (see note replica-logs::). Ensure that the repository can be accessed only by the database administrator. An alternative to storing the password in a file is to use the note 'START SLAVE': start-slave. statement to specify credentials for connecting to the source.
Use a restricted access mode to protect database backups that include log tables or log files containing passwords.
File: manual.info.tmp, Node: password-hashing, Prev: password-logging, Up: password-security
6.1.2.4 Password Hashing in MySQL .................................
Note:
The information in this section applies fully only before MySQL 5.7.5, and only for accounts that use the 'mysql_native_password' or 'mysql_old_password' authentication plugins. Support for pre-4.1 password hashes was removed in MySQL 5.7.5. This includes removal of the 'mysql_old_password' authentication plugin and the 'OLD_PASSWORD()' function. Also, 'secure_auth' cannot be disabled, and 'old_passwords' cannot be set to 1.
As of MySQL 5.7.5, only the information about 4.1 password hashes and the 'mysql_native_password' authentication plugin remains relevant.
MySQL lists user accounts in the 'user' table of the 'mysql' database. Each MySQL account can be assigned a password, although the 'user' table does not store the cleartext version of the password, but a hash value computed from it.
MySQL uses passwords in two phases of client/server communication:
When a client attempts to connect to the server, there is an initial authentication step in which the client must present a password that has a hash value matching the hash value stored in the 'user' table for the account the client wants to use.
After the client connects, it can (if it has sufficient privileges) set or change the password hash for accounts listed in the 'user' table. The client can do this by using the 'PASSWORD()' function to generate a password hash, or by using a password-generating statement (note 'CREATE USER': create-user, note 'GRANT': grant, or *note 'SET PASSWORD': set-password.).
In other words, the server checks hash values during authentication when a client first attempts to connect. The server generates hash values if a connected client invokes the 'PASSWORD()' function or uses a password-generating statement to set or change a password.
Password hashing methods in MySQL have the history described following. These changes are illustrated by changes in the result from the 'PASSWORD()' function that computes password hash values and in the structure of the 'user' table where passwords are stored.
The Original (Pre-4.1) Hashing Method
The original hashing method produced a 16-byte string. Such hashes look like this:
mysql> SELECT PASSWORD('mypass');
+--------------------+
| PASSWORD('mypass') |
+--------------------+
| 6f8c114b58f2ce9e |
+--------------------+To store account passwords, the 'Password' column of the 'user' table was at this point 16 bytes long.
The 4.1 Hashing Method
MySQL 4.1 introduced password hashing that provided better security and reduced the risk of passwords being intercepted. There were several aspects to this change:
Different format of password values produced by the 'PASSWORD()' function
Widening of the 'Password' column
Control over the default hashing method
Control over the permitted hashing methods for clients attempting to connect to the server
The changes in MySQL 4.1 took place in two stages:
MySQL 4.1.0 used a preliminary version of the 4.1 hashing method. This method was short lived and the following discussion says nothing more about it.
In MySQL 4.1.1, the hashing method was modified to produce a longer 41-byte hash value:
mysql> SELECT PASSWORD('mypass');
+-------------------------------------------+
| PASSWORD('mypass') |
+-------------------------------------------+
| *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
+-------------------------------------------+The longer password hash format has better cryptographic properties, and client authentication based on long hashes is more secure than that based on the older short hashes.
To accommodate longer password hashes, the 'Password' column in the 'user' table was changed at this point to be 41 bytes, its current length.
A widened 'Password' column can store password hashes in both the pre-4.1 and 4.1 formats. The format of any given hash value can be determined two ways:
* The length: 4.1 and pre-4.1 hashes are 41 and 16 bytes,
respectively.
* Password hashes in the 4.1 format always begin with a '*'
character, whereas passwords in the pre-4.1 format never do.To permit explicit generation of pre-4.1 password hashes, two additional changes were made:
* The 'OLD_PASSWORD()' function was added, which returns hash
values in the 16-byte format.
* For compatibility purposes, the 'old_passwords' system
variable was added, to enable DBAs and applications control
over the hashing method. The default 'old_passwords' value of
0 causes hashing to use the 4.1 method (41-byte hash values),
but setting 'old_passwords=1' causes hashing to use the
pre-4.1 method. In this case, 'PASSWORD()' produces 16-byte
values and is equivalent to 'OLD_PASSWORD()'To permit DBAs control over how clients are permitted to connect, the 'secure_auth' system variable was added. Starting the server with this variable disabled or enabled permits or prohibits clients to connect using the older pre-4.1 password hashing method. Before MySQL 5.6.5, 'secure_auth' is disabled by default. As of 5.6.5, 'secure_auth' is enabled by default to promote a more secure default configuration DBAs can disable it at their discretion, but this is not recommended, and pre-4.1 password hashes are deprecated and should be avoided. (For account upgrade instructions, see *note account-upgrades::.)
In addition, the *note 'mysql': mysql. client supports a '--secure-auth' option that is analogous to 'secure_auth', but from the client side. It can be used to prevent connections to less secure accounts that use pre-4.1 password hashing. This option is disabled by default before MySQL 5.6.7, enabled thereafter.
Compatibility Issues Related to Hashing Methods
The widening of the 'Password' column in MySQL 4.1 from 16 bytes to 41 bytes affects installation or upgrade operations as follows:
If you perform a new installation of MySQL, the 'Password' column is made 41 bytes long automatically.
Upgrades from MySQL 4.1 or later to current versions of MySQL should not give rise to any issues in regard to the 'Password' column because both versions use the same column length and password hashing method.
For upgrades from a pre-4.1 release to 4.1 or later, you must upgrade the system tables after upgrading. (See *note mysql-upgrade::.)
The 4.1 hashing method is understood only by MySQL 4.1 (and higher) servers and clients, which can result in some compatibility problems. A 4.1 or higher client can connect to a pre-4.1 server, because the client understands both the pre-4.1 and 4.1 password hashing methods. However, a pre-4.1 client that attempts to connect to a 4.1 or higher server may run into difficulties. For example, a 4.0 *note 'mysql': mysql. client may fail with the following error message:
$> mysql -h localhost -u root
Client does not support authentication protocol requested
by server; consider upgrading MySQL clientThe following discussion describes the differences between the pre-4.1 and 4.1 hashing methods, and what you should do if you upgrade your server but need to maintain backward compatibility with pre-4.1 clients. (However, permitting connections by old clients is not recommended and should be avoided if possible.) This information is of particular importance to PHP programmers migrating MySQL databases from versions older than 4.1 to 4.1 or higher.
The differences between short and long password hashes are relevant both for how the server uses passwords during authentication and for how it generates password hashes for connected clients that perform password-changing operations.
The way in which the server uses password hashes during authentication is affected by the width of the 'Password' column:
If the column is short, only short-hash authentication is used.
If the column is long, it can hold either short or long hashes, and the server can use either format:
* Pre-4.1 clients can connect, but because they know only about
the pre-4.1 hashing method, they can authenticate only using
accounts that have short hashes.
* 4.1 and later clients can authenticate using accounts that
have short or long hashes.Even for short-hash accounts, the authentication process is actually a bit more secure for 4.1 and later clients than for older clients. In terms of security, the gradient from least to most secure is:
Pre-4.1 client authenticating with short password hash
4.1 or later client authenticating with short password hash
4.1 or later client authenticating with long password hash
The way in which the server generates password hashes for connected clients is affected by the width of the 'Password' column and by the 'old_passwords' system variable. A 4.1 or later server generates long hashes only if certain conditions are met: The 'Password' column must be wide enough to hold long values and 'old_passwords' must not be set to 1.
Those conditions apply as follows:
The 'Password' column must be wide enough to hold long hashes (41 bytes). If the column has not been updated and still has the pre-4.1 width of 16 bytes, the server notices that long hashes cannot fit into it and generates only short hashes when a client performs password-changing operations using the 'PASSWORD()' function or a password-generating statement. This is the behavior that occurs if you have upgraded from a version of MySQL older than 4.1 to 4.1 or later but have not yet run the *note 'mysql_upgrade': mysql-upgrade. program to widen the 'Password' column.
If the 'Password' column is wide, it can store either short or long password hashes. In this case, the 'PASSWORD()' function and password-generating statements generate long hashes unless the server was started with the 'old_passwords' system variable set to 1 to force the server to generate short password hashes instead.
The purpose of the 'old_passwords' system variable is to permit backward compatibility with pre-4.1 clients under circumstances where the server would otherwise generate long password hashes. The option does not affect authentication (4.1 and later clients can still use accounts that have long password hashes), but it does prevent creation of a long password hash in the 'user' table as the result of a password-changing operation. Were that permitted to occur, the account could no longer be used by pre-4.1 clients. With 'old_passwords' disabled, the following undesirable scenario is possible:
An old pre-4.1 client connects to an account that has a short password hash.
The client changes its own password. With 'old_passwords' disabled, this results in the account having a long password hash.
The next time the old client attempts to connect to the account, it cannot, because the account has a long password hash that requires the 4.1 hashing method during authentication. (Once an account has a long password hash in the user table, only 4.1 and later clients can authenticate for it because pre-4.1 clients do not understand long hashes.)
This scenario illustrates that, if you must support older pre-4.1 clients, it is problematic to run a 4.1 or higher server without 'old_passwords' set to 1. By running the server with 'old_passwords=1', password-changing operations do not generate long password hashes and thus do not cause accounts to become inaccessible to older clients. (Those clients cannot inadvertently lock themselves out by changing their password and ending up with a long password hash.)
The downside of 'old_passwords=1' is that any passwords created or changed use short hashes, even for 4.1 or later clients. Thus, you lose the additional security provided by long password hashes. To create an account that has a long hash (for example, for use by 4.1 clients) or to change an existing account to use a long password hash, an administrator can set the session value of 'old_passwords' set to 0 while leaving the global value set to 1:
mysql> SET @@SESSION.old_passwords = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@SESSION.old_passwords, @@GLOBAL.old_passwords;
+-------------------------+------------------------+
| @@SESSION.old_passwords | @@GLOBAL.old_passwords |
+-------------------------+------------------------+
| 0 | 1 |
+-------------------------+------------------------+
1 row in set (0.00 sec)
mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'newpass';
Query OK, 0 rows affected (0.03 sec)
mysql> SET PASSWORD FOR 'existinguser'@'localhost' = PASSWORD('existingpass');
Query OK, 0 rows affected (0.00 sec)The following scenarios are possible in MySQL 4.1 or later. The factors are whether the 'Password' column is short or long, and, if long, whether the server is started with 'old_passwords' enabled or disabled.
Scenario 1: Short 'Password' column in user table:
Only short hashes can be stored in the 'Password' column.
The server uses only short hashes during client authentication.
For connected clients, password hash-generating operations involving the 'PASSWORD()' function or password-generating statements use short hashes exclusively. Any change to an account's password results in that account having a short password hash.
The value of 'old_passwords' is irrelevant because with a short 'Password' column, the server generates only short password hashes anyway.
This scenario occurs when a pre-4.1 MySQL installation has been upgraded to 4.1 or later but *note 'mysql_upgrade': mysql-upgrade. has not been run to upgrade the system tables in the 'mysql' database. (This is not a recommended configuration because it does not permit use of more secure 4.1 password hashing.)
Scenario 2: Long 'Password' column; server started with 'old_passwords=1':
Short or long hashes can be stored in the 'Password' column.
4.1 and later clients can authenticate for accounts that have short or long hashes.
Pre-4.1 clients can authenticate only for accounts that have short hashes.
For connected clients, password hash-generating operations involving the 'PASSWORD()' function or password-generating statements use short hashes exclusively. Any change to an account's password results in that account having a short password hash.
In this scenario, newly created accounts have short password hashes because 'old_passwords=1' prevents generation of long hashes. Also, if you create an account with a long hash before setting 'old_passwords' to 1, changing the account's password while 'old_passwords=1' results in the account being given a short password, causing it to lose the security benefits of a longer hash.
To create a new account that has a long password hash, or to change the password of any existing account to use a long hash, first set the session value of 'old_passwords' set to 0 while leaving the global value set to 1, as described previously.
In this scenario, the server has an up to date 'Password' column, but is running with the default password hashing method set to generate pre-4.1 hash values. This is not a recommended configuration but may be useful during a transitional period in which pre-4.1 clients and passwords are upgraded to 4.1 or later. When that has been done, it is preferable to run the server with 'old_passwords=0' and 'secure_auth=1'.
Scenario 3: Long 'Password' column; server started with 'old_passwords=0':
Short or long hashes can be stored in the 'Password' column.
4.1 and later clients can authenticate using accounts that have short or long hashes.
Pre-4.1 clients can authenticate only using accounts that have short hashes.
For connected clients, password hash-generating operations involving the 'PASSWORD()' function or password-generating statements use long hashes exclusively. A change to an account's password results in that account having a long password hash.
As indicated earlier, a danger in this scenario is that it is possible for accounts that have a short password hash to become inaccessible to pre-4.1 clients. A change to such an account's password made using the 'PASSWORD()' function or a password-generating statement results in the account being given a long password hash. From that point on, no pre-4.1 client can connect to the server using that account. The client must upgrade to 4.1 or later.
If this is a problem, you can change a password in a special way. For example, normally you use *note 'SET PASSWORD': set-password. as follows to change an account password:
SET PASSWORD FOR 'SOME_USER'@'SOME_HOST' = PASSWORD('PASSWORD');To change the password but create a short hash, use the 'OLD_PASSWORD()' function instead:
SET PASSWORD FOR 'SOME_USER'@'SOME_HOST' = OLD_PASSWORD('PASSWORD');'OLD_PASSWORD()' is useful for situations in which you explicitly want to generate a short hash.
The disadvantages for each of the preceding scenarios may be summarized as follows:
In scenario 1, you cannot take advantage of longer hashes that provide more secure authentication.
In scenario 2, 'old_passwords=1' prevents accounts with short hashes from becoming inaccessible, but password-changing operations cause accounts with long hashes to revert to short hashes unless you take care to change the session value of 'old_passwords' to 0 first.
In scenario 3, accounts with short hashes become inaccessible to pre-4.1 clients if you change their passwords without explicitly using 'OLD_PASSWORD()'.
The best way to avoid compatibility problems related to short password hashes is to not use them:
Upgrade all client programs to MySQL 4.1 or later.
Run the server with 'old_passwords=0'.
Reset the password for any account with a short password hash to use a long password hash.
For additional security, run the server with 'secure_auth=1'.
File: manual.info.tmp, Node: security-against-attack, Next: security-options, Prev: password-security, Up: general-security-issues
When you connect to a MySQL server, you should use a password. The password is not transmitted as cleartext over the connection. Password handling during the client connection sequence was upgraded in MySQL 4.1.1 to be very secure. If you are still using pre-4.1.1-style passwords, the encryption algorithm is not as strong as the newer algorithm. With some effort, a clever attacker who can sniff the traffic between the client and the server can crack the password. (See *note password-hashing::, for a discussion of the different password handling methods.)
All other information is transferred as text, and can be read by anyone who is able to watch the connection. If the connection between the client and the server goes through an untrusted network, and you are concerned about this, you can use the compressed protocol to make traffic much more difficult to decipher. You can also use MySQL's internal SSL support to make the connection even more secure. See *note encrypted-connections::. Alternatively, use SSH to get an encrypted TCP/IP connection between a MySQL server and a MySQL client. You can find an Open Source SSH client at http://www.openssh.org/, and a comparison of both Open Source and Commercial SSH clients at http://en.wikipedia.org/wiki/Comparison_of_SSH_clients.
To make a MySQL system secure, you should strongly consider the following suggestions:
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 *note 'mysql': 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 *note assigning-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 *note 'mysqld': 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, *note 'mysqld': mysqld. refuses to run as 'root' unless that is specified explicitly using the '--user=root' option.
note 'mysqld': 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 note 'mysqld': 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=mysqlThis causes the server to start as the designated user whether you start it manually or by using note 'mysqld_safe': mysqld-safe. or note 'mysql.server': mysql-server. For more details, see *note changing-mysql-user::.
Running *note 'mysqld': 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 note 'mysqld': 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 note 'SELECT ... INTO OUTFILE': select-into. 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 note 'LOAD DATA': load-data. to load '/etc/passwd' into a table, which then can be displayed with note 'SELECT': select.
To limit the location in which files can be read and written, set the 'secure_file_priv' system to a specific directory. See *note server-system-variables::.
Do not grant the 'PROCESS' or 'SUPER' privilege to nonadministrative users. The output of note 'mysqladmin processlist': mysqladmin. and note 'SHOW PROCESSLIST': 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')'.
*note 'mysqld': 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 note 'mysqld': mysqld. as 'root', because anyone that has write access to the server's data directory then could delete any file in the system! See note symbolic-links-to-tables::.
Stored programs and views should be written using the security guidelines discussed in *note stored-objects-security::.
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 note 'mysqld': mysqld. The note 'CREATE USER': create-user. and note 'ALTER USER': alter-user. statements also support resource control options for limiting the extent of server use permitted to an account. See note create-user::, and *note alter-user::.
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 note 'SELECT ... INTO DUMPFILE': select. This can be prevented by making 'plugin_dir' read only to the server or by setting 'secure_file_priv' to a directory where note 'SELECT': select. writes can be made safely.
File: manual.info.tmp, Node: security-options, Next: changing-mysql-user, Prev: security-against-attack, Up: general-security-issues
On Windows, you can run the server as a Windows service using a normal user account.
On Linux, for installations performed using a MySQL repository, RPM packages, or Debian packages, the MySQL server *note 'mysqld': mysqld. should be started by the local 'mysql' operating system user. Starting by another operating system user is not supported by the init scripts that are included as part of the installation.
On Unix (or Linux for installations performed using 'tar' or 'tar.gz' packages) , the MySQL server note 'mysqld': mysqld. can be started and run by any user. However, you should avoid running the server as the Unix 'root' user for security reasons. To change note 'mysqld': mysqld. to run as a normal unprivileged Unix user USER_NAME, you must do the following:
Stop the server if it is running (use *note 'mysqladmin shutdown': mysqladmin.).
Change the database directories and files so that USER_NAME has privileges to read and write files in them (you might need to do this as the Unix 'root' user):
$> chown -R USER_NAME /PATH/TO/MYSQL/DATADIRIf you do not do this, the server is unable to access databases or tables when it runs as USER_NAME.
If directories or files within the MySQL data directory are symbolic links, 'chown -R' might not follow symbolic links for you. If it does not, you must also follow those links and change the directories and files they point to.
Start the server as user USER_NAME. Another alternative is to start note 'mysqld': mysqld. as the Unix 'root' user and use the '--user=USER_NAME' option. note 'mysqld': mysqld. starts, then switches to run as the Unix user USER_NAME before accepting any connections.
To start the server as the given user automatically at system startup time, specify the user name by adding a 'user' option to the '[mysqld]' group of the '/etc/my.cnf' option file or the 'my.cnf' option file in the server's data directory. For example:
[mysqld]
user=USER_NAMEIf your Unix machine itself is not secured, you should assign passwords to the MySQL 'root' account in the grant tables. Otherwise, any user with a login account on that machine can run the note 'mysql': mysql. client with a '--user=root' option and perform any operation. (It is a good idea to assign passwords to MySQL accounts in any case, but especially so when other login accounts exist on the server host.) See note default-privileges::.
File: manual.info.tmp, Node: load-data-local-security, Next: secure-client-programming, Prev: changing-mysql-user, Up: general-security-issues
The *note 'LOAD DATA': load-data. statement loads a data file into a table. The statement can load a file located on the server host, or, if the 'LOCAL' keyword is specified, on the client host.
The 'LOCAL' version of *note 'LOAD DATA': load-data. has two potential security issues:
Because note 'LOAD DATA LOCAL': load-data. is an SQL statement, parsing occurs on the server side, and transfer of the file from the client host to the server host is initiated by the MySQL server, which tells the client the file named in the statement. In theory, a patched server could tell the client program to transfer a file of the server's choosing rather than the file named in the 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 note 'LOAD DATA LOCAL': load-data, 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 *note 'LOAD DATA LOCAL': load-data. 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.
To avoid connecting to untrusted servers, clients can establish a secure connection and verify the server identity by connecting using the '--ssl-mode=VERIFY_IDENTITY' option and the appropriate CA certificate. To implement this level of verification, you must first ensure that the CA certificate for the server is reliably available to the replica, otherwise availability issues will result. For more information, see *note encrypted-connection-options::.
To avoid *note 'LOAD DATA': load-data. issues, clients should avoid using 'LOCAL'.
Adminstrators and applications can configure whether to permit local data loading as follows:
On the server side:
* The 'local_infile' system variable controls server-side
'LOCAL' capability. Depending on the 'local_infile' setting,
the server refuses or permits local data loading by clients
that request local data loading.
* By default, 'local_infile' is enabled. To cause the server to
refuse or permit *note 'LOAD DATA LOCAL': load-data.
statements explicitly (regardless of how client programs and
libraries are configured at build time or runtime), start
*note 'mysqld': mysqld. with 'local_infile' disabled or
enabled. 'local_infile' can also be set at runtime.On the client side:
* The 'ENABLED_LOCAL_INFILE' 'CMake' option controls the
compiled-in default 'LOCAL' capability for the MySQL client
library (see *note source-configuration-options::). Clients
that make no explicit arrangements therefore have 'LOCAL'
capability disabled or enabled according to the
'ENABLED_LOCAL_INFILE' setting specified at MySQL build time.
* By default, the client library in MySQL binary distributions
is compiled with 'ENABLED_LOCAL_INFILE' enabled. If you
compile MySQL from source, configure it with
'ENABLED_LOCAL_INFILE' disabled or enabled based on whether
clients that make no explicit arrangements should have 'LOCAL'
capability disabled or enabled.
* For client programs that use the C API, local data loading
capability is determined by the default compiled into the
MySQL client library. To enable or disable it explicitly,
invoke the 'mysql_options()'
(https://dev.mysql.com/doc/c-api/5.7/en/mysql-options.html) C
API function to disable or enable the 'MYSQL_OPT_LOCAL_INFILE'
option. See mysql_options()
(https://dev.mysql.com/doc/c-api/5.7/en/mysql-options.html).
* For the *note 'mysql': mysql. client, local data loading
capability is determined by the default compiled into the
MySQL client library. To disable or enable it explicitly, use
the '--local-infile=0' or '--local-infile[=1]' option.
* For the *note 'mysqlimport': mysqlimport. client, local data
loading is not used by default. To disable or enable it
explicitly, use the '--local=0' or '--local[=1]' option.
* If you use *note 'LOAD DATA LOCAL': load-data. in Perl scripts
or other programs that read the '[client]' group from option
files, you can add a 'local-infile' option setting to that
group. To prevent problems for programs that do not
understand this option, specify it using the *note 'loose-':
option-modifiers. prefix:
[client]
loose-local-infile=0
or:
[client]
loose-local-infile=1
* In all cases, successful use of a 'LOCAL' load operation by a
client also requires that the server permits local loading.If 'LOCAL' capability is disabled, on either the server or client side, a client that attempts to issue a *note 'LOAD DATA LOCAL': load-data. statement receives the following error message:
ERROR 1148: The used command is not allowed with this MySQL versionMySQL Shell and Local Data Loading
MySQL Shell provides a number of utilities to dump tables, schemas, or server instances and load them into other instances. When you use these utilities to handle the data, MySQL Shell provides additional functions such as input preprocessing, multithreaded parallel loading, file compression and decompression, and handling access to Oracle Cloud Infrastructure Object Storage buckets. To get the best functionality, always use the most recent version available of MySQL Shell's dump and dump loading utilities.
MySQL Shell's data upload utilities use *note 'LOAD DATA LOCAL INFILE': load-data. statements to upload data, so the 'local_infile' system variable must be set to 'ON' on the target server instance. You can do this before uploading the data, and remove it again afterwards. The utilities handle the file transfer requests safely to deal with the security considerations discussed in this topic.
MySQL Shell includes these dump and dump loading utilities:
Table export utility 'util.exportTable()'
Exports a MySQL relational table into a data file, which can be uploaded to a MySQL server instance using MySQL Shell's parallel table import utility, imported to a different application, or used as a logical backup. The utility has preset options and customization options to produce different output formats.
Parallel table import utility 'util.importTable()'
Inports a data file to a MySQL relational table. The data file can be the output from MySQL Shell's table export utility or another format supported by the utility's preset and customization options. The utility can carry out input preprocessing before adding the data to the table. It can accept multiple data files to merge into a single relational table, and automatically decompresses compressed files.
Instance dump utility 'util.dumpInstance()', schema dump utility 'util.dumpSchemas()', and table dump utility 'util.dumpTables()'
Export an instance, schema, or table to a set of dump files, which can then be uploaded to a MySQL instance using MySQL Shell's dump loading utility. The utilities provide Oracle Cloud Infrastructure Object Storage streaming, MySQL HeatWave Service compatibility checks and modifications, and the ability to carry out a dry run to identify issues before proceeding with the dump.
Dump loading utility 'util.loadDump()'
Import dump files created using MySQL Shell's instance, schema, or table dump utility into a MySQL HeatWave Service DB System or a MySQL Server instance. The utility manages the upload process and provides data streaming from remote storage, parallel loading of tables or table chunks, progress state tracking, resume and reset capability, and the option of concurrent loading while the dump is still taking place. MySQL Shell's parallel table import utility can be used in combination with the dump loading utility to modify data before uploading it to the target MySQL instance.
For details of the utilities, see MySQL Shell Utilities (https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities.html).
File: manual.info.tmp, Node: secure-client-programming, Prev: load-data-local-security, Up: general-security-issues
Client applications that access MySQL should use the following guidelines to avoid interpreting external data incorrectly or exposing sensitive information.
*note client-external-data-handling::
*note client-mysql-error-handling::
Handle External Data Properly
Applications that access MySQL should not trust any data entered by users, who can try to trick your code by entering special or escaped character sequences in Web forms, URLs, or whatever application you have built. Be sure that your application remains secure if a user tries to perform SQL injection by entering something like '; DROP DATABASE mysql;' into a form. This is an extreme example, but large security leaks and data loss might occur as a result of hackers using similar techniques, if you do not prepare for them.
A common mistake is to protect only string data values. Remember to check numeric data as well. If an application generates a query such as 'SELECT * FROM table WHERE ID=234' when a user enters the value '234', the user can enter the value '234 OR 1=1' to cause the application to generate the query 'SELECT * FROM table WHERE ID=234 OR 1=1'. As a result, the server retrieves every row in the table. This exposes every row and causes excessive server load. The simplest way to protect from this type of attack is to use single quotation marks around the numeric constants: 'SELECT * FROM table WHERE ID='234''. If the user enters extra information, it all becomes part of the string. In a numeric context, MySQL automatically converts this string to a number and strips any trailing nonnumeric characters from it.
Sometimes people think that if a database contains only publicly available data, it need not be protected. This is incorrect. Even if it is permissible to display any row in the database, you should still protect against denial of service attacks (for example, those that are based on the technique in the preceding paragraph that causes the server to waste resources). Otherwise, your server becomes unresponsive to legitimate users.
Checklist:
Enable strict SQL mode to tell the server to be more restrictive of what data values it accepts. See *note sql-mode::.
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.
Many application programming interfaces provide a means of escaping special characters in data values. Properly used, this prevents application users from entering values that cause the application to generate statements that have a different effect than you intend:
MySQL SQL statements: Use SQL prepared statements and accept data values only by means of placeholders; see *note sql-prepared-statements::.
MySQL C API: Use the 'mysql_real_escape_string_quote()' (https://dev.mysql.com/doc/c-api/5.7/en/mysql-real-escape-string-quote.html) API call. Alternatively, use the C API prepared statement interface and accept data values only by means of placeholders; see C API Prepared Statement Interface (https://dev.mysql.com/doc/c-api/5.7/en/c-api-prepared-statement-interface.html).
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 MySQL and PHP (https://dev.mysql.com/doc/apis-php/en/).
If the older 'ext/mysql' extension must be used, then for escaping use the 'mysql_real_escape_string_quote()' (https://dev.mysql.com/doc/c-api/5.7/en/mysql-real-escape-string-quote.html) function and not 'mysql_escape_string()' (https://dev.mysql.com/doc/c-api/5.7/en/mysql-escape-string.html) or 'addslashes()' because only 'mysql_real_escape_string_quote()' (https://dev.mysql.com/doc/c-api/5.7/en/mysql-real-escape-string-quote.html) is character set-aware; the other functions can be 'bypassed' when using (invalid) multibyte character sets.
Perl DBI: Use placeholders or the 'quote()' method.
Java JDBC: Use a 'PreparedStatement' object and placeholders.
Other programming interfaces might have similar capabilities.
Handle MySQL Error Messages Properly
It is the application's responsibility to intercept errors that occur as a result of executing SQL statements with the MySQL database server and handle them appropriately.
The information returned in a MySQL error is not gratuitous because that information is key in debugging MySQL using applications. It would be nearly impossible, for example, to debug a common 10-way join *note 'SELECT': select. statement without providing information regarding which databases, tables, and other objects are involved with problems. Thus, MySQL errors must sometimes necessarily contain references to the names of those objects.
A simple but insecure approach for an application when it receives such an error from MySQL is to intercept it and display it verbatim to the client. However, revealing error information is a known application vulnerability type (CWE-209 (http://cwe.mitre.org/data/definitions/209.html)) and the application developer must ensure the application does not have this vulnerability.
For example, an application that displays a message such as this exposes both a database name and a table name to clients, which is information a client might attempt to exploit:
ERROR 1146 (42S02): Table 'mydb.mytable' does not existInstead, the proper behavior for an application when it receives such an error from MySQL is to log appropriate information, including the error information, to a secure audit location only accessible to trusted personnel. The application can return something more generic such as 'Internal Error' to the user.
File: manual.info.tmp, Node: access-control, Next: encrypted-connections, Prev: general-security-issues, Up: security