User-Level Security: Prevent Default Access

[Luke Bailey]

I have an access database that I have secured using ueser-level security. I have my own logon in the Admins group. I have also removed Admin from the Admins group so that the standard end-user can't access the program. I have also removed Open/Run access to the Database from the Users group. This has provided correct security for the database.

Here's my one big problem:

All someone has to do is go up and join the default "Security.m dw" workgroup information file, and then they can open the database and do whatever they want without a password.

I thought that removing permisions from the Users group and removing Admin from the Admins group would help with this, but is there a way to make it so that people cannot open my file using any other workgroup information file than the one I choose?

I have made a shortcut that defaults to the workgroup information file I want, but that doesn't stop them from just changing their workgroup information file later.

Any knowledge is most appreciated.

[Luke Bailey]

I have developed a solution to this problem for those who are interested in the future. It provides enough security for my needs however, someone who REALLY knows what they're doing with user-level security access and workgroup information files may be able to crack it.

After you have secured your Access database do the following:

1. Create a Shortcut with Target:

“path to MSACCESS.exe.” “path to file.mdb” /WRKGRP “path to file.mdw”

(example: Target: "C:\Program Files\Microsoft Office\OFFICE\M SACCESS.EXE" "D:\My Databases\MyDat abase.mdb" /WRKGRP "D:\Applica tion Data\Microsoft\ Access\MyDataba seSecurity.mdw" )

2. Create a VBA macro called “Startup()” and use the following code:

Public Sub Startup()
If DBEngine.SystemDB Like "*<YOU WRKGRP INFO FILE>.mdw" Then
Else
    MsgBox "Please open this program using the provided shortcut.", vbCritical, "Open Via Shortcut"
    Application.Quit (acQuitSaveNone)
    Exit Sub
End If
 
End Sub
 

Insert the name of your workgroup information file. In the example given above I would type

"If DBEngine.SystemDB Like "*MyDatabaseSecurity.mdw"
 

3. Create a VBA function to call your startup macro

Public Function StartMe()
Call Startup
 
End Function
 

4. Create an “AutoExec” Macro in Access (an Access Macro). Have it RunCode, and reference your StartMe function so that it will run your Startup code when the database opens. This macro must be saved as “AutoExec” to run on startup

5. Disable the AlloyBypassKey functionality in access with the following code (run the Sub once. It will be effective starting the next time you open the database):

Sub SetBypassProperty()
Const DB_Boolean As Long = 1
    ChangeProperty "AllowBypassKey", DB_Boolean, False
End Sub
 

Function ChangeProperty(strPropName As String, _
    varPropType As Variant, _
    varPropValue As Variant) As Integer
 
    Dim dbs As Object, prp As Variant
    Const conPropNotFoundError = 3270
 
    Set dbs = CurrentDb
    On Error GoTo Change_Err
    dbs.Properties(strPropName) = varPropValue
    ChangeProperty = True
 
Change_Bye:
    Exit Function
 
Change_Err:
    If Err = conPropNotFoundError Then ' Property not found.
        Set prp = dbs.CreateProperty(strPropName, _
            varPropType, varPropValue)
        dbs.Properties.Append prp
        Resume Next
    Else
        ' Unknown error.
        ChangeProperty = False
        Resume Change_Bye
    End If
End Function
 

SUMMARY: After you have run the disable bypass code then you can reopen your database. The end result is that if anyone is using a workgroup information file that is different from your own, the database will request that they open the database using the shortcut you provided them (the one that points to your workgroup information file) and shutdown Access. The disable bypass makes it so that people cannot hold down the SHIFT key to bypass the startup macros.

As you can see, the makes it so that your database requires your workgroup information file to run. However, you can probably also see one problem with this solution...if someone knew what they were doing with workgroup information files and permissions, then they could crack it. I think its save to say, however, that if you require more security than this (you have a risk of someone going to all that trouble) then you should probably be using SQL Server or something with more security than Access.

Any other information/comments/responses that will help me expand my knowledge in this area are much appreciated.