Role-based access control used in MS Access

Question

I am currently working on a project were one of the requirements are to use the users windows login as their login for MS Access, where they would then click there role to gain access into the system. I have never done this before but I have set up a login screen in Access which pulls data from a table. I have code that successfully pulls the users windows login but I am having trouble after this. The table name is tblUser and the users are General User, HR, and Admin. Currently, In the table I have the roles assigned number with General User = 1, HR = 2, Admin = 3.

The Login Screen:
   Log On
General User
HR
Admin


Code that pulls the user information:
Private Sub Form_Load()
Stop

Debug.Print Environ("UserName")
Debug.Print Environ$("ComputerName")

Dim strVar As String
Dim i As Long
For i = 1 To 255
    strVar = Environ$(i)
    If LenB(strVar) = 0& Then Exit For
    Debug.Print strVar
Next
End Sub

Below is the code that I built for my login screen in the past. Through drawing everything out it seems as though it would be the same process but I am not to sure. Is there anything that I can do to the code below?

Private Sub btnLogin_Click()
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("tblUser", dbOpenSnapshot, dbReadOnly)

rs.FindFirst "UserName='" & Me.txtUserName & "'"

If rs.NoMatch = True Then
    Me.lblWrongUser.Visible = True
    Me.txtUserName.SetFocus
    Exit Sub
End If
Me.lblWrongUser.Visible = False

If rs!Password <> Nz(Me.txtPassword, "") Then
    Me.lblWrongPass.Visible = True
    Me.txtPassword.SetFocus
    Exit Sub
End If
Me.lblWrongPass.Visible = False

If rs!EmployeeType_ID = 3 Then

    Dim prop As Property
    On Error GoTo SetProperty
    Set prop = CurrentDb.CreateProperty("AllowBypassKey", dbBoolean, False)

    CurrentDb.Properties.Append prop

SetProperty:
    If MsgBox("Would you like to turn on the bypass key?", vbYesNo, "Allow Bypass") = vbYes Then
        CurrentDb.Properties("AllowBypassKey") = True
    Else
        CurrentDb.Properties("AllowBypassKey") = False
    End If

End If

DoCmd.OpenForm "frmPersonal_Information"
DoCmd.Close acForm, Me.Name
End Sub

I hope this is enough information for what I am trying to accomplish. If anymore information is needed please let me know. Thank you.

Answer 1

You do not need a login screen if the roles are tied to your Windows/Active Directory logins. You should make the assumption that the logged on user in Windows is legitimately using the workstation (and if that is not a safe assumption you need to look into your IT policies).

Access does not support roles and permissions. After accessing which user is currently logged on and getting their role from tblUser will have to:

1. Lock down the back end and especially access to the tables.
2. Lock down most of the user interface inherent to Access and only allow your front end forms to be used.
3. For each form of your front end manually make sure that whatever policies you wish to enforce are enforced using VBA.

Ultimately no matter what you do, somebody that knows how to use Access well can bypass any kind of lockdown you put in place. If you need to guard against more than casual curiosity and honest errors, you will need to combine Access with a much more robust DBMS like SQL server or MySQL.