Solved

Password Protection in the Access table

Posted on 2013-06-29
13
1,523 Views
Last Modified: 2013-07-01
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
Comment
Question by:sglee
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 57
ID: 39287087
<<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
 
LVL 57
ID: 39287090
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
 

Author Comment

by:sglee
ID: 39287091
<<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
 
LVL 26

Accepted Solution

by:
MacroShadow earned 250 total points
ID: 39287266
This should help.
Encryption.mdb
0
 

Author Comment

by:sglee
ID: 39287344
@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
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39287671
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39287823
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
 

Author Comment

by:sglee
ID: 39288003
@MacroShadow
When I use password input mask, it displays **** as I enter the password. Can someone read ***** and figure out what it is?
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39288024
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
 
LVL 26

Assisted Solution

by:MacroShadow
MacroShadow earned 250 total points
ID: 39288061
@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
 

Author Comment

by:sglee
ID: 39288722
@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
 
LVL 26

Assisted Solution

by:MacroShadow
MacroShadow earned 250 total points
ID: 39288972
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
 
LVL 57
ID: 39289792
<< 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now