Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Explore the authentication plugins available in MariaDB, such as ed25519, GSSAPI, and PAM, which provide flexible and secure methods for user verification.
Pluggable authentication allows MariaDB to use various authentication methods, enabling external validation, different hashing algorithms, and role-based access control.
When a user attempts to log in, the authentication plugin controls how MariaDB Server determines whether the connection is from a legitimate user.
When creating or altering a user account with the GRANT, CREATE USER or ALTER USER statements, you can specify the authentication plugin you want the user account to use, by providing the IDENTIFIED VIA clause. By default, when you create a user account without specifying an authentication plugin, MariaDB uses the mysql_native_password plugin.
You can specify multiple authentication plugins for each user account.
The root@localhost user created by has the ability to use two authentication plugins:
It is configured to try to use the authentication plugin. This allows the root@localhost user to log in without a password via the local Unix socket file defined by the system variable, as long as the login is attempted from a process owned by the operating system root user account.
If authentication fails with the authentication plugin, it is configured to try to use the authentication plugin. However, an invalid password is initially set, so in order to authenticate this way, a password must be set with .
The authentication process is a conversation between the server and a client. MariaDB implements both server-side and client-side authentication plugins.
MariaDB provides seven server-side authentication plugins:
MariaDB provides eight client-side authentication plugins:
MariaDB supports the following server options related to authentication plugins:
Most support command-line arguments related to client authentication plugins:
Developers who are using can implement similar functionality in their application by setting the following options with the function:
MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS
MYSQL_PLUGIN_DIR
MYSQL_DEFAULT_AUTH
For example:
supports the following installation options related to authentication plugins:
MariaDB has extended the SQL standard , , and statements, so that they support specifying different authentication plugins for specific users. An authentication plugin can be specified with these statements by providing the IDENTIFIED VIA clause. Examples:
The optional USING clause allows users to provide an authentication string to a plugin. The authentication string's format and meaning is completely defined by the plugin.
For example, for the authentication plugin, the authentication string should be a password hash:
Since is the default authentication plugin, the above is just another way of saying the following:
In contrast, for the authentication plugin, the authentication string should refer to a :
A user account can be associated with multiple authentication plugins.
To configure the root@localhost user account to try the authentication plugin, followed by the authentication plugin as a backup, execute the following query:
See for more information.
Not all server-side authentication plugins are installed by default. If a specific server-side authentication plugin is not installed by default, then you can find the installation procedure on the documentation page for the specific authentication plugin.
The following server-side authentication plugins are installed by default:
The and authentication plugins authentication plugins are installed by default in all builds.
The authentication plugin is installed by default in all builds on Unix and Linux.
The authentication plugin is installed by default in all builds on Windows.
The following server-side authentication plugins are installed by default:
The and authentication plugins are installed by default in all builds.
The authentication plugin is installed by default in new installations that use the packages provided by Debian's default repositories and Ubuntu's default repositories in Ubuntu. See for more information.
The authentication plugin is installed by default in all builds on Windows.
Client-side authentication plugins do not need to be installed in the same way that server-side authentication plugins do. If the client uses either the libmysqlclient or library, then the library automatically loads client-side authentication plugins from the library's plugin directory whenever they are needed.
Most support the --plugin-dir command line argument that can be used to set the path to the library's plugin directory:
Developers who are using can implement similar functionality in their application by setting the MYSQL_PLUGIN_DIR option with the function. Example:
If your client encounters errors similar to the following error, you may need to set the path to the library's plugin directory:
If the client does not use either the libmysqlclient or library, then you will have to determine which authentication plugins are supported by the specific client library used by the client.
If the client uses either the libmysqlclient or library, but the client is not bundled with either library's optional client authentication plugins, then you can only use the conventional authentication plugins (like and ) and the non-conventional authentication plugins that don't require special client-side authentication plugins (like and ).
The authentication plugin is currently the default authentication plugin in all versions of MariaDB if the system variable is set to 0, which is the default.
On a system with the system variable set to 0, this means that if you create a user account with either the or statements, and if you do not specify an authentication plugin with theIDENTIFIED VIAclause, then MariaDB will use the [mysql_native_password](authentication-plugin-mysql_native_password.md) authentication plugin for the user account.
Creating a user account like this, it uses the authentication plugin:
The same is true for this user account:
The authentication plugin becomes the default authentication plugin in all versions of MariaDB, if the system variable is explicitly set to 1.
The authentication plugin is not considered secure. It is recommended to avoid using this authentication plugin. To help prevent undesired use of the authentication plugin, the server supports the system variable that configures the server to refuse connections trying to use the authentication plugin.
Most support secure_auth.
Developers using can implement similar functionality in their application by setting the MYSQL_SECURE_AUTH option with the function:
The default client-side authentication plugin depends on these factors:
If a client doesn't explicitly set the default client-side authentication plugin, the client determines which authentication plugin to use, by checking the length of the scramble in the server's handshake packet.
If the server's handshake packet contains a 9-byte scramble, the client defaults to the authentication plugin.
If the server's handshake packet contains a 20-byte scramble, the client defaults to the authentication plugin.
Most support the --default-auth command-line argument that sets the default client-side authentication plugin:
Developers using can implement similar functionality in applications, by setting the MYSQL_DEFAULT_AUTH option with the function:
If you know that your user account is configured to require a client-side authentication plugin other than or , this helps speed up your connection process when you explicitly set the default client-side authentication plugin.
According to the , the server first sends the handshake packet to the client, then the client replies with a packet containing the user name of the user account that is requesting access. The server handshake packet initially tells the client to use the default server authentication plugin, and the client reply initially tells the server that it will use the default client authentication plugin.
However, the server-side and client-side authentication plugins mentioned in these initial packets may not be the correct ones for this specific user account. The server only knows what authentication plugin to use for this specific user account after reading the user name from the client reply packet and finding the appropriate row for the user account in either the table or the table, depending on the MariaDB version.
If the server finds that either the server-side or client-side default authentication plugin does not match the actual authentication plugin that should be used for the given user account, then the server restarts the authentication on either the server side or the client side.
This means that, if you know what client authentication plugin your user account requires, then you can avoid an unnecessary authentication restart and you can save two packets and two round-trips.between the client and server by configuring your client to use the correct authentication plugin by default.
mysql_native_passwordThe authentication plugin uses the same password hashing algorithm used by the function when is set. This hashing algorithm is based on .
mysql_old_passwordThe authentication plugin uses the same hashing algorithm used by the function and by the function when is set.
ed25519The authentication plugin uses the to securely store users' passwords and to authenticate users. The algorithm is the same one that is . It is based on the elliptic curve and code created by .
gssapiThe authentication plugin allows authenticating with services that use the . Windows has a slightly different but very similar API called .
On Windows, this authentication plugin supports and authentication. Windows authentication is supported regardless of whether a is used in the environment.
On Unix systems, the most dominant GSSAPI service is . However, it is less commonly used on Unix systems than it is on Windows. Regardless, this authentication plugin also supports Kerberos authentication on Unix.
The authentication plugin is most often used for authenticating with .
pamThe authentication plugin allows MariaDB to offload user authentication to the system's framework. PAM is an authentication framework used by Linux, FreeBSD, Solaris, and other Unix-like operating systems.
unix_socketThe authentication plugin allows the user to use operating system credentials when connecting to MariaDB via the local Unix socket file. This Unix socket file is defined by the system variable.
The authentication plugin works by calling the system call with the SO_PEERCRED socket option, which allows it to retrieve the uid of the process that is connected to the socket. It is then able to get the user name associated with that uid. Once it has the user name, it will authenticate the connecting user as the MariaDB account that has the same user name.
For example:
In this example, a user serg is already logged into the operating system and has full shell access. The user has already authenticated with the operating system and the MariaDB account is configured to use the authentication plugin, so the user does not need to authenticate again for the database. MariaDB accepts the user's operating system credentials and allows connecting. However, any attempt to connect to the database as another operating system user will be denied.
named_pipeThe authentication plugin allows the user to use operating system credentials when connecting to MariaDB via named pipe on Windows. Named pipe connections are enabled by the system variable.
The authentication plugin works by using and calling GetUserName() to retrieve the user name of the process that is connected to the named pipe. Once it has the user name, it authenticates the connecting user as the MariaDB account that has the same user name:
The authentication plugin API is extensively documented in the in the following files:
mysql/plugin_auth.h (server part)
mysql/client_plugin.h (client part)
mysql/plugin_auth_common.h (common parts)
The MariaDB also contains some authentication plugins that are intended explicitly to be examples for developers. They are located in plugin/auth_examples.
The definitions of two example authentication plugins called two_questions and three_attempts can be seen in plugin/auth_examples/dialog_examples.c. These authentication plugins demonstrate how to communicate with the user using the client authentication plugin.
The two_questions authentication plugin asks the user for a password and a confirmation ("Are you sure?").
The three_attempts authentication plugin gives the user three attempts to enter a correct password.
The password for both of these plugins should be specified in the plain text in the USING clause:
The client authentication plugin, strictly speaking, is not part of the client-server or authentication plugin API. But it can be loaded into any client application that uses the libmysqlclient or libraries. This authentication plugin provides a way for the application to customize the UI of the dialog function.
In order to use the client authentication plugin to communicate with the user in a customized way, the application will need to implement a function with the following signature:
The function takes the following arguments:
The connection handle.
A question "type", which has one of the following values:
1 - Normal question
2
The function returns a pointer to a string of characters, as entered by the user. It may be stored in buf or allocated with malloc().
By using this function, a GUI application can open a dialog window, and a network application can send the question over the network, as required. If no mysql_authentication_dialog_ask function is provided by the application, the client authentication plugin falls back to and .
Providing this callback is particularly important on Windows, because Windows GUI applications have no associated console and the default dialog function will not be able to reach the user. An example of Windows GUI client that does it correctly is .
This page is licensed: CC BY-SA / Gnu FDL
unix_socket (Unix only)
named_pipe (Windows only)
MYSQL_SECURE_AUTHA prompt.
A buffer.
The length of the buffer.
If set to 1 (0 is default), MariaDB reverts to using the mysql_old_password authentication plugin by default for newly created users and passwords, instead of the mysql_native_password authentication plugin.
Path to the plugin directory. For security reasons, either make sure this directory can only be read by the server, or set secure_file_priv.
The lowest acceptable plugin maturity. MariaDB will not load plugins less mature than the specified level.
Connections will be blocked if they use the mysql_old_password authentication plugin.
--connect-expired-password
Notify the server that this client is prepared to handle expired password sandbox mode even if --batch was specified.
--default-auth=name
Default authentication client-side plugin to use.
--plugin-dir=path
Directory for client-side plugins.
--secure-auth
Refuse to connect to the server if the server uses the mysql_old_password authentication plugin. This mode is off by default, which is different from MySQL.
--auth-root-authentication-method={normal | socket}
If set to normal (the default), it creates a root@localhost account that authenticates with the mysql_native_password authentication plugin and that has no initial password set, which can be insecure. If set to socket, it creates a root@localhost account that authenticates with the unix_socket authentication plugin. Set to normal by default.
--auth-root-socket-user=USER
Used with --auth-root-authentication-method=socket. It specifies the name of the second account to create with SUPER privileges in addition to root, as well as of the system account allowed to access it. Defaults to the value of --user.
--plugin-dir=path
Directory for client-side plugins.
If set to 1 (0 is default), MariaDB reverts to using the mysql_old_password authentication plugin by default for newly created users and passwords, instead of the mysql_native_password authentication plugin.
Connections are blocked if they use the mysql_old_password authentication plugin.
--default-auth=name
Default authentication client-side plugin to use.
mysql_optionsv(mysql, MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS, 1);
mysql_optionsv(mysql, MYSQL_DEFAULT_AUTH, "name");
mysql_optionsv(mysql, MYSQL_PLUGIN_DIR, "path");
mysql_optionsv(mysql, MYSQL_SECURE_AUTH, 1);GRANT <privileges> ON <level> TO <user>
IDENTIFIED VIA <plugin> [ USING <string> ]CREATE USER <user>
IDENTIFIED VIA <plugin> [ USING <string> ]ALTER USER <user>
IDENTIFIED VIA <plugin> [ USING <string> ]CREATE USER mysqltest_up1
IDENTIFIED VIA mysql_native_password USING '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB';CREATE USER mysqltest_up1
IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB';CREATE USER mysqltest_up1
IDENTIFIED VIA pam USING 'mariadb';CREATE USER root@localhost
IDENTIFIED VIA unix_socket
OR mysql_native_password USING PASSWORD("verysecret");mysql_optionsv(mysql, MYSQL_PLUGIN_DIR, "path");ERROR 2059 (HY000): Authentication plugin 'dialog' cannot be loaded: /usr/lib/mysql/plugin/dialog.so: cannot open shared object file: No such file or directoryCREATE USER username@hostname;CREATE USER username@hostname IDENTIFIED BY 'notagoodpassword';mysql_optionsv(mysql, MYSQL_SECURE_AUTH, 1);mysql_optionsv(mysql, MYSQL_DEFAULT_AUTH, "name");$ mysql -uroot
MariaDB []> CREATE USER serg IDENTIFIED VIA unix_socket;
MariaDB []> CREATE USER monty IDENTIFIED VIA unix_socket;
MariaDB []> quit
Bye
$ whoami
serg
$ mysql --user=serg
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.2.0-MariaDB-alpha-debug Source distribution
MariaDB []> quit
Bye
$ mysql --user=monty
ERROR 1045 (28000): Access denied for user 'monty'@'localhost' (using password: NO)CREATE USER wlad IDENTIFIED VIA named_pipe;
CREATE USER monty IDENTIFIED VIA named_pipe;
quit
C:\>echo %USERNAME%
wlad
C:\> mysql --user=wlad --protocol=PIPE
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.12-MariaDB-debug Source distribution
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> quit
Bye
C:\> mysql --user=monty --protocol=PIPE
ERROR 1698 (28000): Access denied for user 'monty'@'localhost'CREATE USER insecure IDENTIFIED VIA two_questions USING 'notverysecret';extern "C" char *mysql_authentication_dialog_ask(
MYSQL *mysql, int type, const char *prompt, char *buf, int buf_len)Learn about authentication with Pluggable Authentication Modules (PAM) in MariaDB Server. This section details how to integrate MariaDB with PAM for centralized and flexible user authentication.
The SHA-256 authentication plugin uses the SHA-256 hashing algorithm for password storage, offering stronger security than the default SHA-1 method.
MySQL 5.6 added support for the sha256_password authentication plugin, and MySQL 8.0 also added support for the caching_sha2_password authentication plugin.
The caching_sha2_password plugin is now the default authentication plugin in MySQL 8.0.4 and above, based on the value of the default_authentication_plugin system variable.
MariaDB Server does not support the plugin. A caching_sha2_password authentication plugin was added in MariaDB and . See for more information.
Reasons for not supporting the SHA-256 plugin:
To use the protocol, you have to distribute the server's public key to all MariaDB users, which can be cumbersome and impractical.
The server receives the password in clear text, which can cause problems if the user connects to a malicious server.
If you are migrating from a MySQL instance that is using SHA-256 authentication, you have to change the SHA-256 authentication to mysql_native_authentication :
For clients that use the library, MariaDB provides client authentication plugins that are compatible with MySQL's SHA-256 authentication plugins:
sha256_password
caching_sha256_password
When connecting with a to a server, using a user account that authenticates with the sha256_password or caching_sha256_password authentication plugin, you may need to tell the client where to find the relevant client authentication plugin by specifying the --plugin-dir option:
For clients that use MariaDB's libmysqlclient library instead of , those authentication plugins are not supported.
sha256_passwordThe sha256_password client authentication plugin is compatible with MySQL's authentication plugin, which was added in MySQL 5.6.
caching_sha256_passwordThe caching_sha256_password client authentication plugin is compatible with MySQL's authentication plugin, which was added in MySQL 8.0.
The caching_sha2_password plugin is now the default authentication plugin in MySQL 8.0.4 and above, based on the value of the system variable.
supports sha256_password and caching_sha2_password authentication using the mentioned in the previous section.
It has supported the sha256_password client authentication plugin since MariaDB Connector/C 3.0.2. See for more information.
It has supported the caching_sha256_password client authentication plugin since MariaDB Connector/C 3.0.8 and MariaDB Connector/C 3.1.0. See for more information.
supports sha256_password and caching_sha2_password authentication using the mentioned in the previous section.
It has supported sha256_password and caching_sha2_password authentication since MariaDB Connector/ODBC 3.1.4. See for more information.
supports sha256_password and caching_sha2_password authentication since MariaDB Connector/J 2.5.0. See and for more information.
note: The version 3.x being a rewrite of the connector, only caching_sha2_password is implemented, since sha256_password is only implemented on EOL version.
supports sha256_password and caching_sha2_password authentication since MariaDB Connector/Node.js 2.5.0. See and for more information.
contains the plans to use if we ever decide to support these protocols.
This page is licensed: CC BY-SA / Gnu FDL
The mysql_native_password plugin is the default legacy authentication method, using SHA-1 hashing to verify passwords stored in the internal mysql.user table.
The mysql_native_password authentication plugin is the default authentication plugin that will be used for an account created when no authentication plugin is explicitly mentioned and is set. It uses the password hashing algorithm introduced in MySQL 4.1, which is also used by the function when is set. This hashing algorithm is based on .
It is not recommended to use the mysql_native_password authentication plugin for new installations that require high password security. If someone is able to both listen to the connection protocol and get a copy of the mysql.user table, then the person would be able to use this information to connect to the MariaDB server. The authentication plugin is a more modern authentication plugin that provides simple password authentication using a more secure algorithm.
The mysql_native_password authentication plugin is statically linked into the server, so no installation is necessary.
The easiest way to create a user account with the mysql_native_password authentication plugin is to make sure that old_passwords=0 is set, and then create a user account via CREATE USER that does not specify an authentication plugin, but does specify a password via the IDENTIFIED BY clause:
If SQL_MODE does not have NO_AUTO_CREATE_USER set, then you can also create the user account via GRANT:
You can also create the user account by providing a password hash via the IDENTIFIED BY PASSWORD clause, and MariaDB will validate whether the password hash is one that is compatible with mysql_native_password:
Similar to all other authentication plugins, you could also specify the name of the plugin in the IDENTIFIED VIA clause while providing the password hash as the USING clause:
You can change a user account's password with the SET PASSWORD statement while providing the plain-text password as an argument to the PASSWORD() function:
You can also change the user account's password with the ALTER USER statement. You would have to make sure that old_passwords=0 is set, and then you would have to specify a password via the IDENTIFIED BY clause:
For clients that use the libmysqlclient or libraries, MariaDB provides one client authentication plugin that is compatible with the mysql_native_password authentication plugin:
mysql_native_password
When connecting with a client or utility to a server as a user account that authenticates with the mysql_native_password authentication plugin, you may need to tell the client where to find the relevant client authentication plugin by specifying the --plugin-dir option:
However, the mysql_native_password client authentication plugin is generally statically linked into client libraries like libmysqlclient or , so this is not usually necessary.
The mysql_native_password client authentication plugin hashes the password before sending it to the server.
The mysql_native_password authentication plugin is one of the conventional authentication plugins, so all client libraries should support it.
For compatibility reasons, the mysql_native_password authentication plugin tries to read the password hash from both the Password and authentication_string columns in the mysql.user table. This has caused issues in the past if one of the columns had a different value than the other.
CREATE USER, ALTER USER, GRANT, and SET PASSWORD set the Password and authentication_string columns in the mysql.user table whenever an account's password is changed.
CREATE USER, ALTER USER, GRANT, and SET PASSWORD do not set the Password and authentication_string columns in the mysql.user table whenever an account's password is changed.
ed25519 secure connection plugin
This page is licensed: CC BY-SA / Gnu FDL
This plugin provides backward compatibility for pre-4.1 clients using an older, insecure password hashing algorithm and should not be used for new installations.
The mysql_old_password authentication plugin is the default authentication plugin that is used for an account created when no authentication plugin is explicitly mentioned and old_passwords=1 is set. It uses the pre-MySQL 4.1 password hashing algorithm, which is also used by the OLD_PASSWORD() function and by the PASSWORD() function when old_passwords=1 is set.
It is not recommended to use the mysql_old_password authentication plugin for new installations. The password hashing algorithm is no longer secure, and the plugin is primarily provided for backward compatibility. The ed25519 authentication plugin is a more modern authentication plugin that provides simple password authentication.
The mysql_old_password authentication plugin is statically linked into the server, so no installation is necessary.
The easiest way to create a user account with the mysql_old_password authentication plugin is to make sure that is set, and create a user account via that does not specify an authentication plugin, but instead specifies a password via the clause:
If does not have NO_AUTO_CREATE_USER set, then you can also create the user via :
You can also create the user account by providing a password hash via the clause, and MariaDB validates whether the password hash is one that is compatible with mysql_old_password:
Similar to all other , you could also specify the name of the plugin in the IDENTIFIED VIA clause, while providing the password hash as the USING clause:
You can change a user account's password with the statement, while providing the plain-text password as an argument to the function:
You can also change the user account's password with the statement. You have to make sure that is set, and you have to specify a password via the clause:
For clients that use the libmysqlclient or libraries, MariaDB provides one client authentication plugin that is compatible with the mysql_old_password authentication plugin:
mysql_old_password
When connecting with a to a server as a user account that authenticates with the mysql_old_password authentication plugin. You may need to tell the client where to find the relevant client authentication plugin by specifying the --plugin-dir option:
However, the mysql_old_password client authentication plugin is generally statically linked into client libraries like libmysqlclient or , so this is not usually necessary.
mysql_old_passwordThe mysql_old_password client authentication plugin hashes the password before sending it to the server.
The mysql_old_password authentication plugin is one of the conventional authentication plugins, so all client libraries should support it.
This page is licensed: CC BY-SA / Gnu FDL
SET old_passwords=0;
CREATE USER username@hostname IDENTIFIED BY 'mariadb';SET old_passwords=0;
GRANT SELECT ON db.* TO username@hostname IDENTIFIED BY 'mariadb';SET old_passwords=0;
SELECT PASSWORD('mariadb');
+-------------------------------------------+
| PASSWORD('mariadb') |
+-------------------------------------------+
| *54958E764CE10E50764C2EECBB71D01F08549980 |
+-------------------------------------------+
CREATE USER username@hostname
IDENTIFIED BY PASSWORD '*54958E764CE10E50764C2EECBB71D01F08549980';CREATE USER username@hostname
IDENTIFIED VIA mysql_native_password USING '*54958E764CE10E50764C2EECBB71D01F08549980';SET PASSWORD = PASSWORD('new_secret')SET old_passwords=0;
ALTER USER username@hostname IDENTIFIED BY 'new_secret';mysql --plugin-dir=/usr/local/mysql/lib64/mysql/plugin --user=aliceALTER USER user_name IDENTIFIED WITH mysql_native_password BY 'new_password'mysql --plugin-dir=/usr/local/mysql/lib64/mysql/plugin --user=aliceSET old_passwords=1;
CREATE USER username@hostname IDENTIFIED BY 'mariadb';SET old_passwords=1;
GRANT SELECT ON db.* TO username@hostname IDENTIFIED BY 'mariadb';SET old_passwords=1;
Query OK, 0 rows affected (0.000 sec)
SELECT PASSWORD('mariadb');
+---------------------+
| PASSWORD('mariadb') |
+---------------------+
| 021bec665bf663f1 |
+---------------------+
1 row in set (0.000 sec)
CREATE USER username@hostname IDENTIFIED BY PASSWORD '021bec665bf663f1';
Query OK, 0 rows affected (0.000 sec)CREATE USER username@hostname IDENTIFIED VIA mysql_old_password USING '021bec665bf663f1';
Query OK, 0 rows affected (0.000 sec)SET PASSWORD = PASSWORD('new_secret')SET old_passwords=1;
ALTER USER username@hostname IDENTIFIED BY 'new_secret';mysql --plugin-dir=/usr/local/mysql/lib64/mysql/plugin --user=aliceThis guide shows how to authenticate database users using local Unix accounts and map Unix groups to MariaDB users with the PAM plugin.
We walk through the configuration of PAM authentication using the pam authentication plugin and user and group mapping with the pam_user_map PAM module. The primary authentication will be handled by the pam_unix PAM module, which performs standard Unix password authentication.
In this walkthrough, we are going to assume the following hypothetical requirements:
The Unix user foo should be mapped to the MariaDB user bar. (foo: bar)
Any Unix user in the Unix group dba should be mapped to the MariaDB user dba. (@dba: dba)
Let's go ahead and create the Unix users and groups that we are using for this hypothetical scenario.
First, let's create the foo user and a couple users to go into the dba group. Note that each of these users needs a password.
Next, let's create the dba group and add our two users to it:
We also need to create Unix users with the same name as the bar and dba MariaDB users. See to read more about why. No one will be logging in as these users, so they do not need passwords.
Next, let's .
Before the module can be compiled from source, we may need to install some dependencies.
On RHEL, CentOS, and other similar Linux distributions that use , we need to install gcc and pam-devel:
On Debian, Ubuntu, and other similar Linux distributions that use , we need to install gcc and libpam0g-dev:
And then we can build and install the library with the following:
Next, let's based on our hypothetical requirements.
The configuration file for the pam_user_map PAM module is /etc/security/user_map.conf. Based on our hypothetical requirements, ours would look like:
Next, let's .
Log into the MariaDB Server and execute the following:
Next, let's . We will call our service mariadb, so our PAM service configuration file will be located at /etc/pam.d/mariadb on most systems.
Since we are only doing Unix authentication with the pam_unix PAM module and group mapping with the pam_user_map PAM module, our configuration file would look like this:
The pam_unix PAM module adds on a lot of systems. We basically have to give the user that runs mysqld access to /etc/shadow.
If the mysql user is running mysqld, then we can do that by executing the following:
The server needs to be restarted for this change to take affect.
Next, let's . Remember that our PAM service is called mariadb.
First, let's create the MariaDB user for the user mapping: foo: bar . This means we need to create a bar user:
Next, let's create the MariaDB user for the group mapping: @dba: dba . This means that we need to create a dba user:
Next, to allow for the user and group mapping, we need to that is also able to PROXY as the bar and dba users. Before we can create the proxy user, we might need to :
Finally, let's create the anonymous proxy user:
Next, let's test out our configuration by . We can verify this by logging in as each of our users and comparing the return value of , which is the original user name and the return value of , which is the authenticated user name.
First, let's test our foo user:
We can verify that our foo Unix user was properly mapped to the bar MariaDB user by looking at the return value of CURRENT_USER().
Next, let's test our alice user in the dba group:
Finally, let's test our bob user in the dba group:
We can verify that our alice and bob Unix users in the dba Unix group were properly mapped to the dba MariaDB user by looking at the return values of CURRENT_USER().
This page is licensed: CC BY-SA / Gnu FDL
The named_pipe authentication plugin allows Windows users connecting via named pipes to authenticate using their operating system credentials without a password.
The named_pipe authentication plugin allows the user to use operating system credentials when connecting to MariaDB via named pipe on Windows. Named pipe connections are enabled by the named_pipe system variable.
The named_pipe authentication plugin works by using named pipe impersonation and calling GetUserName() to retrieve the user name of the process that is connected to the named pipe. Once it has the user name, it authenticates the connecting user as the MariaDB account that has the same user name.
Although the plugin's shared library is distributed with MariaDB by default, the plugin is not actually installed by MariaDB by default. There are two methods that can be used to install the plugin with MariaDB.
The first method can be used to install the plugin without restarting the server. You can install the plugin dynamically by executing or :
The second method can be used to tell the server to load the plugin when it starts up. The plugin can be installed this way by providing the or the options. This can be specified as a command-line argument to mariadbd, or it can be specified in a relevant server in an :
You can uninstall the plugin dynamically by executing or :
If you installed the plugin by providing the or the options in a relevant server in an , those options should be removed to prevent the plugin from being loaded the next time the server is restarted.
To create a user account via , specify the name of the plugin in the clause:
If does not have NO_AUTO_CREATE_USER set, then you can also create the user account via :
The named_pipe authentication plugin does not require any specific client authentication plugins. It should work with all clients.
The named_pipe authentication plugin does not require any special support in client libraries. It should work with all client libraries.
In this example, a user wlad is already logged into the system. Because he has identified himself to the operating system, he does not need to do it again for the database — MariaDB trusts the operating system credentials. However, he cannot connect to the database as another user.
named_pipeDescription: Controls how the server should treat the plugin when the server starts up.
Valid values are:
OFF - Disables the plugin without removing it from the table.
This page is licensed: CC BY-SA / Gnu FDL
ON - Enables the plugin. If the plugin cannot be initialized, then the server will still continue starting up, but the plugin will be disabled.FORCE - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error.
FORCE_PLUS_PERMANENT - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error. In addition, the plugin cannot be uninstalled with UNINSTALL SONAME or UNINSTALL PLUGIN while the server is running.
See Plugin Overview: Configuring Plugin Activation at Server Startup for more information.
There may be ambiguity between this option and the named_pipe system variable. See MDEV-19625 about that.
Command line: --named-pipe=value
Data Type: enumerated
Default Value: ON
Valid Values: OFF, ON, FORCE, FORCE_PLUS_PERMANENT
Introduced: MariaDB 10.1.11
sudo useradd foo
sudo passwd foo
sudo useradd alice
sudo passwd alice
sudo useradd bob
sudo passwd bobsudo groupadd dba
sudo usermod -a -G dba alice
sudo usermod -a -G dba bobsudo useradd bar
sudo useradd dba -g dbasudo yum install gcc pam-develsudo apt-get install gcc libpam0g-devwget https://raw.githubusercontent.com/MariaDB/server/10.4/plugin/auth_pam/mapper/pam_user_map.c
gcc pam_user_map.c -shared -lpam -fPIC -o pam_user_map.so
sudo install --mode=0755 pam_user_map.so /lib64/security/foo: bar
@dba:dbaINSTALL SONAME 'auth_pam';auth required pam_unix.so audit
auth required pam_user_map.so
account required pam_unix.so auditsudo groupadd shadow
sudo usermod -a -G shadow mysql
sudo chown root:shadow /etc/shadow
sudo chmod g+r /etc/shadowCREATE USER 'bar'@'%' IDENTIFIED BY 'strongpassword';
GRANT ALL PRIVILEGES ON *.* TO 'bar'@'%' ;CREATE USER 'dba'@'%' IDENTIFIED BY 'strongpassword';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'%' ;DELETE FROM mysql.db WHERE User='' AND Host='%';
FLUSH PRIVILEGES;CREATE USER ''@'%' IDENTIFIED VIA pam USING 'mariadb';
GRANT PROXY ON 'bar'@'%' TO ''@'%';
GRANT PROXY ON 'dba'@'%' TO ''@'%';$ mysql -u foo -h 172.30.0.198
[mariadb] Password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 22
Server version: 10.3.10-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SELECT USER(), CURRENT_USER();
+------------------------------------------------+----------------+
| USER() | CURRENT_USER() |
+------------------------------------------------+----------------+
| foo@ip-172-30-0-198.us-west-2.compute.internal | bar@% |
+------------------------------------------------+----------------+
1 row in set (0.000 sec)$ mysql -u alice -h 172.30.0.198
[mariadb] Password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.3.10-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SELECT USER(), CURRENT_USER();
+--------------------------------------------------+----------------+
| USER() | CURRENT_USER() |
+--------------------------------------------------+----------------+
| alice@ip-172-30-0-198.us-west-2.compute.internal | dba@% |
+--------------------------------------------------+----------------+
1 row in set (0.000 sec)$ mysql -u bob -h 172.30.0.198
[mariadb] Password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 10.3.10-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SELECT USER(), CURRENT_USER();
+------------------------------------------------+----------------+
| USER() | CURRENT_USER() |
+------------------------------------------------+----------------+
| bob@ip-172-30-0-198.us-west-2.compute.internal | dba@% |
+------------------------------------------------+----------------+
1 row in set (0.000 sec)INSTALL SONAME 'auth_named_pipe';[mariadb]
...
plugin_load_add = auth_named_pipeUNINSTALL SONAME 'auth_named_pipe';CREATE USER username@hostname IDENTIFIED VIA named_pipe;GRANT SELECT ON db.* TO username@hostname IDENTIFIED VIA named_pipe;CREATE USER wlad IDENTIFIED VIA named_pipe;
CREATE USER monty IDENTIFIED VIA named_pipe;
quit
C:\>echo %USERNAME%
wlad
C:\> mysql --user=wlad --protocol=PIPE
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.12-MariaDB-debug Source distribution
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> quit
Bye
C:\> mysql --user=monty --protocol=PIPE
ERROR 1698 (28000): Access denied for user 'monty'@'localhost'The ed25519 authentication plugin provides high-security password authentication using the Elliptic Curve Digital Signature Algorithm, a modern alternative to SHA-1.
MySQL has used SHA-1 based authentication since version 4.1. The authentication plugin is called mysql_native_password. Over the years as computers became faster, new attacks on SHA-1 were being developed. Nowadays SHA-1 is no longer considered as secure as it was in 2001. That's why the ed25519 authentication plugin was created.
The ed25519 authentication plugin uses Elliptic Curve Digital Signature Algorithm (ECDSA) to securely store users' passwords and to authenticate users. The ed25519 algorithm is the same one that is used by OpenSSH. It is based on the elliptic curve and code created by Daniel J. Bernstein.
Although the plugin's shared library is distributed by default with MariaDB, with a file name of auth_ed25519.so (Unix) or auth_ed25519.dll (Windows), the plugin is not actually installed by MariaDB by default. There are two methods that can be used to install the plugin with MariaDB.
The first method can be used to install the plugin without restarting the server. You can install the plugin dynamically by executing or :
The second method can be used to tell the server to load the plugin when it starts up. The plugin can be installed this way by providing the or the options. This can be specified as a command-line argument to or it can be specified in a relevant server in an :
You can uninstall the plugin dynamically by executing or :
If you installed the plugin by providing the or the options in a relevant server in an , those options must be removed to prevent the plugin from being loaded the next time the server is restarted.
You can create a user account by executing the statement and providing the clause followied by the name of the plugin, ed25519, and providing the USING clause followed by the function, with the plain-text password as an argument:
If does not have NO_AUTO_CREATE_USER set, then you can also create the user account via :
The function and statements don't work with the ed25519 authentication plugin. Instead, you have to use the that comes with the authentication plugin to calculate the password hash:
Now you can calculate a password hash by executing this query:
Now you can use it to create the user account using the new password hash. As with any password, you should always use a complex password that isn't easy to guess. If you don't, if anyone gets access to the stored passwords in the mysql.user table, they could use to figure out the original password.
To create a user account via , specify the name of the plugin in the clause while providing the password hash as the USING clause:
If does not have NO_AUTO_CREATE_USER set, you can also create the user account via :
Note that users require a password in order to be able to connect. It is possible to create users without specifying a password, but they will be unable to connect.
You can change a user account's password by executing the statement followed by the function and providing the plain-text password as an argument:
You can also change the user account's password with the statement. You would have to specify the name of the plugin in the clause while providing the plain-text password as an argument to the PASSWORD() function in the USING clause:
The PASSWORD() function and statement did not work with the ed25519 authentication plugin. Instead, you would have to use the that comes with the authentication plugin to calculate the password hash:
Now you can calculate a password hash by executing this query:
Now you can change the user account's password using the new password hash.
You can change the user account's password with the statement. You have to specify the name of the plugin in the clause, while providing the password hash as the USING clause:
For clients that use the libmysqlclient or libraries, MariaDB provides one client authentication plugin that is compatible with the ed25519 authentication plugin:
client_ed25519
When connecting with a to a server as a user account that authenticates with the ed25519 authentication plugin, you may need to tell the client where to find the relevant client authentication plugin by specifying the --plugin-dir option:
client_ed25519The client_ed25519 client authentication plugin hashes and signs the password using the before sending it to the server.
supports ed25519 authentication using the client authentication plugins mentioned in the previous section.
supports ed25519 authentication using the client authentication plugins mentioned in the previous section.
supports ed25519 authentication.
supports ed25519 authentication.
supports ed25519 authentication.
The connector implemented support for this authentication plugin in a separate package called . After the package is installed, your application must call Ed25519AuthenticationPlugin.Install to enable it.
ed25519Description: Controls how the server should treat the plugin when it starts up.
Valid values are:
OFF - Disables the plugin without removing it from the table.
This page is licensed: CC BY-SA / Gnu FDL
The pam_user_map PAM module allows administrators to map external PAM users and groups to specific MariaDB accounts for flexible authorization management.
Even when using the authentication plugin, the authenticating PAM user account still needs to exist in MariaDB, and the account needs to have privileges in the database. Creating these MariaDB accounts and making sure the privileges are correct can be a lot of work. To decrease the amount of work involved, some users would like to be able to map a PAM user to a different MariaDB user. For example, let’s say that alice and bob are both DBAs. It would be nice if each of them could log into MariaDB with their own PAM username and password, while MariaDB sees both of them as the same dba user. That way, there is only one MariaDB account to keep track of.
Although most PAM modules usually do not do things like this, PAM supports the ability to change the user name in the process of authentication.The MariaDB pam authentication plugin fully supports this feature of PAM.
Rather than building user and group mapping into the pam authentication plugin, MariaDB thought that it would cover the most use cases and offer the most flexibility to offload this functionality to an external PAM module. The pam_user_map PAM module was implemented by MariaDB to facilitate this. This PAM module can be configured in the PAM service used by the pam authentication plugin, just like other PAM modules.
Unlike MariaDB, MySQL and Percona implemented group mapping in their PAM authentication plugins. If you've read through MySQL's PAM authentication documentation on group mapping or Percona's PAM authentication documentation on group mapping, you've probably seen syntax where the group mappings are provided in the CREATE USER statement like this:
Since MariaDB's user and group mapping is performed by an external PAM module, MariaDB's pam authentication plugin does not support this syntax. Instead, the user and group mappings for the pam_user_map PAM module are configured in an external configuration file. This is discussed in a later section.
The pam_user_map PAM module gets installed as part of all our MariaDB server packages.
Some Linux distributions have not picked up this change in their own packages yet. When using such an installation, it may be necessary to compile the PAM module from source as described in the next section, or to manually extract it from one of our server packages and copy it to the target system.
Installing Compilation Dependencies
Before the module can be compiled from source, you may need to install some dependencies.
On RHEL, CentOS, and other similar Linux distributions that use RPM packages, you need to install gcc, pam-devel and MariaDB-devel:
On Debian, Ubuntu, and other similar Linux distributions that use DEB packages, you need to install gcc, libpam0g-dev:
Compiling and Installing the pam_user_map PAM Module
The pam_user_map PAM module can be built by downloading plugin/auth_pam/mapper/pam_user_map.c file from the MariaDB source tree and compiling it after minor adjustments. Once it is built, it can be installed to the system's PAM module directory, which is typically /lib64/security/:
You also need to adjust the major version number in the URL on the first line to match your installed MariaDB version, and the #-I include path argument on the gcc line, as depending on operating system and MariaDB server version, the plugin_auth_common.h file may be installed in a directory other than /usr/include/mysql/ .
The pam_user_map PAM module uses the configuration file at the path /etc/security/user_map.conf to determine its user and group mappings. The file's format is described below.
To map a specific PAM user to a specific MariaDB user:
Or to map any PAM user in a specific PAM group to a specific MariaDB user, the group name is prefixed with @:
For example, here is an example /etc/security/user_map.conf:
With user and group mapping, configuring PAM is done similar to how it is normally done with the pam authentication plugin. However, when configuring the PAM service, you will have to add an auth line for the pam_user_map PAM module to the service's PAM configuration file:
With user and group mapping, creating users is done similar to how it is normally done with the pam authentication plugin. However, one major difference is that you will need to GRANT the PROXY privilege on the mapped user to the original user.
Consider having the following configured in /etc/security/user_map.conf:
Then you could execute the following to grant the relevant privileges:
Note that the ''@'%' account is a special catch-all anonymous account. Any login by a user that has no more specific account match in the system will be matched by this anonymous account.
Also note that you might not be able to create the ''@'%' anonymous account by default on some systems without doing some extra steps first. See Fixing a Legacy Default Anonymous Account for more information.
In case any user mapping is performed, the original user name is returned by the SQL function USER(), while the authenticated user name is returned by the SQL function CURRENT_USER(). The latter actually defines what privileges are available to a connected user.
Consider having the following configured:
Then the following output would verify that it is working properly:
We can verify that our foo PAM user was properly mapped to the bar MariaDB user by looking at the return value of CURRENT_USER().
By default, the pam_user_map PAM module does not perform any logging. However, if you want to enable debug logging, then you can add the debug module argument to the service's PAM configuration file:
When debug logging is enabled, the pam_user_map PAM module will write log entries to the same syslog location as other PAM modules, which is typically /var/log/secure on many systems.
For example, this debug log output can look like the following:
With user and group mapping, any PAM user or any PAM user in a given PAM group can be mapped to a specific MariaDB user account. However, due to the way PAM works, a PAM user with the same name as the mapped MariaDB user account must exist.
Consider the configuration file for the PAM service file containing the following:
Consider etc/security/user_map.conf containing the following:
In that case, any PAM user in the PAM group dba are mapped to the MariaDB user account dba. But if a PAM user with the name dba doesn't exist, the pam_user_map PAM module's debug logging writes errors to the syslog, like the following:
In the above log snippet, notice that both the pam_unix and the pam_sss PAM modules are complaining that the dba PAM user does not appear to exist, and that these complaints cause the PAM authentication process to fail, which causes the MariaDB authentication process to fail as well.
This can be fixed by creating a PAM user with the same name as the mapped MariaDB user account, which is dba in this case.
You may also be able to work around this problem by essentially disabling PAM's account verification for the service with the pam_permit PAM module. For example, in the above case, that would be:
See MDEV-17315 for more information.
You may find the following PAM and user mapping-related tutorials helpful:
This page is licensed: CC BY-SA / Gnu FDL
CREATE USER ''@''
IDENTIFIED WITH authentication_pam
AS 'mysql, root=developer, users=data_entry';sudo yum install gcc pam-devel MariaDB-develsudo apt-get install gcc libpam0g-dev libmariadb-devwget https://raw.githubusercontent.com/MariaDB/server/10.4/plugin/auth_pam/mapper/pam_user_map.c
sed -ie 's/config_auth_pam/plugin_auth_common/' pam_user_map.c
gcc -I/usr/include/mysql/ pam_user_map.c -shared -lpam -fPIC -o pam_user_map.so
sudo install --mode=0755 pam_user_map.so /lib64/security/orig_pam_user_name: mapped_mariadb_user_name@orig_pam_group_name: mapped_mariadb_user_name=========================================================
#comments and empty lines are ignored
john: jack
bob: admin
top: accounting
@group_ro: readonlyauth required pam_unix.so audit
auth required pam_user_map.so
account required pam_unix.so auditfoo: bar
@dba:dbaCREATE USER 'bar'@'%' IDENTIFIED BY 'strongpassword';
GRANT ALL PRIVILEGES ON *.* TO 'bar'@'%' ;
CREATE USER 'dba'@'%' IDENTIFIED BY 'strongpassword';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'%' ;
CREATE USER ''@'%' IDENTIFIED VIA pam USING 'mariadb';
GRANT PROXY ON 'bar'@'%' TO ''@'%';
GRANT PROXY ON 'dba'@'%' TO ''@'%';foo: bar$ mysql -u foo -h 172.30.0.198
[mariadb] Password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 22
Server version: 10.3.10-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SELECT USER(), CURRENT_USER();
+------------------------------------------------+----------------+
| USER() | CURRENT_USER() |
+------------------------------------------------+----------------+
| foo@ip-172-30-0-198.us-west-2.compute.internal | bar@% |
+------------------------------------------------+----------------+
1 row in set (0.000 sec)auth required pam_unix.so audit
auth required pam_user_map.so debug
account required pam_unix.so auditJan 9 05:42:13 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): Opening file '/etc/security/user_map.conf'.
Jan 9 05:42:13 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): Incoming username 'alice'.
Jan 9 05:42:13 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): User belongs to 2 groups [alice,dba].
Jan 9 05:42:13 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): Check if user is in group 'dba': YES
Jan 9 05:42:13 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): User mapped as 'dba'
Jan 9 05:43:36 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): Opening file '/etc/security/user_map.conf'.
Jan 9 05:43:36 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): Incoming username 'bob'.
Jan 9 05:43:36 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): User belongs to 2 groups [bob,dba].
Jan 9 05:43:36 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): Check if user is in group 'dba': YES
Jan 9 05:43:36 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): User mapped as 'dba'
Jan 9 06:08:36 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): Opening file '/etc/security/user_map.conf'.
Jan 9 06:08:36 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): Incoming username 'foo'.
Jan 9 06:08:36 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): User belongs to 1 group [foo].
Jan 9 06:08:36 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): Check if user is in group 'dba': NO
Jan 9 06:08:36 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): Check if username 'foo': YES
Jan 9 06:08:36 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): User mapped as 'bar'auth required pam_sss.so
auth required pam_user_map.so debug
account sufficient pam_unix.so
account sufficient pam_sss.so@dba: dbaSep 27 17:17:05 dbserver1 mysqld: pam_user_map(mysql:auth): Opening file '/etc/security/user_map.conf'.
Sep 27 17:17:05 dbserver1 mysqld: pam_user_map(mysql:auth): Incoming username 'alice'.
Sep 27 17:17:05 dbserver1 mysqld: pam_user_map(mysql:auth): User belongs to 4 groups [dba,mongod,mongodba,mysql].
Sep 27 17:17:05 dbserver1 mysqld: pam_user_map(mysql:auth): Check if user is in group 'mysql': YES
Sep 27 17:17:05 dbserver1 mysqld: pam_user_map(mysql:auth): User mapped as 'dba'
Sep 27 17:17:05 dbserver1 mysqld: pam_unix(mysql:account): could not identify user (from getpwnam(dba))
Sep 27 17:17:05 dbserver1 mysqld: pam_sss(mysql:account): Access denied for user dba: 10 (User not known to the underlying authentication module)
Sep 27 17:17:05 dbserver1 mysqld: 2018-09-27 17:17:05 72 [Warning] Access denied for user 'alice'@'localhost' (using password: NO)auth required pam_sss.so
auth required pam_user_map.so debug
account required pam_permit.soON - Enables the plugin. If the plugin cannot be initialized, then the server will still continue starting up, but the plugin will be disabled.FORCE - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error.
FORCE_PLUS_PERMANENT - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error. In addition, the plugin cannot be uninstalled with UNINSTALL SONAME or UNINSTALL PLUGIN while the server is running.
See Plugin Overview: Configuring Plugin Activation at Server Startup for more information.
Command line: --ed25519=value
Data Type: enumerated
Default Value: ON
Valid Values: OFF, ON, FORCE, FORCE_PLUS_PERMANENT
The unix_socket plugin authenticates users connecting via the local Unix socket file by matching the operating system user ID to the database user account.
The unix_socket authentication plugin is installed by default, and it is used by the 'root'@'localhost' user account by default. See Authentication for more information.
The unix_socket authentication plugin allows the user to use operating system credentials when connecting to MariaDB via the local Unix socket file. This Unix socket file is defined by the socket system variable.
The unix_socket authentication plugin works by calling the getsockopt system call with the SO_PEERCRED socket option, which allows it to retrieve the uid of the process that is connected to the socket. It is then able to get the user name associated with that uid. Once it has the user name, it will authenticate the connecting user as the MariaDB account that has the same user name.
The unix_socket authentication plugin is not suited to multiple Unix users accessing a single MariaDB user account.
A unix_socket authentication plugin is a passwordless security mechanism. Its security lies in the strength of the access to the Unix user, rather than the complexity and the secrecy of the password.
As security differs from password security, the strengths and weaknesses need to be considered, and those can differ depending on the specific installation.
Access is limited to the Unix user so, for example, a www-data user cannot access root with the unix_socket authentication plugin.
There is no password which can be cracked by brute force.
There is no password that can be accidentally exposed by user accident, poor security on backups, or poor security on passwords in configuration files.
The strength of a unix_socket authentication plugin is effectively the strength of the security of the Unix users on the system. In most cases, the Unix user default installation is sufficiently secure. However, the following is a non-exhaustive list of potential Unix user security issues that may arise.
Common access areas without screen locks, where an unauthorized user accesses the logged in Unix user of an authorized user.
Extensive sudo access grants that provide users with access to execute commands of a different Unix user.
Scripts writable by Unix users other than the Unix user that are executed (via cron or directly) by the Unix user.
Web pages that are susceptible to command injection, where the Unix user running the web page has elevated privileges in the database that weren't intended to be used.
In some of these scenarios a database password may prevent these security exploits, however it will remove all the strengths of the unix_socket authentication plugin previously mentioned.
The unix_socket authentication plugin is installed by default.
If you do not want it to be available by default, you must disable it.
The unix_socket authentication plugin is also installed by default in new installations that use the packages provided by Debian's default repositories and Ubuntu's default repositories. See for more information.
The unix_socket authentication plugin can be disabled by starting the server with the option set to OFF. This can be specified as a command-line argument to or it can be specified in a relevant server in an :
As an alternative, the option can also be set to OFF by pairing the option with the disable :
The unix_socket authentication plugin is installed by default in almost all MariaDB server versions. If you work with a version that doesn't have the plugin installed, you can install it as described in one of the following ways.
Install the plugin without restarting the server. You can install the plugin dynamically, by executing or :
Instruct the server to load the plugin at startup. The plugin can be installed this way by providing the or the options. This can be specified as a command-line argument to or it can be specified in a relevant server in an :
You can uninstall the plugin dynamically by executing or :
If you installed the plugin by providing the or the options in a server in an , those options should be removed to prevent the plugin from being loaded the next time the server is restarted.
To create a user account via , specify the name of the plugin in the clause:
If does not have NO_AUTO_CREATE_USER set, then you can also create the user account via :
The authentication string (if present) is compared with the socket's user name. Authentication proceeds if there's a match. In this case, the system variable contains the OS user.
Consider an OS user named 'bob' that has been created like this:
That user can connect like this:
Alternatively, accessing the sock file directly, the user can connect like this:
Once connected, you can view that user like this:
The plugin only checks whether the OS socket user id matches the MariaDB user name. It ignores the authentication string.
If Unix socket authentication does not meet your needs, you can switch a user account back to password-based authentication, by telling MariaDB to use a different for the account. The specific authentication plugin is specified with the clause. To switch to the authentication plugin, you need to do this:
If you use scripts that require passwordless access to MariaDB, this would cause them to break. You may be able to fix that by setting a password in the [client] in your /root/.my.cnf .
The unix_socket authentication plugin does not require any specific client authentication plugins. It should work with all clients.
The unix_socket authentication plugin does not require any special support in client libraries. It should work with all client libraries.
In this example, user serg is already logged into the operating system and has full shell access. The user has already authenticated with the operating system and the MariaDB account is configured to use the unix_socket authentication plugin, so there is no need to authenticate again for the database. MariaDB accepts the operating system credentials and allows the user to connect. However, any attempt to connect to the database as another operating system is denied.
unix_socketDescription: Controls how the server should treat the plugin when the server starts up.
Valid values are:
OFF - Disables the plugin without removing it from the table.
This page is licensed: CC BY-SA / Gnu FDL
PARSEC is a modern, secure authentication plugin that uses salted passwords and elliptic curve cryptography to prevent replay attacks and secure user credentials.
The PARSEC Authentication Plugin is intended to be the default in a future release.
The PARSEC (Password Authentication using Response Signed with Elliptic Curve) authentication plugin uses salted passwords, key derivation, extensible password storage format, and both server- and client-side scrambles.
It signs the response with ed25519, but it uses stock unmodified ed25519
INSTALL SONAME 'auth_ed25519';[mariadb]
...
plugin_load_add = auth_ed25519UNINSTALL SONAME 'auth_ed25519';CREATE USER username@hostname IDENTIFIED VIA ed25519 USING PASSWORD('secret');GRANT SELECT ON db.* TO username@hostname IDENTIFIED VIA ed25519 USING PASSWORD('secret');CREATE FUNCTION ed25519_password RETURNS STRING SONAME "auth_ed25519.so";SELECT ed25519_password("secret");
+---------------------------------------------+
| SELECT ed25519_password("secret"); |
+---------------------------------------------+
| ZIgUREUg5PVgQ6LskhXmO+eZLS0nC8be6HPjYWR4YJY |
+---------------------------------------------+CREATE USER username@hostname IDENTIFIED VIA ed25519
USING 'ZIgUREUg5PVgQ6LskhXmO+eZLS0nC8be6HPjYWR4YJY';GRANT SELECT ON db.* TO username@hostname IDENTIFIED VIA ed25519
USING 'ZIgUREUg5PVgQ6LskhXmO+eZLS0nC8be6HPjYWR4YJY';SET PASSWORD = PASSWORD('new_secret')ALTER USER username@hostname IDENTIFIED VIA ed25519 USING PASSWORD('new_secret');CREATE FUNCTION ed25519_password RETURNS STRING SONAME "auth_ed25519.so";SELECT ed25519_password("secret");
+---------------------------------------------+
| SELECT ed25519_password("secret"); |
+---------------------------------------------+
| ZIgUREUg5PVgQ6LskhXmO+eZLS0nC8be6HPjYWR4YJY |
+---------------------------------------------+ALTER USER username@hostname IDENTIFIED VIA ed25519
USING 'ZIgUREUg5PVgQ6LskhXmO+eZLS0nC8be6HPjYWR4YJY';mysql --plugin-dir=/usr/local/mysql/lib64/mysql/plugin --user=aliceDefault Unix user security is usually strong on preventing remote access and password brute force attempts.
Poor Unix user password practices including weak user passwords, password exposure and password reuse accompanied by an access vulnerability/mechanism of an unauthorized user to exploit this weakness.
Weak remote access mechanisms and network file system privileges.
Poor user security behavior including running untrusted scripts and software.
ON - Enables the plugin. If the plugin cannot be initialized, then the server will still continue starting up, but the plugin will be disabled.FORCE - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error.
FORCE_PLUS_PERMANENT - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error. In addition, the plugin cannot be uninstalled with UNINSTALL SONAME or UNINSTALL PLUGIN while the server is running.
See Plugin Overview: Configuring Plugin Activation at Server Startup for more information.
Command line: --unix-socket=value
Data Type: enumerated
Default Value: ON
Valid Values: OFF, ON, FORCE, FORCE_PLUS_PERMANENT
[mariadb]
...
unix_socket=OFF[mariadb]
...
disable_unix_socketINSTALL SONAME 'auth_socket';[mariadb]
...
plugin_load_add = auth_socketUNINSTALL SONAME 'auth_socket';CREATE USER username@hostname IDENTIFIED VIA unix_socket;GRANT SELECT ON db.* TO username@hostname IDENTIFIED VIA unix_socket;CREATE USER A identified via unix_socket as 'bob';
mariadb -uAmariadb -uA -S /var/run/mysqld/mysqld.sockSELECT USER(),@@external_user;
+-------------+-----------------+
| user() | @@external_user |
+-------------+-----------------+
| A@localhost | bob |
+-------------+-----------------+ALTER USER root@localhost IDENTIFIED VIA mysql_native_password;
SET PASSWORD = PASSWORD('foo');[client]
password=foo$ mysql -uroot
MariaDB []> CREATE USER serg IDENTIFIED VIA unix_socket;
MariaDB []> CREATE USER monty IDENTIFIED VIA unix_socket;
MariaDB []> quit
Bye
$ whoami
serg
$ mysql --user=serg
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.2.0-MariaDB-alpha-debug Source distribution
MariaDB []> quit
Bye
$ mysql --user=monty
ERROR 1045 (28000): Access denied for user 'monty'@'localhost' (using password: NO)The KDF function is pbkdf2 (supported by everything, including windows native, Java, javascript, PHP, .NET.
Parameters to the pbkdf2 are stored in with authentication plugin data : hash function (SHA512,SHA256), iteration count, salt, key_length, together with derived key = PBKDF2(func, password, salt, iteration_count, key_length).
The number of iterations is a power of 2, greater than 9.
The algorithm is ed25519, "hash" is the public key generated using ed25519 from the PBKDF2(password).
The authentication string, stored by the server, is this:
For example, it looks like this: P0:WW9sXaaL/o:vubFBzIrapbfHct1/J72dnUryz5VS7lA6XHH8sIx4TI
It consists of colon-separated fields.
The first field is 'P' (denotes KDF algorithm = PBKDF2) and the number of iterations, '0' means 1024, '1' means 2048, etc.
This is followed by the salt.
This is followed by the password hash.
The first two fields together are called ext-salt, extended salt.
The server sends an Authentication Switch Request with a 32-byte random scramble.
The client sends an empty packet to the server to request the ext-salt.
The server sends the ext-salt to the client.
The client sends the random 32-byte scramble, and the concat(server scramble, client scramble) ed25519-signed by a secret key generated from the function PBKDF2(password, ext-salt).
The server replies with or .
If you run into the error ERROR 1524 (HY000): Plugin 'parsec' is not loaded it means you need to install the authentication plugin first. You can do it on a running server with:
There is no need to pass additional command-line options or have config files to keep the PARSEC authentication method available. Running the INSTALL SONAME once is enough and the MariaDB Server will remember it even if server is restarted or upgraded.
PARSEC is currently available in latest MariaDB versions, but not installed or used by default yet. Once MDEV-12320 is implemented, MariaDB plans to start using PARSEC as the default password authentication method.
This page is licensed: CC BY-SA / Gnu FDL
concat('P', conv(log2(iterations)-10, 10, 62), ':', base64(salt), ':', base64(hash))INSTALL SONAME 'auth_parsec';CREATE USER test1@'%' IDENTIFIED VIA parsec USING PASSWORD('pwd');The gssapi authentication plugin enables passwordless single sign-on by authenticating users via the Generic Security Services API, supporting Kerberos on Unix and Windows.
The gssapi authentication plugin allows the user to authenticate with services that use the Generic Security Services Application Program Interface (GSSAPI). Windows has a slightly different but very similar API called Security Support Provider Interface (SSPI). The GSSAPI is a standardized API described in RFC2743 and RFC2744. The client and server negotiate using a standardized protocol described in RFC7546.
On Windows, this authentication plugin supports Kerberos and NTLM authentication. Windows authentication is supported regardless of whether a domain is used in the environment.
On Unix systems, the most dominant GSSAPI service is Kerberos. However, it is less commonly used on Unix systems than it is on Windows. Regardless, this authentication plugin also supports Kerberos authentication on Unix.
The gssapi authentication plugin is most often used for authenticating with Microsoft Active Directory.
This article gives instructions on configuring the gssapi authentication plugin
for MariaDB for passwordless login.
The gssapi authentication plugin's shared library is included in MariaDB packages as the auth_gssapi.so or auth_gssapi.dll shared library on systems where it can be built.
The gssapi authentication plugin is included in on Linux.
The gssapi authentication plugin can also be installed via a package manager on Linux. In order to do so, your system needs to be configured to install from one of the MariaDB repositories.
You can configure your package manager to install it from MariaDB Corporation's MariaDB Package Repository by using the .
You can also configure your package manager to install it from MariaDB Foundation's MariaDB Repository by using the .
Installing with yum/dnf
On RHEL, CentOS, Fedora, and other similar Linux distributions, it is highly recommended to install the relevant from MariaDB's repository using or . Starting with RHEL 8 and Fedora 22, yum has been replaced by dnf, which is the next major version of yum. However, yum commands still work on many systems that use dnf:
Installing with apt-get
On Debian, Ubuntu, and other similar Linux distributions, it is highly recommended to install the relevant from MariaDB's repository using :
Installing with zypper
On SLES, OpenSUSE, and other similar Linux distributions, it is highly recommended to install the relevant from MariaDB's repository using :
On Windows, and on other operating systems, although the plugin's shared library is distributed with MariaDB by default, the plugin is not actually installed by MariaDB by default. There are two methods that can be used to install the plugin with MariaDB.
The first method can be used to install the plugin without restarting the server. You can install the plugin dynamically by executing or :
The second method can be used to tell the server to load the plugin when it starts up. The plugin can be installed this way by providing the or the options. This can be specified as a command-line argument to or it can be specified in a relevant server in an :
You can uninstall the plugin dynamically by executing or :
If you installed the plugin by providing the or the options in a relevant server in an , then those options must be removed to prevent the plugin from being loaded the next time the server is restarted.
If the MariaDB server is running on Unix, then some additional configuration steps will need to be implemented in order to use the plugin.
If the MariaDB server is running on Windows, then no special configuration steps will need to be implemented in order to use the plugin, as long as the following is true:
The Windows server is joined to a domain.
The MariaDB server process is running as either a or a .
If the MariaDB server is running on Unix, then the KDC server will need to create a keytab file for the MariaDB server. The keytab file contains the service principal name, which is the identity that the MariaDB server will use to communicate with the KDC server. The keytab will need to be transferred to the MariaDB server, and the mysqld server process will need read access to this keytab file.
How this keytab file is generated depends on whether the KDC server is or .
If you are using , you may need to create a keytab using the utility on a Windows host. The service principal will need to be mapped to an existing domain user. To do so, follow the steps listed below.
Be sure to replace the following items in the step below:
Replace ${HOST} with the fully qualified DNS name for the MariaDB server host.
Replace ${DOMAIN} with the Active Directory domain.
Replace ${AD_USER} with the existing domain user.
To create the service principal, execute the following command:
If you are using , then you can create a file using the utility. To do so, follow the steps listed below.
In the following steps, be sure to replace ${HOST} with the fully qualified DNS name for the MariaDB server host.
First, create the service principal using the utility:
Then, export the newly created user to the keytab file using the utility:
More details can be found at the following links:
If the MariaDB server is running on Unix, then the path to the keytab file that was previously created can be set by configuring the system variable. This can be specified as a command-line argument to mariadbd, or it can be specified in a relevant server in an :
The service principal name can be set by configuring the system variable. This can be specified as a command-line argument to mariadbd, or it can be specified in a relevant server in an :
If a service principal name is not provided, the plugin tries to use mariadb/host.domain.com@REALM by default.
If the MariaDB server is running on Unix, the plugin needs a service principal name in order to function.
If the MariaDB server is running on Windows, the plugin does not usually need a service principal in order to function. However, if you want to use one, anyway, it can be created with the setspn utility.
Different KDC implementations may use different canonical forms to identify principals. See to learn what the standard says about principal names.
More details can be found at the following links:
To create a user account via , specify the name of the plugin in the clause:
If does not have NO_AUTO_CREATE_USER set, then you can also create the user account via :
You can also specify the user's for MariaDB with the USING clause:
The format of the realm depends on the specific authentication mechanism that is used. For example, the format would need to be machine\\username for Windows users authenticating with NTLM.
If the realm is not provided in the user account's definition, then the realm is not used for comparison. Therefore, 'usr1@EXAMPLE.COM', 'usr1@EXAMPLE.CO.UK' and 'mymachine\usr1' would all identify as the following user account:
On Windows only, it is possible to login using a AD or local group-membership. This is achieved by using the GROUP prefix in IDENTIFIED ... AS:
The effect of the above definition is that every user that identifies as a member of group Administrators can log in using the user name root without a password.
User can also login using own or group (Security Identifier):
Using SIDs will perform slightly faster than using name (since it will spare translation between SID and name which is otherwise done). SIDs are immune against user or group renaming.
On Windows, in addition to the usual authentication with a password, passwordless authentication is permitted when creating the root user during installation. This works in a similar manner to . However, since auth_gssapi, unlike unix_socket, requires client support, to avoid failures when MariaDB is used with third-party drivers, authentication on Windows first attempts password-based native_authentication, and only if it fails, falls back to passwordless auth_gssapi.
For clients that use the libmysqlclient or libraries, MariaDB provides one client authentication plugin that is compatible with the gssapi authentication plugin:
auth_gssapi_client
When connecting with a to a server as a user account that authenticates with the gssapi authentication plugin, you may need to tell the client where to find the relevant client authentication plugin by specifying the --plugin-dir option:
auth_gssapi_clientThe auth_gssapi_client client authentication plugin receives the principal name from the server, and then uses either the function (on Unix) or the function (on Windows) to establish a security context on the client.
supports gssapi authentication using the mentioned in the previous section.
supports gssapi authentication using the mentioned in the previous section.
supports gssapi authentication. Current documentation can be found .
does not yet support gssapi authentication. See for more information.
supports gssapi authentication.
The support is transparent. Normally, the connector only needs to be provided the correct user name, and no other parameters are required.
However, this connector also supports the connection string parameter, which can be used for mutual authentication.
When connecting from Unix client to Windows server with ADO.NET, in an Active Directory domain environment, be aware that .NET Core on Unix does not support principal names in UPN(User Principal Name) form, which is default on Windows (e.g machine$@domain.com) . Thus, upon encountering an authentication exception with "server not found in Kerberos database", use one of workarounds below
Force host-based SPN on server side.
For example, this can be done by setting the system variable to HOST/machine in a server in an .
Pass host-based SPN on client side.
gssapi_keytab_pathDescription: Defines the path to the server's keytab file.
This system variable is only meaningful on Unix.
See and for more information.
Command line: --gssapi-keytab-path
gssapi_principal_nameDescription: Name of the service principal.
See for more information.
Command line: --gssapi-principal-name
Scope: Global
gssapi_mech_nameDescription: Name of the SSPI package used by server. Can be either 'Kerberos' or 'Negotiate'. Set it to 'Kerberos', to prevent less secure NTLM in domain environments, but leave it as default (Negotiate) to allow non-domain environments (e.g if server does not run in a domain environment).
This system variable is only meaningful on Windows.
Command line: --gssapi-mech-name
gssapiDescription: Controls how the server should treat the plugin when the server starts up.
Valid values are:
OFF - Disables the plugin without removing it from the table.
This page is licensed: CC BY-SA / Gnu FDL
${PASSWORD} with the password for the service principal.HOST/machine.Scope: Global
Dynamic: No
Data Type: string
Default Value: ''
Introduced: MariaDB 10.1.11
Dynamic: No
Data Type: string
Default Value: ''
Introduced: MariaDB 10.1.11
Dynamic: No
Data Type: enumerated
Default Value: Negotiate
Valid Values: Kerberos, Negotiate
Introduced: MariaDB 10.1.11
ON - Enables the plugin. If the plugin cannot be initialized, then the server will still continue starting up, but the plugin will be disabled.FORCE - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error.
FORCE_PLUS_PERMANENT - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error. In addition, the plugin cannot be uninstalled with UNINSTALL SONAME or UNINSTALL PLUGIN while the server is running.
See Plugin Overview: Configuring Plugin Activation at Server Startup for more information.
Command line: --gssapi=value
Data Type: enumerated
Default Value: ON
Valid Values: OFF, ON, FORCE, FORCE_PLUS_PERMANENT
Introduced: MariaDB 10.1.11
Learn to configure the PAM plugin to authenticate users via LDAP and map LDAP groups to MariaDB accounts using the pam_user_map module.
In this article, we will walk through the configuration of PAM authentication using the authentication plugin and user and group mapping with the PAM module. The primary authentication will be handled by the PAM module, which performs LDAP authentication. We will also set up an OpenLDAP server.
In this walkthrough, we are going to assume the following hypothetical requirements:
sudo yum install MariaDB-gssapi-serversudo apt-get install mariadb-plugin-gssapi-serversudo zypper install MariaDB-gssapi-serverINSTALL SONAME 'auth_gssapi';[mariadb]
...
plugin_load_add = auth_gssapiUNINSTALL SONAME 'auth_gssapi';ktpass.exe /princ mariadb/${HOST}@${DOMAIN} /mapuser ${AD_USER} /pass ${PASSWORD} /out mariadb.keytab /crypto all /ptype KRB5_NT_PRINCIPAL /mapop setkadmin -q "addprinc -randkey mariadb/${HOST}"kadmin -q "ktadd -k /path/to/mariadb.keytab mariadb/${HOST}"[mariadb]
...
gssapi_keytab_path=/path/to/mariadb.keytab[mariadb]
...
gssapi_principal_name=service_principal_name/host.domain.com@REALMCREATE USER username@hostname IDENTIFIED VIA gssapi;GRANT SELECT ON db.* TO username@hostname IDENTIFIED VIA gssapi;CREATE USER username@hostname IDENTIFIED VIA gssapi USING 'username@EXAMPLE.COM';CREATE USER usr1@hostname IDENTIFIED VIA gssapi;CREATE USER root IDENTIFIED VIA gssapi AS 'GROUP:Administrators'
CREATE USER root IDENTIFIED VIA gssapi AS 'GROUP:BUILTIN\\Administrators'CREATE USER root IDENTIFIED VIA gssapi AS 'SID:S-1-5-32-544'mysql --plugin-dir=/usr/local/mysql/lib64/mysql/plugin --user=alicefoo should be mapped to the MariaDB user bar. (foo: bar)Any LDAP user in the LDAP group dba should be mapped to the MariaDB user dba. (@dba: dba)
Before we can use LDAP authentication, we first need to set up our OpenLDAP Server. This is usually done on a server that is completely separate from the database server.
On the server acting as the OpenLDAP Server, first, we need to install the OpenLDAP components.
On RHEL, CentOS, and other similar Linux distributions that use RPM packages, that would go like this:
Next, let's to configure the OpenLDAP Server. The easiest way to do that is to copy the template configuration file that is included with the installation. In many installations, that will be at /usr/share/openldap-servers/DB_CONFIG.example:
Sometimes it is useful to change the port used by OpenLDAP. For example, some cloud environments block well-known authentication services, so they block the default LDAP port.
On some systems, the port can be changed by setting SLAPD_URLS in /etc/sysconfig/slapd:
I used 3306 because that is the port that is usually used by mysqld, so I know that it is not blocked.
Next, let's start the OpenLDAP Server and configure it to start on reboot. On systemd systems, that would go like this:
In order to use LDAP for authentication, we also need to install some standard objectClasses, such as posixAccount and posixGroup. In LDAP, things like objectClasses are defined in LDIF files. In many installations, these specific objectClasses are defined in /etc/openldap/schema/nis.ldif. nis.ldif also depends on core.ldif and cosine.ldif. However, core.ldif is usually installed by default.
We can install them with ldapmodify:
Next, let’s create a directory manager user. We can do this by using OpenLDAP's olc configuration system to change the olcRootDN directive to the DN of the directory manager user, which means that the user will be a privileged LDAP user that is not subject to access controls. We will also set the root password for the user by changing the olcRootPW directive.
We will also set the DN suffix for our backend LDAP database by changing the olcSuffix directive.
Let’s use the slappasswd utility to generate a password hash from a clear-text password. Simply execute:
This utility provides a password hash that looks like this: {SSHA}AwT4jrvmokeCkbDrFAnGvzzjCMb7bvEl
OpenLDAP's olc configuration system also uses LDIF files. Now that we have the password hash, let’s create an LDIF file to create the directory manager user:
Note that this is using the dc=support,dc=mariadb,dc=com domain for the directory. You can change it to whatever is relevant to you.
Now let’s run the ldif file with ldapmodify:
We will use the new directory manager user to make changes to the LDAP directory after this step.
Next, let's create the structure of the directory by creating parts of our tree.
Now, let’s use our new directory manager user and run the LDIF file with ldapmodify:
Let's go ahead and create the LDAP users and groups that we are using for this scenario.
First, let's create the foo user:
Next, let's create a couple of users to go into the dba group:
Note that each of these users needs a password, so we can set it for each user with ldappasswd:
Next, let's create our dba group:
Next, let's add our two users to it:
We also need to create LDAP users with the same name as the bar and dba MariaDB users. See here to read more about the reasons to do so. No one will be logging in as these users, so they do not need passwords. Instead of the People organizationalUnit, we create them in the System Users organizationalUnit.
At this point, we can move on to setting up the MariaDB Server.
First, we need to make sure that the LDAP and PAM libraries are installed.
On RHEL, CentOS, and other similar Linux distributions that use RPM packages, we need to install the following packages:
Next, let's configure LDAP on the system. We can use authconfig for this:
Be sure to replace -–ldapserver and -–ldapbasedn with values that are relevant for your environment.
The pam_user_map PAM module is included in the base install. No installation is needed.
Next, let's install the pam_user_map PAM module.
Before the module can be compiled from source, we may need to install some dependencies.
On RHEL, CentOS, and other similar Linux distributions that use RPM packages, we need to install gcc and pam-devel:
On Debian, Ubuntu, and other similar Linux distributions that use DEB packages, we need to install gcc and libpam0g-dev:
And then we can build and install the library with the following:
Next, let's configure the pam_user_map PAM module based on our hypothetical requirements.
The configuration file for the pam_user_map PAM module is /etc/security/user_map.conf. Based on our requirements, ours would look like:
Next, let's install the pam authentication plugin.
Log into the MariaDB Server and execute the following:
Next, let's configure the PAM service. We will call our service mariadb, so our PAM service configuration file will be located at /etc/pam.d/mariadb on most systems.
Since we are only doing LDAP authentication with the pam_ldap PAM module and group mapping with the pam_user_map PAM module, our configuration file would look like this:
If we want to allow authentication from LDAP users and from local Unix users through pam_unix, while giving priority to the local users, then we could do this instead:
Configuring the pam_unix PAM Module
If you also want to allow authentication from local Unix users, the pam_unix PAM module adds some additional configuration steps on a lot of systems. We basically have to give the user that runs mysqld access to /etc/shadow.
If the mysql user is running mysqld, then we can do that by executing the following:
The server needs to be restarted for this change to take affect.
Next, let's create the MariaDB users. Remember that our PAM service is called mariadb.
First, let's create the MariaDB user for the user mapping: foo: bar
That means that we need to create a bar user:
And then let's create the MariaDB user for the group mapping: @dba: dba
That means that we need to create a dba user:
And then to allow for the user and group mapping, we need to create an anonymous user that authenticates with the pam authentication plugin that is also able to PROXY as the bar and dba users. Before we can create the proxy user, we might need to clean up some defaults:
And then let's create the anonymous proxy user:
Next, let's test our configuration by verifying that mapping is occurring. We can verify this by logging in as each of our users and comparing the return value of USER(), which is the original user name and the return value of CURRENT_USER(), which is the authenticated user name.
First, let's test our foo user:
We can verify that our foo LDAP user was properly mapped to the bar MariaDB user by looking at the return value of CURRENT_USER().
Then let's test our gmontee user in the dba group:
And then let's test our bstillman user in the dba group:
We can verify that our gmontee and bstillman LDAP users in the dba LDAP group were properly mapped to the dba MariaDB user by looking at the return values of CURRENT_USER().
If you chose the option that also allowed local Unix authentication, then let's test that out. Let's create a Unix user and give the user a password real quick:
And let's also map this user to dba:
And we know that the existing anonymous user already has the PROXY privilege granted to the dba user, so this should just work without any other configuration. Let's test it out:
We can verify that our alice Unix user was properly mapped to the dba MariaDB user by looking at the return values of CURRENT_USER().
If you are connecting to MariaDB Server through MariaDB MaxScale, it is also recommended to configure the proxy to authenticate users via .
This page is licensed: CC BY-SA / Gnu FDL
sudo yum install gcc pam-develsudo apt-get install gcc libpam0g-devsudo yum install openldap openldap-servers openldap-clients nss-pam-ldapdsudo cp /usr/share/openldap-servers/DB_CONFIG.example /var/lib/ldap/DB_CONFIG
sudo chown ldap. /var/lib/ldap/DB_CONFIGSLAPD_URLS="ldapi:/// ldap://0.0.0.0:3306/"sudo systemctl start slapd
sudo systemctl enable slapdsudo ldapmodify -a -Y EXTERNAL -H ldapi:/// -f /etc/openldap/schema/cosine.ldif
sudo ldapmodify -a -Y EXTERNAL -H ldapi:/// -f /etc/openldap/schema/nis.ldifslappasswdtee ~/setupDirectoryManager.ldif <<EOF
dn: olcDatabase={1}monitor,cn=config
changetype: modify
replace: olcAccess
olcAccess: {0}to *
by dn.base="gidNumber=0+uidNumber=0,cn=peercred,cn=external,cn=auth" read
by dn.base="cn=Manager,dc=support,dc=mariadb,dc=com" read
by * none
dn: olcDatabase={2}hdb,cn=config
changetype: modify
replace: olcSuffix
olcSuffix: dc=support,dc=mariadb,dc=com
dn: olcDatabase={2}hdb,cn=config
changetype: modify
replace: olcRootDN
olcRootDN: cn=Manager,dc=support,dc=mariadb,dc=com
dn: olcDatabase={2}hdb,cn=config
changetype: modify
add: olcRootPW
olcRootPW: {SSHA}AwT4jrvmokeCkbDrFAnGvzzjCMb7bvEl
dn: olcDatabase={2}hdb,cn=config
changetype: modify
add: olcAccess
olcAccess: {0}to attrs=userPassword,shadowLastChange
by dn="cn=Manager,dc=support,dc=mariadb,dc=com" write
by anonymous auth
by self write
by * none
olcAccess: {1}to dn.base=""
by * read
olcAccess: {2}to *
by dn="cn=Manager,dc=support,dc=mariadb,dc=com" write
by * read
EOFsudo ldapmodify -Y EXTERNAL -H ldapi:/// -f ~/setupDirectoryManager.ldiftee ~/setupDirectoryStructure.ldif <<EOF
dn: dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: dcObject
objectclass: organization
o: MariaDB Support Team
dc: support
dn: cn=Manager,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: organizationalRole
cn: Manager
description: Directory Manager
dn: ou=People,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: organizationalUnit
ou: People
dn: ou=Groups,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: organizationalUnit
ou: Groups
dn: ou=System Users,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: organizationalUnit
ou: System Users
EOFldapmodify -a -x -D cn=Manager,dc=support,dc=mariadb,dc=com -W -f ~/setupDirectoryStructure.ldiftee ~/createFooUser.ldif <<EOF
dn: uid=foo,ou=People,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: account
objectClass: posixAccount
objectClass: shadowAccount
cn: foo
uid: foo
uidNumber: 16859
gidNumber: 100
homeDirectory: /home/foo
loginShell: /bin/bash
gecos: foo
userPassword: {crypt}x
shadowLastChange: -1
shadowMax: -1
shadowWarning: 0
EOF
ldapmodify -a -x -D cn=Manager,dc=support,dc=mariadb,dc=com -W -f ~/createFooUser.ldiftee ~/createDbaUsers.ldif <<EOF
dn: uid=gmontee,ou=People,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: account
objectClass: posixAccount
objectClass: shadowAccount
cn: gmontee
uid: gmontee
uidNumber: 16860
gidNumber: 100
homeDirectory: /home/gmontee
loginShell: /bin/bash
gecos: gmontee
userPassword: {crypt}x
shadowLastChange: -1
shadowMax: -1
shadowWarning: 0
dn: uid=bstillman,ou=People,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: account
objectClass: posixAccount
objectClass: shadowAccount
cn: bstillman
uid: bstillman
uidNumber: 16861
gidNumber: 100
homeDirectory: /home/bstillman
loginShell: /bin/bash
gecos: bstillman
userPassword: {crypt}x
shadowLastChange: -1
shadowMax: -1
shadowWarning: 0
EOF
ldapmodify -a -x -D cn=Manager,dc=support,dc=mariadb,dc=com -W -f ~/createDbaUsers.ldifldappasswd -x -D cn=Manager,dc=support,dc=mariadb,dc=com -W -S uid=foo,ou=People,dc=support,dc=mariadb,dc=com
ldappasswd -x -D cn=Manager,dc=support,dc=mariadb,dc=com -W -S uid=gmontee,ou=People,dc=support,dc=mariadb,dc=com
ldappasswd -x -D cn=Manager,dc=support,dc=mariadb,dc=com -W -S uid=bstillman,ou=People,dc=support,dc=mariadb,dc=comtee ~/createDbaGroup.ldif <<EOF
dn: cn=dba,ou=Groups,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: posixGroup
gidNumber: 678
EOF
ldapmodify -a -x -D cn=Manager,dc=support,dc=mariadb,dc=com -W -f ~/createDbaGroup.ldiftee ~/addUsersToDbaGroup.ldif <<EOF
dn: cn=dba,ou=Groups,dc=support,dc=mariadb,dc=com
changetype: modify
add: memberuid
memberuid: gmontee
dn: cn=dba,ou=Groups,dc=support,dc=mariadb,dc=com
changetype: modify
add: memberuid
memberuid: bstillman
EOF
ldapmodify -a -x -D cn=Manager,dc=support,dc=mariadb,dc=com -W -f ~/addUsersToDbaGroup.ldiftee ~/createSystemUsers.ldif <<EOF
dn: uid=bar,ou=System Users,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: account
objectClass: posixAccount
objectClass: shadowAccount
cn: bar
uid: bar
uidNumber: 16862
gidNumber: 100
homeDirectory: /home/bar
loginShell: /bin/bash
gecos: bar
userPassword: {crypt}x
shadowLastChange: -1
shadowMax: -1
shadowWarning: 0
dn: uid=dba,ou=System Users,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: account
objectClass: posixAccount
objectClass: shadowAccount
cn: dba
uid: dba
uidNumber: 16863
gidNumber: 100
homeDirectory: /home/dba
loginShell: /bin/bash
gecos: dba
userPassword: {crypt}x
shadowLastChange: -1
shadowMax: -1
shadowWarning: 0
EOF
ldapmodify -a -x -D cn=Manager,dc=support,dc=mariadb,dc=com -W -f ~/createSystemUsers.ldifsudo yum install openldap-clients nss-pam-ldapd pam pam-develsudo authconfig --enableldap \
--enableldapauth \
--ldapserver="ldap://172.30.0.238:3306" \
--ldapbasedn="dc=support,dc=mariadb,dc=com" \
--enablemkhomedir \
--updatefoo: bar
@dba:dbaINSTALL SONAME 'auth_pam';auth required pam_ldap.so
auth required pam_user_map.so
account required pam_ldap.soauth [success=1 new_authtok_reqd=1 default=ignore] pam_unix.so audit
auth required pam_ldap.so try_first_pass
auth required pam_user_map.so
account sufficient pam_unix.so audit
account required pam_ldap.sosudo groupadd shadow
sudo usermod -a -G shadow mysql
sudo chown root:shadow /etc/shadow
sudo chmod g+r /etc/shadowCREATE USER 'bar'@'%' IDENTIFIED BY 'strongpassword';
GRANT ALL PRIVILEGES ON *.* TO 'bar'@'%' ;CREATE USER 'dba'@'%' IDENTIFIED BY 'strongpassword';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'%' ;DELETE FROM mysql.db WHERE User='' AND Host='%';
FLUSH PRIVILEGES;CREATE USER ''@'%' IDENTIFIED VIA pam USING 'mariadb';
GRANT PROXY ON 'bar'@'%' TO ''@'%';
GRANT PROXY ON 'dba'@'%' TO ''@'%';$ mysql -u foo -h 172.30.0.198
[mariadb] Password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 134
Server version: 10.3.10-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SELECT USER(), CURRENT_USER();
+------------------------------------------------+----------------+
| USER() | CURRENT_USER() |
+------------------------------------------------+----------------+
| foo@ip-172-30-0-198.us-west-2.compute.internal | bar@% |
+------------------------------------------------+----------------+
1 row in set (0.000 sec)$ mysql -u gmontee -h 172.30.0.198
[mariadb] Password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 135
Server version: 10.3.10-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SELECT USER(), CURRENT_USER();
+----------------------------------------------------+----------------+
| USER() | CURRENT_USER() |
+----------------------------------------------------+----------------+
| gmontee@ip-172-30-0-198.us-west-2.compute.internal | dba@% |
+----------------------------------------------------+----------------+
1 row in set (0.000 sec)$ mysql -u bstillman -h 172.30.0.198
[mariadb] Password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 136
Server version: 10.3.10-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SELECT USER(), CURRENT_USER();
+------------------------------------------------------+----------------+
| USER() | CURRENT_USER() |
+------------------------------------------------------+----------------+
| bstillman@ip-172-30-0-198.us-west-2.compute.internal | dba@% |
+------------------------------------------------------+----------------+
1 row in set (0.000 sec)sudo useradd alice
sudo passwd alice@dba:dba
foo: bar
alice: dba$ mysql -u alice -h 172.30.0.198
[mariadb] Password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 141
Server version: 10.3.10-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SELECT USER(), CURRENT_USER();
+--------------------------------------------------+----------------+
| USER() | CURRENT_USER() |
+--------------------------------------------------+----------------+
| alice@ip-172-30-0-198.us-west-2.compute.internal | dba@% |
+--------------------------------------------------+----------------+
1 row in set (0.000 sec)wget https://raw.githubusercontent.com/MariaDB/server/10.4/plugin/auth_pam/mapper/pam_user_map.c
gcc pam_user_map.c -shared -lpam -fPIC -o pam_user_map.so
sudo install --mode=0755 pam_user_map.so /lib64/security/The PAM authentication plugin delegates password validation to the operating system's PAM framework, enabling integration with LDAP, Kerberos, and other services.
The pam authentication plugin allows MariaDB to offload user authentication to the system's Pluggable Authentication Module (PAM) framework. PAM is an authentication framework used by Linux, FreeBSD, Solaris, and other Unix-like operating systems.
Note: Windows does not support PAM, so the pam authentication plugin does not support Windows. However, one can use a MariaDB client on Windows to connect to MariaDB server that is installed on a Unix-like operating system and that is configured to use the pam authentication plugin. For an example of how to do this, see the blog post: .
PAM makes it possible to implement various authentication scenarios of different complexity:
Authentication using passwords from /etc/shadow (this is what a default PAM configuration usually does). See the PAM module.
Authentication using LDAP. See the PAM module.
Authentication using Microsoft's Active Directory. See the , , and PAM modules.
The pam authentication plugin's library is provided in in all releases on Linux.
Although the plugin's shared library is distributed with MariaDB by default, the plugin is not actually installed by MariaDB by default. There are two methods that can be used to install the plugin with MariaDB.
The first method can be used to install the plugin without restarting the server. You can install the plugin dynamically by executing or :
The second method can be used to tell the server to load the plugin when it starts up. The plugin can be installed this way by providing the or the options. This can be specified as a command-line argument to or it can be specified in a relevant server in an :
The auth_pam shared library actually refers to version 2.0 of the pam authentication plugin. Version 1.0 of the plugin as the auth_pam_v1 shared library is also available.
If you need to install version 1.0 of the authentication plugin instead of version 2.0, install it with or :
Alternatively, specify it in a relevant server in an :
You can uninstall the plugin dynamically by executing or :
If you installed the plugin by providing the or the options in a relevant server in an , those options must be removed to prevent the plugin from being loaded the next time the server is restarted.
If you installed version 1.0 of the authentication plugin, you can uninstall it by executing a similar statement for auth_pam_v1:
The pam authentication plugin tells MariaDB to delegate the authentication to the PAM authentication framework. How exactly that authentication is performed depends on how PAM is configured.
PAM is divided into services. PAM services are configured by . Typically, the global PAM configuration file is located at /etc/pam.conf and for individual services are located in /etc/pam.d/.
If you want to use a PAM service called mariadb for your MariaDB PAM authentication, then the PAM configuration file for that service would also be called mariadb, and it would typically be located at /etc/pam.d/mariadb.
For example, here is a minimal PAM service configuration file that performs simple password authentication with UNIX passwords:
Let's breakdown this relatively simple PAM service configuration file.
Each line of a PAM service configuration file has the following general format:
It instructs the PAM authentication framework that for successful authentication (i.e. type=), it is required that the pam_unix.so PAM module returns a success. It also instructs the PAM authentication framework that for an account (i.e. type=) to be valid, it is required that the pam_unix.so PAM module returns a success.
PAM also supports and types, but MariaDB's pam authentication plugin does not support those.
The above PAM service configuration file also provides the audit module argument to the pam_unix PAM module. The says that this module argument enables extreme debug logging to the syslog.
On most systems, you can find many other examples of PAM service configuration files in your /etc/pam.d/ directory.
If you configure PAM to use the PAM module (as in the above example), then you might notice on some systems that this will fail by default with errors like the following:
The problem is that on some systems, the pam_unix PAM module needs access to /etc/shadow in order to function, and most systems only allow root to access that file by default.
Newer versions of PAM do not have this limitation, so you may want to try upgrading your version of PAM to see if that fixes the issue.
If that does not work, then you can work around this problem by giving the user that runs access to /etc/shadow. For example, if the mysql user runs , then you could do the following:
After configuring, you have to restart the server. The server should now be able to read /etc/shadow.
The pam authentication plugin uses a wrapper to perform its PAM checks, so it should not need any special workarounds to perform privileged operations, such as reading /etc/shadow when using the pam_unix PAM module. See for more information.
To create a user in MariaDB which uses the pam authentication plugin, execute while specifying the name of the plugin in the clause:
If does not have NO_AUTO_CREATE_USER set, then you can also create the user this way with :
You can also specify a for MariaDB to use by providing it with the USING clause:
This line creates a user that needs to be authenticated via the pam authentication plugin using the mariadb. As mentioned in a previous section, this service's configuration file will typically be present in /etc/pam.d/mariadb.
If no service name is specified, then the plugin will use mysql as the default .
For clients that use the libmysqlclient or libraries, MariaDB provides two client authentication plugins that are compatible with the pam authentication plugin:
dialog
mysql_clear_password
When connecting with a to a server as a user account that authenticates with the pam authentication plugin, you may need to tell the client where to find the relevant client authentication plugin by specifying the --plugin-dir option:
Both the dialog and the mysql_clear_password client authentication plugins transmit the password to the server in clear text. Therefore, when you use the pam authentication plugin, it is very important to to prevent the clear-text passwords from being seen by unauthorized users.
dialogUsually the pam authentication plugin uses the dialog client authentication plugin to communicate with the user. This client authentication plugin allows MariaDB to support arbitrarily complex PAM configurations with regular or one-time passwords, challenge-response, multiple questions, or just about anything else. When using a MariaDB client library, there is no need to install or enable anything — the dialog client authentication plugin is loaded by the client library completely automatically and transparently for the application.
The dialog client authentication plugin was developed by MariaDB, so MySQL's clients and client libraries as well as third party applications that bundle MySQL's client libraries do not support the dialog client authentication plugin out of the box. If the server tells an unsupported client to use the dialog client authentication plugin, then the client is likely to throw an error like the following:
For some libraries or applications, this problem can be fixed by copying dialog.so or dialog.dll from a MariaDB client installation that is compatible with the system into the system's MySQL client authentication plugin directory. However, not all clients are compatible with the dialog client authentication plugin, so this may not work for every client.
If your client does not support the dialog client authentication plugin, then you may need to use the client authentication plugin instead.
The dialog client authentication plugin transmits the password to the server in clear text. Therefore, when you use the pam authentication plugin, it is incredibly important to to prevent the clear-text passwords from being seen by unauthorized users.
mysql_clear_passwordUsers can instruct the pam authentication plugin to use the mysql_clear_password client authentication plugin instead of the client authentication plugin by configuring the system variable on the server. It can be set in a relevant server in an :
It is important to note that the mysql_clear_password plugin has very limited functionality.
The mysql_clear_password client authentication plugin only supports PAM services that require password-based authentication.
The mysql_clear_password client authentication plugin also only supports PAM services that ask the user a single question.
If the PAM service requires challenge-responses, multiple questions, or other similar complicated authentication schemes, then the PAM service is not compatible with mysql_clear_password client authentication plugin. In that case, the client authentication plugin will have to be used instead.
The mysql_clear_password client authentication plugin transmits the password to the server in clear text. Therefore, when you use the pam authentication plugin, it is incredibly important to to prevent the clear-text passwords from being seen by unauthorized users.
The mysql_clear_password client authentication plugin is similar to MySQL's client authentication plugin.
The mysql_clear_password client authentication plugin is compatible with MySQL clients and most MySQL client libraries, while the client authentication plugin is not always compatible with them. Therefore, the mysql_clear_password client authentication plugin is most useful if you need some kind of MySQL compatibility in your environment, but you still want to use the pam authentication plugin.
Even though the mysql_clear_password client authentication plugin is compatible with MySQL clients and most MySQL client libraries, the mysql_clear_password client authentication plugin may be disabled by default by these clients and client libraries. For example, MySQL's version of the command-line client has the option that must be set in order to use the mysql_clear_password client authentication plugin:
Other clients may require other methods to enable the authentication plugin. For example, has a checkbox titled Enable Cleartext Authentication Plugin under the Advanced tab on the connection configuration screen.
For applications that use MySQL's libmysqlclient, the authentication plugin can be enabled by setting the MYSQL_ENABLE_CLEARTEXT_PLUGIN option with the mysql_options() function:
For MySQL compatibility, also allows applications to set the MYSQL_ENABLE_CLEARTEXT_PLUGIN option with the function. However, this option does not actually do anything in , because the mysql_clear_password client authentication plugin is always enabled for MariaDB clients and client libraries.
supports pam authentication using the , regardless of the value of the system variable.
supports pam authentication using the , regardless of the value of the system variable.
supports pam v1 authentication, regardless of the value of the system variable.
supports pam v2 authentication, regardless of the value of the system variable.
supports pam authentication, regardless of the value of the system variable.
supports pam authentication, but only if the system variable is enabled on the server.
Errors and messages from PAM modules are usually logged using the daemon with the authpriv facility. To determine the specific log file where the authpriv facility is logged, you can check .
On RHEL, CentOS, Fedora, and other similar Linux distributions, the default location for these messages is usually /var/log/secure.
On Debian, Ubuntu, and other similar Linux distributions, the default location for these messages is usually /var/log/auth.log.
For example, the syslog can contain messages like the following when MariaDB's pam authentication plugin is configured to use the PAM module, and the user enters an incorrect password:
MariaDB's pam authentication plugin can also log additional verbose debug logging to the . This is only done if the plugin is a and if is set.
The output looks like this:
The PAM module can be used to implement some custom logging. This can be very useful when debugging certain kinds of issues.
Consider creating a script that writes the log output:
Change the to execute the script using the PAM module:
Whenever the above PAM service is used, the output of the script is written to /tmp/pam_output.txt. It looks similar to this output:
Even when using the pam authentication plugin, the authenticating PAM user account still needs to exist in MariaDB, and the account needs to have privileges in the database. Creating these MariaDB accounts and making sure the privileges are correct can be a lot of work. To decrease the amount of work involved, some users would like to be able to map a PAM user to a different MariaDB user. For example, let’s say that alice and bob are both DBAs. It would be nice if each of them could log into MariaDB with their own PAM username and password, while MariaDB sees both of them as the same dba user. That way, there is only one MariaDB account to keep track of. See for more information on how to do this.
There are many PAM modules. The ones described below are the ones that have been seen most often by MariaDB.
The PAM module provides support for Unix password authentication. It is the default PAM module on most systems.
For a tutorial on setting up PAM authentication and user or group mapping with Unix authentication, see .
The PAM module was developed by MariaDB to support user and group mapping.
The PAM module provides support for LDAP authentication.
For a tutorial on setting up PAM authentication and user or group mapping with LDAP authentication, see .
This can also be configured for authentication.
The PAM module provides support for authentication with .
This can be configured for authentication.
The pam_lsass PAM module provides support for authentication. It is provided by .
The PAM module provides support for authentication. It is provided by from the suite.
This PAM module converts all provided user names to lowercase. There is no way to disable this functionality. If you do not want to be forced to use all lowercase user names, then you may need to configure the system variable. See for more information.
The PAM module provides support for authentication. It integrates with the commercial .
The PAM module provides support for authentication.
This can be configured for authentication.
The pam_google_authenticator PAM module provides two-factor identification with Google Authenticator. It is from Google's open-source project. The PAM module should work with the open-source mobile apps built by Google's and projects as well as the closed source Google Authenticator mobile apps that are present in each mobile app store.
For an example of how to use this PAM module, see the blog post: .
The pam_securid PAM module provides support for multi-factor authentication. It is part of the commercial .
Note that current versions of this module are not , and the vendor does not officially support the product on MariaDB. See about that. However, the module may work with a current version of MariaDB.
The PAM module provides authentication using SSH keys.
The PAM module provides time-controlled access.
MariaDB is a multi-threaded program, which means that different connections concurrently run in different threads. Current versions of MariaDB's pam authentication plugin execute PAM module code in the server address space. This means that any PAM modules used with MariaDB must be safe for multi-threaded environments. Otherwise, if multiple clients try to authenticate with the same PAM module in parallel, undefined behavior can occur. For example, the pam_fprintd PAM module is not safe for multi-threaded environments, and if you use it with MariaDB, you may experience server crashes.
The pam authentication plugin isolates PAM module code from the server address space, so even PAM modules that are known to be unsafe for multi-threaded environments should not cause issues with MariaDB. See for more information.
When a is enabled, MariaDB won't allow an account to be created if the password validation plugin says that the account's password is too weak. This creates a problem for accounts that authenticate with the pam authentication plugin, since MariaDB has no knowledge of the user's password. When a user tries to create an account that authenticates with the pam authentication plugin, the password validation plugin would throw an error, even with set.
The workaround is to uninstall the with , and then create the account, and then reinstall the with :
Accounts that authenticate with the pam authentication plugin should be exempt from password validation checks. See and for more information.
may cause issues when using the pam authentication plugin. For example, using with the pam authentication plugin while SELinux is enabled can sometimes lead to SELinux errors involving , such as the following::
Sometimes issues like this can be fixed by updating the system's SELinux policies. You may be able to update the policies using . See for more information.
If you can't get the pam authentication plugin to work with SELinux at all, then it can help to disable SELinux entirely. See for information on how to do this.
You may run into authentication failures with the following log message in the MariaDB error log:
This can happen on operating system setups that are configured to prevent memory overcommit. When the MariaDB server process spawns the auth_pam_tool helper process there's a brief period where the new process inherits the memory of the MariaDB process before releasing that memory and executing the new command. When having a MariaDB server configured to use more than 50% of the server machine's RAM — which is common for dedicated database servers — this duplication would lead to an over-commit situation.
Current MariaDB version use posix_spawn() instead of the classic fork();exec() to prevent memory overcommit issues. However, operating systems with older glibc versions (< 2.26) still use fork();exec() to implement posix_spawn() internally. Therefore, they are still affected; this is for example still the case on RedHat Enterprise Linux 7.
Older MariaDB versions still use fork();exec() , which means they are affected by the memory overcommit issue.
To solve this you can do this:
Change the vm.overcommit_memory kernel setting to allow memory overcommit.
Alternatively, install the older auth_pam_v1 plugin version that does not spawn a helper process (but may run into problems with file permissions or multi threading with some PAM modules).
See also and .
pam_debugDescription: Enables verbose debug logging to the for all authentication handled by the plugin.
This system variable is only available when the plugin is a .
Command line: --pam-debug
pam_use_cleartext_pluginDescription: Use the client authentication plugin instead of the client authentication plugin. This may be needed for compatibility reasons, but it only supports simple PAM configurations that don't require any input besides a password.
Command line: --pam-use-cleartext-plugin
Scope: Global
Dynamic: No
pam_winbind_workaroundDescription: Configures the authentication plugin to compare the user name provided by the client with the user name returned by the PAM module in a case insensitive manner. This may be needed if you use the PAM module, which is known to convert all user names to lowercase, and which does not allow this behavior to be disabled.
Command line: --pam-winbind-workaround
Scope: Global
pamDescription: Controls how the server should treat the plugin when the server starts up.
Valid values are:
OFF - Disables the plugin without removing it from the table.
This page is licensed: CC BY-SA / Gnu FDL
Authentication using SSH keys. See the pam_ssh PAM module.
User and group mapping. See the pam_user_map PAM module.
Combining different authentication modules in interesting ways in a PAM service.
Password expiration.
Limiting access by time, date, day of the week, etc. See the pam_time PAM module.
Logging of every login attempt.
Dynamic: No
Data Type: boolean
Default Value: OFF
Introduced: MariaDB 10.2.2, MariaDB 10.1.17
Data Type: boolean
Default Value: OFF
Introduced: MariaDB 10.1.1, MariaDB 5.5.32
Data Type: boolean
Default Value: OFF
Introduced: MariaDB 10.4.5, MariaDB 10.3.15, MariaDB 10.2.24, MariaDB 10.1.39
ON - Enables the plugin. If the plugin cannot be initialized, then the server will still continue starting up, but the plugin will be disabled.FORCE - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error.
FORCE_PLUS_PERMANENT - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error. In addition, the plugin cannot be uninstalled with UNINSTALL SONAME or UNINSTALL PLUGIN while the server is running.
See Plugin Overview: Configuring Plugin Activation at Server Startup for more information.
Command line: --pam=value
Data Type: enumerated
Default Value: ON
Valid Values: OFF, ON, FORCE, FORCE_PLUS_PERMANENT
INSTALL SONAME 'auth_pam';[mariadb]
...
plugin_load_add = auth_pamINSTALL SONAME 'auth_pam_v1';[mariadb]
...
plugin_load_add = auth_pam_v1UNINSTALL SONAME 'auth_pam';UNINSTALL SONAME 'auth_pam_v1';auth required pam_unix.so audit
account required pam_unix.so audittype control module-path module-argumentsApr 14 12:56:23 localhost unix_chkpwd[3332]: check pass; user unknown
Apr 14 12:56:23 localhost unix_chkpwd[3332]: password check failed for user (alice)
Apr 14 12:56:23 localhost mysqld: pam_unix(mysql:auth): authentication failure; logname= uid=991 euid=991 tty= ruser= rhost= user=alicesudo groupadd shadow
sudo usermod -a -G shadow mysql
sudo chown root:shadow /etc/shadow
sudo chmod g+r /etc/shadowCREATE USER username@hostname IDENTIFIED VIA pam;GRANT SELECT ON db.* TO username@hostname IDENTIFIED VIA pam;CREATE USER username@hostname IDENTIFIED VIA pam USING 'mariadb';mariadb --plugin-dir=/usr/local/mysql/lib64/mysql/plugin --user=aliceERROR 2059 (HY000): Authentication plugin 'dialog' cannot be loaded: /usr/lib/mysql/plugin/dialog.so: cannot open shared object file: No such file or directory[mariadb]
...
pam_use_cleartext_pluginmysql --enable-cleartext-plugin --user=alice -pmysql_options(mysql, MYSQL_ENABLE_CLEARTEXT_PLUGIN, 1);Jan 9 05:35:41 ip-172-30-0-198 unix_chkpwd[1205]: password check failed for user (foo)
Jan 9 05:35:41 ip-172-30-0-198 mysqld: pam_unix(mariadb:auth): authentication failure; logname= uid=997 euid=997 tty= ruser= rhost= user=fooPAM: pam_start(mariadb, alice)
PAM: pam_authenticate(0)
PAM: conv: send(Enter PASSCODE:)
PAM: conv: recv(123456789)
PAM: pam_acct_mgmt(0)
PAM: pam_get_item(PAM_USER)
PAM: status = 0 user = ��\>tee /tmp/pam_log_script.sh <<EOF
#!/bin/bash
echo "\${PAM_SERVICE}:\${PAM_TYPE} - \${PAM_RUSER}@\${PAM_RHOST} is authenticating as \${PAM_USER}"
EOF
chmod 0775 /tmp/pam_log_script.shauth optional pam_exec.so log=/tmp/pam_output.txt /tmp/pam_log_script.sh
auth required pam_unix.so audit
account optional pam_exec.so log=/tmp/pam_output.txt /tmp/pam_log_script.sh
account required pam_unix.so audit*** Tue May 14 14:53:23 2019
mariadb:auth - @ is authenticating as alice
*** Tue May 14 14:53:25 2019
mariadb:account - @ is authenticating as alice
*** Tue May 14 14:53:28 2019
mariadb:auth - @ is authenticating as alice
*** Tue May 14 14:53:31 2019
mariadb:account - @ is authenticating as aliceINSTALL PLUGIN simple_password_check SONAME 'simple_password_check';
Query OK, 0 rows affected (0.002 sec)
SET GLOBAL strict_password_validation=OFF;
Query OK, 0 rows affected (0.000 sec)
CREATE USER ''@'%' IDENTIFIED VIA pam USING 'mariadb';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
UNINSTALL PLUGIN simple_password_check;
Query OK, 0 rows affected (0.000 sec)
CREATE USER ''@'%' IDENTIFIED VIA pam USING 'mariadb';
Query OK, 0 rows affected (0.000 sec)
INSTALL PLUGIN simple_password_check SONAME 'simple_password_check';
Query OK, 0 rows affected (0.001 sec)Apr 14 12:37:59 localhost setroubleshoot: Plugin Exception restorecon_source
Apr 14 12:37:59 localhost setroubleshoot: SELinux is preventing /usr/sbin/unix_chkpwd from execute access on the file . For complete SELinux messages. run sealert -l c56fe6e0-c78c-4bdb-a80f-27ef86a1ea85
Apr 14 12:37:59 localhost python: SELinux is preventing /usr/sbin/unix_chkpwd from execute access on the file .
***** Plugin catchall (100. confidence) suggests **************************
If you believe that unix_chkpwd should be allowed execute access on the file by default.
Then you should report this as a bug.
You can generate a local policy module to allow this access.
Do
allow this access for now by executing:
# grep unix_chkpwd /var/log/audit/audit.log | audit2allow -M mypol
# semodule -i mypol.pppam: cannot exec /usr/lib64/mysql/plugin/auth_pam_tool_dir/auth_pam_tool (errno: 12 "Cannot allocate memory")