MariaDB also supports authentication plugins. These plugins can implement different methods for login and logout. Generally, this is useful to prevent an external program from authenticating users into a MariaDB server. Some plugins require the client to interact with them using appropriate client plugins. The client plugins are dynamically loaded and need not be installed, but this requires the client to be built dynamically.

Currently, MariaDB 10.0 comes with four authentication plugins:

The last two plugins are not installed by default. They can be installed with the following statements:

INSTALL SONAME 'auth_socket';
INSTALL SONAME 'auth_pam';

Other plugins may be added to the MariaDB packages in the future, or developed by companies that need them and have the internal skills to develop authentication plugins. The exact list of authentication plugins can be obtained with the following query:

SELECT PLUGIN_NAME, PLUGIN_STATUS
  FROM information_schema.PLUGINS
  WHERE PLUGIN_TYPE = 'AUTHENTICATION';

It is possible that you may require an account to connect MariaDB using a particular authentication plugin. To do this, both CREATE USER and GRANT can be used, like in the following examples:

CREATE USER federico IDENTIFIED VIA 'mysql_native_password';
GRANT USAGE ON *.* TO federico IDENTIFIED VIA 'unix_socket';

In the preceding example, first the user federico was created specifying explicitly that he uses the default MariaDB authentication (this is not necessary when creating a user, but can be done). Then, we require that the user connects via the unix_socket plugin, shown as follows:

MariaDB [(none)]> CREATE USER federico IDENTIFIED VIA 'mysql_native_password';
Query OK, 0 rows affected (0.00 sec) 
MariaDB [(none)]> GRANT USAGE ON *.* TO federico IDENTIFIED VIA 'unix_socket';
Query OK, 0 rows affected (0.03 sec)

The USAGE command is a virtual permission. It can be specified when we don't want to grant any real permission. Simply omitting the permissions list will cause a syntax error. In the preceding example, we used USAGE so that the user federico connects using unix_socket.

To complete the example, let's see how this user can connect to MariaDB.

Let's use a shell as federico, and start the mysql command-line client:

federico@this:/usr/local/mysql$ bin/mysql
Welcome to the MariaDB monitor.  Commands end with ; or g.

It works because we are already logged in as federico. The unix_socket plugin verifies whether the thread belongs to federico and accepts the connection.

Now, let's use the shell as the root, and start mysql:

root@this:/usr/local/mysql# bin/mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) 

As the user of the current system is root and not federico, the connection is not accepted. However, we can use the --user option, which starts the client as the specified user:

root@this:/usr/local/mysql# bin/mysql --user federico
Welcome to the MariaDB monitor.  Commands end with ; or g. 

With the client running as the appropriate user, the connection is accepted.

Limiting user resources, MariaDB allows us to limit the amount of system resources an account can consume. This is done with the GRANT statement. The following are the limits that can be set:

For all these values, the default is 0, which means that the limit does not apply to the account. However, MAX_USER_CONNECTIONS is an exception; if it is set to 0, the limit is set to the global value of the max_user_connections server variable. Its default value is 0, which means that the limit does not apply. The user can query the session value of this variable to know the maximum number of simultaneous connections that this account can establish.

An example of how to assign a resource limit is shown as follows:

MariaDB [(none)]> GRANT USAGE ON TABLE *.* TO 'u1'@'%' WITH MAX_QUERIES_PER_HOUR 100 MAX_UPDATES_PER_HOUR 50;
Query OK, 0 rows affected (0.00 sec) 

The GRANT statement has some mandatory clauses; this means that we cannot execute a GRANT statement that does not assign any permission. Since our purpose is not to assign permission, we specify USAGE, which is a virtual right; it does not allow us to do anything. Then, we specify the WITH keyword, followed by the resource limits we want to set.

An example of how to know which account we are using and how many simultaneous connections it is allowed to establish is shown as follows:

MariaDB [(none)]> SELECT CURRENT_USER(), @@session.max_user_connections;
+----------------+--------------------------------+ 
| CURRENT_USER() | @@session.max_user_connections | 
+----------------+--------------------------------+ 
| root@localhost |                              0 | 
+----------------+--------------------------------+ 
1 row in set (0.00 sec) 

Traditionally, MariaDB and MySQL create a new thread every time a client connects to the server. This thread handling method is called one thread per connection. It is still the default method on most systems, with the only exception of Windows (starting from Vista).

In most cases, MariaDB is used for the workloads described with the On Line Transaction Processing (OLTP). This includes all cases where data is often modified, such as most websites and desktop applications. In such workloads, usually a client connects, executes a few fast statements, and closes the connection. If there are many users, this means that many clients connect to the server for a very short time. The one thread per connection method is not good for such workloads, because creating and destroying a thread has a cost in terms of CPU and memory, and the execution of a small series of fast operations may not be worth that cost.

In MariaDB 5.1, a new thread handling method was added called the pool of threads. The idea is that each thread can handle multiple connections. Each thread is part of a group. In MariaDB 5.1, the total number of threads is fixed. The pool of threads method has been refactored in MariaDB 5.5. This method now creates new threads when the current number of threads is insufficient and destroys threads when they are not useful anymore. It always tries to keep the number of threads reasonable; if it is too low, this method is not beneficial and if it is too high, there is a wastage of resources.

Note that the pool of threads method can queue several threads in the same group. This means that while this method avoids the overhead of a thread's creation and destruction, and generally speeds up the global performance of an OLTP workload, the individual statements or transactions may be slower. For example, a trivial query such as SELECT VERSION() should immediately return one row, but with the pool of threads method, it may have to wait for more complex queries to be executed.

The pool of threads is a bit different on Windows, because it uses the native threadpool implementation. On all other systems, MariaDB uses its own threadpool implementation. From a user's point of view, this mainly means that on Windows, the available configuration variables are not the same that are available on other systems. On Windows versions older than Vista, this feature does not work, and the one thread per connection method is always used.

thread_handling=pool-of-threads

thread_handling=one-thread-per-connection

MariaDB [(none)]> SELECT @@global.thread_handling;
+--------------------------+ 
| @@global.thread_handling | 
+--------------------------+ 
| pool-of-threads          | 
+--------------------------+ 
1 row in set (0.00 sec) 

MariaDB [(none)]> SHOW STATUS LIKE 'threadpool%';
+-------------------------+-------+ 
| Variable_name           | Value | 
+-------------------------+-------+ 
| Threadpool_idle_threads | 0     | 
| Threadpool_threads      | 0     | 
+-------------------------+-------+ 
2 rows in set (0.00 sec) 

SHOW VARIABLES LIKE 'thread_pool%';