Display a more informative error when msg": "Error granting privileges, invalid priv string: ALL" is returned.

[bizmate]

SUMMARY

When a mysql_user module is used and fails the error usually is abstracted/reduced to msg": "Error granting privileges, invalid priv string: ALL"
This often has nothing to do with the actual error but the user will never know

ISSUE TYPE

• Bug Report

COMPONENT NAME

mysql_user

ANSIBLE VERSION

ansible [core 2.13.6rc1]
  config file = /home/bizmate/Documents/siti-web/myapp-infra/rancher1/ansible/ansible.cfg
  configured module search path = ['/home/bizmate/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python3/dist-packages/ansible
  ansible collection location = /home/bizmate/.ansible/collections:/usr/share/ansible/collections
  executable location = /usr/bin/ansible
  python version = 3.10.6 (main, Nov  2 2022, 18:53:38) [GCC 11.3.0]
  jinja version = 3.0.3
  libyaml = True

COLLECTION VERSION

# /usr/lib/python3/dist-packages/ansible_collections
Collection      Version
--------------- -------
community.mysql 3.5.1  

# /home/bizmate/.ansible/collections/ansible_collections
Collection      Version
--------------- -------
community.mysql 3.5.1  

CONFIGURATION

DEFAULT_HOST_LIST(/home/bizmate/Documents/siti-web/myapp-infra/rancher1/ansible/ansible.cfg) = ['/home/bizmate/Documents/siti-web/myapp-infra/rancher1/ansible/inventory']
DEFAULT_PRIVATE_KEY_FILE(/home/bizmate/Documents/siti-web/myapp-infra/rancher1/ansible/ansible.cfg) = /home/bizmate/.ssh/id_rsa_MyApp_Infra
DEFAULT_REMOTE_USER(/home/bizmate/Documents/siti-web/myapp-infra/rancher1/ansible/ansible.cfg) = root
HOST_KEY_CHECKING(/home/bizmate/Documents/siti-web/myapp-infra/rancher1/ansible/ansible.cfg) = False

OS / ENVIRONMENT

ubuntu 18, mysql 5.7

STEPS TO REPRODUCE

1 - add a user without grant options, ie not root
2 - use this user to create another user with access to a specific DB
3 - the command mysql_user will fail and display error "Error granting privileges, invalid priv string: ALL" even if there is not problem with the actual permission requested and instead the problem is the user used as the admin user when adding another user.

# This creates a user for administration purposes. Notice that it is created without WITH GRANT OPTION
- name: create mysql admin user
  block:
    - name: run query as admin with password
      community.mysql.mysql_query:
        login_db: mysql
        query: SHOW DATABASES;
        login_user: "{{ lookup('env', 'MYSQL_RANCHER1_ADMIN_USER') }}"
        login_password: "{{ lookup('env', 'MYSQL_RANCHER1_ADMIN_PASS') }}"
  rescue:
    - name: set up admin user
      become: true
      become_user: root
      shell:
        cmd: sudo mysql -u root -e "USE mysql;CREATE USER '{{ lookup('env', 'MYSQL_RANCHER1_ADMIN_USER') }}'@'{{ item }}' IDENTIFIED BY '{{ lookup('env', 'MYSQL_RANCHER1_ADMIN_PASS') }}';GRANT ALL PRIVILEGES ON *.* TO '{{ lookup('env', 'MYSQL_RANCHER1_ADMIN_USER') }}'@'{{ item }}' ;FLUSH PRIVILEGES;"
      with_items:
        - localhost
        - "{{ ansible_default_ipv4.address }}"
  tags:
    - mysqldebug

# This Database is created and will be consumed by the user we create below
- name: create a new database
  mysql_db: name=cattle state=present login_user={{ lookup('env', 'MYSQL_RANCHER1_ADMIN_USER') }} login_password="{{ lookup('env', 'MYSQL_RANCHER1_ADMIN_PASS') }}"

- name: add rancher user to consume cattle db
  become: true
  become_user: root
  mysql_user:
    name: rancher
    host: "{{ item }}"
    password: "{{ lookup('env', 'MYSQL_RANCHER1_RANCHER_PASS') }}"
    login_user: "{{ lookup('env', 'MYSQL_RANCHER1_ADMIN_USER') }}"
    login_password: "{{ lookup('env', 'MYSQL_RANCHER1_ADMIN_PASS') }}"
    check_implicit_admin: yes
    priv:
      'cattle.*' : 'ALL,GRANT'
    state: present
    check_implicit_admin: no
  with_items:
    - "{{ ansible_default_ipv4.address }}"
    - localhost
  tags:
    - mysqldebug

EXPECTED RESULTS

When mysql_user throws an exception also print the actual exception message so the user knows what really happened ie

Error granting privileges, invalid priv string: ALL and exception: XYZ"

ACTUAL RESULTS

As per steps to reproduce the user does not really know what is happening.

As similar problem is most likely happening with #462

[bizmate]

See pull request #466

[razerrazer]

Hi Man,

We have the same issue, but i use Debian 11 on my machine, this is a bug. I Jumped over to Rocky Linux v9 and it works fine.