MySQL/MariaDB – Allowed Characters in User Account Names

mariadbMySQLusers

It's not clear to me which characters can occur in MySQL/MariaDB user account names, and which are illegal. I've read section 6.3.1 "User Names and Passwords" in the MySQL documentation. This section includes information on character encodings and the maximum name length, but it doesn't state whether the characters that can be used in an account name are restricted (or perhaps I missed that part?).

So, are there any special charachters in MySQL/MariaDB that cannot be used in user account names? For example, are control characters (like a line break) or wildcard characters (like the asterisk) illegal characters?

Best Answer

User names are stored on the mysql.user table. On my system (mariadb 10.3), that table is created as a CHAR(80) with the utf8 character set. You can check yours by running:

MariaDB [(none)]> SHOW CREATE TABLE mysql.user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '',

That means, for my version, usernames can be between 0 and 80 characters that are valid utf8 strings but not outside the basic multilingual plane (only 3-byte characters).

So control and wildcard characters are valid, but for example, not emojis:

MariaDB [(none)]> CREATE USER 'a*b';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> CREATE USER 'a
    '> b';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> CREATE USER '?';
ERROR 1470 (HY000): String '????' is too long for user name (should be no longer than 80)

MariaDB [(none)]> CREATE USER 'Robert\'); DROP TABLE Students;-- ';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SELECT user FROM mysql.user;
+----------------------------------+
| user                             |
+----------------------------------+
| Robert'); DROP TABLE Students;-- |
| a
b                              |
| a*b                              |
| root                             |
| root                             |
| root                             |
| root                             |
+----------------------------------+
7 rows in set (0.000 sec)

Check the type in your system, I guess it could change depending on the version/vendor or be different in the future/past.

Related Question