Need help with security level coding

[omnivox84]

Hello.. I am currently developing a database, and want it to be secure, which seems to be a mountain to climb in access 2013...

I have, through google and such, pieced together a sort of "User Level Security". Now... firstly, it works, almost the way I want it to, but i feel like there are two problems.
Firstly, I am very new to VBA coding... Very little experience.. So the fact that I got it to work at all is astounding to me.

Anyhow.. Here are my issues. I feel like it could be neater.. I have all kinds of code crammed into all kinds of places. I want it to be efficient, not cluttered, but have no idea of how to do this.

And secondly, I have seen ways or more accurately, gleened, that it is possible to attached windows login credentials to the database via VBA. I would love to be able to use this information for security.

I guess what I'm asking is, how can this be more efficient, and how can it be more secure, while easy.

here is the first portion.

'------Options----------
Option Compare Database
Option Explicit


'-------------Constants--------------------
Global Const MAX_LOGIN_ATTEMPTS As Long = 3
Global Const MAX_PASSWORD_FAILS As Long = 3


'-----Variables--------
Global nCounter As Long
Global sEc As String
Global User As String
Global User2 As String
Global un As String
Global Target As String
Global mCounter As Long
Global Inf As String

This is in a global module.



Second Portion

Private Sub Command1_Click()


User = DLookup("[First Name]", "tblUser", "[UserName] ='" & Me.UsrName.Value & "'")
sEc = DLookup("[Security Level]", "tblUser", "[UserName] ='" & Me.UsrName.Value & "'")
User2 = DLookup("[Second Name]", "tbluser", "[Username] ='" & Me.UsrName.Value & "'")
un = DLookup("[UserName]", "tbluser", "[Username] ='" & Me.UsrName.Value & "'")
Target = DLookup("[email]", "tbluser", "[Username] ='" & Me.UsrName.Value & "'")
Inf = DLookup("[Info]", "tbluser", "[Username] ='" & Me.UsrName.Value & "'")




If sEc = "user" Then
        DoCmd.ShowToolbar "ribbon", acToolbarNo
        DoCmd.ShowToolbar "menu bar", acToolbarNo
Else
        DoCmd.ShowToolbar "menu bar", acToolbarYes
        DoCmd.ShowToolbar "ribbon", acToolbarYes
        If sEc = "reader" Then
                DoCmd.ShowToolbar "ribbon", acToolbarNo
                DoCmd.ShowToolbar "menu bar", acToolbarNo
        Else
                DoCmd.ShowToolbar "menu bar", acToolbarYes
                DoCmd.ShowToolbar "ribbon", acToolbarYes
        End If
End If


If sEc = "banned" Then
        MsgBox "Your account has been banned due to a security breach.", vbOKOnly
        DoCmd.Quit
Else
        If sEc = "locked" Then
              MsgBox "Your account has been locked due to repeated failed password attempts." & vbCrLf & vbCrLf & _
              "Please contact the database administrator."
              Me.UsrName = Null
              Me.UsrPass = Null
        Else
                If IsNull(Me.UsrName) Then
                        MsgBox "Please type in your UserName!", vbCritical
                        Me.UsrName.SetFocus
                Else
                    If Me.UsrPass = "password" Then
                            DoCmd.OpenForm "pwchangesf", , , "[UserName] ='" & Me.UsrName.Value & "'"
                            MsgBox "Please change your password before you continue.", , "Password Change"
                            DoCmd.Close acForm, "Loginfrm"
                    Else
                            If Me.UsrPass = DLookup("[Password]", "tbluser", "[UserName] ='" & Me.UsrName & "'") Then
                                        DoCmd.OpenForm "NavigationHome"
                                        Forms("NavigationHome").signstatus = "Sign out?"
                                        DoCmd.Close acForm, "loginfrm"
                            Else
                                    nCounter = nCounter + 1
                                            If nCounter < MAX_PASSWORD_FAILS Then
                                                    MsgBox "Password does not match, " & vbCrLf & _
                                                    "You have " & (MAX_PASSWORD_FAILS - nCounter) & " attempts remaining.", vbOKOnly
                                                    Me.UsrPass = Null
                                                    Me.UsrPass.SetFocus
                                            Else
                                                    MsgBox "Your account has been locked due to repeated failed password attempts." & vbCrLf & vbCrLf & _
                                                    "Please contact the database administrator."
                                                    DoCmd.OpenForm "locked", , , "[username] ='" & Me.UsrName & "'"
                                                    DoCmd.Close acForm, "loginfrm"


                                            End If
                                   End If
                          End If
                 End If
         End If
End If
End Sub

This is in the loginfrm Form


third portion

Private Sub Form_Timer()
Me.UsrLogged = (User & " " & User2 & " " & "[" & sEc & "]")


If sEc <> "user" And _
   sEc <> "reader" Then
        DoCmd.SelectObject acTable, , True
    Me.TimerInterval = 0
    Else
        DoCmd.SelectObject acTable, , True
    DoCmd.RunCommand acCmdWindowHide
    Me.TimerInterval = 0
End If




MsgBox "Welcome, " & User & "!" & vbCrLf & _
       "Please make your selection.", , "Welcome"
End Sub

This is in the navigationhome form.

Any advice / comments / concerns or anything else would be helpful.

[June7]

I've never designed a 'secure' db. My db is split and frontend placed on authorized computers. User who logs in to computer is presumed to be a legitimate user of db. Network permissions restrict who can get into folders on server.

I do, however, grab the user network ID for some internal tracking in the db. I use Environ("USERNAME") to accomplish that.

[omnivox84]

Unfortunately, doing it that way is not an option. I need it to be user friendly from one machine if need be. I can split it, but I can't have links to the tables visible, thus the code. I have tried it the other way and it is much simpler by far. This is the option I am given. Make it secure, or no one can touch it.

It is very secure. No one can get into it. It's not a question of making it secure, its whether or not it can be improved upon, or cleaned up.

[June7]

I do hide navigation pane and ribbon and disable X close, function keys, shortcut menus. Review links in https://www.accessforums.net/access/...ers-48543.html

However, I have never disabled shift key bypass nor utilized password encryption.

[omnivox84]

Yes, I have it set up to where any one with a security level lower than admin loses the ribbon, menu bar, and the navigation menu. And by default, I have shortcut menu turned off. I have to leave the shift key bypass on, because of confidential information. some users are expected to use this for certain purposes without being able to pry into information they aren't supposed to know. therefore I have a navigation page that opens, and contains multiple subforms and navigation subforms. It works quite well, and no one can access information they shouldn't be seeing. It even emails me when they try.

I just feel like my coding is not.... Proper i guess.. It's so clustered and dispersed and random. .. not even sure how it functions, as I am constantly having to debug and compile to get new code entries to work. The syntax does not always match when I enter a new line. (as you can see from the plethora of "end if"'s at the bottom of the one code.

[June7]

More indentation would make the code flow easier to read, follow and analyze.

[omnivox84]

I cleaned it up a bit, and added more indentation.

[June7]

Still hard to follow. Consider this indentation of your code:

If sEc = "user" Then
    DoCmd.ShowToolbar "ribbon", acToolbarNo
    DoCmd.ShowToolbar "menu bar", acToolbarNo
Else
    DoCmd.ShowToolbar "menu bar", acToolbarYes
    DoCmd.ShowToolbar "ribbon", acToolbarYes
    If sEc = "reader" Then
        DoCmd.ShowToolbar "ribbon", acToolbarNo
        DoCmd.ShowToolbar "menu bar", acToolbarNo
    Else
        DoCmd.ShowToolbar "menu bar", acToolbarYes
        DoCmd.ShowToolbar "ribbon", acToolbarYes
    End If
End If

If sEc = "banned" Then
    MsgBox "Your account has been banned due to a security breach.", vbOKOnly
    DoCmd.Quit
Else
    If sEc = "locked" Then
        MsgBox "Your account has been locked due to repeated failed password attempts." & vbCrLf & vbCrLf & _
        "Please contact the database administrator."
        Me.UsrName = Null
        Me.UsrPass = Null
    Else
        If IsNull(Me.UsrName) Then
            MsgBox "Please type in your UserName!", vbCritical
            Me.UsrName.SetFocus
        Else
            If Me.UsrPass = "password" Then
                DoCmd.OpenForm "pwchangesf", , , "[UserName] ='" & Me.UsrName.Value & "'"
                MsgBox "Please change your password before you continue.", , "Password Change"
                DoCmd.Close acForm, "Loginfrm"
            Else
                If Me.UsrPass = DLookup("[Password]", "tbluser", "[UserName] ='" & Me.UsrName & "'") Then
                    DoCmd.OpenForm "NavigationHome"
                    Forms("NavigationHome").signstatus = "Sign out?"
                    DoCmd.Close acForm, "loginfrm"
                Else
                    nCounter = nCounter + 1
                    If nCounter < MAX_PASSWORD_FAILS Then
                        MsgBox "Password does not match, " & vbCrLf & _
                        "You have " & (MAX_PASSWORD_FAILS - nCounter) & " attempts remaining.", vbOKOnly
                        Me.UsrPass = Null
                        Me.UsrPass.SetFocus
                    Else
                        MsgBox "Your account has been locked due to repeated failed password attempts." & vbCrLf & vbCrLf & _
                              "Please contact the database administrator."
                        DoCmd.OpenForm "locked", , , "[username] ='" & Me.UsrName & "'"
                        DoCmd.Close acForm, "loginfrm"
                    End If
                End If
            End If
        End If
    End If
End If

And this version using ElseIf

If sEc = "banned" Then
    MsgBox "Your account has been banned due to a security breach.", vbOKOnly
    DoCmd.Quit
Else
    If sEc = "locked" Then
        MsgBox "Your account has been locked due to repeated failed password attempts." & vbCrLf & vbCrLf & _
        "Please contact the database administrator."
        Me.UsrName = Null
        Me.UsrPass = Null
    ElseIf IsNull(Me.UsrName) Then
        MsgBox "Please type in your UserName!", vbCritical
        Me.UsrName.SetFocus
    ElseIf Me.UsrPass = "password" Then
        DoCmd.OpenForm "pwchangesf", , , "[UserName] ='" & Me.UsrName.Value & "'"
        MsgBox "Please change your password before you continue.", , "Password Change"
        DoCmd.Close acForm, "Loginfrm"
    ElseIf Me.UsrPass = DLookup("[Password]", "tbluser", "[UserName] ='" & Me.UsrName & "'") Then
        DoCmd.OpenForm "NavigationHome"
        Forms("NavigationHome").signstatus = "Sign out?"
        DoCmd.Close acForm, "loginfrm"
    Else
        nCounter = nCounter + 1
        If nCounter < MAX_PASSWORD_FAILS Then
            MsgBox "Password does not match, " & vbCrLf & _
            "You have " & (MAX_PASSWORD_FAILS - nCounter) & " attempts remaining.", vbOKOnly
            Me.UsrPass = Null
            Me.UsrPass.SetFocus
        Else
            MsgBox "Your account has been locked due to repeated failed password attempts." & vbCrLf & vbCrLf & _
                  "Please contact the database administrator."
                  DoCmd.OpenForm "locked", , , "[username] ='" & Me.UsrName & "'"
                  DoCmd.Close acForm, "loginfrm"
        End If
    End If
End If

[omnivox84]

That is exactly what I need lol... It looks so much cleaner now, thank you. As far as the coding itself, it works. I haven't had any issues, but can you think of an easier way to code it all? Is there such a thing? I reposted the code above.

Edit* : The elseif code helped too. Was not even sure how to use that syntax, as I kept trying to use else if. Thanks for that.

[omnivox84]

I don't even think you want to see the calculation page I have LOL... What a monstrosity it is...

But it does what it is supposed to do!

p.s. It's > 2k lines :X Maybe I'll post a shell of the database for you to look at...

[InsuranceGuy]

Access DBs are fraught with security risks. Hiding toolbars is not sufficient to protect your data. Users can lock and unlock the shift-key bypass in other databases from any database. With Access 2013's encryption features you can do better because people not-involved in the DB's usage can't get to the VBA or table data, but anyone with the password can still undo/skip your VBA security. For those who are working on something security sensitive, it's best to protect your data using a SQL Server backend which is access controlled by the individual user's domain account. If you use an Access DB back end or SQL Server with a SQL username/password instead of the domain account, you're back to an "at risk" database which a knowledgeable person could get past.

If you're only interested in dissuading people from dabbling, your approach will probably work fine.

You may want to look into the "select case" vba structure instead of the "if" structure you're using to achieve clean code.

[June7]

I considered Select Case for suggested revision but not all the conditions are based on same input. This may be about as clean as can get.

[InsuranceGuy]

Here's a sample of a select case approach:

Select Case sEc    
    Case "user", "reader"
            DoCmd.ShowToolbar "ribbon", acToolbarNo
            DoCmd.ShowToolbar "menu bar", acToolbarNo
    Case "banned"
            MsgBox "Your account has been banned due to a security breach.", vbOKOnly
            DoCmd.Quit
    Case "locked"
            MsgBox "Your account has been locked due to repeated failed password attempts." & vbCrLf & vbCrLf & _
            "Please contact the database administrator."
            Me.UsrName = Null
            Me.UsrPass = Null
     Case Else
        'this is your unverified user / security handling
        
            DoCmd.ShowToolbar "menu bar", acToolbarYes
            DoCmd.ShowToolbar "ribbon", acToolbarYes
    
            If IsNull(Me.UsrName) Then
                'Force user name entry
                MsgBox "Please type in your UserName!", vbCritical
                Me.UsrName.SetFocus
                
                'Exit routine
                Exit Sub
            End If
            
            If Me.UsrPass = "password" Then
                'Force a password reset.
                DoCmd.OpenForm "pwchangesf", , , "[UserName] ='" & Me.UsrName.Value & "'"
                MsgBox "Please change your password before you continue.", , "Password Change"
                DoCmd.Close acForm, "Loginfrm"
                
                'Exit routine
                Exit Sub
            End If
            
            'Validate password
             If Me.UsrPass = DLookup("[Password]", "tbluser", "[UserName] ='" & Me.UsrName & "'") Then
                 'Password validated, act
                 DoCmd.OpenForm "NavigationHome"
                 Forms("NavigationHome").signstatus = "Sign out?"
                 DoCmd.Close acForm, "loginfrm"
             Else
                 'Password not validated, track attempts
                 nCounter = nCounter + 1
                 If nCounter < MAX_PASSWORD_FAILS Then
                     'Retry will be allowed
                     MsgBox "Password does not match, " & vbCrLf & _
                     "You have " & (MAX_PASSWORD_FAILS - nCounter) & " attempts remaining.", vbOKOnly
                     Me.UsrPass = Null
                     Me.UsrPass.SetFocus
                 Else
                     'Retries exceeded
                     MsgBox "Your account has been locked due to repeated failed password attempts." & vbCrLf & vbCrLf & _
                     "Please contact the database administrator."
                     DoCmd.OpenForm "locked", , , "[username] ='" & Me.UsrName & "'"
                     DoCmd.Close acForm, "loginfrm"
                 End If
            End If
    End Select

[omnivox84]

The "case" method definately seems cleaner and easier to understand. As I said before, I am very new to VBA and all of your input is definately appreciated.

The reason I can't do a split method, is because even if you do split the database, it seems that you can still view the tables. This cannot be allowed... So in my mind this approach is pointless.
With hiding the tool bars, and using the shift bypass, it has shown me that this information can be hidden in a way that makes it inaccessible even to someone looking to dabble.. I have also compiled the vba
code so that it shows as project unviewable. This also is password protected with different encryption than the database. And yes, the database is also encrypted. Anyone looking to enter it needs two passwords to get into the database.

I tried several different methods to remove the shift key bypass. I set up the code in another blank database, tried opening a different database while using the shift key bypass, and it still does not let me past my login form. Perhaps I don't know the method you described, but as far as I can tell, there is no way for someone to bypass that code without clicking the button I have hidden. Also, this button is ONLY clickable by a Developer Security Level.Thanks.

For security purposes, I need to do anything possible to ensure that anyone who is NOT authorized, CANNOT view the tables. If I can somehow integrate "Row Level Security" I will, though my tables do not house the security levels, nor the usernames, so this type of security does not seem to be plausible.

As I stated, if you can give me a description of a better way to secure it, I'm all ears.. But a back end is not the option right now.. I may be able to use this approach in the future, though I suspect that anyone will still be able to see the tables in any case.

Thanks again.

Edit: I feel like I should mention that this is not ALL of the code. Each form has a series of lines for ensuring security level is reached. If not, it drops you out to the log in screen. So if someone were to skip the login form, they would not be able to bring any others up without loging in. I understand that if they get by with a bypass, the tables are at risk, which is what I need to ensure does NOT happen.

Here is the code I'm using for the disable shift bypass key. Seems to work very well as you have to enter a password to disable it.

Public Function SetProperties(strPropName As String, _
varPropType As Variant, varPropValue As Variant) As Integer


    On Error GoTo Err_SetProperties


    Dim db As DAO.Database, prp As DAO.Property


    Set db = CurrentDb
    db.Properties(strPropName) = varPropValue
    SetProperties = True
    Set db = Nothing


Exit_SetProperties:
    Exit Function


Err_SetProperties:
    If Err = 3270 Then    'Property not found
        Set prp = db.CreateProperty(strPropName, varPropType, varPropValue)
        db.Properties.Append prp
        Resume Next
    Else
        SetProperties = False
        MsgBox "SetProperties", Err.Number, Err.Description
        Resume Exit_SetProperties
    End If
End Function

and

Private Sub Image10_Click()
If sEc = "Developer" Then


    On Error GoTo Err_bDisableBypassKey_Click
    Dim strInput As String
    Dim strMsg As String
    Beep
    strMsg = "Do you want to enable the Bypass Key?" & vbCrLf & vbLf & _
             "Please key the programmer's password to enable the Bypass Key."
    strInput = InputBox(Prompt:=strMsg, title:="Disable Bypass Key Password")
    If strInput = "removedpassword" Then
        SetProperties "AllowBypassKey", dbBoolean, True
        Beep
        MsgBox "The Bypass Key has been enabled." & vbCrLf & vbLf & _
               "The Shift key will allow the users to bypass the startup" & _
               " options the next time the database is opened.", vbInformation, "Set Startup Properties"
    Else
        Beep
        SetProperties "AllowBypassKey", dbBoolean, False
        MsgBox "Incorrect ''AllowBypassKey'' Password!" & vbCrLf & vbLf & _
               "The Bypass Key was disabled." & vbCrLf & vbLf & _
               "The Shift key will NOT allow the users to bypass the" & _
               "startup options the next time the database is opened.", _
               vbCritical, "Invalid Password"
        Exit Sub
    End If
Exit_bDisableBypassKey_Click:
    Exit Sub
Err_bDisableBypassKey_Click:
    MsgBox "bDisableBypassKey_Click", Err.Number, Err.Description
    Resume Exit_bDisableBypassKey_Click
End If
End Sub