Pre-General Availability Draft: 2017-12-14
Passwords can be written as plain text in SQL statements such as
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 in 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,
UPDATE statements for the
mysql.user table that refer to literal
passwords are logged as is, so you should avoid such statements.
(Direct manipulation 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.
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.
Replication slaves store the password for the replication master
in the master info repository, which by default is a table in
mysql database named
slave_master_info. The use of a file in the
data directory for the master info repository is now deprecated,
but still possible (see
Section 18.2.4, “Replication Relay and Status Logs”). Ensure that the master
info repository can be accessed only by the database
administrator. An alternative to storing the password in the
master info repository is to use the
SLAVE statement to specify credentials for connecting
to the master.
Use a restricted access mode to protect database backups that include log tables or log files containing passwords.