MySQL Enterprise Edition supports an authentication method that enables MySQL Server to use LDAP (Lightweight Directory Access Protocol) to authenticate MySQL users by accessing directory services such as X.500. MySQL uses LDAP to fetch user, credential, and group information.
The same user names, passwords and permissions can be used to enhance the security by leveraging existing AD rules for strong password enforcement, password expiry, etc.
LDAP authentication enables MySQL Server to accept connections from users defined outside the MySQL grant tables in LDAP directories.
Integrate existing AD to MySQL Enterprise hosted in Linux in 2 steps
Prerequisites
Microsoft AD or Azure AD.
Sufficient privileges to create a AD user in Windows AD Domain Controller or Azure.
Connectivity between Domain Controller (DC) to Linux Server. Appropriate TCP/UDP ports needs to be allowed, and whitelisted to Security Groups or Firewalls.
Test whether telnet is successful to DC (Port: 389) from MySQL Server.
~ Make a manual entry in /etc/hosts file in in MySQL Server if unable to resolve DNS
Copied to clipboard!
172.13.19.107 IP-C6130110.tessellpoc.com
Test ldap authentication by using ldapsearch to AD Server.
~ ldapsearch using SIMPLE authentication. For example below we have user john.smith@tessellpoc.com registered in AD
Copied to clipboard!
[ec2-user@ip-10-10-54-42 ~]$ ldapsearch -x -h 172.31.19.107 -D "cn=john smith, ou=users, ou=tessell, dc=tessellpoc, dc=com" -b "cn=john smith, ou=users, ou=tessell, dc=tessellpoc, dc=com" -W
Enter LDAP Password:
# extended LDIF
#
# LDAPv3
# base cn=john smith, ou=users, ou=tessell, dc=tessellpoc, dc=com with scope subtree
# filter: (objectclass=*)
# requesting: ALL
#
# john smith, Users, tessell, tessellpoc.com
dn: CN=john smith,OU=Users,OU=tessell,DC=tessellpoc,DC=com
objectClass: top
objectClass: person
objectClass: organizationalPerson
objectClass: user
cn: john smith
sn: smith
givenName: john
distinguishedName: CN=john smith,OU=Users,OU=tessell,DC=tessellpoc,DC=com
instanceType: 4
whenCreated: 20240131120639.0Z
whenChanged: 20240131121658.0Z
displayName: john smith
uSNCreated: 59907
uSNChanged: 59953
name: john smith
objectGUID:: 4s0CMuyRo0ecQ6MvPpvGPQ==
userAccountControl: 512
badPwdCount: 0
codePage: 0
countryCode: 0
badPasswordTime: 133518346365243210
lastLogon: 133518346452412448
pwdLastSet: 133511763990930643
primaryGroupID: 513
objectSid:: AQUAAAAAAAUVAAAAbQoP3GssrQfjyE8lXwYAAA==
accountExpires: 9223372036854775807
logonCount: 0
sAMAccountName: john.smith
sAMAccountType: 805306368
userPrincipalName: john.smith@tessellpoc.com
objectCategory: CN=Person,CN=Schema,CN=Configuration,DC=tessellpoc,DC=com
dSCorePropagationData: 16010101000000.0Z
lastLogonTimestamp: 133511770189880265
# search result
search: 2
result: 0 Success
# numResponses: 2
# numEntries: 1
Enable LDAP Server Side plugin in MySQL Enterprise
MySQL supports two types of authentication methods using LDAP - LDAP Simple and LDAP SASL. We will demonstrate the use of LDAP Simple method due to its compatibility with Microsoft AD Server.
Server side plugin file authentication_ldap_simple.so is already present with MySQL Enterprise software distribution under <default installation path>/lib/plugin/
~ Modify or Add following Server side variables in my.cnf
Alternatively we can load the plugin at runtime using below steps.
Copied to clipboard!
MySQL Shell 8.0.33
Copyright (c) 2016, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'root@localhost:3306'
Fetching global names for auto-completion... Press ^C to stop.
Your MySQL connection id is 23
Server version: 8.0.33-commercial MySQL Enterprise Server - Commercial
No default schema selected; type \use schema to set one.
MySQL localhost:3306 ssl SQL > INSTALL PLUGIN authentication_ldap_simple SONAME 'authentication_ldap_simple.so';
Query OK, 0 rows affected (0.0047 sec)
MySQL localhost:3306 ssl SQL > SET PERSIST authentication_ldap_simple_server_host="IP-C6130110.tessellpoc.com";
Query OK, 0 rows affected (0.0024 sec)
MySQL localhost:3306 ssl SQL > SET PERSIST authentication_ldap_simple_bind_base_dn="OU=Users,OU=tessell,DC=tessellpoc,DC=com" ;
Query OK, 0 rows affected (0.0026 sec)
MySQL localhost:3306 ssl SQL >
Check Plugin status
Copied to clipboard!
MySQL localhost:3306 ssl SQL > SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%ldap%';
+----------------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+----------------------------+---------------+
| authentication_ldap_simple | ACTIVE |
+----------------------------+---------------+
1 row in set (0.0008 sec)
Create MySQL User with same user name as AD user as below. You can repeat this for all the users to be added for accessing databases.
~ The Distinguished Name suffix you can obtain from AD Server using ldapsearch (example given above).
~ For john.smith@tessellpoc.com we have DN as CN=john smith,OU=Users,OU=tessell,DC=tessellpoc,DC=com
Copied to clipboard!
MySQL localhost:3306 ssl SQL > CREATE USER 'john.smith' IDENTIFIED WITH authentication_ldap_simple AS 'CN=john smith,OU=Users,OU=tessell,DC=tessellpoc,DC=com';
Query OK, 0 rows affected (0.0108 sec)
Use MySQL Client to authenticate. Here we are using the MySQL client which resides in the MySQL Server.
~ For LDAP simple authentication configured with the use of server-side authentication_ldap_simple plugin, invoke client programs (mysql client) with the --enable-cleartext-plugin option to enable the client-side mysql_clear_password plugin. For example below:
Copied to clipboard!
[ec2-user@ip-10-10-54-42 ~]$ mysql -u 'john.smith' -p --enable-cleartext-plugin --protocol=tcp
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 49
Server version: 8.0.33-commercial MySQL Enterprise Server - Commercial
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
We can now successfully integrate AD authentication using LDAP leveraging advanced security features and ease of user credential management using readily available plugins for MySQL Enterprise edition.
