4 ms·

Because certain groups of users need the same sets of permissions on hundreds of databases and servers. And the members of those groups are constantly changing.

by zaat 7y ago

Because certain groups of users need the same sets of permissions on hundreds of databases and servers. And the members of those groups are constantly changing. In addition this ties an identity across system and facilitates SSO on the one hand and auditing on the other. There's more to it but these are the most obvious. This is a common, standard requirement in enterprise systems, and it seem quite sensible. If you have a better, more robust solution, there are billions in this market, in the most literal way. One more thing, although Active Directory is a standard in the enterprise, LDAP is an open protocol that existed before Microsoft were dreaming on being a player in the enterprise field and have at least half a dozen open source implementations.

AnonymousPlanet 7y ago
> Because certain groups of users need the same sets of permissions on hundreds of databases and servers. And the members of those groups are constantly changing. In addition this ties an identity across system and facilitates SSO on the one hand and auditing on the other. There's more to it but these are the most obvious. That is precisely what I meant with messed up requirements. Why would any of your SSO be relevant on the database level? A database user should be coupled to an application, not to a single user in AD. If you are letting constantly changing users do their own SQL requests on DB level, there is something rotten in the first place. > This is a common, standard requirement in enterprise systems, and it seem quite sensible. If you have a better, more robust solution, there are billions in this market, in the most literal way. I have never come across this kind of setup in a normal enterprise environment. There, you have everything behind some kind of enterprise software. Data entry, auditing, etc. However, you could have need for this if we are talking developers sharing a single database and you don't want to manage accounts and passwords separately. You said the solutions you found for Postgres were too "hacky". I think if you have a setup where enterprise users who are not developers or DBAs need personal access on DB level, your setup is quite "hacky" to begin with. > One more thing, although Active Directory is a standard in the enterprise, LDAP is an open protocol that existed before Microsoft were dreaming on being a player in the enterprise field and have at least half a dozen open source implementations. Microsoft has bent LDAP and Kerberos in its own way. Trying to use AD like an LDAP server is full of unpleasant surprises dealt to you by badly documented or outright undocumented "features", and useless error messages and logging. Believe me, it's a major nightmare to get anything integrated with AD.
- zaat 7y ago
  There are tens of DBAs, hundreds of developers, hundreds of application users, those are the users in those groups. I have done my fair share of integration with AD, including in some of the largest enterprises existing on this planet, where you have hundreds of groups of developers and admins. I read the RFCs and the documentation, in most cases, it is actually quite good and detailed, both on their LDAP and on their Kerberos implementation details. > I have never come across this kind of setup in a normal enterprise environment. There, you have everything behind some kind of enterprise software. Data entry, auditing, etc. Well, for you everything is behind the enterprise software, but I'm among those that need implementing it.
  - AnonymousPlanet 7y ago
    > There are tens of DBAs, hundreds of developers, hundreds of application users, those are the users in those groups. Ah, so this is a special case with a shop that has developers and DBAs in their user base. Now that I can understand. > I read the RFCs and the documentation, in most cases, it is actually quite good and detailed, both on their LDAP and on their Kerberos implementation details. Then you know what a giant mess all of it is. I have done my share of LDAP/AD integration as well and found that LDAP is a very malleable tool that has been bent in many ways. There are many details to get right. MS use a certain subset of schemata and have added their special flavoured behaviour in the background. That means, what the AD admin sees is not exactly what an LDAP query might return. Similarly Kerberos or any other authentication scheme like e.g. SAML. Getting all of this right from the view of a third party application is not simple. That's why I'm never surprised why pretty much all of them are incomplete or incorrect in this manner. Oracle offers a similar integration for AD. Do you happen to have any experience with it and if so, how does it compare to MS SQL?
    - zaat 7y ago
      I was never asked to give a hand in Oracle integration, hence I believe it is quite straight forward. In my experience usually third party tools, i.e. ldapsearch, don't implement all AD options, if will use them for a LDAP query with AD features you will receive legitimate reply, but they will not be humanly readable as they were not parsed by the tool. However, the documentation from Microsoft in this front is actually surprisingly extensive and if anyone felt like it they could implement the correct parsing. It will also quite safe to assume at this point that intentional changes in the future in those areas are extremely unlikely. Reading and understanding the LDAP and Kerberos implementation is quite a task, and I can fully understand why no one sat to re-implement it all in open source. Most of the time, even features that are implemented in open source libraries, for stuff like GSSAPI as an example, are not implemented in the software that uses them. And in this I find PostgreSQL is extremely lacking. In comparison, Apache lets you use LDAP groups for ACLs, NGinx have a unmaintained plugin stripped from the Apache base code and it only implements support for LDAP users, not groups. PostgreSQL will only let you authenticate users that match users that you already defined in PostgreSQL. I'm far from being Microsoft fan in general, and I suffer whenever I have to use their OS. But AD is a robust piece of work and pretty much the only game in town. I feel that is is not in their focus right now, as is isn't part of their everything Azure strategy, and that's a shame.

by zaat 7y ago

AnonymousPlanet 7y ago

> Because certain groups of users need the same sets of permissions on hundreds of databases and servers. And the members of those groups are constantly changing. In addition this ties an identity across system and facilitates SSO on the one hand and auditing on the other. There's more to it but these are the most obvious. That is precisely what I meant with messed up requirements. Why would any of your SSO be relevant on the database level? A database user should be coupled to an application, not to a single user in AD. If you are letting constantly changing users do their own SQL requests on DB level, there is something rotten in the first place. > This is a common, standard requirement in enterprise systems, and it seem quite sensible. If you have a better, more robust solution, there are billions in this market, in the most literal way. I have never come across this kind of setup in a normal enterprise environment. There, you have everything behind some kind of enterprise software. Data entry, auditing, etc. However, you could have need for this if we are talking developers sharing a single database and you don't want to manage accounts and passwords separately. You said the solutions you found for Postgres were too "hacky". I think if you have a setup where enterprise users who are not developers or DBAs need personal access on DB level, your setup is quite "hacky" to begin with. > One more thing, although Active Directory is a standard in the enterprise, LDAP is an open protocol that existed before Microsoft were dreaming on being a player in the enterprise field and have at least half a dozen open source implementations. Microsoft has bent LDAP and Kerberos in its own way. Trying to use AD like an LDAP server is full of unpleasant surprises dealt to you by badly documented or outright undocumented "features", and useless error messages and logging. Believe me, it's a major nightmare to get anything integrated with AD.

zaat 7y ago
There are tens of DBAs, hundreds of developers, hundreds of application users, those are the users in those groups. I have done my fair share of integration with AD, including in some of the largest enterprises existing on this planet, where you have hundreds of groups of developers and admins. I read the RFCs and the documentation, in most cases, it is actually quite good and detailed, both on their LDAP and on their Kerberos implementation details. > I have never come across this kind of setup in a normal enterprise environment. There, you have everything behind some kind of enterprise software. Data entry, auditing, etc. Well, for you everything is behind the enterprise software, but I'm among those that need implementing it.
- AnonymousPlanet 7y ago
  > There are tens of DBAs, hundreds of developers, hundreds of application users, those are the users in those groups. Ah, so this is a special case with a shop that has developers and DBAs in their user base. Now that I can understand. > I read the RFCs and the documentation, in most cases, it is actually quite good and detailed, both on their LDAP and on their Kerberos implementation details. Then you know what a giant mess all of it is. I have done my share of LDAP/AD integration as well and found that LDAP is a very malleable tool that has been bent in many ways. There are many details to get right. MS use a certain subset of schemata and have added their special flavoured behaviour in the background. That means, what the AD admin sees is not exactly what an LDAP query might return. Similarly Kerberos or any other authentication scheme like e.g. SAML. Getting all of this right from the view of a third party application is not simple. That's why I'm never surprised why pretty much all of them are incomplete or incorrect in this manner. Oracle offers a similar integration for AD. Do you happen to have any experience with it and if so, how does it compare to MS SQL?
  - zaat 7y ago
    I was never asked to give a hand in Oracle integration, hence I believe it is quite straight forward. In my experience usually third party tools, i.e. ldapsearch, don't implement all AD options, if will use them for a LDAP query with AD features you will receive legitimate reply, but they will not be humanly readable as they were not parsed by the tool. However, the documentation from Microsoft in this front is actually surprisingly extensive and if anyone felt like it they could implement the correct parsing. It will also quite safe to assume at this point that intentional changes in the future in those areas are extremely unlikely. Reading and understanding the LDAP and Kerberos implementation is quite a task, and I can fully understand why no one sat to re-implement it all in open source. Most of the time, even features that are implemented in open source libraries, for stuff like GSSAPI as an example, are not implemented in the software that uses them. And in this I find PostgreSQL is extremely lacking. In comparison, Apache lets you use LDAP groups for ACLs, NGinx have a unmaintained plugin stripped from the Apache base code and it only implements support for LDAP users, not groups. PostgreSQL will only let you authenticate users that match users that you already defined in PostgreSQL. I'm far from being Microsoft fan in general, and I suffer whenever I have to use their OS. But AD is a robust piece of work and pretty much the only game in town. I feel that is is not in their focus right now, as is isn't part of their everything Azure strategy, and that's a shame.