.::: Create User Access MariaDB MySQL using login Active Directory Windows via PAM :::.

 

1. correlation from https://teguhth.blogspot.com/2019/08/join-domain-active-directory-on-centos.html

2. enable pam plugin 

INSTALL SONAME 'pam';
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%pam%';

MariaDB [(none)]> INSTALL SONAME 'pam';
Query OK, 0 rows affected (0.007 sec)

MariaDB [(none)]>
MariaDB [(none)]> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%pam%';
+-------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------+---------------+
| pam         | ACTIVE        |
+-------------+---------------+
1 row in set (0.002 sec)

MariaDB [(none)]>
 
3. adjust sssd.conf

[root@teguhth-all mysql]# cat /etc/sssd/sssd.conf

[sssd]
domains = teguhth.com
config_file_version = 2
services = nss, pam

[domain/teguhth.com]
default_shell = /bin/bash
krb5_store_password_if_offline = True
cache_credentials = True
krb5_realm = TEGUHTH.COM
realmd_tags = manages-system joined-with-adcli
id_provider = ad
fallback_homedir = /home/%u@%d
ad_domain = teguhth.com
use_fully_qualified_names = False
ldap_id_mapping = True
#access_provider = ad
access_provider = permit
[root@teguhth-all mysql]#

4. configure pam 

[root@teguhth-all mysql]# cat /etc/pam.d/mariadb
auth    required pam_sss.so
account required pam_sss.so
[root@teguhth-all mysql]#

5. check gent 

[root@teguhth-all ~]# getent passwd lab
lab:*:92201105:92200513:lab:/home/lab@teguhth.com:/bin/bash
[root@teguhth-all ~]#
[root@teguhth-all ~]# getent passwd lab@teguhth.com
lab:*:92201105:92200513:lab:/home/lab@teguhth.com:/bin/bash
[root@teguhth-all ~]#
[root@teguhth-all ~]# getent passwd maria
maria:*:92201108:92200513:maria:/home/maria@teguhth.com:/bin/bash
[root@teguhth-all ~]#
[root@teguhth-all ~]# getent passwd maria@teguhth.com
maria:*:92201108:92200513:maria:/home/maria@teguhth.com:/bin/bash
[root@teguhth-all ~]#

6. create user using pam ad

CREATE USER `lab`@`%` IDENTIFIED VIA pam USING 'mariadb';

CREATE USER `maria`@`%` IDENTIFIED VIA pam USING 'mariadb';

CREATE USER `lab`@`%` IDENTIFIED VIA pam USING 'mariadb';

CREATE USER `maria@teguhth.com`@`%` IDENTIFIED VIA pam USING 'mariadb';

7. check user pam 

select user,host,plugin from mysql.user where plugin='pam';
SELECT plugin_name,plugin_version,plugin_status,plugin_type,plugin_library,plugin_author FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%pam%';

MariaDB [(none)]> select user,host,plugin from mysql.user where plugin='pam';
+-------------------+------+--------+
| User              | Host | plugin |
+-------------------+------+--------+
| lab@teguhth.com   | %    | pam    |
| lab               | %    | pam    |
| maria             | %    | pam    |
| maria@teguhth.com | %    | pam    |
+-------------------+------+--------+
4 rows in set (0.002 sec)

MariaDB [(none)]>

MariaDB [(none)]> SELECT plugin_name,plugin_version,plugin_status,plugin_type,plugin_library,plugin_author FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%pam%';
+-------------+----------------+---------------+----------------+----------------+---------------+
| plugin_name | plugin_version | plugin_status | plugin_type    | plugin_library | plugin_author |
+-------------+----------------+---------------+----------------+----------------+---------------+
| pam         | 2.0            | ACTIVE        | AUTHENTICATION | auth_pam.so    | MariaDB Corp  |
+-------------+----------------+---------------+----------------+----------------+---------------+
1 row in set (0.003 sec)

MariaDB [(none)]>
 

8. testing 

mysql -h 10.10.10.90 -u lab -p
mysql -h 10.10.10.90 -u lab@teguhth.com -p
mysql -h 10.10.10.90 -u maria -p
mysql -h 10.10.10.90 -u maria@teguhth.com -p

mariadb -h 10.10.10.90 -u lab -p
mariadb -h 10.10.10.90 -u lab@teguhth.com -p
mariadb -h 10.10.10.90 -u maria -p
mariadb -h 10.10.10.90 -u maria@teguhth.com -p