The access control layer of MariaDB is based on accounts. An account is composed of a username and the name of the host from which the user connects. The account's syntax is shown as follows (the quotes are optional, if no special characters are used):

'username'@'hostname'

It is good practice to create new users with the CREATE USER statement. Then, permissions can be assigned to the users with GRANT. By default, MariaDB allows assigning permissions to accounts even if its user does not exist, in which case the server will automatically create it. Though, in this way, unwanted users could be created by mistyping the username in the GRANT statement. The autocreation of users can be disabled by setting the NO_AUTO_CREATE_USER flag in the SQL_MODE system variable, shown as follows:

MariaDB [(none)]> SET @@global.sql_mode = CONCAT(@@global.sql_mode, ',NO_AUTO_CREATE_USER'),
Query OK, 0 rows affected (0.07 sec)

In an account, both the username and hostname can use the same wildcard characters that are used with the LIKE operator. For example, 'user__'@'%' matches usernames such as user01 from whatever host they connect. When a user tries to connect, MariaDB searches for an account that matches its username and hostname (or the IP address) in the mysql.user system table. If an account matches, MariaDB checks the password. The password is encrypted in the system table, and also the client encrypts it before sending it to the server. If the encrypted passwords are not identical, or if no account matches the username and hostname, the connection is rejected with an error.

If we provide an incorrect password, we obtain an error similar to the following:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

This error provides all details, which can be used to diagnose problems such as the username does not exist, account cannot be accessed from this host, or password is wrong. If the password does not appear and the error message tells us that we have provided it, we can easily know whether we just forgot to enter the password.

Sometimes, multiple accounts can match a username and hostname. In such cases, MariaDB tries to choose the least generic account. For example, 'user01'@'mandarino' will be preferred over 'user__'@'%'. This account will be used by MariaDB to check the permissions every time the user issues a command. Permissions associated to other matching accounts will be ignored.

The USER() function returns the complete username and hostname used by the current connection. The CURRENT_USER() function returns the account that was chosen by MariaDB during authentication. For example:

SELECT CURRENT_USER(), USER();
+----------------+------------------+
| CURRENT_USER() | USER()            
+----------------+------------------+
| user01@%       | user01@mandarino |
+----------------+------------------+

In this example, whenever the user sends a SQL statement, the server will check whether the 'user01'@'%' account has the permissions to execute it. More generic accounts may exist such as 'user__'@'%', but they are ignored.