December 31st, 2012, 06:32 AM
User authentication using T-SQL and/or VBA with LDAP
I have a VBA-program that uses SQL Server as database and ActiveDirectory (in Windows Server 2008 R2) as user authentication and I'm having big time problems with getting the LDAP-part there to work.
This is a program for a bigger corporation - several companies that are not allowed see the data of other companies - and each company has their own projects of which each project uses this program. I need to be able to have a simple function that takes as parameters the userID (i.e. ActiveDirectory user) and the project the user is trying to access. Both of those are easily done with VBA. The function needs to return the level of access the user got: No Access, Read Only or Full Access. Yes, I'm aware that VBA is relatively easy to crack, but let's put that part aside, the users here are not very advanced and the info is not _that_ secret anyway.
While I could implement this with making two groups (Full Access and Read Only) for each project, I have here also users like IT sector and financial sector and the board of directors that needs to have full access on every project there is. I also have company leaders that can only access the info of their own company. This still could work as just putting each user into each project they have access.
Now clearly I could make this kind of groups:
-Corporation full access (read and write every single project there is)
-Corporation read only (read every single project there is, write to none)
-Company A full access (read and write to Company A projects, no access to Company B projects)
-Company A read only (read Company A projects, write to none)
-Company B full access (read and write to Company B projects, no access to Company A projects)
-Company B read only (read Company B projects, write to none)
-Project A1 full access (read and write to Project 1 of Company A, no access to other projects)
-Project A2 full access (read and write to Project 2 of Company A, no access to other projects)
-Project B1 full access (read and write to Project 1 of Company B, no access to other projects)
-Project B2 full access (read and write to Project 2 of Company B, no access to other projects)
-Project A1 read only (read Project 1 of Company A, no access to other projects)
-Project A2 read only (read Project 2 of Company A, no access to other projects)
-Project B1 read only (read Project 1 of Company B, no access to other projects)
-Project B2 read only (read Project 2 of Company B, no access to other projects)
but how do I make an LDAP-query that gets as parameters
1) The name of user, say, the owner of the whole corporation
2) The project, say, Project5G (company 5, project 7)
and returns as answer "He has full access to it".
I would need the answer in that kind of form that I can embed it to either
1) VBA code
2) SQL Server as T-SQL code.
For VBA the function could look this
Function AuthenticateUser(strUser As String, strProject As String) As Integer
'And to here the LDAP-code that I'm asking for here
For SQL Server / T-SQL I have already a linked server so I can query and receive info of each user. Like this:
WHERE objectClass <> ''computer''
) AS sqone
) AS sqtwo
ORDER BY sAMAccountName
I can also easily mix and match VBA and T-SQL code, so as long as I get that LDAP-code, I think I can manage.
But the hard part for me here is that while I can ask that is a user a part of a certain group, I have no idea how to do it for nested groups. So while I know that person X is a part of Company A board and thus should have the full access to project Project1A, I can only receive the info that he is a part of the board of Company A and that's it. So I don't know how to extend the query to return also that Company A board has a full access to all Company A projects => person X has "indirectly" access to Project1A (not by being person X, but by being a part of the board of Company A).
Also, if you feel like there's some simpler and/or better solution, I'm totally open to hear it.