Securing spit database

Question

Hello,

I have a split database. The BE is secured by a password, and this password is hard coded in the FE. In other words, only my FE should be able to access the BE.

I always compile my FE to an accde so assume my password is reasonably protected.

The problem is, anyone can open a new database and external link to my FE, and view all tables including the linked BE tables.

How can this be prevented? I don't want to place a password on my front end. In fact, I want to later implement some basic user control where a few different users can have different levels of form access; I can do this in code but don't want to require 1 password to open the FE, then another password for their user access.

Can external linking be prevented? Or how best to lock everything down?

A lot of internet stuff is A2003 / mdw workgroup stuff which is no good. I can't find anything that seems to definitively secure the data, without a FE password.

Thanks

swas

Answer 1

You can secure both databases with username and password.

Than in your VBA you can make use of the username and password in the connectionstrings.

So when you export the Access file with the code to a .mde-file the usernames and passwords can't be discover nor open the databases.

GL

Dan Cas 

Answer 2

Dan,

Thanks for the comment.

I didn't mention I am using A2007 / accdb, and I am taking your comment to reference workgroups?

Sorry, otherwise I don't know what you are referring to. If you could expand it would be great.

swas

Answer 3

Hi mr swas,

You can look into this thread "10 tips of securing a database":

http://www.techrepublic.com/blog/10things/10-tips-for-securing-a-microsoft-access-database/552

When having your 'security' in place (disable shift, and hiding the navigation pane etc.) and creating the ACCDE, you can look into Runtime as well.

It's very common to use Runtime to deploy your database amongst the users, see for Runtime and Deployment below threads:

http://hitechcoach.com/index.php?option=com_content&task=view&id=14&Itemid=9

http://office.microsoft.com/en-us/access-help/deploy-an-access-2007-application-HA010218864.aspx

Hope this helps,

---

Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.

Answer 4

Hi Swas

mr swas wrote:

The problem is, anyone can open a new database and external link to my
FE, and view all tables including the linked BE tables.

An Access BE can't be secured completely. Even if you think the hardcoded password in the FE to access the BE this isn't sure at all because one can read the values out from the MDE. Strings have to remain strings in there and aren't encrypted. There are public paid services that will restore an MDB from an MDE.

If you need to secure your backend data you will have to migrate to an active database server like SQL Server. To be even more sure you will also have to encrypt the data between the FE and the BE by enforcing a secure connection and install a certificate on the SQL Server.

HTH
Henry

Answer 5

If you don't encrypt your FE (ie apply a password) then it can be opened in Notepad and the BE PWD is there to see.

I ran this test in A2010

Try this - open you accdb in Notepad, search for "PWD" and there it is, now make it an accde, try the same thing again. Yep, still there.

Now open the accdb in exclusive mode and apply a password (this will encrypt it), now search for "PWD" or the password in Notepad. Not there.

OK so now you have a FE that only someone with FE password can attach to it or get into and therefore remove the password to decrypt it and with that they can easily find the BE password.

Now convert the encrypted FE to an accde and it's then it's all a lot harder to get at the BE password. You can remove the password to decrypt the accde but even then you can't find the password in Notepad anymore. Of course if you stored the BE password in a table then it's there for the taking but if it's hardcoded, you can't get at it using the VBE or a text editor. There may be some other way to get to it but so far I haven't found it. To make it even harder you can create the links using code when the app opens and destroy them when it closes.

So the short answer is, sorry you need to put a password on the FE and then convert it to an accde.

---

Kent

Answer 6

Thanks for the responses.

Comments as follows, in sequence of replies:

 - Dan, I went through the security tips (Which I have seen prior but not ensured each is done). Hiding tables doesn't help as they are still visible from the 3rd db app and can be imported (Perhaps because show hidden objects is selected) . I also use the runtime on some machines, others need the full version.

 - Henry, I don't really need sql server level security; my app isn't that critical but acknowledge the reality of your comments. I just felt it a little 'unsecured' when all password protected BE tables can be browsed by just linking to the FE. I don't know how much it helps but I encode the password in vba code, and only when creating the connect string call a small subroutine that decodes, so it is created 'on the fly' in compiled accde code. Perhaps something small, but at least the password isn't directly stored as a string.

 - Kent, Similar comments re not storing the password directly which may help. Interested if you or others agree.

I'm not so concerned with the password protected BE though, or a skilled person extracting this password. The password protection is fine for my level of security needed. It's more how the FE makes this protection completely redundant by revealing all tables. With the recommended format being a split db this seems a poor situation.

Thanks for the comments. I'll leave the thread open a little longer for any more comments.

swas

Answer 7

Kent,

The PWD for the Linked Table object is visible through the Immediate window using: CurrentDb.TableDefs("someLinkedTable").Connect.  It makes no difference whether the FE is password protected or if the format is ACCDE or not.  Bottom line: when you store the PWD in the .Connect property it can be seen without much effort.

---

Brent Spaulding | Access MVP

Answer 8

swas,

I am short on time at the moment, but check out ...

Really hide your tables:

http://www.utteraccess.com/forum/Hide-table-names-code-t1526084.html&p=1527390#entry1527390

vPPC

http://www.utteraccess.com/forum/Access-2007-security-t1242310.html&p=1243573#entry1243573

---

Brent Spaulding | Access MVP

Answer 9

Ok ... a little more time. :)

The "Really Hide Your Tables" link has code that will set up your Table object so that it is hidden from view --- even if you set Access to "Show Hidden Objects", you still will NOT see the tables you hide with the code.  The attribute to really hide your tables is a database engine setting, so the database engine (Jet/ACE) does not "broadcast" the name to the client (the Access UI), however, you can still find the table reference in MSysObjects.

The vPPC link shows you a technique that "caches" your BE database password so you can thwart the crux of the problem, which is the fact that the password is stored in the .Connect property of a Linked Table object.  So the long and short of vPPC is this: REMOVE the BE password, re-create your linked tables, ADD the BE password (but DON'T relink), then use FE code to open (and persist the open state) the BE database (the technique in the link shows how to use a hidden form to persist the open state of the BE db). Once BE is opened, your linked table (with out the BE password in the .Connect property) will open just fine because Access already "knows" the password.  The thread I linked you to is long, but it is informative.

Please note the vPPC is not "bullet proof" either because once your code connects to a database via the pwd, you do some creative stuff and extract the BE pwd.  But, between "really hiding your tables" and using vPPC, you can create some fairly challenging barriers to hurdle for people who are curious.

-----

Another option that has not been mentioned is a "Launcher" db application.  What you can do is link the way you link --- with BE pwd exposed an all, then password protect your FE and keep it is ACCDB format.  Then, you can create a very simple NON-Password protected ACCDE formated file that does nothing more than open your FE.  Since the Launcher is NOT pwd protected, your users are not bothered with a password prompt, but if your users try to crack the FE, and interrogate the linked tables, they ARE prompted for the FE's password.  Implementing this technique is pretty simple: you create the launcher with an autoexec macro or start up form and using either method, run the following code:

Function OpenMySecureDatabase()
    
    Dim app As Object
    
    Set app = CreateObject("Access.Application")
    With app
        .UserControl = True
        .AutomationSecurity = 1
        .OpenCurrentDatabase "C:\someFolder\someDatabaseThisDbPwdProtected.accdb", False, "somePassword"
    End With
    Set app = Nothing
    Application.Quit
End Function

The kicker is this --- Once the FE is opened, people knowledgable in VBA and still interrogate the FE without the need for the FE pwd by simply hooking into the FE instance via VBA in another instance of Access, or an instance of Excel, Word, or other VBA host environment --- but those are fairly high barriers to hurdle for curious users to hurdle.

---

Brent Spaulding | Access MVP

Answer 10

Thanks Brett, I knew there still had to be a hole somewhere.

---

Kent

Answer 11

Thanks Brent for the thoughts - both directly here and the other links.

I need to spend some time and try a few of the options.

Life would be much easier if there were a currentdb flag which simply prevented all extenal linking to it. Or the ability for an opening form (Perhaps unbound) to be displayed before a database password was required.

While the answers are insightful and help, do they define an 'answer'?

swas

Answer 12

Your original questions ...

"The problem is, anyone can open a new database and external link to my FE, and view all tables including the linked BE tables.  How can this be prevented?"

The 'Really Hide Your tables' link shows how to accomplish the task of preventing external apps from seeing all the Table object through 'normal' mechanisms (ie: the Get External Data dialogs).

"I don't want to place a password on my front end. In fact, I want to later implement some basic user control where a few different users can have different levels of form access; I can do this in code but don't want to require 1 password to open the FE, then another password for their user access."

With the ACCDB format and user control, you are bound to 'rolling your own' simply because the ACCDB format does not support user level security.  Also, if a FE and BE password are implemented you can create a Launcher db app that launches your password protected FE, so the user won't have to enter the passwords.

"Can external linking be prevented?"

Let's just say that external linked can be thwarted by building barriers.  If the barriers are high enough to "protect" at a level you're comfortable with then the answer is Yes, otherwise, No.  I personally find the "Really Hide Your Tables" implemented alongside vPPC as high enough barriers to prevent external linking... but remember once the FE is opened, even with SQL Server as your Back End, in many cases an expert can interrogate your db app via Office Automation and get stuff.

"... Or how best to lock everything down?"

To me it depends on the environment in which your app resides and the level of barrier you want to build.  I use a variety of techniques to prevent my users from getting to what they should not get to: Really Hiding Tables, vPPC, custom Form object properties, and code that looks at the Active Directory user or terminalId of the PC (or both!).  But, remember the Access, by its nature, is -- well -- accessible!  Especially with Automation and GetObject().

----

>> While the answers are insightful and help, do they define an 'answer'? <<

So, yes the responses defined AN answer :) LOL! ... but if the responses you have don't build a high enough barrier of protection, then there is the option of going all or partially "unbound".  What that means is NO Table objects (not even Really Hidden ones) in the database.  Plus, no Query objects either.  Then for every Control object or Form object that uses data from the Back End, you would have to set the source via VBA.  It is extremely rewarding to build your app this way, but it is more code intensive -- and possibly overkill for what you really want! Remember to keep asking yourself the question: how high of barrier do I want? (knowing that Access may not be able to build a barrier high enough to acheive your ultimate goal)

---

Brent Spaulding | Access MVP

Answer 13

Brent,

That's a comprehensive and easy to read /understand summary.

Your help is appreciated.

swas

Answer 14

You are most welcome swas!  Good luck on your project!

---

Brent Spaulding | Access MVP

Answer 15

the option of going all or partially "unbound".  What that means is NO Table objects (not even Really Hidden ones) in the database.  Plus, no Query objects either.  Then for every Control object or Form object that uses data from the Back End, you would have to set the source via VBA.  It is extremely rewarding to build your app this way, but it is more code intensive

Brent Spaulding | Access MVP

Good Morning Brent,

Where can someone learn more about this?

---

Chris Ward