none
Securing table RRS feed

  • Question

  • I have an Access (2013) application that has a front end (an Access database containing forms, modules, queries, reports, macros) and a separate back end Access database that contains only Tables.  The front end "links" to the files in the back end, and in that way users can add/modify data that reside in the back end.

     I think I have the front end pretty well secure.  But what can I do to prevent someone from creating their own fresh Access database and then linking to the tables in my back end to manipulate data?

    Thanks in advance for any guidance you can offer.

    Monday, March 26, 2018 4:48 PM

Answers

  • There is really no concrete way to secure a back end. If a user can get into the folder and has write/read/modify permissions on that folder they can open the database or link to its tables. Access was never meant to be a secure environment. About the only way to "secure" the tables is to password-protect the database and link to the tables via VBA code. Working with ACCDE front ends will allow you to hide the code and put the password in it as a global. But even that is not completely secure.

    If you need security on your tables use SQL Server Express as the back end. With that engine you can secure all the way down to the column (field) level.


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

    • Marked as answer by Rich 30005 Monday, March 26, 2018 6:54 PM
    Monday, March 26, 2018 5:00 PM
  • "If you need security on your tables use SQL Server Express as the back end. With that engine you can secure all the way down to the column (field) level."

    Bill is, of course, complete correct!  If security is a concern, then another RDMS back-end is the only solution and obviously SQL Server (Access' big brother) is a great choice and you can use the SSMA (Microsoft SQL Server Migration Assistant) to upsize your tables/data with relative ease.


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    • Marked as answer by Rich 30005 Monday, March 26, 2018 6:41 PM
    Monday, March 26, 2018 6:18 PM

All replies

  • Hi Rich,

    Have you tried the usual steps? i.e. password protect the BE or set the tables as "hidden" or set the BE folder as Traverse to avoid manually navigating to it

    Can just anybody get a copy and use your FE? I hope not...

    Monday, March 26, 2018 4:56 PM
  • Password protect the back-end.

    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Monday, March 26, 2018 4:58 PM
  • There is really no concrete way to secure a back end. If a user can get into the folder and has write/read/modify permissions on that folder they can open the database or link to its tables. Access was never meant to be a secure environment. About the only way to "secure" the tables is to password-protect the database and link to the tables via VBA code. Working with ACCDE front ends will allow you to hide the code and put the password in it as a global. But even that is not completely secure.

    If you need security on your tables use SQL Server Express as the back end. With that engine you can secure all the way down to the column (field) level.


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

    • Marked as answer by Rich 30005 Monday, March 26, 2018 6:54 PM
    Monday, March 26, 2018 5:00 PM
  • ... and then linking to the tables in my back end to manipulate data?

    Hi Rich,

    You don't even have to link the tables, knowing the location is enough for late binding.

    Imb.

    Monday, March 26, 2018 5:14 PM
  • "If you need security on your tables use SQL Server Express as the back end. With that engine you can secure all the way down to the column (field) level."

    Bill is, of course, complete correct!  If security is a concern, then another RDMS back-end is the only solution and obviously SQL Server (Access' big brother) is a great choice and you can use the SSMA (Microsoft SQL Server Migration Assistant) to upsize your tables/data with relative ease.


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    • Marked as answer by Rich 30005 Monday, March 26, 2018 6:41 PM
    Monday, March 26, 2018 6:18 PM
  • Thank you for the prompt input, which sort of confirmed what I was fearing.

    On my front end, when you open the file I do a simple Environ$("username") to get the person's user ID on our network, and that is validated against a table of approved users.  So if someone not authorized on our network gets a copy, it'll stop the show.

    But the back end is an issue.  Luckily, this is a relatively simple application to be used by something like 25-30 users, none of whom is really regarded as a threat.  And our network guys are going to make the folder where the back end data resides accessible only to that group.  This is probably enough -- it'll have to be :-)

    Hey, Bill and Daniel, I really appreciate your help thinking this through.

    Regards,

    Rich


    Monday, March 26, 2018 6:54 PM
  • I've never like Environ$("username")  because it can be spoofed.  I recommend using an API (http://access.mvps.org/access/api/api0008.htm) which can't be messed with.  Call me paranoid.

    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Monday, March 26, 2018 7:33 PM
  • Rich - If you want to use Daniel's suggestion of an API call here is the code I use:

    Option Compare Database
    Option Explicit
    Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
            (ByVal lpBuffer As String, nSize As Long) As Long
    
    
    
    
    Public Function CurrentUserName() As String
    'Purpose  : Returns the network login name
    'DateTime : 5/28/2002 09:54
    'Author   : Bill Mosca
        Dim lngLen As Long
        Dim X As Long
        Dim strUserName As String
    
        'Create buffer
        strUserName = String(254, Chr$(0))
        lngLen = Len(strUserName)
        X = GetUserName(strUserName, lngLen)
    
        If (X > 0) Then
            'Trucate remaining buffer space from end of string.
            CurrentUserName = Left$(strUserName, InStr(strUserName, Chr$(0)) - 1)
        Else
            CurrentUserName = vbNullString
        End If
    
    End Function
    


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

    Tuesday, March 27, 2018 7:21 PM