{"id":3297,"date":"2011-02-28T15:50:52","date_gmt":"2011-02-28T13:50:52","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=3297"},"modified":"2011-03-01T19:24:46","modified_gmt":"2011-03-01T17:24:46","slug":"upgrading-passwords-from-old_passwords-to-new-passwords","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/upgrading-passwords-from-old_passwords-to-new-passwords","title":{"rendered":"Upgrading passwords from old_passwords to “new passwords”"},"content":{"rendered":"

You have old_passwords=1<\/strong> in your my.cnf<\/strong>. I’m guessing this is because you used one of the my-small.cnf<\/strong>, my-large.cnf<\/strong> etc. templates provided with your MySQL distribution.<\/p>\n

These files can easily win the “most outdated sample configuration file contest”.<\/p>\n

Usually it’s no big deal: if some parameter isn’t right, you just go and change it. Some variables, though, have a long-lasting effect, and are not easily reversed.<\/p>\n

What’s the deal with old_passwords?<\/h4>\n

No one should be using these anymore. This variable makes the password hashing algorithm compatible with that of MySQL 4.0<\/strong>. I’m pretty sure 4.0<\/strong> was released 9<\/strong> years ago. I don’t know of anyone still using it (or 4.0<\/strong> client libraries).<\/p>\n

The deal is this: with old_passwords you get a 16<\/strong> hexadecimal digits (64<\/strong> bit) hashing of your passwords. With so called “new passwords”<\/em> you get 40<\/strong> hexadecimal digits (plus extra “*<\/strong>“). So this is about better encryption of your password. Read more on the manual<\/a>.<\/p>\n

How do I upgrade to new password format?<\/h4>\n

You can’t just put a comment on the “old_passwords=1<\/strong>” entry in the configuration file. If you do so, the next client to connect will attempt to match a 41<\/strong> characters hashed password to your existing 16<\/strong> characters entry in the mysql.users<\/strong> table. So you need to make a simultaneous change: both remove the old_passwords<\/strong> entry and set a new password. You must know all accounts’ passwords before you begin.<\/p>\n

Interestingly, old_passwords<\/strong> is both a global and a session variable. To work out an example, let’s assume the account ‘webuser’@’localhost’<\/strong> enters with ‘123456’. Take a look at the following:<\/p>\n

\n
root@mysql-5.1.51> SET SESSION old_passwords=0;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nroot@mysql-5.1.51> SELECT PASSWORD('123456');\r\n+-------------------------------------------+\r\n| PASSWORD('123456')\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+-------------------------------------------+\r\n| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |\r\n+-------------------------------------------+\r\n1 row in set (0.00 sec)\r\n\r\nroot@mysql-5.1.51> SET SESSION old_passwords=1;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nroot@mysql-5.1.51> SELECT PASSWORD('123456');\r\n+--------------------+\r\n| PASSWORD('123456') |\r\n+--------------------+\r\n| 565491d704013245\u00a0\u00a0 |\r\n+--------------------+\r\n1 row in set (0.00 sec<\/pre>\n<\/blockquote>\n
So, the PASSWORD()<\/strong> function consults the old_passwords<\/strong> session variable.<\/p>\n
To upgrade ‘webuser’@’localhost’<\/strong>‘s password we do:<\/p>\n
\n
root@mysql-5.1.51> SET SESSION old_passwords=0;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nroot@mysql-5.1.51> SET PASSWORD FOR 'webuser'@'localhost' = PASSWORD('123456')<\/pre>\n<\/blockquote>\n
Go ahead and see the password<\/strong> entry on the mysql.users<\/strong> table.<\/p>\n
What we’ve just done is to set a 41<\/strong> characters password hash for that account. Now, the next time the client wishes to connect, it must know in advance it is to expect a new password, otherwise it will encode a 16<\/strong> characters hash, and try to match it with our new 41<\/strong> characters hash. It is now time to perform:<\/p>\n
\n
root@mysql-5.1.51> SET GLOBAL old_passwords=0;\r\nQuery OK, 0 rows affected (0.00 sec<\/pre>\n<\/blockquote>\n
This will apply to all new connections made from that moment on (not affecting any existing connections). So, make sure you have updated passwords for all accounts.<\/p>\n
To wrap it up, don’t forget to set old_passwords=0<\/strong> in the my.cnf<\/strong> file, or, better yet, completely remove the entry.<\/p>\n","protected":false},"excerpt":{"rendered":"
You have old_passwords=1 in your my.cnf. I’m guessing this is because you used one of the my-small.cnf, my-large.cnf etc. templates provided with your MySQL distribution. These files can easily win the “most outdated sample configuration file contest”. Usually it’s no big deal: if some parameter isn’t right, you just go and change it. Some variables, […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[11,16],"class_list":["post-3297","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-configuration","tag-security"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-Rb","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3297","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=3297"}],"version-history":[{"count":9,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3297\/revisions"}],"predecessor-version":[{"id":3336,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3297\/revisions\/3336"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=3297"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=3297"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=3297"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}