This howto post focuses on configuring the MariaDB user authentication using active directory. For the example in this post, we have used a CentOS 7.5 system. Follow the steps as given below:
1. In order to configure MariaDB to authenticate users against AD, first make sure the system is configured as an AD client as per the post below:
2. Install MariaDB packages.
# yum install mariadb mariadb-server
3. Enable PAM plugin and restart MariaDB service. In /etc/my.cnf file add following under the “[mysqld]” section:
# vi /etc/my.cnf plugin-load=auth_pam.so
4. Then restart service.
# systemctl restart mariadb
5. Next, configure a PAM file to interface with MariaDB:
# vi /etc/pam.d/mysql auth include system-auth account required pam_nologin.so account include system-auth password include system-auth session optional pam_keyinit.so force revoke session include system-auth session required pam_loginuid.so
6. After configuring MariaDB by running the initial installation script, login as root user then create ‘catchall‘ user in MariaDB and configure to use PAM configuration:
CREATE USER ''@'%' IDENTIFIED VIA pam USING 'mysql';
CREATE USER 'aduser'@'%' IDENTIFIED VIA pam USING 'mysql';
7. Finally, grant permissions in MariaDB and specify PAM as the authentication mechanism:
GRANT ALL PRIVILEGES on [database].* to '[user]'@'[host]' IDENTIFIED VIA pam;
GRANT ALL PRIVILEGES on db1.* to 'aduser'@'%' IDENTIFIED VIA pam;