mysql_user: Grant all privileges result in missing dynamic privileges in every second run in MySQL 8

[bigo8525]

SUMMARY

In MySQL 8 are dynamic privileges new. The GRANT ALL includes per default these dynamic privileges. The dynamic privileges are stored in mysql.global_grants. At restart the dynamic privileges are assigned to the user by reading the mysql.global_grants table.
dynamic privileges docs

As in #77 mentioned a priv: "*.*:ALL,GRANT" results in a change for the user. The ALL is executed with the following SQL statements

SHOW GRANTS FOR 'testing_admin'@'localhost'
REVOKE GRANT OPTION ON *.* FROM 'testing_admin'@'localhost'
REVOKE SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,RELOAD,SHUTDOWN,PROCESS,FILE,REFERENCES,INDEX,ALTER,SHOW DATABASES,SUPER,CREATE TEMPORARY TABLES,LOCK TABLES,EXECUTE,REPLICATION SLAVE,REPLICATION CLIENT,CREATE VIEW,SHOW VIEW,CREATE ROUTINE,ALTER ROUTINE,CREATE USER,EVENT,TRIGGER,CREATE TABLESPACE,CREATE ROLE,DROP ROLE,APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* FROM 'testing_admin'@'localhost'
GRANT ALL ON *.* TO 'testing_admin'@'localhost' WITH GRANT OPTION

The main problem is that the dynamic privileges need a FLUSH PRIVILEGES. Now the dynmaic privileges are only on every second run available. This happens because the REVOKE cleans the dynmaic privileges in mysql.global_grants the direct following GRANT ALL does not affect the table because of a missing FLUSH PRIVILEGES. The SHOW GRANTS FOR 'testing_admin'@'localhost' is correct to the next restart. After the restart mysql assign the dynamic privileges from mysql.global_grants and this does not work because the table is empty for the specific user.

After many test I assume the main reason should be the GRANT without it all workes even without the FLUSH PRIVILEGES
This is a issue because the dynamic privileges are needed for a user who should be able to maintain a InnoDB Cluster. By playing around with the cluster I mentioned this behavior because on every second deployment my user was not able to run the mysqlsh cluster commands because of missing privileges.

I hope I described the problem good enough for understanding it. A FLUSH PRIVILEGES at the end in the privilege_revoke method could fix this issue. But I´m not sure if this could have a negative effect on other parts of the module.

ISSUE TYPE

• Bug Report

COMPONENT NAME

mysql_user

ANSIBLE VERSION

ansible 2.9.18
  config file = /home/bigo/Dokumente/git/config/Ansible/ansible.cfg
  configured module search path = ['/home/bigo/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python3.9/site-packages/ansible
  executable location = /usr/bin/ansible
  python version = 3.9.2 (default, Feb 20 2021, 00:00:00) [GCC 10.2.1 20201125 (Red Hat 10.2.1-9)]

CONFIGURATION

OS / ENVIRONMENT

mysql_user.py from main branch copied into the collaction path.
Original found this on RHEL8 with MySQL 8.0.23 Commercial, Testing was on Fedora 33 with MySQL 8.0.23 community

STEPS TO REPRODUCE

The behavor can be proofed by only execute the following SQL comands:

CREATE USER 'testing_admin'@'localhost' IDENTIFIED BY 'testing123!'
GRANT ALL on *.* TO 'testing_admin'@'localhost' WITH GRANT OPTION; 
SELECT * FROM mysql.global_grants;
REVOKE GRANT OPTION ON *.* FROM 'testing_admin'@'localhost';
REVOKE SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,RELOAD,SHUTDOWN,PROCESS,FILE,REFERENCES,INDEX,ALTER,SHOW DATABASES,SUPER,CREATE TEMPORARY TABLES,LOCK TABLES,EXECUTE,REPLICATION SLAVE,REPLICATION CLIENT,CREATE VIEW,SHOW VIEW,CREATE ROUTINE,ALTER ROUTINE,CREATE USER,EVENT,TRIGGER,CREATE TABLESPACE,CREATE ROLE,DROP ROLE,APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* FROM 'testing_admin'@'localhost';
GRANT ALL ON *.* TO 'testing_admin'@'localhost' WITH GRANT OPTION;
SELECT * FROM mysql.global_grants;

After the first SELECT * FROM mysql.global_grants; the result should be

+---------------+-----------+----------------------------+-------------------+
| USER          | HOST      | PRIV                       | WITH_GRANT_OPTION |
+---------------+-----------+----------------------------+-------------------+
| testing_admin | localhost | APPLICATION_PASSWORD_ADMIN | Y                 |
| testing_admin | localhost | AUDIT_ADMIN                | Y                 |
| testing_admin | localhost | BACKUP_ADMIN               | Y                 |
| testing_admin | localhost | BINLOG_ADMIN               | Y                 |
| testing_admin | localhost | BINLOG_ENCRYPTION_ADMIN    | Y                 |
| testing_admin | localhost | CLONE_ADMIN                | Y                 |
| testing_admin | localhost | CONNECTION_ADMIN           | Y                 |
| testing_admin | localhost | ENCRYPTION_KEY_ADMIN       | Y                 |
| testing_admin | localhost | FLUSH_OPTIMIZER_COSTS      | Y                 |
| testing_admin | localhost | FLUSH_STATUS               | Y                 |
| testing_admin | localhost | FLUSH_TABLES               | Y                 |
| testing_admin | localhost | FLUSH_USER_RESOURCES       | Y                 |
| testing_admin | localhost | GROUP_REPLICATION_ADMIN    | Y                 |
| testing_admin | localhost | INNODB_REDO_LOG_ARCHIVE    | Y                 |
| testing_admin | localhost | INNODB_REDO_LOG_ENABLE     | Y                 |
| testing_admin | localhost | PERSIST_RO_VARIABLES_ADMIN | Y                 |
| testing_admin | localhost | REPLICATION_APPLIER        | Y                 |
| testing_admin | localhost | REPLICATION_SLAVE_ADMIN    | Y                 |
| testing_admin | localhost | RESOURCE_GROUP_ADMIN       | Y                 |
| testing_admin | localhost | RESOURCE_GROUP_USER        | Y                 |
| testing_admin | localhost | ROLE_ADMIN                 | Y                 |
| testing_admin | localhost | SERVICE_CONNECTION_ADMIN   | Y                 |
| testing_admin | localhost | SESSION_VARIABLES_ADMIN    | Y                 |
| testing_admin | localhost | SET_USER_ID                | Y                 |
| testing_admin | localhost | SHOW_ROUTINE               | Y                 |
| testing_admin | localhost | SYSTEM_USER                | Y                 |
| testing_admin | localhost | SYSTEM_VARIABLES_ADMIN     | Y                 |
| testing_admin | localhost | TABLE_ENCRYPTION_ADMIN     | Y                 |
| testing_admin | localhost | XA_RECOVER_ADMIN           | Y                 |
+---------------+-----------+----------------------------+-------------------+

After both REVOKE and the GRANT ALL the output of SELECT * FROM mysql.global_grants; sould be exactly as above but its empty. After a restart of the MySQL the user is missing all this dynamic privileges to the next GRANT ALL.

---
- name: testing privilege problem
  hosts: localhost
  tasks:
  - name: create user with privs
    community.mysql.mysql_user:
      login_user: root
      name: testing_admin
      password: testing123!
      host: localhost
      priv: "*.*:ALL,GRANT"
      state: present
    become: yes
  
  - name: show user grants
    shell: mysql -e 'SHOW GRANTS FOR testing_admin@LOCALHOST'
    become: yes
    register: user_grants
  
  - name: show dynamic privileges for user
    shell: mysql -e 'SELECT * FROM mysql.global_grants WHERE USER="testing_admin"'
    become: yes
    register: dynamic_privs
  
  - name: print user_grants
    debug:
      msg: "{{ user_grants['stdout'] }}"

  - name: print dynamic_privs
    debug:
      msg: "{{ dynamic_privs['stdout'] }}"

  - name: mysqld_restarted
    service:
      name: mysqld
      state: restarted
      enabled: yes
    become: yes

  - name: show user grants
    shell: mysql -e 'SHOW GRANTS FOR testing_admin@LOCALHOST'
    become: yes
    register: user_grants
  
  - name: show dynamic privileges for user
    shell: mysql -e 'SELECT * FROM mysql.global_grants WHERE USER="testing_admin"'
    become: yes
    register: dynamic_privs

  - name: print user_grants
    debug:
      msg: "{{ user_grants['stdout'] }}"

  - name: print dynamic_privs
    debug:
      msg: "{{ dynamic_privs['stdout'] }}"

EXPECTED RESULTS

normale i would expect to have every time the ALL,GRANT for the user.

ACTUAL RESULTS

As explaind earlier every second run of the playbook the user testing_admin as no dynamic privileges.

ansible-playbook 2.9.18
  config file = /home/bigo/Dokumente/git/config/Ansible/ansible.cfg
  configured module search path = ['/home/bigo/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python3.9/site-packages/ansible
  executable location = /usr/bin/ansible-playbook
  python version = 3.9.2 (default, Feb 20 2021, 00:00:00) [GCC 10.2.1 20201125 (Red Hat 10.2.1-9)]
Using /home/bigo/Dokumente/git/config/Ansible/ansible.cfg as config file
setting up inventory plugins
host_list declined parsing /home/bigo/Dokumente/git/config/Ansible/inventory/inventory as it did not pass its verify_file() method
script declined parsing /home/bigo/Dokumente/git/config/Ansible/inventory/inventory as it did not pass its verify_file() method
auto declined parsing /home/bigo/Dokumente/git/config/Ansible/inventory/inventory as it did not pass its verify_file() method
Parsed /home/bigo/Dokumente/git/config/Ansible/inventory/inventory inventory source with ini plugin
Loading callback plugin debug of type stdout, v2.0 from /usr/lib/python3.9/site-packages/ansible/plugins/callback/debug.py
Skipping callback 'actionable', as we already have a stdout callback.
Skipping callback 'counter_enabled', as we already have a stdout callback.
Skipping callback 'debug', as we already have a stdout callback.
Skipping callback 'dense', as we already have a stdout callback.
Skipping callback 'dense', as we already have a stdout callback.
Skipping callback 'full_skip', as we already have a stdout callback.
Skipping callback 'json', as we already have a stdout callback.
Skipping callback 'minimal', as we already have a stdout callback.
Skipping callback 'null', as we already have a stdout callback.
Skipping callback 'oneline', as we already have a stdout callback.
Loading callback plugin profile_tasks of type aggregate, v2.0 from /usr/lib/python3.9/site-packages/ansible/plugins/callback/profile_tasks.py
Skipping callback 'selective', as we already have a stdout callback.
Skipping callback 'skippy', as we already have a stdout callback.
Skipping callback 'stderr', as we already have a stdout callback.
Skipping callback 'unixy', as we already have a stdout callback.
Skipping callback 'yaml', as we already have a stdout callback.

PLAYBOOK: mysql_test.yml **********************************************************************************************************************************************************************************************************************
Positional arguments: playbooks/mysql_test.yml
verbosity: 4
private_key_file: /home/bigo/.ssh/id_ed25519
remote_user: bigo
connection: smart
timeout: 15
become_method: sudo
tags: ('all',)
inventory: ('/home/bigo/Dokumente/git/config/Ansible/inventory/inventory',)
forks: 5
1 plays in playbooks/mysql_test.yml

PLAY [testing privilege problem] **************************************************************************************************************************************************************************************************************

TASK [Gathering Facts] ************************************************************************************************************************************************************************************************************************
task path: /home/bigo/Dokumente/git/config/Ansible/playbooks/mysql_test.yml:2
Monday 15 March 2021  15:27:08 +0100 (0:00:00.023)       0:00:00.023 ********** 
Using module file /usr/lib/python3.9/site-packages/ansible/modules/system/setup.py
Pipelining is enabled.
<127.0.0.1> ESTABLISH LOCAL CONNECTION FOR USER: bigo
<127.0.0.1> EXEC /bin/sh -c '/usr/bin/python3 && sleep 0'
ok: [localhost]
META: ran handlers

TASK [create user with privs] *****************************************************************************************************************************************************************************************************************
task path: /home/bigo/Dokumente/git/config/Ansible/playbooks/mysql_test.yml:5
Monday 15 March 2021  15:27:09 +0100 (0:00:01.285)       0:00:01.308 ********** 
Using module file /home/bigo/.ansible/collections/ansible_collections/community/mysql/plugins/modules/mysql_user.py
Pipelining is enabled.
<127.0.0.1> ESTABLISH LOCAL CONNECTION FOR USER: bigo
<127.0.0.1> EXEC /bin/sh -c 'sudo -H -S -n  -u root /bin/sh -c '"'"'echo BECOME-SUCCESS-yqbkaxehgpwvrtaktoitttwgrnyfijkr ; /usr/bin/python3'"'"' && sleep 0'
changed: [localhost] => {
    "changed": true,
    "invocation": {
        "module_args": {
            "append_privs": false,
            "ca_cert": null,
            "check_hostname": null,
            "check_implicit_admin": false,
            "client_cert": null,
            "client_key": null,
            "config_file": "/root/.my.cnf",
            "connect_timeout": 30,
            "encrypted": false,
            "host": "localhost",
            "host_all": false,
            "login_host": "localhost",
            "login_password": null,
            "login_port": 3306,
            "login_unix_socket": null,
            "login_user": "root",
            "name": "testing_admin",
            "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "plugin": null,
            "plugin_auth_string": null,
            "plugin_hash_string": null,
            "priv": "*.*:ALL,GRANT",
            "resource_limits": null,
            "sql_log_bin": true,
            "state": "present",
            "tls_requires": null,
            "update_password": "always",
            "user": "testing_admin"
        }
    },
    "user": "testing_admin"
}

MSG:

Privileges updated

TASK [show user grants] ***********************************************************************************************************************************************************************************************************************
task path: /home/bigo/Dokumente/git/config/Ansible/playbooks/mysql_test.yml:15
Monday 15 March 2021  15:27:09 +0100 (0:00:00.440)       0:00:01.749 ********** 
Using module file /usr/lib/python3.9/site-packages/ansible/modules/commands/command.py
Pipelining is enabled.
<127.0.0.1> ESTABLISH LOCAL CONNECTION FOR USER: bigo
<127.0.0.1> EXEC /bin/sh -c 'sudo -H -S -n  -u root /bin/sh -c '"'"'echo BECOME-SUCCESS-enuzukrrjrhnwcpvwzjkggxcjssetpwf ; /usr/bin/python3'"'"' && sleep 0'
changed: [localhost] => {
    "changed": true,
    "cmd": "mysql -e 'SHOW GRANTS FOR testing_admin@LOCALHOST'",
    "delta": "0:00:00.009372",
    "end": "2021-03-15 15:27:10.309675",
    "invocation": {
        "module_args": {
            "_raw_params": "mysql -e 'SHOW GRANTS FOR testing_admin@LOCALHOST'",
            "_uses_shell": true,
            "argv": null,
            "chdir": null,
            "creates": null,
            "executable": null,
            "removes": null,
            "stdin": null,
            "stdin_add_newline": true,
            "strip_empty_ends": true,
            "warn": true
        }
    },
    "rc": 0,
    "start": "2021-03-15 15:27:10.300303"
}

STDOUT:

Grants for testing_admin@localhost
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `testing_admin`@`localhost` WITH GRANT OPTION
GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `testing_admin`@`localhost` WITH GRANT OPTION

TASK [show dynamic privileges for user] *******************************************************************************************************************************************************************************************************
task path: /home/bigo/Dokumente/git/config/Ansible/playbooks/mysql_test.yml:20
Monday 15 March 2021  15:27:10 +0100 (0:00:00.365)       0:00:02.114 ********** 
Using module file /usr/lib/python3.9/site-packages/ansible/modules/commands/command.py
Pipelining is enabled.
<127.0.0.1> ESTABLISH LOCAL CONNECTION FOR USER: bigo
<127.0.0.1> EXEC /bin/sh -c 'sudo -H -S -n  -u root /bin/sh -c '"'"'echo BECOME-SUCCESS-pmfyhcrzkxdtczkscufinbmleunrqwzl ; /usr/bin/python3'"'"' && sleep 0'
changed: [localhost] => {
    "changed": true,
    "cmd": "mysql -e 'SELECT * FROM mysql.global_grants WHERE USER=\"testing_admin\"'",
    "delta": "0:00:00.009211",
    "end": "2021-03-15 15:27:10.548459",
    "invocation": {
        "module_args": {
            "_raw_params": "mysql -e 'SELECT * FROM mysql.global_grants WHERE USER=\"testing_admin\"'",
            "_uses_shell": true,
            "argv": null,
            "chdir": null,
            "creates": null,
            "executable": null,
            "removes": null,
            "stdin": null,
            "stdin_add_newline": true,
            "strip_empty_ends": true,
            "warn": true
        }
    },
    "rc": 0,
    "start": "2021-03-15 15:27:10.539248"
}

TASK [print user_grants] **********************************************************************************************************************************************************************************************************************
task path: /home/bigo/Dokumente/git/config/Ansible/playbooks/mysql_test.yml:25
Monday 15 March 2021  15:27:10 +0100 (0:00:00.243)       0:00:02.357 ********** 
ok: [localhost] => {}

MSG:

Grants for testing_admin@localhost
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `testing_admin`@`localhost` WITH GRANT OPTION
GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `testing_admin`@`localhost` WITH GRANT OPTION

TASK [print dynamic_privs] ********************************************************************************************************************************************************************************************************************
task path: /home/bigo/Dokumente/git/config/Ansible/playbooks/mysql_test.yml:29
Monday 15 March 2021  15:27:10 +0100 (0:00:00.061)       0:00:02.419 ********** 
ok: [localhost] => {}

TASK [mysqld_restarted] ***********************************************************************************************************************************************************************************************************************
task path: /home/bigo/Dokumente/git/config/Ansible/playbooks/mysql_test.yml:33
Monday 15 March 2021  15:27:10 +0100 (0:00:00.050)       0:00:02.470 ********** 
Running systemd
Using module file /usr/lib/python3.9/site-packages/ansible/modules/system/systemd.py
Pipelining is enabled.
<127.0.0.1> ESTABLISH LOCAL CONNECTION FOR USER: bigo
<127.0.0.1> EXEC /bin/sh -c 'sudo -H -S -n  -u root /bin/sh -c '"'"'echo BECOME-SUCCESS-thtyyshkfrkefsiivwnhpodeeykhokkt ; /usr/bin/python3'"'"' && sleep 0'
changed: [localhost] => {
    "changed": true,
    "enabled": true,
    "invocation": {
        "module_args": {
            "daemon_reexec": false,
            "daemon_reload": false,
            "enabled": true,
            "force": null,
            "masked": null,
            "name": "mysqld",
            "no_block": false,
            "scope": null,
            "state": "restarted",
            "user": null
        }
    },
    "name": "mysqld",
    "state": "started",
    "status": {
        "ActiveEnterTimestamp": "Mon 2021-03-15 15:27:05 CET",
        "ActiveEnterTimestampMonotonic": "62423320324",
        "ActiveExitTimestamp": "Mon 2021-03-15 15:27:03 CET",
        "ActiveExitTimestampMonotonic": "62421567141",
        "ActiveState": "active",
        "After": "systemd-journald.socket syslog.target basic.target system.slice -.mount network.target tmp.mount sysinit.target systemd-tmpfiles-setup.service",
        "AllowIsolate": "no",
        "AllowedCPUs": "",
        "AllowedMemoryNodes": "",
        "AmbientCapabilities": "",
        "AssertResult": "yes",
        "AssertTimestamp": "Mon 2021-03-15 15:27:04 CET",
        "AssertTimestampMonotonic": "62422657449",
        "Before": "shutdown.target multi-user.target",
        "BlockIOAccounting": "no",
        "BlockIOWeight": "[not set]",
        "CPUAccounting": "no",
        "CPUAffinity": "",
        "CPUAffinityFromNUMA": "no",
        "CPUQuotaPerSecUSec": "infinity",
        "CPUQuotaPeriodUSec": "infinity",
        "CPUSchedulingPolicy": "0",
        "CPUSchedulingPriority": "0",
        "CPUSchedulingResetOnFork": "no",
        "CPUShares": "[not set]",
        "CPUUsageNSec": "[not set]",
        "CPUWeight": "[not set]",
        "CacheDirectoryMode": "0755",
        "CanFreeze": "yes",
        "CanIsolate": "no",
        "CanReload": "no",
        "CanStart": "yes",
        "CanStop": "yes",
        "CapabilityBoundingSet": "cap_chown cap_dac_override cap_dac_read_search cap_fowner cap_fsetid cap_kill cap_setgid cap_setuid cap_setpcap cap_linux_immutable cap_net_bind_service cap_net_broadcast cap_net_admin cap_net_raw cap_ipc_lock cap_ipc_owner cap_sys_module cap_sys_rawio cap_sys_chroot cap_sys_ptrace cap_sys_pacct cap_sys_admin cap_sys_boot cap_sys_nice cap_sys_resource cap_sys_time cap_sys_tty_config cap_mknod cap_lease cap_audit_write cap_audit_control cap_setfcap cap_mac_override cap_mac_admin cap_syslog cap_wake_alarm cap_block_suspend cap_audit_read cap_perfmon cap_bpf cap_checkpoint_restore",
        "CleanResult": "success",
        "CollectMode": "inactive",
        "ConditionResult": "yes",
        "ConditionTimestamp": "Mon 2021-03-15 15:27:04 CET",
        "ConditionTimestampMonotonic": "62422657447",
        "ConfigurationDirectoryMode": "0755",
        "Conflicts": "shutdown.target",
        "ControlGroup": "/system.slice/mysqld.service",
        "ControlPID": "0",
        "CoredumpFilter": "0x33",
        "DefaultDependencies": "yes",
        "DefaultMemoryLow": "0",
        "DefaultMemoryMin": "0",
        "Delegate": "no",
        "Description": "MySQL 8.0 database server",
        "DevicePolicy": "auto",
        "DynamicUser": "no",
        "EffectiveCPUs": "",
        "EffectiveMemoryNodes": "",
        "Environment": "MYSQLD_PARENT_PID=1",
        "ExecMainCode": "0",
        "ExecMainExitTimestampMonotonic": "0",
        "ExecMainPID": "126796",
        "ExecMainStartTimestamp": "Mon 2021-03-15 15:27:04 CET",
        "ExecMainStartTimestampMonotonic": "62422723857",
        "ExecMainStatus": "0",
        "ExecStart": "{ path=/usr/libexec/mysqld ; argv[]=/usr/libexec/mysqld --basedir=/usr ; ignore_errors=no ; start_time=[Mon 2021-03-15 15:27:04 CET] ; stop_time=[n/a] ; pid=126796 ; code=(null) ; status=0/0 }",
        "ExecStartEx": "{ path=/usr/libexec/mysqld ; argv[]=/usr/libexec/mysqld --basedir=/usr ; flags= ; start_time=[Mon 2021-03-15 15:27:04 CET] ; stop_time=[n/a] ; pid=126796 ; code=(null) ; status=0/0 }",
        "ExecStartPre": "{ path=/usr/libexec/mysql-prepare-db-dir ; argv[]=/usr/libexec/mysql-prepare-db-dir mysqld.service ; ignore_errors=no ; start_time=[Mon 2021-03-15 15:27:04 CET] ; stop_time=[Mon 2021-03-15 15:27:04 CET] ; pid=126761 ; code=exited ; status=0 }",
        "ExecStartPreEx": "{ path=/usr/libexec/mysql-prepare-db-dir ; argv[]=/usr/libexec/mysql-prepare-db-dir mysqld.service ; flags= ; start_time=[Mon 2021-03-15 15:27:04 CET] ; stop_time=[Mon 2021-03-15 15:27:04 CET] ; pid=126761 ; code=exited ; status=0 }",
        "ExecStopPost": "{ path=/usr/libexec/mysql-wait-stop ; argv[]=/usr/libexec/mysql-wait-stop ; ignore_errors=no ; start_time=[n/a] ; stop_time=[n/a] ; pid=0 ; code=(null) ; status=0/0 }",
        "ExecStopPostEx": "{ path=/usr/libexec/mysql-wait-stop ; argv[]=/usr/libexec/mysql-wait-stop ; flags= ; start_time=[n/a] ; stop_time=[n/a] ; pid=0 ; code=(null) ; status=0/0 }",
        "FailureAction": "none",
        "FileDescriptorStoreMax": "0",
        "FinalKillSignal": "9",
        "FragmentPath": "/usr/lib/systemd/system/mysqld.service",
        "FreezerState": "running",
        "GID": "27",
        "Group": "mysql",
        "GuessMainPID": "yes",
        "IOAccounting": "no",
        "IOReadBytes": "18446744073709551615",
        "IOReadOperations": "18446744073709551615",
        "IOSchedulingClass": "0",
        "IOSchedulingPriority": "0",
        "IOWeight": "[not set]",
        "IOWriteBytes": "18446744073709551615",
        "IOWriteOperations": "18446744073709551615",
        "IPAccounting": "no",
        "IPEgressBytes": "[no data]",
        "IPEgressPackets": "[no data]",
        "IPIngressBytes": "[no data]",
        "IPIngressPackets": "[no data]",
        "Id": "mysqld.service",
        "IgnoreOnIsolate": "no",
        "IgnoreSIGPIPE": "yes",
        "InactiveEnterTimestamp": "Mon 2021-03-15 15:27:04 CET",
        "InactiveEnterTimestampMonotonic": "62422656283",
        "InactiveExitTimestamp": "Mon 2021-03-15 15:27:04 CET",
        "InactiveExitTimestampMonotonic": "62422659235",
        "InvocationID": "632a24964dd34ebd8d55abf047ae893a",
        "JobRunningTimeoutUSec": "infinity",
        "JobTimeoutAction": "none",
        "JobTimeoutUSec": "infinity",
        "KeyringMode": "private",
        "KillMode": "control-group",
        "KillSignal": "15",
        "LimitAS": "infinity",
        "LimitASSoft": "infinity",
        "LimitCORE": "infinity",
        "LimitCORESoft": "infinity",
        "LimitCPU": "infinity",
        "LimitCPUSoft": "infinity",
        "LimitDATA": "infinity",
        "LimitDATASoft": "infinity",
        "LimitFSIZE": "infinity",
        "LimitFSIZESoft": "infinity",
        "LimitLOCKS": "infinity",
        "LimitLOCKSSoft": "infinity",
        "LimitMEMLOCK": "65536",
        "LimitMEMLOCKSoft": "65536",
        "LimitMSGQUEUE": "819200",
        "LimitMSGQUEUESoft": "819200",
        "LimitNICE": "0",
        "LimitNICESoft": "0",
        "LimitNOFILE": "10000",
        "LimitNOFILESoft": "10000",
        "LimitNPROC": "63418",
        "LimitNPROCSoft": "63418",
        "LimitRSS": "infinity",
        "LimitRSSSoft": "infinity",
        "LimitRTPRIO": "0",
        "LimitRTPRIOSoft": "0",
        "LimitRTTIME": "infinity",
        "LimitRTTIMESoft": "infinity",
        "LimitSIGPENDING": "63418",
        "LimitSIGPENDINGSoft": "63418",
        "LimitSTACK": "infinity",
        "LimitSTACKSoft": "8388608",
        "LoadState": "loaded",
        "LockPersonality": "no",
        "LogLevelMax": "-1",
        "LogRateLimitBurst": "0",
        "LogRateLimitIntervalUSec": "0",
        "LogsDirectoryMode": "0755",
        "MainPID": "126796",
        "MemoryAccounting": "yes",
        "MemoryCurrent": "353370112",
        "MemoryDenyWriteExecute": "no",
        "MemoryHigh": "infinity",
        "MemoryLimit": "infinity",
        "MemoryLow": "0",
        "MemoryMax": "infinity",
        "MemoryMin": "0",
        "MemorySwapMax": "infinity",
        "MountAPIVFS": "no",
        "MountFlags": "",
        "NFileDescriptorStore": "0",
        "NRestarts": "0",
        "NUMAMask": "",
        "NUMAPolicy": "n/a",
        "Names": "mysqld.service",
        "NeedDaemonReload": "no",
        "Nice": "0",
        "NoNewPrivileges": "no",
        "NonBlocking": "no",
        "NotifyAccess": "main",
        "OOMPolicy": "stop",
        "OOMScoreAdjust": "0",
        "OnFailureJobMode": "replace",
        "Perpetual": "no",
        "PrivateDevices": "no",
        "PrivateMounts": "no",
        "PrivateNetwork": "no",
        "PrivateTmp": "yes",
        "PrivateUsers": "no",
        "ProtectClock": "no",
        "ProtectControlGroups": "no",
        "ProtectHome": "no",
        "ProtectHostname": "no",
        "ProtectKernelLogs": "no",
        "ProtectKernelModules": "no",
        "ProtectKernelTunables": "no",
        "ProtectSystem": "no",
        "RefuseManualStart": "no",
        "RefuseManualStop": "no",
        "ReloadResult": "success",
        "RemainAfterExit": "no",
        "RemoveIPC": "no",
        "Requires": "-.mount system.slice sysinit.target tmp.mount",
        "RequiresMountsFor": "/tmp /var/tmp",
        "Restart": "on-failure",
        "RestartKillSignal": "15",
        "RestartPreventExitStatus": "1",
        "RestartUSec": "100ms",
        "RestrictNamespaces": "no",
        "RestrictRealtime": "no",
        "RestrictSUIDSGID": "no",
        "Result": "success",
        "RootDirectoryStartOnly": "no",
        "RootHashSignature": "",
        "RuntimeDirectoryMode": "0755",
        "RuntimeDirectoryPreserve": "no",
        "RuntimeMaxUSec": "infinity",
        "SameProcessGroup": "no",
        "SecureBits": "0",
        "SendSIGHUP": "no",
        "SendSIGKILL": "yes",
        "Slice": "system.slice",
        "StandardError": "inherit",
        "StandardInput": "null",
        "StandardInputData": "",
        "StandardOutput": "journal",
        "StartLimitAction": "none",
        "StartLimitBurst": "5",
        "StartLimitIntervalUSec": "10s",
        "StartupBlockIOWeight": "[not set]",
        "StartupCPUShares": "[not set]",
        "StartupCPUWeight": "[not set]",
        "StartupIOWeight": "[not set]",
        "StateChangeTimestamp": "Mon 2021-03-15 15:27:05 CET",
        "StateChangeTimestampMonotonic": "62423320324",
        "StateDirectoryMode": "0755",
        "StatusErrno": "0",
        "StatusText": "Server is operational",
        "StopWhenUnneeded": "no",
        "SubState": "running",
        "SuccessAction": "none",
        "SyslogFacility": "3",
        "SyslogLevel": "6",
        "SyslogLevelPrefix": "yes",
        "SyslogPriority": "30",
        "SystemCallErrorNumber": "0",
        "TTYReset": "no",
        "TTYVHangup": "no",
        "TTYVTDisallocate": "no",
        "TasksAccounting": "yes",
        "TasksCurrent": "39",
        "TasksMax": "19025",
        "TimeoutAbortUSec": "5min",
        "TimeoutCleanUSec": "infinity",
        "TimeoutStartFailureMode": "terminate",
        "TimeoutStartUSec": "5min",
        "TimeoutStopFailureMode": "terminate",
        "TimeoutStopUSec": "5min",
        "TimerSlackNSec": "50000",
        "Transient": "no",
        "Type": "notify",
        "UID": "27",
        "UMask": "0022",
        "UnitFilePreset": "disabled",
        "UnitFileState": "enabled",
        "User": "mysql",
        "UtmpMode": "init",
        "WantedBy": "multi-user.target",
        "WatchdogSignal": "6",
        "WatchdogTimestampMonotonic": "0",
        "WatchdogUSec": "0"
    }
}

TASK [show user grants] ***********************************************************************************************************************************************************************************************************************
task path: /home/bigo/Dokumente/git/config/Ansible/playbooks/mysql_test.yml:40
Monday 15 March 2021  15:27:13 +0100 (0:00:02.884)       0:00:05.354 ********** 
Using module file /usr/lib/python3.9/site-packages/ansible/modules/commands/command.py
Pipelining is enabled.
<127.0.0.1> ESTABLISH LOCAL CONNECTION FOR USER: bigo
<127.0.0.1> EXEC /bin/sh -c 'sudo -H -S -n  -u root /bin/sh -c '"'"'echo BECOME-SUCCESS-pyjsmqirhvlnditpjwaaukrmprynxymr ; /usr/bin/python3'"'"' && sleep 0'
changed: [localhost] => {
    "changed": true,
    "cmd": "mysql -e 'SHOW GRANTS FOR testing_admin@LOCALHOST'",
    "delta": "0:00:00.010054",
    "end": "2021-03-15 15:27:13.807948",
    "invocation": {
        "module_args": {
            "_raw_params": "mysql -e 'SHOW GRANTS FOR testing_admin@LOCALHOST'",
            "_uses_shell": true,
            "argv": null,
            "chdir": null,
            "creates": null,
            "executable": null,
            "removes": null,
            "stdin": null,
            "stdin_add_newline": true,
            "strip_empty_ends": true,
            "warn": true
        }
    },
    "rc": 0,
    "start": "2021-03-15 15:27:13.797894"
}

STDOUT:

Grants for testing_admin@localhost
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `testing_admin`@`localhost` WITH GRANT OPTION

TASK [show dynamic privileges for user] *******************************************************************************************************************************************************************************************************
task path: /home/bigo/Dokumente/git/config/Ansible/playbooks/mysql_test.yml:45
Monday 15 March 2021  15:27:13 +0100 (0:00:00.254)       0:00:05.608 ********** 
Using module file /usr/lib/python3.9/site-packages/ansible/modules/commands/command.py
Pipelining is enabled.
<127.0.0.1> ESTABLISH LOCAL CONNECTION FOR USER: bigo
<127.0.0.1> EXEC /bin/sh -c 'sudo -H -S -n  -u root /bin/sh -c '"'"'echo BECOME-SUCCESS-ulopwohqvxqsoeyplufypycctrqebsxr ; /usr/bin/python3'"'"' && sleep 0'
changed: [localhost] => {
    "changed": true,
    "cmd": "mysql -e 'SELECT * FROM mysql.global_grants WHERE USER=\"testing_admin\"'",
    "delta": "0:00:00.010359",
    "end": "2021-03-15 15:27:14.056443",
    "invocation": {
        "module_args": {
            "_raw_params": "mysql -e 'SELECT * FROM mysql.global_grants WHERE USER=\"testing_admin\"'",
            "_uses_shell": true,
            "argv": null,
            "chdir": null,
            "creates": null,
            "executable": null,
            "removes": null,
            "stdin": null,
            "stdin_add_newline": true,
            "strip_empty_ends": true,
            "warn": true
        }
    },
    "rc": 0,
    "start": "2021-03-15 15:27:14.046084"
}

TASK [print user_grants] **********************************************************************************************************************************************************************************************************************
task path: /home/bigo/Dokumente/git/config/Ansible/playbooks/mysql_test.yml:50
Monday 15 March 2021  15:27:14 +0100 (0:00:00.261)       0:00:05.870 ********** 
ok: [localhost] => {}

MSG:

Grants for testing_admin@localhost
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `testing_admin`@`localhost` WITH GRANT OPTION

TASK [print dynamic_privs] ********************************************************************************************************************************************************************************************************************
task path: /home/bigo/Dokumente/git/config/Ansible/playbooks/mysql_test.yml:54
Monday 15 March 2021  15:27:14 +0100 (0:00:00.065)       0:00:05.936 ********** 
ok: [localhost] => {}
META: ran handlers
META: ran handlers

PLAY RECAP ************************************************************************************************************************************************************************************************************************************
localhost                  : ok=11   changed=6    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0   

Monday 15 March 2021  15:27:14 +0100 (0:00:00.054)       0:00:05.990 ********** 

I hope this was all correct and is understandable and reproducable. My local fix would be a cursor.execute("FLUSH PRIVILEGES") at the end of the privileges_revoke method. But i need more testing time at some different OS and MySQL versions. Need to verify RHEL 7 and MySQL 5.7.21 and also RHEL 8 with MySQL 8.0.23 that nothing unexpected is happening in production. I Also tired a different workaround by not using ALL and list every needed privileg. But this is not working because the VALID_PRIVS do not have all privileges assigned by the GRANT ALL. The idea was that the SHOW GRANTS has the same result like the priv: "*.*:..." so the REVOKE is not needed.

[Andersson007]

@bigo8525 thanks for the issue!
BTW VALID_PRIVS has been removed IIRC, so no you can pass anything:)

[Andersson007]

also you can install the latest version of the collection from Galaxy using ansible-galaxy CLI command. Ansible 2.9 supports collections.
it's just a fyi

[bigo8525]

yes is saw this today. The last time i looked into the code was more than a year ago.
This is also the main reason for me too submit the fix. my local mysql_user.py fix is over a year old. So many new nice features are missing.

But beside VALID_PRIVS no longer exists this is still a problem.
This bug took me pretty much time to understand why i get an error after every second run when i wanted to create a innodb cluster.

If my fix is in the next release i definitly switch to the newest version of this collection. but befor that i got a problem when the cluster admin user is not able to create a cluster after a config deployment.

[rsicart]

Just for information, this link can be interesting:

https://dev.mysql.com/doc/refman/8.0/en/privilege-changes.html

From this docs I understand that we shouldn't need a FLUSH PRIVILEGES command when using GRANT or REVOKE to manage privileges. We should verify that we always do that.

I'm not a mysql expert but FLUSH PRIVILEGES command seems to reload privileges on memory, it doesn't look dangerous to me.

[bigo8525]

i find it a bit difficult to describe the problem. From docs i would agree with you. But the dynamic privileges are not really described in the docs at this specific point.

After a successful run of the mysql_user module the dynamic privileges are set to y in the mysql.global_grants tale.
If im correct with the module in

community.mysql/plugins/module_utils/user.py

Line 616 in 4aab8ac

priv_string = ",".join([p for p in priv if p not in ('GRANT', )])

there are the privileges evaluated to a list of all rights. Because the *.*:ALL,GRANT is not evaluated to ALL in the mysql. Instead every privilege is used.

I tried to get a list of the privileges created or used with this line

community.mysql/plugins/module_utils/user.py

Line 323 in 4aab8ac

priv_diff = set(new_priv[db_table]) ^ set(curr_priv[db_table])

by setting msg = priv_diff
This is the result:
['ALL', 'ALTER', 'ALTER ROUTINE', 'APPLICATION_PASSWORD_ADMIN', 'AUDIT_ABORT_EXEMPT', 'AUDIT_ADMIN', 'AUTHENTICATION_POLICY_ADMIN', 'BACKUP_ADMIN', 'BINLOG_ADMIN', 'BINLOG_ENCRYPTION_ADMIN', 'CLONE_ADMIN', 'CONNECTION_ADMIN', 'CREATE', 'CREATE ROLE', 'CREATE ROUTINE', 'CREATE TABLESPACE', 'CREATE TEMPORARY TABLES', 'CREATE USER', 'CREATE VIEW', 'DELETE', 'DROP', 'DROP ROLE', 'ENCRYPTION_KEY_ADMIN', 'EVENT', 'EXECUTE', 'FILE', 'FLUSH_OPTIMIZER_COSTS', 'FLUSH_STATUS', 'FLUSH_TABLES', 'FLUSH_USER_RESOURCES', 'GROUP_REPLICATION_ADMIN', 'GROUP_REPLICATION_STREAM', 'INDEX', 'INNODB_REDO_LOG_ARCHIVE', 'INNODB_REDO_LOG_ENABLE', 'INSERT', 'LOCK TABLES', 'PASSWORDLESS_USER_ADMIN', 'PERSIST_RO_VARIABLES_ADMIN', 'PROCESS', 'REFERENCES', 'RELOAD', 'REPLICATION CLIENT', 'REPLICATION SLAVE', 'REPLICATION_APPLIER', 'REPLICATION_SLAVE_ADMIN', 'RESOURCE_GROUP_ADMIN', 'RESOURCE_GROUP_USER', 'ROLE_ADMIN', 'SELECT', 'SERVICE_CONNECTION_ADMIN', 'SESSION_VARIABLES_ADMIN', 'SET_USER_ID', 'SHOW DATABASES', 'SHOW VIEW', 'SHOW_ROUTINE', 'SHUTDOWN', 'SUPER', 'SYSTEM_USER', 'SYSTEM_VARIABLES_ADMIN', 'TABLE_ENCRYPTION_ADMIN', 'TRIGGER', 'UPDATE', 'XA_RECOVER_ADMIN']
Some of these privileges are static and some dynamic. Because this list is different to *.*:ALL,GRANT all these privileges are revoked.
If the mysql is not restarted all is dynamic privileges are assigned to the user but the mysql.global_grants is emtpy. This means that the privileges are not present after restart of mysql. but this also means, that each run of ansible does not affect the dynamic privileges.

mysql> SHOW GRANTS FOR 'testing_admin'@'%';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for testing_admin@%                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `testing_admin`@`%` WITH GRANT OPTION                                                                                                                                                                                                                                                                                                                    |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `testing_admin`@`%` WITH GRANT OPTION |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0,00 sec)

mysql> select * from mysql.global_grants where user="testing_admin";
Empty set (0,00 sec)

as visable in the example above BACKUP_ADMIN for example is a dynamic privilege. because the dynamic privileges are only on reload of the mysql loaded the privileges change only on restart or FLUSH PRIVILEGES

mysql> flush privileges;
Query OK, 0 rows affected (0,01 sec)

mysql> select * from mysql.global_grants where user="testing_admin";
Empty set (0,00 sec)

mysql> SHOW GRANTS FOR 'testing_admin'@'%';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for testing_admin@%                                                                                                                                                                                                                                                                                                                                                                                |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `testing_admin`@`%` WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

After the FLUSH PRIVILEGES now the dynamic privileges are cleared and the GRANT ALL ON from the next ansible run make the dynamic privileges present again.

I actualy do not understand whether this is a possible misbehaviour in mysql or a tricky point of the mysql_user module. But the FLUSH PRIVILEGES in my PR seemse to be an easy fix.