none
1) How to Protect Access Product/Logic/Code/etc for Distribution? 2)Can you split all tables but 'some' ?

    Question

  • 1 - With Access 2007 the user no longer needs Access on their computer... right?

    1a - via using the Package Solution Wizard... right? (is there an alternate or better way?)

    1b - What else must you do to maximize the protection of your solution ?

    1c - I thought I had read about  a product designed to package an Access Product/Solution for distribution... if so what are the advantages of such a distribution tool... why not just zip all the stuff... or make it self-extracting exe ? Some product suggestions would be helpful on this topic & reasons to consider such.

    2 - Regarding Splitting the database so the data is on the back-end and the logic is on the front-end...

    2a - the split data is just in a mdb file that can be opened by anyone with a copy of Access... right?

    2b -Can you keep one or more tables in with the system logic on the front end in order to protect it from easy access ?

    Example: Product Serial Number, Product Key, Distribution Date, Registration Information, etc

    Thanks for any help with this.

     

    Tuesday, October 11, 2011 10:16 PM

Answers

  • Here are some things you can also do:

    1. Create a new Public Function module and paste the Public Functions code below into it. Run ap_disable in the VBA Immediate menu or call it on application startup. This prevents anyone from opening the file using SHIFT+ENTER, bypassing any startup forms and code etc.

    2. Create an .accde file from your .accdb file (protects code)

    3. Open the .accde file

    4. Open Access Options--->Current Database and uncheck the Use Access Special Keys option (prevents using F11 to open the Navigation Pane and ALT+F11 to open the VBA window)

    Now you can distribute the application .accde file and no one can open the file by bypassing any opening forms or code, change anything using the Navigation pane, get at any code or open the VBA window even if they change the file name back to an .accdb file. Do NOT use the procedures under 4. on your original .accdb file and always create a backup file before doing these procedures.

    Here is an alternative distribution system which is free. It takes time to learn and some practice but is very flexibile There is a Help Forum which I have found useful. It's called Inno Setup.

    http://www.jrsoftware.org/isdl.php

    I have also used the Access Developer Runtime Solution (also free) at http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=4438. It is less fexibile but works fine. It creates an .accdr file from the .accde file you made.

    Hope this helps you.

    Prevents using SHIFT+ENTER to bypass startup forms or code:

    Public Function ap_DisableShift()
    'This function disables the shift at startup. This action causes the Autoexec macro and Startup properties to always be executed.
    On Error GoTo errDisableShift
    Dim db As DAO.Database
    Dim prop As DAO.property
    Const conPropNotFound = 3270
    Set db = CurrentDb()
    'This next line disables the shift key on startup.
    db.Properties("AllowByPassKey") = False
    'The function is successful.
    Exit Function
    errDisableShift:
    'The first part of this error routine creates the "AllowByPassKey property if it does not exist.
    If Err = conPropNotFound Then
    Set prop = db.CreateProperty("AllowByPassKey", dbBoolean, False)
    db.Properties.Append prop
    Resume Next
    Else
    MsgBox "Function 'ap_DisableShift' did not complete successfully."
    Exit Function
    End If
    End Function

    Public Function ap_EnableShift()
    'This function enables the SHIFT key at startup. This action causes the Autoexec macro and the Startup properties to be bypassed
    'if the user holds down the SHIFT key when the user opens the database.
    On Error GoTo errEnableShift
    Dim db As DAO.Database
    Dim prop As DAO.property
    Const conPropNotFound = 3270
    Set db = CurrentDb()
    'This next line of code disables the SHIFT key on startup.
    db.Properties("AllowByPassKey") = True
    'function successful
    Exit Function
    errEnableShift:
    'The first part of this error routine creates the "AllowByPassKey property if it does not exist.
    If Err = conPropNotFound Then
    Set prop = db.CreateProperty("AllowByPassKey", dbBoolean, True)
    db.Properties.Append prop
    Resume Next
    Else
    MsgBox "Function 'ap_DisableShift' did not complete successfully."
    Exit Function
    End If
    End Function

    • Proposed as answer by Bruce Song Friday, October 21, 2011 1:19 PM
    • Marked as answer by Bruce Song Monday, October 24, 2011 12:18 PM
    Wednesday, October 12, 2011 6:12 PM

All replies

  • 1. Users require Access to use Access applications. You do have the option of providing the users with the free Access run-time, but they still have to install it in order to use your application.

    1a. The Package Solution Wizard is probably sufficient for most needs. Note, however, that using it does not change your application in any way, shape or form. In other words, it does not "protect" your application in any way.

    1b To protect your VBA code, you can convert the application to an MDE (if using the older MDB file format) or ACCDE (if using the newer ACCDB file format). However, this does nothing to protect macros (or queries). With the MDB file format, you can apply Access user-level security to control access to the data in the table, but recognize that it's not straight-forward nor is it actually that secure (probably part of the reason why Microsoft removed the ability with the newer ACCDB file format)

    1c. A good distribution tool should be able to detect if the user already has Access installed and either not install the run time, or, if the installed version is older, install the run time in a way that does not affect the user's ability to use their existing version of Access.

    2. All databases should be split. Your understanding of what's what is correct.

    2a As mentioned in 1b, you can apply Access ULS to your MDB (there's no point converting the back end MDB to an MDE since MDEs are only intended to protect VBA code), but other than that, yes, it's open to anyone. And not only those with a copy of Access. Any ODBC-compliant application (such as Excel or Word) will be able to read a non-protected MDB file.

    2b You can put tables in the front end, but, as already mentioned, that does nothing to "protect it from easy access".


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    Tuesday, October 11, 2011 11:03 PM
  • Doug already gave good and generic answers. If we would understand better what you need to protect and how far you are willing to go to do it, we may be able to offer more detailed suggestions.

    For example, there are ways to encrypt passwords in such a way mere mortals cannot decrypt them. Is that of interest to you?

    Is it still of interest if that would mean you would need to ship an extra COM component, register it (and deal with 32-bit vs. 64-bit issues both at the Office and Windows level), and ensure the .NET framework is installed?

    Is it still of interest if there is a service that can decompile an mde/accde so your source code is essentially available in plain text?

     


    -Tom. Microsoft Access MVP
    Wednesday, October 12, 2011 2:01 AM
  • 1b - would this be a good idea,

    Create a security db which programatically opens an encrypted FE which contains the password for the encrypted BE?

    This way, only a succesful login to the security db will open the FE and only the FE has the password to the BE.

     

    2b - You could put a ULS in the FE instead of making a separate db.

    I defer to Doug and Tom as I know they are really good and I am learning still.


    Chris Ward
    Wednesday, October 12, 2011 1:46 PM
  • Thanks for the help Guys. Let me double check my understanding of the answers...

     

    Doug's 1b:
    Converting to  MDE or ACCDE...
    ... does protect the VBA code
    ... does not protect macros
    ... does not protect queries
    ... Some people call VBA code Macro's... but here I'm assuming Doug is not doing that... right?

     

    Doug's 1c:
    Can anyone suggest a distribution tool that will accomplish the items Doug mentioned...
    ... detect if the user already has Access installed
    ... and not install the run time unless the installed version is older
    ... and install the run time in a way that does not affect the user's ability to use their existing version of Access

     

    Doug's 2b:
    Since putting a table in the Front End does not prevent a user from accessing the data in the table...
    ... would putting that data into a VBA array, string, or other vba variable be the way to included protected data in the distributyon?
    … Data like Version, Expiration Date, Unique Product ID, Copyright data, etc?

     

    Tom's 1st point:
    ... Just trying to protect a simple little Access/VBA Application that we are considering offering for sale as a download.
    … Of course there is no perfect protection as all application developers know very well. But there is generally customary and reasonable techniques employed by developers to prevent their work from being a) redistributed illegally and b) disassembled, copied, and modified for redistribution as a competing product under another name.

     

    Tom's 2nd point:
    ... what would the encrypted password protect? Tables I guess. Anything else?

     

    Tom's 3rd point:
    ... Well I don't have a COM library checked in Tools > Reference... so I guess I'm not using any COM Objects... right?
    ... Are you telling me that I will have to activate a COM library in Access in order to create code that checks if the user has .NET framework installed?
    ... And is my Access Application using the .NET framework... how can I tell?

     

    Tom's 4th point:
    ... What is this "service" that will decompile the vba source code?

     

     Thank you both for your help... and anyone else that wants’ to jump in on this thread.

     

     

    Wednesday, October 12, 2011 2:37 PM
  • Here are some things you can also do:

    1. Create a new Public Function module and paste the Public Functions code below into it. Run ap_disable in the VBA Immediate menu or call it on application startup. This prevents anyone from opening the file using SHIFT+ENTER, bypassing any startup forms and code etc.

    2. Create an .accde file from your .accdb file (protects code)

    3. Open the .accde file

    4. Open Access Options--->Current Database and uncheck the Use Access Special Keys option (prevents using F11 to open the Navigation Pane and ALT+F11 to open the VBA window)

    Now you can distribute the application .accde file and no one can open the file by bypassing any opening forms or code, change anything using the Navigation pane, get at any code or open the VBA window even if they change the file name back to an .accdb file. Do NOT use the procedures under 4. on your original .accdb file and always create a backup file before doing these procedures.

    Here is an alternative distribution system which is free. It takes time to learn and some practice but is very flexibile There is a Help Forum which I have found useful. It's called Inno Setup.

    http://www.jrsoftware.org/isdl.php

    I have also used the Access Developer Runtime Solution (also free) at http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=4438. It is less fexibile but works fine. It creates an .accdr file from the .accde file you made.

    Hope this helps you.

    Prevents using SHIFT+ENTER to bypass startup forms or code:

    Public Function ap_DisableShift()
    'This function disables the shift at startup. This action causes the Autoexec macro and Startup properties to always be executed.
    On Error GoTo errDisableShift
    Dim db As DAO.Database
    Dim prop As DAO.property
    Const conPropNotFound = 3270
    Set db = CurrentDb()
    'This next line disables the shift key on startup.
    db.Properties("AllowByPassKey") = False
    'The function is successful.
    Exit Function
    errDisableShift:
    'The first part of this error routine creates the "AllowByPassKey property if it does not exist.
    If Err = conPropNotFound Then
    Set prop = db.CreateProperty("AllowByPassKey", dbBoolean, False)
    db.Properties.Append prop
    Resume Next
    Else
    MsgBox "Function 'ap_DisableShift' did not complete successfully."
    Exit Function
    End If
    End Function

    Public Function ap_EnableShift()
    'This function enables the SHIFT key at startup. This action causes the Autoexec macro and the Startup properties to be bypassed
    'if the user holds down the SHIFT key when the user opens the database.
    On Error GoTo errEnableShift
    Dim db As DAO.Database
    Dim prop As DAO.property
    Const conPropNotFound = 3270
    Set db = CurrentDb()
    'This next line of code disables the SHIFT key on startup.
    db.Properties("AllowByPassKey") = True
    'function successful
    Exit Function
    errEnableShift:
    'The first part of this error routine creates the "AllowByPassKey property if it does not exist.
    If Err = conPropNotFound Then
    Set prop = db.CreateProperty("AllowByPassKey", dbBoolean, True)
    db.Properties.Append prop
    Resume Next
    Else
    MsgBox "Function 'ap_DisableShift' did not complete successfully."
    Exit Function
    End If
    End Function

    • Proposed as answer by Bruce Song Friday, October 21, 2011 1:19 PM
    • Marked as answer by Bruce Song Monday, October 24, 2011 12:18 PM
    Wednesday, October 12, 2011 6:12 PM
  • Here are some things you can also do:

    1. Create a new Public Function module and paste the Public Functions code below into it. Run ap_disable in the VBA Immediate menu or call it on application startup. This prevents anyone from opening the file using SHIFT+ENTER, bypassing any startup forms and code etc.

    2. Create an .accde file from your .accdb file (protects code)

    3. Open the .accde file

    4. Open Access Options--->Current Database and uncheck the Use Access Special Keys option (prevents using F11 to open the Navigation Pane and ALT+F11 to open the VBA window)

    Now you can distribute the application .accde file and no one can open the file by bypassing any opening forms or code, change anything using the Navigation pane, get at any code or open the VBA window even if they change the file name back to an .accdb file. Do NOT use the procedures under 4. on your original .accdb file and always create a backup file before doing these procedures.

    This is partially incorrect.  Step 4 does not prevent the user from using F11 or Alt+F11.  The user would receive the "Project is unviewable" error after pressing Alt+F11 and attempting to view a module, so the objective is partially accomplished.  They could still open the navigation pane, but will be unable to design or delete forms, etc -- so the objective is essentially accomplished. Fortunately, there's no "Undo" option to convert an ACCDE back to an ACCDB, so at least your code will remain protected.

    I love the idea of running the your ap_DisableShift() module with AutoExec.  However, there are tools out there that can reenable the Shift Bypass key, even in an ACCDE with password-protected code.  Suggestions for how to protect against that?

    • Edited by Paul R. Miller Sunday, February 12, 2012 7:24 PM Clarification
    Sunday, February 12, 2012 6:44 PM
  • Actually disabling Microsoft Keys does prevent the use of F11 and Alt+F11. If you are finding that not being the case for your application, then you should manualy run compact and repair and test again. If there is still a problem you can create a Macro named "AutoKeys" listing the function keys and give them a code to perform something other than Microsoft. i.e.

    {F11} Beep

    Alt{F11} Quit, if you want to punish them

    You may also want to give yourself a backdoor in like a small box set to transparent background and border that has a code to enable shift bypass, show ribbon, show navigation.

    however keep in mind the accdb is for the developer and the accde is only for the end user. The accde is disposable.


    Chris Ward

    Sunday, February 12, 2012 9:26 PM
  • Actually disabling Microsoft Keys does prevent the use of F11 and Alt+F11. If you are finding that not being the case for your application, then you should manualy run compact and repair and test again. If there is still a problem you can create a Macro named "AutoKeys" listing the function keys and give them a code to perform something other than Microsoft.

    Hi Chris,

    Very strange... disabling "Access Special Keys" does not prevent a user from using F11 or Alt+F11 on my personal laptop, which is using Access 2010.  The functionality remains, both in ACCDB and ACCDE format.  I removed the password from my code, thinking that my be throwing Access off, but the keys were still enabled.

    I then attempted the same thing on a copy of a completely different database app I am developing in Access 2007 on my work laptop.  Same result:  F11 and Alt+F11 were not disabled, in either ACCDB or ACCDE format. 

    My home laptop uses Windows 7 and my work laptop uses Windows XP.

    What platform and version are you using where the disabled functions as designed?

    Nice tip for AutoKeys!

    Thanks,
    Paul

    Monday, February 13, 2012 3:17 AM
  • I have multiple stations with different OS's each using A2007. I don't have access to 2010 and I understand it has a different navigation option that can be disabled but I can't remember where.

    You may want to look through your macros and code to see if you have something turning these options on. I have in my application a hidden spot that I can click to set the options as true maybe you have code or a macro that runs OnOpen of the db or a Form or something. Are there other special keys working or just those two?


    Chris Ward

    Monday, February 13, 2012 2:39 PM
  • Paul

    When you run code to disable the bypass key you have to run the code, close the database and then re-open it.

    My Properties Setter can be used to set quite a few database properties. You can download the freeware at:
    http://thatlldoit.com/utilsaddins.aspx 


    Bill Mosca
    http://www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, February 13, 2012 4:09 PM
  • Chris:  I figured it out after created a new database and playing around.  You are indeed correct:  removing the check did disable F11 and Alt+F11.  I misunderstood the context.  When opening a database normally (double-clicking the name, etc), the keys are indeed disabled.  However, when opening the database while holding down the shift key, the keys remain enabled even if that box has been unchecked.

    Bill:  Right, as the method needs to be run in order to set the property.  However, a savy user could create or run a tool such as Ken Getz's AllowBypassKey2kTo2k7 and thus re-enable the Shift Bypass. 

    I'm wondering what the best steps are to protect against all of this.  My current process is as follows:

    1. Password-protect VBA code, including locking the project for viewing
    2. Convert from ACCDB to ACCDE
    3. Disable shift bypass

    While a user can reenable shift bypass, they will not be able to delete forms.  However, they can delete (but not run code from) macros and joined tables, and they can modify queries.  Arguably, this is the user's loss, but I think there is something to be said for also protecting "intellectually" the way one design's their database.

    I'm really interested in hearing how everyone approaches security, and this has been an educational thread.  Thanks!

    Monday, February 13, 2012 6:19 PM
  • You will need to reverse 2 and 3 as you cannot disable shift key after converting to accde unless you have special setups such as after you convert to accde have an OnLoad that checks for if bypass keys is enabled and if so then run the code to disable. So while it is possible to keep 2 & 3 in the current order it is more work and not as secure (if you forget to set it before you send it).

    Bill I am interested in your properties setting app.


    Chris Ward


    • Edited by KCDW Monday, February 13, 2012 7:17 PM
    Monday, February 13, 2012 7:12 PM
  • you cannot disable shift key after converting to accde unless you have special setups such as after you convert to accde have an OnLoad that checks for if bypass keys is enabled and if so then run the code to disable

    Ok yes you can, and very easily.  You can also enable it if it is disabled.  All you have to do is call the following function in another file, passing the path to the .accde file, the name of the property, its type and the value to be set:

    Public Function SetProperties(strDbs As String, strPropName As String, _
    varPropType As Variant, varPropValue As Variant) As Boolean

        On Error GoTo Err_Handler

        Dim db As DAO.Database, prp As DAO.Property

        Set db = DBEngine(0).OpenDatabase(Name:=strDbs)
        db.Properties(strPropName) = varPropValue
        SetProperties = True

    Exit_Here:
        Exit Function

    Err_Handler:
        If Err = 3270 Then    'Property not found
            Set prp = db.CreateProperty(strPropName, varPropType, varPropValue)
            db.Properties.Append prp
            Resume Next
        Else
            SetProperties = False
            MsgBox Err.Description, vbExclamation, "Error"
            Resume Exit_Here
        End If

    End Function


    Ken Sheridan, Stafford, England

    Monday, February 13, 2012 11:48 PM