Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2652
  • Last Modified:

Password Protection in the Access table

VB ProtectionPassword TableHi,
I have access database where I maintain a list of username and password in the Password table (shown above).
To protect it from user's view, I made "password" table hidden object and in the Startup, I un-checked "Display Database Window" so that users can't see actual tables and forms ... etc unless they hold down "Shift" key when running the database.

Is there a way that I can assign a password to this table so that if anyone wants to open it, they have to know the password? Kind of like assigning a password to your VB code (shown above).
0
sglee
Asked:
sglee
  • 5
  • 4
  • 3
  • +1
3 Solutions
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Is there a way that I can assign a password to this table so that if anyone wants to open it, they have to know the password? >>

 No.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
What you could do is encrypt the password in some way and store the encrypted password in the table.

Then in code, take the password supplied by the user, encrypt it, and compare it to the table.

Since VBA projects can be password protected, no one could figure out the passwords even though they can see the table.  They also couldnot fill in their own password value because they don't know the encryption method you used.

 License keys work the same way.

Jim.
0
 
sgleeAuthor Commented:
<<you could do is encrypt the password in some way and store the encrypted password in the table.>> ---> Can you show me how to do this?
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
MacroShadowCommented:
This should help.
Encryption.mdb
0
 
sgleeAuthor Commented:
@MacroShadow
Thanks for the database, but I don't really understand it.

Anyway what I have decided to do is to use ***** as input mask of Password field.
In that way even if someone opens the table, at least the password would be protected.
0
 
MacroShadowCommented:
Well I don't expect you to understand it, all you have to do is use it. Using an input mask does not secure your passwords!
0
 
GrahamSkanCommented:
MacroShadow.
This is intended to be a how-to forum where we can all learn  as opposed to a do-it-for-you site.  Answers to questions should be accessible to all users, including, but not confined to, the one who asked, so any obfuscation is to be avoided.

sglee,
As JDettman suggests, you can scramble the password and store the scrambled version. If you protect the code it will be difficult to unscramble. Even better, use MD5 encryption. It is virtually impossible unencrypt back to a usable password. Googling for MD5 and VB will find many pre-written code blocks
0
 
sgleeAuthor Commented:
@MacroShadow
When I use password input mask, it displays **** as I enter the password. Can someone read ***** and figure out what it is?
0
 
MacroShadowCommented:
Yes. there are many freeware applications that can passwords masked behind asterisks. http://www.nirsoft.net/utils/bullets_password_view.html

Time permitting I will upload a sample of how to accomplish what you need.
0
 
MacroShadowCommented:
@GrahamSkan
While generally I agree with you that it is far better to teach the OP how to achieve the necessary result, in this case I believe that is well beyond the scope of this forum. Repeating JDettman's advice hardly counts as a how-to answer, and suggesting to Google for ready made code is in my opinion at least less useful than the db I uploaded.

However, I will show how to encrypt/decrypt existing data. The routine has 2 arguments;
blnEncrypt: a boolean type (yes/no) variable, indicating whether to encrypt (True) or decrypt (False).
intEncryptionMode: a integer type variable, indicating which encryption algorithm should be used. 0 = Hex, 1 = BlowFish, 2 = RijnDael.
You will be prompted to enter the following info:
1. Table name
2. Username field in the above table
3. Password

Option Explicit

Sub MyTest(blnEncrypt As Boolean, intEncryptionMode As Integer)

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strTableName As String, strUserNameField As String, strPasswordField As String

    On Error GoTo err_handler

    strTableName = InputBox("Please enter the table name that contains the data to encrypt.", "Table Name")
    strUserNameField = InputBox("Please enter the name of the field that contains the Usernames.", "Username Field Name")
    strPasswordField = InputBox("Please enter the name of the field that contains the Passwords.", "Password Field Name")

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strTableName, dbOpenDynaset)

    'Check to see if the recordset actually contains rows
    With rs
        If Not (.EOF And .BOF) Then
            .MoveFirst    'Unnecessary in this case, but still a good habit
            Do Until .EOF = True
                'Perform an edit
                .Edit

                Select Case intEncryptionMode
                    Case 0    ' Hex
                        If blnEncrypt Then
                            rs(strUserNameField) = ToHEX(rs(strUserNameField))
                            rs(strPasswordField) = ToHEX(rs(strPasswordField))
                        Else
                            rs(strUserNameField) = HexToString(rs(strUserNameField))
                            rs(strPasswordField) = HexToString(rs(strPasswordField))
                        End If
                    Case 1    ' BlowFish
                        If blnEncrypt Then
                            rs(strUserNameField) = blEncryptString(rs(strUserNameField), "YourSecretKey") ' You can change YourSecretKey to any text
                            rs(strPasswordField) = blEncryptString(rs(strPasswordField), "YourSecretKey") ' You can change YourSecretKey to any text
                        Else
                            rs(strUserNameField) = blDecryptString(rs(strUserNameField), "YourSecretKey") ' You can change YourSecretKey to any text
                            rs(strPasswordField) = blDecryptString(rs(strPasswordField), "YourSecretKey") ' You can change YourSecretKey to any text
                        End If
                    Case 2    ' RijnDael
                        If blnEncrypt Then
                            rs(strUserNameField) = rdEncryptString(rs(strUserNameField), "YourSecretKey") ' You can change YourSecretKey to any text
                            rs(strPasswordField) = rdEncryptString(rs(strPasswordField), "YourSecretKey") ' You can change YourSecretKey to any text
                        Else
                            rs(strUserNameField) = rdDecryptString(rs(strUserNameField), "YourSecretKey") ' You can change YourSecretKey to any text
                            rs(strPasswordField) = rdDecryptString(rs(strPasswordField), "YourSecretKey") ' You can change YourSecretKey to any text
                        End If
                End Select

                .Update

                'Move to the next record. Don't ever forget to do this.
                .MoveNext
            Loop
        Else
            MsgBox "There are no records in the recordset."
        End If
    End With
    MsgBox "Finished looping through records."

err_handler:

    If Not rs Is Nothing Then
        rs.Close    'Close the recordset
        Set rs = Nothing    'Clean up
    End If

End Sub

Open in new window


I you want I can add other encryption and hashing methods too.
0
 
sgleeAuthor Commented:
@MacroShadow
I appreciate your effort to share VB code. Let me try to digest the code and figure out how I can use your code in my password entry form.
0
 
MacroShadowCommented:
What exactly is your process?

Take your time understanding the code. In the mean time I'll give you some more code that will illustrate one possible method of accomplishing your goal.

Once the fields are encrypted, to check if the info entered if valid you would do something like this (Non-tested code):
Option Explicit
Option Compare Database

Private intLogonAttempts As Integer

Private Sub Form_Open(Cancel As Integer)
'On open set focus to combo box
    Me.YourUserNameFieldComboBox.SetFocus
    intLogonAttempts = 0
End Sub

Private Sub YourUserNameFieldComboBox_AfterUpdate()
'After selecting user name set focus to password field
    Me.YourPasswordFieldTextBox.SetFocus
End Sub

Private Sub cmdLogin_Click()

    'Check if data is entered into the UserName combo box

    If Len(Me.YourUserNameFieldComboBox) = 0 Then
        MsgBox "User Name is a required field.", vbOKOnly, "Required Data"
        Me.YourUserNameFieldComboBox.SetFocus
        Exit Sub
    End If

    'Check if data is entered into the password box

    If Len(Me.YourPasswordFieldTextBox) = 0 Then
        MsgBox "Password is a required field.", vbOKOnly, "Required Data"
        Me.YourPasswordFieldTextBox.SetFocus
        Exit Sub
    End If

    'Check value of password in Your Table to see if it matches the value chosen in combo box
    
    ' Depending on the encryption algorithm chosen you should use one of the following three lines
    ' if using Hex encryption use the following line
    ' If Me.YourPasswordField.Value = DLookup(HexToString(YourPasswordFieldInTable), "YourTable", "[ID]=" & Me.YourPasswordFieldComboBox.Value) Then
    ' if using BlowFish encryption use the following line:
    ' If Me.YourPasswordField.Value = DLookup(blDecryptString(YourPasswordFieldInTable, "YourSecretKey"), "YourTable", "[ID]=" & Me.YourPasswordFieldComboBox.Value) Then
    ' if using RijnDael encryption use the following line:
    ' If Me.YourPasswordField.Value = DLookup(rdDecryptString(YourPasswordFieldInTable, "YourSecretKey"), "YourTable", "[ID]=" & Me.YourPasswordFieldComboBox.Value) Then

        YourIDFieldInTable = Me.YourPasswordFieldComboBox.Value

        'Close logon form and open splash screen (could be Switchboard or another form instead)

        DoCmd.Close acForm, "frmLogon", acSaveNo  'substitute correct name if using form other than frmLogon in the example.

        DoCmd.OpenForm "frmSplashScreen"  'substitute correct name if using switchboard or other form.

    Else
        MsgBox "Password Invalid.  Please Try Again", vbOKOnly, "Invalid Entry!"
        Me.YourPasswordFieldTextBox.SetFocus
    End If

    'If User Enters incorrect password 3 times database will shutdown

    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts = 3 Then
        MsgBox "You do not have access to this database.  Please contact your system administrator.", vbCritical, "Access to Access is Restricted!"
        Application.Quit
    End If

End Sub

Open in new window


Of course you have to change the field names, to match the names of your fields on the form and in the table.

The above example assumes the following:
1. You are using a ComboBox to select a username
2. The ComboBox is unbound
3. The ComboBox has two columns
4. The width of the first column is 0
5. The bound column is column 1
6. The ComboBox's RowSource is something like this (adjusting the field names to match your field names):
SELECT tblUsers.UserID, tblUsers.UserName
FROM tblUsers;

Open in new window

0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<< Repeating JDettman's advice hardly counts as a how-to answer, and suggesting to Google for ready made code is in my opinion at least less useful than the db I uploaded.
>>

  Yes, but he did add in the fact that MD5 would be a good choice.

"Even better, use MD5 encryption. It is virtually impossible unencrypt back to a usable password. Googling for MD5 and VB will find many pre-written code blocks "

 and I have a MD5 sample here, which I can post.

Jim.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now