神刀安全网

Change user password in MySQL 5.7 with “plugin: auth_socket”

Change user password in MySQL 5.7 with “plugin: auth_socket” In this blog, we’ll discuss how to use “plugin: auth_socket” to change user password in MySQL 5.7. In

In Debian/Ubuntu it is pretty common to install MySQL/Percona Server with an empty password for the root user. After everything is configured and tested, then a password is set. This is not a good practice in production servers (or soon-to-be production servers), but you can do it for your own test servers. With regards to authentication, things have changed a bit in 5.7, and methods that worked before now need a different procedure.

Let’s say that you install 5.7 and don’t specify a password. You will see the following:

Shell

SELECT User, Host, HEX(authentication_string) FROM mysql.user; +-----------+-----------+------------------------------------------------------------------------------------+ | User      | Host      | HEX(authentication_string)                                                         | +-----------+-----------+------------------------------------------------------------------------------------+ | root      | localhost |                                                                                    |
SELECTUser, Host, HEX(authentication_string) FROMmysql.user; +-----------+-----------+------------------------------------------------------------------------------------+ | User      | Host      | HEX(authentication_string)                                                        | +-----------+-----------+------------------------------------------------------------------------------------+ | root      | localhost |                                                                                    | 

OK, the password is empty. Let’s change it:

Shell

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'test'; mysql> SELECT User, Host, HEX(authentication_string) FROM mysql.user; +-----------+-----------+------------------------------------------------------------------------------------+ | User      | Host      | HEX(authentication_string)                                                         | +-----------+-----------+------------------------------------------------------------------------------------+ | root      | localhost |                                                                                    |
mysql> ALTERUSER 'root'@'localhost' IDENTIFIEDBY 'test';   mysql> SELECTUser, Host, HEX(authentication_string) FROMmysql.user; +-----------+-----------+------------------------------------------------------------------------------------+ | User      | Host      | HEX(authentication_string)                                                        | +-----------+-----------+------------------------------------------------------------------------------------+ | root      | localhost |                                                                                    | 

That doesn’t work, it’s still empty:

Shell

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('test'); mysql> SELECT User, Host, HEX(authentication_string) FROM mysql.user; +-----------+-----------+------------------------------------------------------------------------------------+ | User      | Host      | HEX(authentication_string)                                                         | +-----------+-----------+------------------------------------------------------------------------------------+ | root      | localhost |                                                                                    |
mysql> SETPASSWORDFOR 'root'@'localhost' = PASSWORD('test');   mysql> SELECTUser, Host, HEX(authentication_string) FROMmysql.user; +-----------+-----------+------------------------------------------------------------------------------------+ | User      | Host      | HEX(authentication_string)                                                        | +-----------+-----------+------------------------------------------------------------------------------------+ | root      | localhost |                                                                                    | 

But why? Let’s check the warnings:

Shell

mysql> show warnings; +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level   | Code | Message                                                                                                                                                                                 | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1287 | 'SET PASSWORD FOR  = PASSWORD('')' is deprecated and will be removed in a future release. Please use SET PASSWORD FOR  = '' instead | | Note    | 1699 | SET PASSWORD has no significance for user 'root'@'localhost' as authentication plugin does not support it.                                                                              | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
mysql> showwarnings; +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level  | Code | Message                                                                                                                                                                                | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1287 | 'SET PASSWORD FOR  = PASSWORD('')' is deprecatedand willberemovedin a futurerelease. Pleaseuse SETPASSWORDFOR  = '' instead | | Note    | 1699 | SETPASSWORDhasnosignificancefor user 'root'@'localhost' as authenticationplugindoesnot supportit.                                                                              | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 

The problem is in the note coded 1699. This user is using an authentication plugin that doesn’t support a password. Which one could it be?

Shell

mysql> SELECT * from user where User="root"G *************************** 1. row ***************************                   Host: localhost                   User: root [...]                 plugin: auth_socket [...]
mysql> SELECT * fromuserwhereUser="root"G *************************** 1. row ***************************                   Host: localhost                   User: root [...]                 plugin: auth_socket [...] 

Ok, auth_socket . If you install 5.7 and don’t provide a password to the root user, it will use the auth_socket plugin. That plugin doesn’t care and doesn’t need a password. It just checks if the user is connecting using a UNIX socket and then compares the username.

If we want to configure a password, we need to change the plugin and set the password at the same time, in the same command. First changing the plugin and then setting the password won’t work, and it will fall back to auth_socket again. So, run:

So, the correct way to do this is to run the following:

Shell

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';
ALTERUSER 'root'@'localhost' IDENTIFIEDWITHmysql_native_passwordBY 'test'; 

And now, it works

Shell

mysql> SELECT User, Host, HEX(authentication_string) FROM mysql.user; +-----------+-----------+------------------------------------------------------------------------------------+ | User      | Host      | HEX(authentication_string)                                                         | +-----------+-----------+------------------------------------------------------------------------------------+ | root      | localhost | 2A39344244434542453139303833434532413146393539464430324639363443374146344346433239 |
mysql> SELECTUser, Host, HEX(authentication_string) FROMmysql.user; +-----------+-----------+------------------------------------------------------------------------------------+ | User      | Host      | HEX(authentication_string)                                                        | +-----------+-----------+------------------------------------------------------------------------------------+ | root      | localhost | 2A39344244434542453139303833434532413146393539464430324639363443374146344346433239 | 

If your deployments use empty passwords, and you change them later on, remember to update your scripts/recipes!

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » Change user password in MySQL 5.7 with “plugin: auth_socket”

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
分享按钮