mysql_user: fix overriding user passowrd to the same (#70833)

3 files changed, 23 insertions, 13 deletions

diff --git a/changelogs/fragments/609-mysql_user_fix_overriding_password_to_the_same.yml b/changelogs/fragments/609-mysql_user_fix_overriding_password_to_the_same.yml
new file mode 100644
index 0000000000..9ad0c083e5
--- /dev/null
+++ b/changelogs/fragments/609-mysql_user_fix_overriding_password_to_the_same.yml
@@ -0,0 +1,2 @@
+bugfixes:
+- mysql_user - fix overriding password to the same (https://github.com/ansible-collections/community.general/issues/543).
diff --git a/lib/ansible/modules/database/mysql/mysql_user.py b/lib/ansible/modules/database/mysql/mysql_user.py
index 0dcbc700f9..de5bd7edc7 100644
--- a/lib/ansible/modules/database/mysql/mysql_user.py
+++ b/lib/ansible/modules/database/mysql/mysql_user.py
@@ -298,10 +298,19 @@ def user_add(cursor, user, host, host_all, password, encrypted, new_priv, check_
     if check_mode:
         return True
 
+    # Determine what user management method server uses
+    old_user_mgmt = use_old_user_mgmt(cursor)
+
     if password and encrypted:
         cursor.execute("CREATE USER %s@%s IDENTIFIED BY PASSWORD %s", (user, host, password))
     elif password and not encrypted:
-        cursor.execute("CREATE USER %s@%s IDENTIFIED BY %s", (user, host, password))
+        if old_user_mgmt:
+            cursor.execute("CREATE USER %s@%s IDENTIFIED BY %s", (user, host, password))
+        else:
+            cursor.execute("SELECT CONCAT('*', UCASE(SHA1(UNHEX(SHA1(%s)))))", (password,))
+            encrypted_password = cursor.fetchone()[0]
+            cursor.execute("CREATE USER %s@%s IDENTIFIED WITH mysql_native_password AS %s", (user, host, encrypted_password))
+
     else:
         cursor.execute("CREATE USER %s@%s", (user, host))
     if new_priv is not None:
diff --git a/test/integration/targets/mysql_user/tasks/user_password_update_test.yml b/test/integration/targets/mysql_user/tasks/user_password_update_test.yml
index 1f126c48b5..a85e4edf04 100644
--- a/test/integration/targets/mysql_user/tasks/user_password_update_test.yml
+++ b/test/integration/targets/mysql_user/tasks/user_password_update_test.yml
@@ -46,18 +46,17 @@
   register: user_password_old  
   when: user_password_old_create is failed
 
-# FIXME: not sure why this is failing, but it looks like it should expect changed=true
-#- name: update user2 state=present with same password (expect changed=false)
-#  mysql_user:
-#    name: '{{ user_name_2 }}'
-#    password: '{{ user_password_2 }}'
-#    priv: '*.*:ALL'
-#    state: present
-#    login_unix_socket: '{{ mysql_socket }}'
-#  register: result
-#
-#- name: assert output user2 was not updated 
-#  assert: { that: "result.changed == false" }
+- name: update user2 state=present with same password (expect changed=false)
+  mysql_user:
+    name: '{{ user_name_2 }}'
+    password: '{{ user_password_2 }}'
+    priv: '*.*:ALL'
+    state: present
+    login_unix_socket: '{{ mysql_socket }}'
+  register: result
+
+- name: assert output user2 was not updated 
+  assert: { that: "result.changed == false" }
 
 - include: assert_user.yml user_name={{user_name_2}} priv='ALL PRIVILEGES'