MySQLmysql-5.5password
Is it possible to set up a user in MySQL 5.5 with a new password whilst keeping existing users with the old type of passwords? By old and new passwords I mean the style of hash.
I was thinking I could try specify the hash directly but I'm struggling to get that working (I'm new MySQL).
Unfortunately I can't update all users to the new type of passwords as it would break access to a system I can't control. I also need the default new account to use the old style hash.
According to MySQL Documentation on SET PASSWORD
The SET PASSWORD statement assigns a password to an existing MySQL user account. If the password is specified using the PASSWORD() or OLD_PASSWORD() function, the literal text of the password should be given. If the password is specified without using either function, the password should be the already-encrypted password value as returned by PASSWORD().
With no FOR clause, this statement sets the password for the current user. Any client that has connected to the server using a nonanonymous account can change the password for that account
However, you just been struck by lightning because the same webpage says
Important
SET PASSWORD may be recorded in server logs or in a history file such as ~/.mysql_history, which means that plaintext passwords may be read by anyone having read access to that information. See Section 6.1.2, “Password Security in MySQL”.
Note
If you are connecting to a MySQL 4.1 or later server using a pre-4.1 client program, do not use the preceding SET PASSWORD or UPDATE statement without reading Section 6.1.2.3, “Password Hashing in MySQL”, first. The password format changed in MySQL 4.1, and under certain circumstances it is possible that if you change your password, you might not be able to connect to the server afterward.
Your password change is forever, so to speak.
Suppose your user is myuser@localhost, you can have someone with ALL PRIVILEGES run this
UPDATE mysql.user SET password=OLD_PASSWORD('[your password]')
WHERE user='myuser' AND host='localhost';
FLUSH PRIVILEGES;
If you have no DBA but can restart mysql, then do this
service mysql restart --skip-networking --skip-grant-tables
mysql -e"UPDATE mysql.user SET password=OLD_PASSWORD('[your password]') WHERE user='myuser' AND host='localhost'"
service mysql restart
I think using a MySQL gui tool like SQLyog would solve your problem. It has User manager tool where you can set privilege to different users.
Best Answer
It is possible before version 5.7, where it has been disabled.
To set up an old password for a user, use command:
More information: Password Hashing in MySQL.
You can enable/disable old passwords globally or per session. On a server where they are enabled globally (i.e. the server was started with
old_passwords = 1), run this:to disable them for a session. The PASSWORD in the above snippet will return a password in the new format, affecting only the current session.