Cannot create user in MariaDB with MAX_USER_CONNECTIONS

Question

I'm trying to create a user in MariaDB 10.1 with the following statement:

 CREATE USER 'exporter' IDENTIFIED BY 'exporter' WITH MAX_USER_CONNECTIONS 3;

However this command fails with:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'MAX_USER_CONNECTIONS 3' at line 1

It works when omitting the WITH MAX_USER_CONNECTIONS option.

Answer 1

The 10.2 syntax implies that the password option comes after the resource options.

CREATE USER 'exporter'
    WITH MAX_USER_CONNECTIONS 3
    IDENTIFIED BY 'exporter';

Answer 2

For MariaDB < 10.2 it works if I split the statements:

CREATE USER 'exporter' IDENTIFIED BY 'exporter';
GRANT [...] WITH MAX_USER_CONNECTIONS 3;

For newer versions please see this answer.

Answer 3

The syntax mentioned is available since MariaDB 10.2.0.

https://mariadb.com/kb/en/library/create-user/