Password protecting a database

[sumdumgai]

Can someone please explain to me (or point me to a good thread) how to password protect an Access 2007 shared database (i.e., on a server), and do so in a way that the password cannot be changed by anyone except those authorized?

Thanks.

 

[GinaWhipp]

There really is no good way without splitting the database and putting the tables in SQL server and using that security UNLESS your database is in MDB format and then you can use that security. Not easy to implement but it can be done.

 

[sumdumgai]

Thanks for your response. It's not in MDB format. Is there a way to prevent users from opening the database exclusively, which would be needed to change the password. Or, with VBA, can I allow exclusive opening for only certain users?

 

[GinaWhipp]

If the database is opened exclusively and the front end is being shared (not a good idea) then no one else will be able to open it. What exactly are you trying to prevent them from changing?

 

[sumdumgai]

I want to prevent unauthorized users from viewing all of the data, including tables, queries, etc. In this case, an unauthorized user could be someone who is actually signed on to the server but not given permission to view the database, or someone who has copied the database to another computer.

Thanks.

 

[boblarson]

Well, with 2007 you can encrypt the database and add the password. The password in 2007 is harder to crack than in 2003 or earlier but I believe there are possible ways of doing it but if you have someone to go to that trouble for this, then you have larger issues. It was fairly easy in versions prior to 2007 to hack in but it is a bit harder now.

So, you can add the password but you would also need to keep users out of the tables in the frontend too because the password to the backend, when you have linked the tables after setting the password, has the password stored in plain text in the MsysObjects table. So, not too secure there. So we do the disable bypass key but again, this won't stop someone who has some Access knowledge (or knows how to Google) and the desire to break in.

 

[sumdumgai]

Thanks for reply. I hear what you're saying about hacking the password, but my bigger fear is that if I give the encryption password to the users who are authorized to use the database (so that they can open the database), without some sort of user-level security, then they also have the ability to remove or change the password, locking everybody else out.

Am I right?

Thanks.

 

[GinaWhipp]

Yes, that is correct... And then we are back no User Level Security in Access 2007 or Access 2010 without using SQL Server and it's security.

 

[sumdumgai]

So is there a way to trap or prevent an open database event where the user is requesting exclusive use?

 

[GinaWhipp]

Is this database split? if each User had their front end this shouldn't cause a problem.

 

[sumdumgai]

Not split. Thanks.

 

[GinaWhipp]

Hmmm, I have never used it but this *might* work...

http://www.dbforums.com/microsoft-access/983758-get-mode-current-db-exclusive-not.html

 

[gemma-the-husky]

you say your data is not in mdb format. what is it in, then? accdb/mdb are the same as far as this is concerned

 

[GinaWhipp]

@Dave,

I think what the OP is saying is it's not split and I'm thinking the database will not be split.

 

[sumdumgai]

The database is not and will not be split. It's a 2007 DB. Back to my question: can I prevent, with VBA, someone from opening exclusive unless, for example, they passed my VBA security question?

Thanks.

 

[boblarson]

The database is not and will not be split.

Then expect corruption and trouble with conflicts between people. That's a given - like it or not. It WILL corrupt, it is just a matter of time and if it isn't split, then that time will likely be shorter.

Back to my question: can I prevent, with VBA, someone from opening exclusive unless, for example, they passed my VBA security question?

No, there isn't a way for that to happen.

 

[GinaWhipp]

The only suggestion I had is what I posted...

 

[speakers_86]

First of all, you should split your db.

As for your question, you will need a login form, every user will need a username and password, and user type (to define what their rights are). In the forms log in, you can use this code in the submit commands on click:

Private Sub cmdSubmit_Click()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Set db = CurrentDb()
    strSQL = "SELECT * FROM tblEmployee WHERE [EmployeeID] = " & Me.cboLogin.value
'Backdoor
        If IsNull(Me.cboLogin) And Me.txtPassword.value = "admin" Then
On Error GoTo Err_bDisableBypassKey_Click
    'This ensures the user is the programmer needing to disable the Bypass Key
    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 = "admin" 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:
    DoCmd.Quit
Err_bDisableBypassKey_Click:
    MsgBox "bDisableBypassKey_Click", Err.Number, Err.Description
    Resume Exit_bDisableBypassKey_Click
        Else
'CHECKING FOR NULLS
        If IsNull(Me.cboLogin) Then
            MsgBox Mssg1, vbCritical, Title1
            Me.cboLogin.SetFocus
            Call CheckLogAttempts
        ElseIf IsNull(Me.txtPassword) Then
            MsgBox Mssg1, vbCritical, Title1
            Me.txtPassword.SetFocus
            Call CheckLogAttempts
'VALIDATING PASSWORD
        Else
            If Me.txtPassword.value = DLookup("Password", "tblEmployee", "[EmployeeID]=" & Me.cboLogin.value) Then
            
'PASSING VARIABLES TO DB
                Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
                With user
                    .CurrentUser = rst.Fields("EmployeeID")
                End With
                rst.Close
'Open Appropriate Home Form
                If Me.txtPassword.value = "changeme" Then
                DoCmd.OpenForm "frmchangePassword", acNormal, , "EmployeeID=" & Me!cboLogin, acFormEdit, acDialog
                DoCmd.Close acForm, "frmbackground", acSaveNo
                Else
                      DoCmd.OpenForm "frmhome", acNormal
                      DoCmd.Close acForm, "frmBackground", acSaveNo
                           Me.Visible = False
                           End If
                

'Close Login Form
            Else
                MsgBox "Password Invalid.  Please Try Again", vbOKOnly, "Invalid Entry!"
                Me.txtPassword.SetFocus
                Call CheckLogAttempts
            End If
        End If
        End If
        
End Sub
 
Private Sub CheckLogAttempts()
    If lngLogAttempts >= 2 Then
        MsgBox "     It appears you are having trouble logging on.    " & Chr(13) & "Please contact your System Administrator " & "for assistance.       ", vbCritical, Title1
        DoCmd.Quit
    Else
        lngLogAttempts = lngLogAttempts + 1
    End If
End Sub

You'll notice that it gives the admin (you) backdoor access. This is the only way to get to the navigation pane. If you use this code, make sure you backup your db before hand, so you don't get locked out.

 

[sumdumgai]

Thanks for all of your help and suggestions.