7

(Apologies if I'm asking the wrong kind of question or in the wrong place.)

We run IT for our association (all volunteers). We have got a server with member database in OpenLDAP, mail server, ftp and a bunch of homemade web-applications.

Most of this is fine, except for the OpenLDAP member database. The people who set it up are long gone and the current IT group is not really capable of making changes.

So, I was thinking about moving the member database from OpenLDAP to a MySQL database. This seems possible, our email and FTP server support SQL sources and we can modify our webapps accordingly. But I still can't decide if there is a disadvantage to doing this. Hence my questions:

  • What are the advantages of an LDAP over an SQL database? (For simple user database)
  • Are there reasons not to use MySQL as a users database?

I couldn't find a comparable question around here. And information I find externally takes me to pages of over 10 years old.

  • 7
    If you're going to SQL, for the love of Ghu at least make it PostgreSQL... – Shadur Sep 7 '19 at 23:26
  • do your members login somewhere? also, it's not super hard to edit with Apache DS – Neil McGuigan Sep 8 '19 at 1:26
  • I'll consider PostgreSQL! And yes, members login in centrally for the webapps and of course they login for FTP and mail. – Roberto Sep 8 '19 at 7:58
  • 7
    Sounds like LDAP is what you want. That's pretty much exactly what it's designed for. You'll get significantly better mileage out of learning How To LDAP than you will out of installing an SQL database, learning how to admin PostgreSQL, designing a user access schema, implementing it and rewriting all of your apps to use it. – Shadur Sep 8 '19 at 8:12
12

This is a broad topic with no simple answer, as it depends on a lot of factors.

Some benefits of (Open)LDAP as user database:

  • LDAP has an inherent tree structure that can make structuring large organizations easier. It's also easier to have a data field for a record that is really a list of things (like "the user is member of these groups") then doing this in SQL databases (note: both is absolutely possible in SQL, just more complicated).
  • It's easier to use LDAP as a central user database for multiple systems. This is not so much an issue for things like Mail or FTP that could also use Linux PAM as auth source or for home-grown systems you control, but especially if you add third party web applications, you often end up with each system having its own user database with incompatible schemas that can't share any data. In that case, you can often at least sync with and/or auth against LDAP.
  • LDAP object classes for records makes it easy to use the same database for a lot of different systems with different requirements - just add an object class to the record and get a whole bunch of class-specific fields that are not present with other records and would need to be a whole lot of empty fields for every record in SQL.
  • LDAP can be a black box auth system. This means you can have an app that only sends a username and password to LDAP and ask "Is this combo valid?" and get a Yes/No answer. With SQL, the app actually needs to read the database and do the check itself.

Unfortunately, actually managing LDAP is much more involved compared to a simple SQL database and if all your systems can fully work with the same SQL user database, there is no real reason why you need to have LDAP instead. On the other hand, I would think very long and hard about if it's not actually easier to sit down and understand LDAP before ripping it out and replacing it with something else. These kind of changes tend to look easy when you start, but along the way there are always issues appearing you haven't considered that makes things a lot harder then anticipated.

| improve this answer | |
  • 3
    Besides, doesn't LDAP software usually use an SQL database of one stripe or another as backend? – Shadur Sep 7 '19 at 23:27
  • 2
    @Shadur: Normally, OpenLDAP and 389DS use some Berkeley DB variant as storage backend (which is not an SQL database, it's a key-value store), but you can configure others for special needs. Apache DS does something similar, but I don't know what MS does for the AD LDAP component (my guess: It's also not SQL). – Sven Sep 8 '19 at 4:40
  • @Shadur LDAP is itself a database, not just a protocol (the same way people consider SQL a database, not just a database query language). Most LDAP vendors develop software as a database vendor so for most it doesn't make sense to implement LDAP on top of SQL. That's like implementing Mysql on top of Postgresql. (I used to work for Nokia-Siemens developing subscriber databases supporting both DAP and LDAP, the data store is memmapped hard disks storing raw C structs) – slebetman Sep 8 '19 at 21:56
  • @siebetman I stand corrected. Thank you. – Shadur Sep 9 '19 at 5:46
4

Can you use a MySQL database as a shared source of user accounts? Yes, definitely. I have mounted such system and it works quite well with multiple applications.

So, which are the drawbacks of using a MySQL database instead of LDAP?

a) You need to adapt all the applications to use your database.

It's usually not a lot of work to adapt an application. Often, you can just solve it by making a view that adapts the column names. Sometimes, you may need to add a bit of code to cater for a different password hashing format, or to create an authentication plugin.

However, you will need to do that for each and every application. And when you are asked tomorrow to install a new web-application (e.g. someone decides to add a Wordpress blog), you will need to do again study the application and see how to adapt it.

'Everyone' supports authenticating against LDAP. It is the de facto standard, and you will find plugins for doing LDAP authentication on any application with a decent size (and for those that don't, there's a clear case for implementing that). There are a few (very few) alternatives, and they have little support for being used for authentication.

Also note, if an application happens to use a different database engine (suppose there is a new development that requires using PostgreSQL), it may be harder to additionally make it support a user table from a different engine (mysql).

Of course, the adaptation itself, you can only do that if it's your own development or open source. Forget about getting Microsoft Windows to support such custom scheme!

b) Security centralization

Using a central authentication server has multiple benefits:

For one, if you have a central server, and someone tries to bruteforce the password for user john, a common setup is to have it block the user for some time (or until manually unblocked). If you have a dozen of services, even if all of them had some throttling measures (hint: they probably don't), each of them would be separated, and by attacking multiple services an attacker would actually have N tries times number of services. You would need to coordinate that metadata into the shared database, and all the services to check them and implement in the same way (more code to add to the applications, mail and ftp servers...).

Second, central logging. Performing authentication on a single service allows you to have a single authentication log, rather than having it split over multiple places.

Third, it is a single point for updates. You want to move away from MD5 hashes to use pdkdf2 or Argon2? You only need to update the authentication server to support it. Else you would need to get each and every service to support that new format.

Fourth, greater isolation. If all authentication goes through the central server, it should be configured so that he is the only one able to read the sensitive data, and you can concentrate your efforts to make sure it is secure. However, if every service needs to verify the user passwords, you have a much greater surface: a SQL injection vulnerability on any of them would allow dumping the list of users and hashes (and I am already assuming that it was made read-only, otherwise they may even be able to modify them, or insert fake users).


Please note that you could solve (b) by having a central server that didn't use LDAP at all. But given (a) it makes sense to keep using LDAP protocol for authentication.

There's no reason a LDAP server mainly used for authentication couldn't use a MySQL database for storage. In fact, LDAP is much more potent than the typical use it gets (which is why it looks so daunting). However, I am not aware of any implementation doing that.

My recommendation is to keep your current LDAP server and create if needed an interface for performing the most common actions on it (there are multiple LDAP frontends, maybe one of them would cover your IT group needs?).

If you wanted to change your custom web applications login process, I would make them to support a Single Sign-On solution such as SAML, but I would nonetheless still have it use that LDAP backend, which would be shared with your FTP, mail and other services.

| improve this answer | |
3

Just wanted to add something not (yet) pointed out in the other answers: comparing LDAP and SQL is comparing apples and oranges since they sit at different layers in the stack.

  • LDAP = supports directory services over IP
  • SQL = generic database management, which could of course include user management

You can implement LDAP using SQL databases, but just remember they support different features. (In fact, most LDAP solutions probably use some SQL or other database behind the scenes.)

LDAP is an industry standard for directory services. It's possible that many of your downstream services rely on the LDAP service for authentication, authorization, etc, in which case you would still need to build an LDAP compatible layer on top of your new SQL database.

At the same time, it's possible that your other applications don't need this LDAP compatibility, and it might be easier to migrate to your own SQL user management. But even in this case, I suspect you might need your own user management logic in the applications (unless they support OAuth or something similar.)

I would recommend looking at your other IT applications first to see how tied they are to the LDAP service for login and authorization services, and then decide how easy or painful it would be to migrate to a custom SQL database solution.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.