Securing table

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.

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

"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

Answer 6

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

Answer 7

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

Answer 8

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