Changing mysql user password for security - recommendations

Question

I am running MySQLserver version 5.0.96 on SLES 11 SP2 and I am looking for a safe way to change the user password. Of course, changing the user password is very easy:

SET PASSWORD FOR 'user'@'192.168.0.%' = PASSWORD('mysecret'); 

But this query gets into the query log, binary log and MySQL history file. To prevent this, I used:

 export MYSQL_HISTFILE=/dev/null 

before starting the client and:

 SET sql_log_bin = 0; SET sql_log_off = 1; 

then. Grep and mysqlbinlog prove that my plan is coming together, but I overlooked that MySQL quietly changed the file mode bit / dev / null to 0600. Oops!

At this point, I asked myself if there is a better way to safely change the user password. And now I ask you:

What is the best way to securely change a user password for a MySQL server?

Answer 1

It seems that according to dev.mysql , they recommend simply protecting the logs, rather than disinfecting them.

To protect against unreasonable access to the log files, they should be located in a directory that restricts access only to the server and the database administrator.

Replication Services stores the password for the replication master in the master.info file. Install this file to access only the database administrator.

Database backups containing tables or log files containing passwords must be protected using restricted access mode.

It seems that this problem has been fixed more elegantly in later versions of MySQL. See: http://dev.mysql.com/doc/refman/5.7/en/password-logging.html

In MySQL 5.7, logging avoids writing passwords in text form for the following statements:

CREATE A USER ... IDENTIFIED ... GRANT ... IDENTIFIED ... PASSWORD ... SLAVE START ... PASSWORD = ... CREATE SERVER ... OPTIONS (... PASSWORD ...) ALTER SERVER .. . OPTIONS (... PASSWORD ...)

The passwords in these statements are rewritten so as not to appear literally in the query text, the general query log, the slow query log, and the binary log file. Rewriting does not apply to other operators.

So, if you cannot upgrade to a later version, your decision seems to be the right one.

Answer 2

I use mysqladmin for Linux systems, but with an extra step in advance, to prevent showing or writing a new password. The read command will allow you to enter a new password, which will be stored in the "newpass" variable, without being displayed on the screen. The mysqladmin command will prompt you to enter the current password without being displayed on the screen, and if the current password is correct, it will set the new MySQL user password to the value of the "newpass" variable.

 echo -n "Enter new password: "; read -s newpass; echo; mysqladmin -u <yourusername> password "$newpass" -p 

You can copy and paste the above into the terminal on your Linux system as is, but do not forget to replace <yourusername> with your MySQL username.