MySQL rights not added properly when adding SYSTEM_USER, SET_USER_ID and similar privileges

[MontyBeda]

Describe the Bug

When trying to add privileges to account that is displayed on second or third line when running SHOW GRANTS for the account it will fail and readd the rights every run of Puppet and thus being marked as always doing changes and not run on servers running in read only mode even when all accounts having proper rights/privileges.
At least it happens when using create_resources(mysql_grant, $grants) construction to grant the rights it happens.

Expected Behavior

A clear and concise description of what you expected to happen.
I would expect that either the module will be able to parse to rights to appropriate lines from SHOW GRANTS or have some way to add multiple lines for privileges for multiple lines and described in documentation the usage for these cases.

Steps to Reproduce

Steps to reproduce the behavior:

1. Have in manifest calling adding mysql rights like create_resources(mysql_grant, $grants)
2. Have the rights granted contain SYSTEM_USER and/or SET_USER_ID or such rights that are displayed not on first rights in SHOW GRANTS. For example like this: backup_user@backupserver/.:
   privileges: ['SET_USER_ID', 'SYSTEM_USER', 'ALTER', 'ALTER ROUTINE', 'CREATE', 'CREATE ROLE', 'CREATE ROUTINE', 'CREATE TABLESPACE', 'CREATE TEMPORARY TABLES', 'CREATE USER', 'CREATE VIEW', 'DELETE', 'DROP', 'DROP ROLE', 'EVENT', 'EXECUTE', 'FILE', 'INDEX', 'INSERT', 'LOCK TABLES', 'PROCESS', 'REFERENCES', 'RELOAD', 'REPLICATION CLIENT', 'REPLICATION SLAVE', 'SELECT', 'SHOW DATABASES', 'SHOW VIEW', 'SHUTDOWN', 'SUPER', 'TRIGGER', 'UPDATE']
   table: '.'
   user: 'backup_user@backupserver'
   options: ['GRANT']
3. Run it on server using puppet and get error/warning about user always missing SET_USER_ID and SYSTEM_USER rights and being added. Or failing the adding if the server running in read_only mode.

Environment

• MySQL Puppet module Version v10.9.1
• Platform Debian GNU/Linux 10 (buster)

Additional Context

I have looked into it and the problem starts how module is gathering details about privileges of users as it will save each line from SHOW GRANTS in modules/mysql/lib/puppet/provider/mysql_grant/mysql.rb in self.instances as per each line from SHOW GRANTS so stripped privileges for example account will look like this:
User: backup_user@backupserver
Stripped privileges: ["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"]
Stripped privileges: ["SET_USER_ID", "SYSTEM_USER"]

Then when from the same script is called privileges=(privileges) and it will call diff_privileges and it will compare the first line from stripped privileges from the instances with the privileges line from the hiera or other setup and determine missing rights and try to add them. I have tried looking into what is calling the privileges function but got stopped in middle of Puppet internal functions without really knowing how it is called and therefore what should be modified so therefore I am asking for help somebody who knows the code and how it works.
(Just for curiosity the functions calling the privileges have this logic of calling each other and the first one not giving any details about caller of the function: /opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/graph/relationship_graph.rb:125:in traverse' -> /opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/transaction.rb:194:in call' -> /opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/transaction.rb:296:in eval_resource' -> /opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/transaction.rb:273:in apply' -> /opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/transaction/resource_harness.rb:26:in evaluate' -> /opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/transaction/resource_harness.rb:90:in perform_changes' -> /opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/transaction/resource_harness.rb:98:in block in perform_changes' -> /opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/transaction/resource_harness.rb:147:in sync_if_needed' -> /opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/transaction/resource_harness.rb:255:in sync' -> /opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/property.rb:579:in sync' -> /opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/property.rb:513:in set' -> /opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/property.rb:196:in call_provider' -> modules/mysql/lib/puppet/provider/mysql_grant/mysql.rb in privileges)

[jhrcz-ls]

please could anyone with experience with this module code take look of my changes? the problem i found is, that when multiple rows of grants are returned from "show grants" there is first line with static privileges, and second with (new, mysql 8+) dynamic privileges. but the module code does not merge correctly all privileges for comparison of current and expected grants. moving part of the code out of inner block, and little work with merging permissions could be the way to go.

main...jhrcz-ls:puppetlabs-mysql:fix-mysql-dynamic-perms-grants-multiple-rows
... there will be some glitches to finetune, definitely im not ruby programmer ;o)

[github-actions]

Hello! 👋

This issue has been open for a while and has had no recent activity. We've labelled it with attention-needed so that we can get a clear view of which issues need our attention.

If you are waiting on a response from us we will try and address your comments on a future Community Day.

Alternatively, if it is no longer relevant to you please close the issue with a comment.