mysql_user fails after first successful run with "no such grant defined" when a wildcard host exists

[bluikko]

SUMMARY

mysql_user succeeds on the first run but on the second and subsequent runs it fails with error There is no such grant defined for user 'dbuser' on host '10.170.213.4'.

The mysql_user task is run in a loop of 2, with first item being the IPv4 address host and second item being an IPv6 address. Only the first item fails - because there is a pre-existing user with host '10.170.213.%' and this confuses mysql_user.

mysql_user tries to revoke the existing grants that are for a wildcard host - but using the exact host in the revoke. It fails because those grants do not exist, the grants are for a wildcard host.

mysql_user should not attempt to revoke grants from hosts that do not match the host that was passed to mysql_user.

ISSUE TYPE

• Bug Report

COMPONENT NAME

mysql_user

ANSIBLE VERSION

ansible [core 2.11.6]
  config file = /etc/ansible/ansible.cfg
  configured module search path = ['/x/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /opt/ansible-venv/lib64/python3.6/site-packages/ansible
  ansible collection location = /x/.ansible/collections:/usr/share/ansible/collections
  executable location = /opt/ansible-venv/bin/ansible
  python version = 3.6.8 (default, Nov 16 2020, 16:55:22) [GCC 4.8.5 20150623 (Red Hat 4.8.5-44)]
  jinja version = 3.0.2
  libyaml = True

COLLECTION VERSION

# /usr/share/ansible/collections/ansible_collections
Collection        Version
----------------- -------
community.general 4.0.1

CONFIGURATION

not relevant

OS / ENVIRONMENT

CentOS 7, MySQL 5.7 / Percona XtraDB Cluster 5.7

Percona-XtraDB-Cluster-57-5.7.34-31.51.1.el7.x86_64

STEPS TO REPRODUCE

Create an existing user in MySQL before running mysql_user.

CREATE USER 'dbuser'@'10.170.213.%' IDENTIFIED BY 'blah';
GRANT ALL PRIVILEGES ON `db\_server`.* TO 'dbuser'@'10.170.213.%'

The above commands are examples only. The real user privileges from the system is listed below - there is a difference that may be important in that the existing user has a backslash before the underscore in the database name. The above commands are just a reconstruction from this, I do not know if they are valid and the backslash is used correctly!

Edit: the backslash was irrelevant. The problem is caused by a existing user with a wildcard host.

Create user with mysql_user:

- name: create mysql tables and users
  ansible.builtin.include_role:
    name: include/db
  vars:
    database_host:
      - 10.170.213.4
      - 2001:db8::4
    database_name: "{{ database.database }}"
    database_user: "{{ database.user }}"
    database_password: "{{ database.password }}"
    database_priv: "{{ database.database ~ '.*:CREATE,CREATE VIEW,DROP,SELECT/' ~ database.database ~ '.virtual_d:INSERT' }}"

From the included role "include/db":

- name: create mysql user
  community.mysql.mysql_user:
    name: "{{ database_user }}"
    host: "{{ database_item }}"
    login_password: "{{ credentials.password }}"
    login_unix_socket: /var/lib/mysql/mysql.sock
    login_user: "{{ credentials.user }}"
    password: "{{ database_password }}"
    priv: "{{ database_priv | default(database_name ~ '.*:ALL') }}"
    state: present
  delegate_to: "{{ dbserver }}"
  loop: "{{ [database_host] | flatten }}"
  loop_control:
    loop_var: database_item

Final privileges after the 1st (successful) run for IPv4 are:

Grants for [email protected]: GRANT USAGE ON *.* TO 'dbuser'@'10.170.213.4'
Grants for [email protected]: GRANT SELECT, CREATE, DROP, CREATE VIEW ON `db_server`.* TO 'dbuser'@'10.170.213.4'
Grants for [email protected].%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `db\_server`.* TO 'dbuser'@'10.170.213.%'
Grants for [email protected]: GRANT INSERT ON `db_server`.`virtual_d` TO 'dbuser'@'10.170.213.4'

Note that the pre-existing 'dbuser'@'10.170.213.%' has a backslash in the database name!

• Existing user:

`db\_server`.* TO 'dbuser'@'10.170.213.%'

• User being created:

`db_server`.*
`db_server`.`virtual_d`

The pre-existing user has been created years ago and I could not say why it has a backslash before the underscore in the database name. But the old user works without problems even like that.

EXPECTED RESULTS

The second run should succeed like the first run did, or like the user with IPv6 host succeeds.

After the existing user with wildcard host was modified to not cover the IP address on the new user host, the error goes away. But I should not need to do this - mysql_user should check the host on existing grants matches the host of the request before trying to revoke the existing grant.

ACTUAL RESULTS

Suspect that the existing user in database causes a problem for the IPv4 user that has IPv4 address within the existing wildcard user host.

failed: [server -> db] (item=10.170.213.4) => changed=false
  ansible_loop_var: database_item
  msg: (1141, u"There is no such grant defined for user 'dbuser' on host '10.170.213.4'")
  database_item: 10.170.213.4
ok: [server -> db] => (item=2001:db8::4) => changed=false
  ansible_loop_var: database_item
  msg: User unchanged
  database_item: 2001:db8::4
  user: dbuser

Edit: Found in MySQL logs: this is the bug:

2021-11-14T01:44:16.569744Z 6 [ERROR] Slave SQL: Error 'There is no such grant defined for user 'dbuser' on host '10.170.213.4'' on query. Default database: ''. Query: 'REVOKE SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,REFERENCES,INDEX,ALTER,CREATE TEMPORARY TABLES,LOCK TABLES,EXECUTE,CREATE VIEW,SHOW VIEW,CREATE ROUTINE,ALTER ROUTINE,TRIGGER ON `db\_server`.* FROM 'dbuser'@'10.170.213.4'', Error_code: 1141

So the reason can be seen here. mysql_user tries to revoke privileges that are for a different host - these privileges are not for host '10.170.213.4', they are for a different wildcard host, so mysql_user should not touch these permissions at all.

Most likely mysql_user is too eager to revoke permissions. It should not touch the user with a wildcard host. Probably the module fails to check if the host returned by SHOW GRANTS matches the host of the user currently being changed - or the entry in SHOW GRANTS is a wildcard entry that's just being returned in the same list!

[Andersson007]

@bluikko thanks for reporting this and the very detailed report!

[jeffmacdonald]

I think I'm having a very similar issue kind of.

• I create user with any host
• I try to create another user with same username, but different host.
• It fails.

[Andersson007]

@jeffmacdonald hello, thanks for the confirmation! I've put the help wanted label. Any help with investigating the case in the code will be much appreciated.