[BUG] Error revoking grants when database name contains underscore

[lvinagre]

Description
When trying to manage a MySQL account through Saltstack, the creation/grants process works without issues, but when trying to revoke the added grants, it fails to properly revoke them.

Setup

• Debian 12
• MySQL 8.0.30 Community Server

Master with a handful of minions connected. All states work perfectly fine, including this one when there is no need to run such revoke (other revokes to databases without _ work fine too).

Please be as specific as possible and give set-up details.

• on-prem machine
• VM (Virtualbox, KVM, etc. please specify)
• VM running on a cloud service, please be explicit and add details
• container (Kubernetes, Docker, containerd, etc. please specify)
• or a combination, please be explicit
• jails if it is FreeBSD
• classic packaging
• onedir packaging
• used bootstrap to install

Steps to Reproduce the behavior

• Create a database with name that matches the criteria (contains _):

CREATE database underscore_db;

• Add the grant using Saltstack:

mysql_grants_testuser@172.%_underscore_db.*:
  mysql_grants.present:
    - connection_host: 'localhost'
    - connection_pass: 'my_pass'
    - connection_unix_socket: /my/mysql.sock
    - connection_user: 'root'
    - database: "underscore_db.*"
    - escape: False
    - grant: 'SELECT'
    - host: "172.%"
    - user: "testuser"

• Confirm grant has been added as expected:

mysql> show grants for 'testuser'@'172.%';
+----------------------------------------------------------------------------------------------------------------+
| Grants for testuser@172.%                                                                                      |
+----------------------------------------------------------------------------------------------------------------+
| GRANT SELECT ON `underscore_db`.* TO `testuser`@`172.%`                                                        |
+----------------------------------------------------------------------------------------------------------------+

• Try to revoke the grant using Saltstack:

mysql_grants_revoke_testuser@172.%_underscore_db_ALL:
  mysql_grants.absent:
    - connection_host: 'localhost'
    - connection_pass: 'my_pass'
    - connection_unix_socket: /my/mysql.sock
    - connection_user: 'root'
    - database: "underscore_db.*"
    - escape: False
    - grant: 'SELECT'
    - host: "172.%"
    - user: "testuser"

• The state will report an error:

          ID: mysql_grants_revoke_testuser@172.%_underscore_db_ALL
    Function: mysql_grants.absent
      Result: False
     Comment: Unable to revoke grant SELECT on underscore_db.* for testuser@172.% (MySQL Error 1141: There is no such grant defined for user 'testuser' on host '172.%')
     Started: 10:08:23.006328
    Duration: 20.268 ms
     Changes:

• Check MySQL log for errors:

[Warning] Did not write failed 'REVOKE SELECT ON `underscore\_db`.* FROM 'testuser'@'172.%'' into binary log while granting/revoking privileges in databases.

Expected behavior
Grants are revoked as expected.

Versions Report

Salt Version:
          Salt: 3006.7

Python Version:
        Python: 3.10.13 (main, Feb 19 2024, 03:31:20) [GCC 11.2.0]

Dependency Versions:
          cffi: 1.14.6
      cherrypy: 18.6.1
      dateutil: 2.8.1
     docker-py: Not Installed
         gitdb: Not Installed
     gitpython: Not Installed
        Jinja2: 3.1.3
       libgit2: Not Installed
  looseversion: 1.0.2
      M2Crypto: Not Installed
          Mako: Not Installed
       msgpack: 1.0.2
  msgpack-pure: Not Installed
  mysql-python: Not Installed
     packaging: 22.0
     pycparser: 2.21
      pycrypto: Not Installed
  pycryptodome: 3.19.1
        pygit2: Not Installed
  python-gnupg: 0.4.8
        PyYAML: 6.0.1
         PyZMQ: 23.2.0
        relenv: 0.15.1
         smmap: Not Installed
       timelib: 0.2.4
       Tornado: 4.5.3
           ZMQ: 4.3.4

System Versions:
          dist: debian 12 bookworm
        locale: utf-8
       machine: x86_64
       release: 6.1.0-32-amd64
        system: Linux
       version: Debian GNU/Linux 12 bookworm

Additional context
Tried reading both https://dev.mysql.com/doc/refman/5.7/en/identifiers.html and https://dev.mysql.com/doc/refman/8.0/en/identifiers.html to support the statement present in https://github.com/salt-extensions/saltext-mysql/blob/main/src/saltext/mysql/modules/mysql.py#L706-L710 and https://github.com/salt-extensions/saltext-mysql/blob/main/src/saltext/mysql/modules/mysql.py#L2604-L2605 however I cannot find the requirement from MySQL to do so. Given that the code is already quoting the values, escaping it should not be done?

[lvinagre]

I forgot about it, but seems I already opened something related saltstack/salt#66020. Please feel free to keep the one that makes more sense.