mysql.user and different syntax between mysql and mariadb

[hubiongithub]

Hello

in user.py there is a code block like:

# Mysql and MariaDB differ in naming pam plugin and Syntax to set it
        if plugin == 'pam':  # Used by MariaDB which requires the USING keyword, not BY
            query_with_args = "CREATE USER %s@%s IDENTIFIED WITH %s USING %s", (user, host, plugin, plugin_auth_string)
        elif plugin == 'ed25519':  # Used by MariaDB which requires the USING keyword, not BY
            query_with_args = "CREATE USER %s@%s IDENTIFIED WITH %s USING PASSWORD(%s)", (user, host, plugin, plugin_auth_string)
        elif salt:
            if plugin in ['caching_sha2_password', 'sha256_password']:
                generated_hash_string = mysql_sha256_password_hash_hex(password=plugin_auth_string, salt=salt)

in MariaDB 11.8 (or earlier) a new plugin "parsec" was added which when used results in an error as the code will use "BY" instead of "USING"
(whyever these two groups of people couldn't stick to one syntax :)
In 12.1 mariadb will add some sort of sha_256 plugin:
https://mariadb.com/docs/server/reference/plugins/authentication-plugins/authentication-plugin-sha-256

so distinction of what syntax to use might get messier and probably doesn't work on checking on plugin alone.
Does the code check on what server it is creating user ...? So it would be able to do something like

if mariadb
  if password should be mysql_native_password -> do this
  if plugin = pam -> query_with_args = "CREATE USER %s@%s IDENTIFIED WITH %s USING %s", (user, host, plugin, plugin_auth_string)
  else query_with_args = "CREATE USER %s@%s IDENTIFIED WITH %s USING PASSWORD(%s)", (user, host, plugin, plugin_auth_string)
else if mysql
  ...

I'm not sure if that will make coding easier, but I suspect whenever there is a plugin called the same in both variants but uses a different syntax we will need a distinction between mariadb and mysql.

Thanks for all your hard work.

[Andersson007]

@hubiongithub SGTM, though I'm not an expert. As far as I know, there's a check in the code used in a few places to handle the differences. Grep through it using "mariadb|mysql". Feel free to open an issue (and tackle it).