Examples of using Domain login to control user access

[JMongi]

one user record with a yes/no field for each possible role they could have, and just store that record in memory somewhere

This is what I have in mind. But, I'm not certain what form "store the record in memory somewhere" would take. I was trying to avoid one of my classic long-winded posts, but here goes:

EXISTING TABLES

tblUsers
ID
UserName
Active
LoggedIn
Computer

tblLoginSessions
LoginID
UserName
LoginEvent
LogoutEvent
ComputerName

These tables are part of @isladogs password login sample database. There are a couple of forms and some VBA functions associated with them so I am going to modify them as needed.

NEW TABLES

tblGroup
ID
Group
GroupDescription

tblUserGroup
ID
UsersID (FK)
GroupID (FK)
Access (1 = Member; 2 = No Member)

tblFormPermission
ID
frmName
GroupID (FK)
FormAccess (1 = Yes; 2 = No)
FormEdit (1 = Yes; 2 = No)

I would then need queries/forms for: adding new user groups; viewing/editing user group permissions; adding a new form; viewing/editing form permissions.

After running a query to pull the group member statuses of a user. I need to store that somewhere so that I have a check when a form opens (running a query to check form group permissions and then checking that against the stored user group membership).

 

[JMongi]

Oh, there is an error in the self-healing part. It SHOULD read:

That's what I get for retyping instead of copying and pasting...lol! Thanks!

 

[The_Doc_Man]

IF you have some clue as to how many roles you will eventually have (and more important, what they are), you are ahead of the game. However, it sounds like you aren't so sure. I've been there, I've done that, I've bought that T-short, and I've worn it until it was a holey wreck.

Trust me, that isn't the way to go. You should have a handle on all possible roles before you start implementing code that would rely on someone having such a role. Define the roles AND their effects ahead of time so that you don't have to constantly go back and retrofit everything. This is what leads to spaghetti code that is tough to follow or spider-web code that looks pretty but breaks apart under stress.

Basically, you seriously need to know where you are going ahead of time and map out a route. This might be a little "pithy" but the old rule is:

Piss Poor Prior Project Planning Produces Pitiful Performance
Persistently Precise Prior Project Planning Provides Perfect Products.

If you haven't mapped out your route to your end design (to include knowing the roles and what you will do with them), I have one remaining question: How will you ever know when you get there?

By the way, there is nothing wrong with the idea that you might have some roles that were defined but haven't been filled yet. You just won't exercise the code right away. But if you don't have the code there on the day that your first Accountant shows up, that person CAN'T be a user until you release the next version of your DB.

 

[JMongi]

As an aside to the self-healing question, I should just admit my ignorance and post this link instead!
Link

 

[theDBguy]

As an aside to the self-healing question, I should just admit my ignorance and post this link instead!
Link

Hi. Thanks for saving me the time to go back through my notes to try to explain it.

 

[JMongi]

IF you have some clue as to how many roles you will eventually have (and more important, what they are), you are ahead of the game. However, it sounds like you aren't so sure. I've been there, I've done that, I've bought that T-short, and I've worn it until it was a holey wreck.

Trust me, that isn't the way to go. You should have a handle on all possible roles before you start implementing code that would rely on someone having such a role. Define the roles AND their effects ahead of time so that you don't have to constantly go back and retrofit everything. This is what leads to spaghetti code that is tough to follow or spider-web code that looks pretty but breaks apart under stress.

Basically, you seriously need to know where you are going ahead of time and map out a route. This might be a little "pithy" but the old rule is:

Piss Poor Prior Project Planning Produces Pitiful Performance
Persistently Precise Prior Project Planning Provides Perfect Products.

If you haven't mapped out your route to your end design (to include knowing the roles and what you will do with them), I have one remaining question: How will you ever know when you get there?

By the way, there is nothing wrong with the idea that you might have some roles that were defined but haven't been filled yet. You just won't exercise the code right away. But if you don't have the code there on the day that your first Accountant shows up, that person CAN'T be a user until you release the next version of your DB.

I appreciate the input. I do have a general idea of the groups that I will need. But, I don't think what I'm envisioning will create spaghetti code or spaghetti anything really. In fact, I'm creating the structure with which to guide and control such future expansion. I already have a framework that I think will work as I outlined above. What I'm currently lacking is the way to record a non-fixed number of values for quicker lookup when forms open. I could run a query on form open to pull the intersection of the user's groups and those groups permission for the form being opened. But, it seemed like that was not considered an efficient way to handle it.

 

[June7]

Maybe you will find this useful. My code to handle new user as well as new version of db. This is an older version. The FSO copy code quit working due to new IT restrictions and I had to replace with a notification to user to download new version and code opens folder that contains file. Code is behind Login form that loads by default when db opens. Security is not really a concern, if they can login to network then they can use this db, frontend of which is only on certain computers. Purpose is simply to gather user initials into Users table which are then recorded with records during data entry.

Private Sub Form_Load()
      
'Check for updates to the program on start up - if values don't match then there is a later version
If Me.tbxVersion <> Me.lblVersion.Caption Then
    'because administrator opens the master development copy, only run this for non-administrator users
    If DLookup("Permissions", "Users", "UserNetworkID='" & Environ("UserName") & "'") <> "admin" Then
        'copy Access file
        CreateObject("Scripting.FileSystemObject").CopyFile _
            gstrBasePath & "Program\Install\MaterialsDatabase.accdb", "c:\", True
        'allow enough time for file to completely copy before opening
        Dim Start As Double
        Start = Timer
        While Timer < Start + 3
            DoEvents
        Wend
        'load new version - SysCmd function gets the Access executable file path
        'Shell function requires literal quote marks in the target filename string argument, apostrophe delimiters fail, hence the quadrupled quote marks
        Shell SysCmd(acSysCmdAccessDir) & "MSAccess.exe " & """" & CurrentProject.FullName & """", vbNormalFocus
        'close current file
        DoCmd.Quit
    End If
Else
    'tbxVersion available only to administrator to update version number in Updates table
    Me.tbxVersion.Visible = False
    Call UserLogin
End If

End Sub

Private Sub tbxUser_AfterUpdate()
If Me.tbxUser Like "[A-z][A-z][A-z]" Or Me.tbxUser Like "[A-z][A-z]" Then
    CurrentDb.Execute "INSERT INTO Users(UserNetworkID, UserInitials, Permissions) VALUES('" & VBA.Environ("UserName") & "', '" & UCase(Me.tbxUser) & "', 'staff')"
    Call UserLogin
Else
    MsgBox "Not an appropriate entry.", vbApplicationModal, "EntryError"
End If
End Sub

Private Sub UserLogin()
Me.tbxUser = DLookup("UserInitials", "Users", "UserNetworkID='" & Environ("UserName") & "'")
If Not IsNull(Me.tbxUser) Then
    CurrentDb.Execute "UPDATE Users SET ComputerName='" & VBA.Environ("ComputerName") & "' WHERE UserInitials='" & Me.tbxUser & "'"
    DoCmd.OpenForm "Menu", acNormal, , "UserInitials='" & Me.tbxUser & "'", , acWindowNormal
    DoCmd.Close acForm, Me.Name, acSaveNo
End If
End Sub

 

[JMongi]

Thanks for the post! Here is what I have so far. Everything compiles but I haven't had time to test it yet. I can't really test until I completely disable the old PW management code blocks. There are separate login and session logging tables already functioning from the old system.

Public Sub UserCheck()
Dim strCriteria
strCriteria = "Username='" & GetDomainUsername() & "'"
If DCount("UserName", "tblUsers", strCriteria) = 0 Then 'Check if New User
    CurrentDb.Execute "INSERT INTO tblUsers ( UserName, Active, LoggedIn, Computer )" & _
    " VALUES(GetDomainUsername(), True, False,"");"
    'prompt user that it has basic access with modal form
    'email administrator list
ElseIf DCount("UserName", "tblUsers", strCriteria And "Active='True'") = 0 Then 'Check if inactive user
    CurrentDb.Execute "UPDATE tblUsers SET LoggedIn = False, Active = True" & _
        " WHERE UserName='" & GetDomainUsername & "';"
    'prompt user that they have been reactivated with modal form
    'email administrator list
End If
'Call save user info function
End Sub

It's the end of the day, so see you tomorrow.

 

[Isaac]

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.

The way I approach this is: Anyone who plans to use the database simply must go through the 'add new user' process ahead of time. The database should not/need not be capable of handling someone it's never heard of - Except for kicking them out.

Better, give your Managers access to a tab/screen where they can configure users - add, delete, change permissions, etc. Thus, you hand over the appropriate level of responsibility/empowerment as a chain that can sort of self-maintain...
and your vba code can verify a valid network username, although I can't remember at the moment how I did that but it's out there somewhere

 

[Galaxiom]

Active Directory, including the Group membership, can be read using an LDAP query in VBA. This can be used to determine what the user should have access to.

Have a look at this thread. There is a sample database in post 16.
The OP developed it some more and posted an enhanced sample further into the thread and later having used recursion to return information about nested groups.

You will need to do a lot more work to use the output to control the access to objects.

However I would strongly suggest switching to an SQL Server back end if you want to go down the path of using Windows logins for security. SQL Server has native support for this functionality with fine grained security on everything. There are many other advantages to SQL Server too. The Express version is free.

BTW SQL Server can also read Active Directory via a linked server far more simply and efficiently than the VBA code.

 

[JMongi]

The way I approach this is: Anyone who plans to use the database simply must go through the 'add new user' process ahead of time. The database should not/need not be capable of handling someone it's never heard of - Except for kicking them out.

Better, give your Managers access to a tab/screen where they can configure users - add, delete, change permissions, etc. Thus, you hand over the appropriate level of responsibility/empowerment as a chain that can sort of self-maintain...
and your vba code can verify a valid network username, although I can't remember at the moment how I did that but it's out there somewhere

In general, I agree Isaac. However, since the db admin (me now, someone else later) will not have direct access to the windows domain username of a new user, I felt that using the login attempt to at least pull the name and place it in a table that can now be accessed by the admin and permission granted is more streamlined. I don't see much different security risk in:

Launch DB > Auto Open Login Form > Perform user check > Non User = Application.Quit
vs
Launch DB > Auto Open Login Form > Perform user check > Non User = Log domain username & Application.Quit

I do think you informed my decision that I'm not going to grant any access to a new user, just log their username, show a splash screen, kick them out and send an e-mail to the db admin.

@Galaxiom - I am not using Active Directory/Group Permissions as my actual database permissions. I am only using domain login usernames to eliminate the need to roll my own database password system. All database permissions will be managed within the database itself.

 

[Galaxiom]

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

Any group system that doesn't allow a user to be in multiple groups is lame.

 

[The_Doc_Man]

Have to agree with you, G, although the Navy didn't originally see it that way - until they realized that they had more groups to manage than they had administrators. We got upwards of 80 projects but never had more than 40 admins - and only about 25 during prime shift.

 

[JMongi]

Can't sort the error I'm getting on this line:

ElseIf DCount("UserName", "tblUsers", strCriteria And "Active='True'") = 0 Then

I'm not too familiar with the construction of a Dcount command, so it could be basic.

 

[theDBguy]

Can't sort the error I'm getting on this line:

ElseIf DCount("UserName", "tblUsers", strCriteria And "Active='True'") = 0 Then

I'm not too familiar with the construction of a Dcount command, so it could be basic.

No error message? What's in strCriteria at this point? Maybe try it this way?

ElseIf DCount("*","tblUsers",strCriteria & " AND Active=True")=0 Then

 

[June7]

Concatenate variable to literal text. And if Active is a Yes/No field, don't use apostrophe delimiters and don't really need =True. Can probably use * wildcard.

ElseIf DCount("*", "tblUsers", strCriteria & " And Active") = 0 Then

 

[JMongi]

Sorry, post 29 has the full code block. Most recent error was type mismatch.
The strCriteria works fine in the first DCount.
Ah, it's a boolean check. Right. So my error was a type mismatch. Trying to check a boolean field with a string. That makes sense.

 

[JMongi]

That sorted it. Thanks to you both.

Edit: Also, the previous code block had a mistake in the first SQL INSERT TO line where I needed ' vs " Here is the current corrected code block including what we just corrected.

Function UserCheck() As Boolean
Dim strCriteria
strCriteria = "Username='" & GetDomainUsername() & "'"
If DCount("UserName", "tblUsers", strCriteria) = 0 Then 'Check if New User
    CurrentDb.Execute "INSERT INTO tblUsers ( UserName, Active, LoggedIn, Computer )" & _
    " VALUES(GetDomainUsername(), False, False,'');"
    MsgBox ("New User") 'prompt to contact administrator for database access
    'email administrator list
    UserCheck = False
ElseIf DCount("UserName", "tblUsers", strCriteria & "And Active=True") = 0 Then 'Check if inactive user
    MsgBox ("Inactive User") 'prompt user to contact administrator to reactivate their account with modal form
    'email administrator list
    UserCheck = False
End If
UserCheck = True
'Call save user info function
End Function

 

[theDBguy]

That sorted it. Thanks to you both.

Edit: Also, the previous code block had a mistake in the first dcount line where I needed ' vs " Here is the current corrected code block including what we just corrected.

Function UserCheck() As Boolean
Dim strCriteria
strCriteria = "Username='" & GetDomainUsername() & "'"
If DCount("UserName", "tblUsers", strCriteria) = 0 Then 'Check if New User
    CurrentDb.Execute "INSERT INTO tblUsers ( UserName, Active, LoggedIn, Computer )" & _
    " VALUES(GetDomainUsername(), False, False,'');"
    MsgBox ("New User") 'prompt to contact administrator for database access
    'email administrator list
    UserCheck = False
ElseIf DCount("UserName", "tblUsers", strCriteria & "And Active=True") = 0 Then 'Check if inactive user
    MsgBox ("Inactive User") 'prompt user to contact administrator to reactivate their account with modal form
    'email administrator list
    UserCheck = False
End If
UserCheck = True
'Call save user info function
End Function

Hi. Glad to hear you got it sorted out. Cheers!