Solved

Password Protection in the Access table

Posted on 2013-06-29
13
1,730 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 27

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 27

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
 
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 27

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 27

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 27

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

810 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