Examples of using Domain login to control user access

[JMongi]

I realized after the fact that I might be reinventing the wheel and adding administrative overhead I do not need or have time for when it comes to user access to my database as we already have windows domain logins. What I can't seem to find are any robust examples of using domain usernames along with permission tables as well as session logging. I have existing tables and forms from @isladogs password login sample database and I don't want to throw out the baby with the bathwater. So, I was hoping to find some good examples using domain usernames so I can see how I can replace the authentication portion without too much alteration to the existing session logging and access control portions. I tried many different searches and have found many posts by @The_Doc_Man (and others) referring to the VBA code to pull the username, but as of yet haven't found a more thorough discussion of the implementation.

If I could be pointed in the right direction of existing threads or sites that discuss this, that would be great. One thing that might scuttle the whole ship is this...is the idea to use Active Directory Groups to manage form/data access or to use a domain username as a trusted user authentication but manage access within MS Access itself? It's never very clear which is being discussed. The former would be more cumbersome as the domain registration and user permissions are managed by a 3rd party IT company. I was envisioning the latter, using domain usernames to authenticate (instead of rolling my own username/password infrastructure) and then managing form access within the table structure of the database.

I hope I've made that more clear instead of less clear

 

[theDBguy]

Hi. I imagine it would simply be a matter of replacing any reference to the login information with the call to the network username function or variable. No?

 

[JMongi]

I suppose so. When you roll your own username, you handle things like new usernames and first time logins a little differently. I guess I'm trying to wrap my head around handling a first time user and how to write their info into the permission table for the first time when I won't have direct access to their domain name to do it ahead of time. I'm probably over complicating it. I also wouldn't mind seeing a fully functional domain login version just to see the nuts and bolts of it all.

 

[theDBguy]

I suppose so. When you roll your own username, you handle things like new usernames and first time logins a little differently. I guess I'm trying to wrap my head around handling a first time user and how to write their info into the permission table for the first time when I won't have direct access to their domain name to do it ahead of time. I'm probably over complicating it. I also wouldn't mind seeing a fully functional domain login version just to see the nuts and bolts of it all.

When you say "domain name," are you referring to their legal name or just the network username?

 

[JMongi]

Sorry, I'm probably using these terms a little loosely. I mean their network username that has an associated password that they use to logon to our network domain.

 

[theDBguy]

Sorry, I'm probably using these terms a little loosely. I mean their network username that has an associated password that they use to logon to our network domain.

Okay, in that case, you should have access to it using either Environ() or this non-API function.

 

[JMongi]

Right. I meant I wouldn't have access to it to put an initial entry in the permissions table. So, I'll have to have code to check for a new user connection and create a default low level permission for new database connections. I guess it's not too complicated, I'm always a little leery of me figuring things out on the fly for the first time when there are much more seasoned developers that have been down this path many times before.

 

[theDBguy]

Right. I meant I wouldn't have access to it to put an initial entry in the permissions table. So, I'll have to have code to check for a new user connection and create a default low level permission for new database connections. I guess it's not too complicated, I'm always a little leery of me figuring things out on the fly for the first time when there are much more seasoned developers that have been down this path many times before.

Will you be storing the usernames with their permissions in a table? If so, you should be able to simply check the table if the result of the GetUsername() function (or whichever you're using to get the network username) exists or not. If not, it's a new user.

 

[JMongi]

Yes I will. That's the way I envisioned it as well.

 

[theDBguy]

Yes I will. That's the way I envisioned it as well.

Alright, let us know how it goes. Good luck!

 

[Gasman]

Wouldn't that be up to the IT dept to inform you? if you start using their levels?
In Lloyds bank we were constantly chasing IT to find out what this person was authorised to do. They would not update our list for new users promptly enough, yet would delete users who had left, but whose cases we were still processing.

 

[Pat Hartman]

In all cases, I create a form that can create a new user and assign roles to it. Then I assign the "create user" role to an administrator or two for backup. Then the process of adding a new user goes through the admin person rather than me, the database developer. You should not insinuate yourself into the day-to-day operation of the application. You need to make the app as self sufficient as possible. This is also the reason I always create table maintenance forms that allow the users to manage their own lookups.

 

[The_Doc_Man]

Just as a comparison discussion, my system for the Navy had to comply with various regulations. So what happened was that the potentially new user would try to log in. My auto-setup involved the user ID being used to send a message with the fact that user x.a.smith tried to get in, and their account WAS created - but disabled. When I or my assistant got the mail, we would check with the department supervisors to see if they even knew that person. From that discussion, we would alter the user role from "Disabled" (actually a code of 0 in the role slot) to either Reviewer (code 1, everything was read-only), or SysAdmin (code 2) or DB Admin (code 3). The system administrators in my department used the DB to record patch activity for the 1200 to 1500 servers we managed in our server farm. The project managers could log in to get "rollups" of their projects so they could ask the admin teams why their system hadn't been patched yet or off up a "WTF" or anything else.

We had a strongly managed domain-based login, so with our security manager's permission, I trusted the domain login and just used the networked user ID. As pointed out, you can make an entry if you find that the person isn't in the table. The point of this is, I got the IT security guy to agree to this plan BEFORE I actually implemented it. Once he blessed it, I was golden and the security team left it alone.

 

[JMongi]

Wouldn't that be up to the IT dept to inform you? if you start using their levels?

1. We are a small company. We don't have an IT department. We have a 3rd party IT service that manages our domain and exchange server.
2. I believe the point is that I wouldn't be using Group permissions on the windows level to manage access levels within my database. I would only be using the existing domain login information to authenticate a person's identity. Further granular access would be managed within the database, not the Windows environment.

In all cases, I create a form that can create a new user and assign roles to it.

That is good practice and I would still want/need that capability for db administrators. This is really an exploration of an edge case about new domain users.

So what happened was that the potentially new user would try to log in. My auto-setup involved the user ID being used to send a message with the fact that user x.a.smith tried to get in, and their account WAS created - but disabled.

This is what I envisioned. But, as you can tell, this is my first rodeo. So, I am always open for input.

Thanks for everyone's input. I feel more comfortable with my general approach now.

@Pat Hartman - It might be the morning without coffee, but, can you elaborate on this statement. I feel it may fall into the category I know the least about, DB administration when it is up and running.

This is also the reason I always create table maintenance forms that allow the users to manage their own lookups.

 

[The_Doc_Man]

As you have obviously recognized, there is an issue with using Group permissions for anything other than initial access. The problem? They aren't directly applicable to PARTS of a file. Once you are "in" you are all the way in with Access, so if you want to implement roles within the DB App, you have to "roll your own" screens.

My next discussion is based on a U.S. Navy database using Access. In overview, it was a record of system administrators having patched servers to comply with Navy regulations. We would know we HAD patched, but the problem was to provide reports for the project managers whose servers we managed. We were a server farm housed at the Naval Data Enterprise Center New Orleans. We had as many as 40 admins registered at one time, with varying numbers of projects and servers as we grew.

We had a pair of group identifiers. Since I believe those DBs are potentially still in use, I'll have to make up the names. So... if you were going to be a user of the DB, you were made a member of the group SECHIST_USER but if you were an admin, you were a member of SECHIST_ADMIN. The difference? The _USER group got MODIFY-level access to the folder, for which the OWNER was the _ADMIN group. (This allowed the _ADMIN users to grant rights as needed.)

The app had an opening form declared that was my dispatcher form. It had an _OPEN routine to look up who you were, and that is when it decided whether to let you in or offer you a chance to create the new but DISABLED account. If you had no account when you entered, the opening form WAS going to abort by setting CANCEL=1. If you had an account, the DB looked up your role based on your network ID in a table of users that included user roles. Within the front-end, we had some public variables that would be set up by that entry. All other code that was user-role sensitive could just look up the public variables to find your allowed level of access.

We had "groups" within the DB that had nothing to do with external groups because the servers we managed belonged to one of between 60 and 80 projects (the number varied as we grew). The DB admins had a form that associated a new user with a given group of other admins. If you were associated with a group, you had access to the severs managed by the group. At one point we had seven groups of admins to manage our 1200-1500 servers (again, numbers varied as the site gained projects).

Each form that you could use tested your public-variable info stored by the opening form. If you had access, the form would open. If not, you got a message box and the form closed, sending you back to the dispatcher. BUT if you used the form and tried to get to a server that was not within your scope of authority, each form's _CURRENT routine would look at the server's assigned group and would enabled or disable controls based on your right to touch the sever.

There was a lot more to it than that, but this is the start of "rolling your own security" when you have a larger DB to manage.

 

[JMongi]

@The_Doc_Man
Thanks for the more detailed description of your personal experiences. Very insightful. Though your implementation was much more robust, I believe the principles are pretty closely aligned with what I thought would work for our situation. I may have to tweak the permission/role table that I have, but the main thing will be creating the open form. I have a main navigation form, but since forms are closed/reopened regularly I have to see if it makes more sense to have a special open form that stays open but hidden.

Again, thanks for all of the good advice. I'm almost done with my existing code cleanup. So, I'll probably be thread dumping again as I make this change to the domain user trusting.

 

[JMongi]

Back to tackle the specifics of my new login authentication process!

First question:

I know there are many different ways to handle storing a users permissions once they are pulled. Since my structure is being created to accommodate the creation of future group roles for permissions, what is the best way to handle an unspecific number of group roles?

An example for clarification:

Month 1 Groups: Admin, User
Month 3 Groups: Admin, Supervisor, User
Month 6 Groups: Admin, Supervisor, Confidential, User
Month 12 Groups: Admin, Supervisor, Office, Shop, Confidential, View
etc, etc

I'm trying to develop the permission system to be expandable/editable by future database admins without needing to change the database.

 

[NauticalGent]

Okay, in that case, you should have access to it using either Environ() or this non-API function.

Good morning DBG,

Can you please expound on what you mean by "self-healing properties"?

 

[JMongi]

I'm no expert, but I was intrigued as well. This is my explanation based on the threads I read.
It heals the issues of the value got wiped somehow due to whatever unforseen reason.
But, don't keep doing the same thing over and over again as a waste of resources. Here's an example (I think, since I implemented it myself).

Public Function GetDomainUsername() As String
'Get the users Windows Domain Login username
Static sUser As String
If sUser = "" Then
    GetDomainUsername = CreateObject("WScript.Network").UserName
End If
GetDomainUsername = sUser
End Function

So, I need to know the username for a variety of access reasons. But, I don't want to pull the username each time I need it...but, I also need to make sure I have it if it disappears for reasons unknown. With the construction above, I can use GetDomainUsername() as a variable (because I can trust it's going to have the value I need) without constantly making the call to get the username every time I use it.

The same concept gets applied to system object instantiation so that you always have an instantiation to be used.

At least, that's my understanding...

 

[The_Doc_Man]

The problem with adding groups depends on this question: Can a person ever be a member of more than one group?

I.e. you have "USER" as a group. OK, Windows has "Everyone" as a group and it also has "Authenticated User" (they ARE different). Then there is the Administrator group, the Operator group, the Backup Operator group, ... you get the idea. If someone is a User, OK. But is an Admin also a User? I.e. "User" lets you in; "Admin" lets you do something. It is perfectly normal to have a multi-role user if that is your security model.

If those are non-overlapping groups and role is single-valued for a give person, it is trivial - stick the (unique) role in the user record and you are done. But I had a case where I realized I needed overlap, so what I did was had a child table of UserRoles where the PK was the compound key of UserID and RoleID. A PK of two integer fields is almost zero drag on table performance for short tables. The multi-role table was simple: Use DCount. If you had an entry for your ID and the "Supervisor" ID, then you were a supervisor - but you still had a role of "User" because that got you in through the initial connection (through my gatekeeper / dispatcher form).

In the Opening Form _Open routine, I would look up the user info table and set public variables (in my "Security' module) to TRUE or FALSE based on whether you had specific entries in the UserRoles table. (Actually, kept more than that, but I'm limiting the answer to the question you asked.) Might have sounded tedious, but (a) I could use a filtering query based on User ID and nobody had more that a couple of roles and (b) the code only ran once per session - during the processing of the Opening Form's _Open and _Load routines, so after that it wasn't a drag on anything.

You could also do it the other way for speed (though doing it the way I did it wasn't slow) - one user record with a yes/no field for each possible role they could have, and just store that record in memory somewhere. There's a gazillion ways to skin that cat. {{{loud, screeching meow}}}.