By: Fernando Mattera

Fernando Mattera — Mon, 05 Jun 2023 14:08:02 +0000

Hey Karl,

Thank you so much for reading and making this suggestion. I have a few things to say:

Not only do you need a server version equal to or higher than 10.3, but you also need at least the corresponding version of the MariaDB client tools, including mysqldump. Although my test was made using the latest version of MariaDB, my intention was to cover all possible versions such as 10.0 onwards. I didn’t write such clarification in order to avoid making this blog so long.Regardless you have version 10.3 or higher, the syntax of the CREATE USER differs and doesn’t work in MySQL/Percona Server. Below I show you an example

The dump on MariaDB

$ mariadb-dump –system=users –compact | grep user_want_all | grep -i create
CREATE USER user_want_all@% IDENTIFIED BY PASSWORD
‘*210F0DA943585CF2372ADF07342C92824DAA9EE4’;

$ mariadb-dump –system=users –compact | grep user_want_all | grep -i create

CREATE USER user_want_all@% IDENTIFIED BY PASSWORD

‘*210F0DA943585CF2372ADF07342C92824DAA9EE4’;

The CREATE USER attempt on MySQL/Percona Server

mysql> CREATE USER user_want_all@% IDENTIFIED BY PASSWORD ‘*210F0DA943585CF2372ADF07342C92824DAA9EE4’;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘PASSWORD ‘*210F0DA943585CF2372ADF07342C92824DAA9EE4” at line 1

mysql> CREATE USER user_want_all@% IDENTIFIED BY PASSWORD ‘*210F0DA943585CF2372ADF07342C92824DAA9EE4’;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘PASSWORD ‘*210F0DA943585CF2372ADF07342C92824DAA9EE4” at line 1

Also, you may ask, “Why are you talking about ROLES above all?” Again, ROLES were introduced in MariaDB 10.2, and the dump doesn’t look so good

MariaDB_10.2 > select @@version;
+———————+
| @@version      |
+———————+
| 10.2.44-MariaDB-log |
+———————+
1 row in set (0.03 sec)

MariaDB_10.2 > create role foo;
Query OK, 0 rows affected (0.03 sec)

MariaDB_10.2 > show grants for foo;
+—————————–+
| Grants for foo       |
+—————————–+
| GRANT USAGE ON *.* TO ‘foo’ |
+—————————–+
1 row in set (0.02 sec)

$ mysqldump –system=users | grep -i foo
/*!80001 CREATE ROLE ‘foo’ */;
/*M!100005 CREATE ROLE ‘foo’ WITH ADMIN mariadb_dump_import_role */;
/*M!100005 GRANT ‘foo’ TO ‘root’@’localhost’ WITH ADMIN OPTION */;
GRANT ‘foo’ TO ‘root’@’localhost’ WITH ADMIN OPTION;
GRANT USAGE ON *.* TO ‘foo’;

MariaDB_10.2 > select @@version;

+———————+

| @@version |

+———————+

| 10.2.44-MariaDB-log |

+———————+

1 row in set (0.03 sec)

MariaDB_10.2 > create role foo;

Query OK, 0 rows affected (0.03 sec)

MariaDB_10.2 > show grants for foo;

+—————————–+

| Grants for foo |

+—————————–+

| GRANT USAGE ON *.* TO ‘foo’ |

+—————————–+

1 row in set (0.02 sec)

$ mysqldump –system=users | grep -i foo

/*!80001 CREATE ROLE ‘foo’ */;

/*M!100005 CREATE ROLE ‘foo’ WITH ADMIN mariadb_dump_import_role */;

/*M!100005 GRANT ‘foo’ TO ‘root’@’localhost’ WITH ADMIN OPTION */;

GRANT ‘foo’ TO ‘root’@’localhost’ WITH ADMIN OPTION;

GRANT USAGE ON *.* TO ‘foo’;

Thanks for your suggestion, anyway. Best,

Fernando.

By: Karl Levik

Karl Levik — Thu, 01 Jun 2023 14:32:04 +0000

Did you consider the special --system=users option to mariadb-dump?

From man mariadb-dump:

• users – the users, roles and their grants outputed as CREATE USER,
CREATE ROLE, GRANT, and SET DEFAULT ROLE (ALTER USER for MySQL-8.0+).

Comments on: Migrating From MariaDB to Percona Server for MySQL 8: The Security Witchcraft

By: Fernando Mattera

By: Karl Levik