Access Security Ideas from an Experienced Developer

Some good Access security/protection ideas from a reader who wishes to remain anonymous because he wants his application security kept a secret.

Hi Garry, I thought it might be a good idea to share these ideas with you about security that I thought of while reading your book.

Note: Since my main application is distributed as an mde file I have not reviewed these features specifically for use in mdb files. There will be some additional security considerations for mdb files.

Developer mode

You may want your application to behave differently on your development machine to how it behaves on the client’s machine. To do this, create a ‘secret’ text file, for example DevsComputer.txt, to identify that this computer is your developer’s computer. I put it in the root directory (C:\DevsComputer.txt) so it is easily accessible to applications in any folder. When you need your application to behave differently, just check for the existence of your secret developer file with the following code.

If Dir(“C:\DevsComputer.txt”)<>”” Then 
  'Developer code

Else 
  'User Code
End If 

You could run this procedure when the application opens and set a global variable which you can more easily access from anywhere in the application.

Global gDevMode As Boolean 
gDevMode = Dir(“C:\DevsComputer.txt”)<>”” 

in any procedure …

If gDevMode=True Then 
  ...

Else
  ... 
End If 

I usually use this to turn on or off various environment variables such as the AllowBypassKey, AllowSpecialKeys, AllowFullMenus, Show Hidden Objects. It is also useful for debugging as described in the next section. And when the application quits, I use it to have the application return to the database window for more development, while it quits completely on the client’s machine. If you want to look at how the application will work on the client’s machine, just rename the text file so it can’t be found. I usually just put an x in front, e.g. xDevsComputer.txt.

Then I can easily remove the x when I want to go back to developer mode. If you are working on site, you can create the text file on the client’s computer if you need to use specific developer features. But don’t forget to delete it before you leave! (Might be a good idea to remove it from the recycle bin too.) This process is not particularly secure if your clients are inclined to view your code. You may need to consider the likelihood of this happening and how it might compromise your application.

Generic error handler +/- logging.

Ref Chapter 4 in Garry’s Access Security Book

There are plenty of generic error handling procedures already described. This is just a small addition to improve the developer’s lot. When developing an application, I want the code to show me the exact line where an unexpected error occurred. The code below shows you the error message, then you can choose whether to continue with the normal error handling routine, or whether to break into the code. If you go to the code, hit the F8 key twice to go to the line that caused the error. The key is to use the Resume statement – but this should never be used except in break mode or the app may go into an infinite loop.

Note that you need to use the Developer Mode function in the error handling procedure.

Private Sub RunSomeCode()

On Error GoTo ErrHandler

 <Your code here >

ExitHere:
 Exit Sub

ErrHandler:

  Select Case Err.Number
    Case XXX    'For a specific error, add code here to manage that error

      <Your error management code for specific error>

      Resume Next  'Or Resume or Resume ExitHere
    Case Else
      If ShowGenericErrorMessage("RunSomeCode") = True Then
        Stop
        Resume  'To manually debug
      Else
        Resume ExitHere
      End If

  End Select

End Sub

Public Function ShowGenericErrorMessage(ProcName As String) As Boolean
'Don't add any error code to this procedure since it will reset the Err 
'to 0 and prevent the error being displayed.
'Called by various error procedures
'If false the code continues in the calling procedure
'If true the code stops in debug mode in the calling procedure
   
'Reset the environment in case it had been modified by the calling procedure

Application.Echo True
DoCmd.Hourglass False
DoCmd.SetWarnings True
If Dir("C:\DevsComputer.txt") <> "" Then

   ShowGenericErrorMessage = MsgBox("The following unexpected program error” & _" has occurred:" & _
vbCrLf & vbCrLf & "Module:             " & _ Application.CurrentObjectName & vbCrLf & _
"Procedure:        " & ProcName & _
vbCrLf & "Error number:    " & Err & vbCrLf & "Description:      " & Error & _
vbCrLf & vbCrLf & "Do you want to debug the program code?", vbExclamation + _
vbYesNo + vbDefaultButton2, "Unexpected application error") = vbYes

 Else

  MsgBox "The following unexpected program error has occurred:" & vbCrLf & _
  vbCrLf & "Module:             " & Application.CurrentObjectName & vbCrLf &
_
"Procedure:        " & ProcName & vbCrLf & "Error number:    " & Err & _
 vbCrLf & "Description:      " & Error & vbCrLf & vbCrLf & _
 "Please inform technical support. ", vbExclamation, _
  "Unexpected application error"

  ShowGenericErrorMessage = False
End If
End Function

Turn bypass key back off in code after startup.

If clients work out how to re-enable the Shift Bypass in your code, you can have the application turn it back off in its startup procedure. This won’t stop the persistent hacker, but will ensure the application behaves itself most of the time. Garry discusses this in detail in Chapter 2 of his book.

How to licence users

Many developers use a specific licence key to unlock functions of their applications. I have seen very few descriptions of how to do this. My own method requires the code to be secured from prying eyes – using an mde file is the easiest way to ensure this. This system works well with no user level security.

My application has 4 levels of client permission each with different functions. These are
• Demonstration – clients can create only 25 accounts in a single back end
• Single user – clients can use the system for unlimited accounts with one back end only
• Multiuser – clients can use up to 5 different back ends
• Unlimited – clients can use unlimited back ends

To manage these different levels, I use a single registration number. The registration number determines which level of access a client receives. If the client has no registration number, the system operates in Demonstration mode.

One way to have the system recognize a registration number would be to store a large number of possible registration numbers in the database. However, this has several problems – where do you store them – how can they be secured – what if you need more – etc. A better way is to have the system use a formula to determine if a registration number is valid.

With my application, I also wanted to be able to prevent one client giving his registration number to another client. Consequently I needed a way to register the application to a specific client. What I settled on was a method which involved reading the disk serial number in code, and tying the registration number to the serial number.

Each time the application opens, it reads the disk serial number and compares it to the registration number. If they don’t match correctly, the system runs in demonstration mode. The registration number itself is unsecured, the client knows what it is and can give it to others. But it won’t work on their machine. To make this work I have a registration form in the client’s app which displays a manipulated version of the disk serial number. The client gives me this number and I create a registration number which will match it. By match it I mean that the two numbers obey a specific mathematical relationship. If the disk serial number is not the one which was matched to the registration number (say the client tried to install on another computer) the mathematical relationship would no longer be valid and the system would revert to demonstration mode. The mathematical relationship and number manipulation code is hidden within the mde so it cannot be discovered by the clients.

After being entered, the registration number is stored in the front end application in a hidden table (not that it can’t be found). One problem which arose was that when clients installed a new front end they would have to reinsert their registration number. To avoid this, I save the registration number to a text file and have the system look for it when it is not found in the front end. Of course it checks it against the disk serial number too to make sure it is valid.

Using the mathematical relationship, you can set up different registration numbers to unlock different functions. So if disk number = 2 x reg number, then function set A might be active, while if disk number x 4.357 = reg number, the function set B may be active.

If a client reformats their hard drive or buys a new computer, they will need a new registration number. It’s easy to automate this by setting up and appropriate client database which the ability to enter a serial number and have a registration number created – they email the computer ID and you generate a new matching reg number and email it back.

USER INTERACTION PLEASE … If any of the readers of this article would like to share their own ideas on how to register their databases, please send them to Garry using the contacts links on the side of this page.

Winzip command line + password

Often my clients need to email me their databases for development or troubleshooting purposes. Since these may contain sensitive information, I feel uncomfortable that the data may be picked up in transit on the Internet somewhere. What I prefer to do is to have the client zip their data into a password protected Winzip file. However, this is not second nature to all clients, and in some cases, they need to do this regularly. Consequently a bit of automation goes a long way.

If you have the registered version of Winzip you can also get the command line add on which allows you to create zip files from code. However, even the standard version of Winzip accepts command line instructions although these are not documented on the site. For example, the following line will create a zip file from code if you have Winzip installed (even the unregistered version)

 Shell("c:\program files\winzip\winzip32.exe -a -ex """ & strDestination & """ """ & strSource & """", vbNormalFocus)

If you have the unregistered version, you will have to click the I agree button in the Winzip dialog box before the file will be created.

You can add a password using the following syntax …

"c:\program files\winzip\winzip32.exe -a -ex -s" & strPassword & " """ & strDestination & """ """ & strSource & """", vbNormalFocus)

For ease of use without compromising security too much, you and your client don’t have to use a specific password, you can have the code generate this for you. If you settle on a particular format which includes the current date and/or time, the password can be different each time, but you will both be able to decipher it. For example, use ClientYYYYNameMMM!DD, where YYYYMMMDD is the date the file was created. The recipient can then simply check the date on the received file and work out what the correct password is. Not foolproof, but not a bad compromise.

The string strSource can include multiple files and wildcards. Note the use of double quotes and spaces in the code.

Note that there are plenty of zip utilities on the Internet which you can incorporate into your applications (not everyone has Winzip). Many of these are active X controls and vary in cost from 0 to $300+ for royalty free distribution versions.

Live update

Mostly I provide updates to my clients as self extracting zip files. While these are fine for most people, I spend considerable time helping a small number to extract and install files and even to help them find out whether they need the update.

I have now developed a system where the clients can click a button in their application to check my website for updates, and to download and install them.

 On the site, I put a csv file containing the latest versions of the relevant files. Included is the version, filename to download (zip file), file name contained in the zip, and notes.

Using the ftp/http functions from the AccessWeb (http://www.mvps.org/access/modules/mdl0037.htm), I put a button on my application to check for updates. This downloads the csv file to the client computer. Then the csv file is attached as a linked table, and the versions in the file are compared with the versions in the system. If there is a newer version listed in the csv file, the user is given the option of downloading and installing it.

Downloading the files is done with the same code. Next, the downloaded files must be unzipped. I had to invest in one of the Active X components to do this as I couldn’t rely on all clients having Winzip. Using the properties of the Active X controls, I unzip the downloaded files to their correct folders. Mostly these files are updated back end tables which can be copied while the front end is open (as long as no bound forms are open).

When I have to update the front end itself, the file is unzipped to a separate folder (I create a LiveUpdate folder as a subfolder of the data location) and the user is requested to close and reopen the application. When the application opens, it looks to the LiveUpdate folder to see if there is a newer version of the front end. If there is, it opens a separate mdb file called VersionUpdateUtility.mdb and then closes itself. The VersionUpdateUtility copies the newer front end from the LiveUpdate folder to the main folder, then opens the copied front end and closes itself. Voila! New front end open and functioning!

The great benefits of this system are that the clients can easily see if their application is up to date. They can download and install updates without having to think about where to unzip the files. That means less support queries for me.

And I can prevent them from downloading updates if they have not keep their subscription fees up to date. To do this I keep an encrypted text file on my site containing all the clients usernames, registration numbers and subscription status. When the client requests an update, the application first examines the client’s file to see whether their subscription is current. If not, no downloading is allowed. And all this without a web database!

Other Pages at VB123.com That You May Want To Visit

Microsoft Access Security And Passwords
Save Reports As PDF Files From Access

The Workbench now makes startup property management easier

Click on the button for the next help page in this Access Loop.

Further Posts on this topic

Just reading some of your tips. Your licensing idea that uses the disc serial number sounds very useful. What's the API call to get the number?  See below

Also - in a Citrix or Terminal Server environment won't all users be running on the same hard drive? You would need to combine this method with a user count (concurrent and/or total) to limit the number of users.

My own method is to have an encrypted, workgroup secured mdb with the licence number list in it. I make a couple of sales a year with that system so it's no hassle to create new licences manually. To stop users passing on the licence key, the Organization Name is also stored in the Licence.mdb - and this is included in the footer of every report. It's a long way from bullet-proof I know, but adequate in my case.  

Mark Plumpton

The original author wrote

I got this from Calvin Smith (http://www.calvinsmithsoftware.com/VolumeSerialNumber.txt), but there are a number of other places where you can find similar code.

  
Private Declare Function GetVolumeInformation Lib "kernel32" Alias _
"GetVolumeInformationA" (ByVal lpRootPathName As String, ByVal _
lpVolumeNameBuffer As String, ByVal nVolumeNameSize As Long, _
lpVolumeSerialNumber As Long, lpMaximumComponentLength As _
Long, lpFileSystemFlags As Long, ByVal lpFileSystemNameBuffer As _
String, ByVal nFileSystemNameSize As Long) As Long

Function ReturnVolumeSerialNumber$(strDrive$)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Author:        Calvin Smith
'E-Mail:        codeheadcalvinsmith@yahoo.com
'Environment:   Visual Basic (32-Bit) MS Access (32-Bit)
'WEB:           http://home.sprintmail.com/~calvinsmith/
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
On Error GoTo ReturnVolumeSerialNumber_Err

'32-Bit Usage: strRetVal$ = ReturnVolumeSerialNumber$("C:\")

    Dim strVolumeBuffer$
    Dim strSysName$
    Dim lSerialNum&
    Dim lSysFlags&
    Dim lComponentLength&
    Dim lRetVal&

    strVolumeBuffer$ = String$(256, 0)
    strSysName$ = String$(256, 0)

    lRetVal& = GetVolumeInformation(strDrive$, strVolumeBuffer$, 255, lSerialNum&, _
            lComponentLength&, lSysFlags&, strSysName$, 255)

    'If lRetVal& = 0 Then
        'ReturnVolumeSerialNumber = "Unable to retrieve volume information"
    'Else
        'ReturnVolumeSerialNumber = "The VSN of " & strDrive$ & " is: " & lSerialNum&
    'End If

    ReturnVolumeSerialNumber = lSerialNum&
    
ReturnVolumeSerialNumber_Exit:
    Exit Function

ReturnVolumeSerialNumber_Err:
    If Err Then
        'Do whatever you need to here!
        Resume ReturnVolumeSerialNumber_Exit
    End If


End Function

Then I use the following line where ProgramPath is the path to the current database ...

DiskSerialNumber = ReturnVolumeSerialNumber(Left$(ProgramPath, 3))


To use my system, I take the SerialNumber and do some mathematical and string things with it, then display it on the clients order form. The clients sends me this number which I unencrypt to find the SerialNumber. Then I create a registration number which matches the serial number in another mathematical and string algorithm and send it to the client. They put in the registration number and it unlocks whatever functions you like. I have several algorithms which equate to different versions of my application - so if the client has a particular reg num, specific functions are allowed. The reg num will only work on a computer where the algorithm matches the drive serial number.

This system has a number of potential disadvantages, and you can find discussions of this on Access newsgroups. If a client buys a new computer, new hard drive, formats the drive, and so on, a new number is required. This is not a problem for me because I have a database of only about 150 registered clients, so the new number requests are not that frequent. Also, I communicate directly with the clients (phone or email) so can be reasonably confident that they're not installing the system outside the licencing rules. And it's no great hardship for the clients because I am usually quite prompt in providing a new number.

Also, not all drive numbers are unique, but in my niche market, there's not a great problem with people trying to rort the system.