How to deny users from accessing tables

Question

I have an Access App FE with a SQL Server 2014 backend. Migrating BE to Azure DB this week I hope. Anyways, I've also been given the job of integrating features to handle all the Accounts Receivable for the company. This Access App is 3 years old and pretty freaking huge for an Access app. Now that the app is going to be holding financial data, and since it will be on Azure, we expect copies of the FE to be floating around, I need to make sure people can't get into the tables directly.

I'm rather taken aback. All my research points in the direction that you can't prevent users from accessing the DAO tables. How the heck did Microsoft expect people to design secure database?

I figure I can prevent the Navigation Pane from opening, disable opening the FE using the Shift key if someone tries to bypass the code, make it an ACCDE file, Also write startup code to shut down the application if they are not using Access Runtime. Even have code check these configs before it closes in case user gets in and resets them back before closing. Also I can create a ribbon disabling all the BackStage stuff.

Is there any more I can do from Access to prevent users from opening the tables from the Nav Pane? I designed the SQL Server BE too, but I don't know much about restrincting the user tables from there, plus it will require a crap load of work and recoding of a 3 year old huge front end Access app too. So I'm looking for an easier way. Any help out there?

Thanks!

Answer 1

Take a look at: http://www.devhut.net/2016/09/01/securing-your-ms-access-database-front-end/ for a few options as to what you can do.

---

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

Answer 2

Hi,

You could see the links below.

Security Considerations and Guidance for Access 2007

Real World Microsoft Access Database Protection and Security

Besides, If you have any issue about SQL Server, I suggest you post new thread on SQL Server Forum.

Thanks for your understanding.

Answer 3

Hi HTHP,

Everything you mentioned you can do to secure your front end application can be reversed engineered if users are savvy enough. However, since your real concern is the data table and luckily you're using SQL Azure as your back end, which means you can create security permissions at the table level.

So, the bottom line is, if a user has permission to a table in SQL Azure, then he or she can potentially access the data directly whether by using your front end or another front end they created. But, if the user does not have permission to a table, then it doesn't matter what they try to use to access the data, they won't be able to.

This is not the same if you were using an Access database file as a backend, to which, anyone who has access to the file can access the data.

Just my 2 cents...

Answer 4

I second this.  I'm guessing your current security model is that users either have no way to get to your Access data or they can get to it and do whatever they want, i.e. all or nothing. You should be able to do the same thing pretty straightforwardly in SQL.  You need to lock down the SQL back end.  Trying to lock down the front end is not going to get you very far if all your users need is a full copy of Access and knowledge of how to link to tables on a SQL server or a DSN to read or manipulate your data.

-Bruce

Answer 5

So does anyone have a good method for using SQL Server permissions? All I can guess is to not save the credentials in the linked tables defs, then Access asks for user name and password to server. But it also lets users select another server. This is a bit awkward. Any way to prompt user myself and somehow link that to their current login? I have 50 application users, who will all have to log in twice. Once to server and once to the app.

My second issue is that there is a lot of metadata, and other data that I wouldn't want changed by "savvy" users editing. There are just table values that need strong validating, and some data that the app needs to change that if a user goes in there and tweaks can disrupt the way the appliation works for everyone.

Answer 6

Hi,

See if this blog helps any. Cheers!

Power Tip: Improve the security of database connections

Answer 7

When you say that your users have to log in "once to the app" do you mean that you are using a database password?  If all of your sensitive data is in the SQL back end and you properly secure that the database password on your front end will become unnecessary.

At our center everyone logs into a Windows active directory domain and we have our SQL servers set up to authenticate users based on their Windows login.  In this scenario there is no need to worry about asking users to (re) enter a user id or password, or hiding a password somewhere in the application.  SQL server simply looks at who they are currently logged into the Windows domain as.  This way we don't care who manages to get a copy of the front end database; if they do not have access to the back-end SQL data then it is a non-issue.  I know Azure SQL has some sort of active directory also available to authenticate users but I am not familiar with how it works.  It may even be possible to link that to your current Windows domain for user authentication.

The blog posted by DBGuy is good, especially the section regarding DSN-less connections.  I think this would work well in a scenario where you set up all of your users directly in SQL rather than validating against a Windows domain.  Setting up users directly in SQL does involve managing a separate set of user id's and passwords though so keep that in mind.

-Bruce

Answer 8

No matter what you do to secure FE and BE files, can't anyone with ACCESS on their machine and access to any FE file simply import the data tables into a new database? Even if the FE is an .accde or .accdr file, they can simply change the extension to .accdb and import the tables to a new database file. How do you prevent that from happening?

Answer 9

When I say they have to "log into the app", I am talking about my custom coded User system in the application domain. I wrote custom classes for user authentication and authorization, and I created my own User Roles. I have created Users tables on the SQL Server. When they log in to the app, their user info is loaded into a customized class object. I guess what I mean by "app", is the application I created within Access so to speak, not the Access Application itself.

As for using Active Directory, I could learn it if I had to, but the front end is going to be on personal computers outside the business domain's control, so if my understanding of AD is correct, I can't go that route. I'll check out DBGuy's link.

Lawrence. I believe if you don't save the password within the tabledef, access prompts for the password to the server, so even if they could import the tableDefs, I don't think they could connect without the password.

Answer 10

No matter what you do to secure FE and BE files, can't anyone with ACCESS on their machine and access to any FE file simply import the data tables into a new database? Even if the FE is an .accde or .accdr file, they can simply change the extension to .accdb and import the tables to a new database file. How do you prevent that from happening?

Hi Lawrence,

There are a couple of ways to prevent it, but maybe not totally eliminate it. For example, you can deploy an FE with no linked tables. Use unbound form and then use code to populate them. Furthermore, you can encrypt the BE with a password, and since there are no linked tables, the intruder shouldn't see what the password is, as long as you compile your code to a ACCDE.

There are other methods as well relating to using a third database to handle all connections. You can do a search on "VPPC."

Cheers!

Answer 11

If you are this serious about security, then I believe Access is the wrong tool for you.

Also note, that you cannot merely change the extension of an accde to regain an accdb file.  You can't reverse engineer an accde in that manner that's why it is critical to always keep the original accdb for development.  An accdr is another thing, yes, you can change the extension to get the originating accdb file.

---

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

Answer 12

Regarding vPPC, to save you some searching, see: http://hitechcoach.com/index.php/microsoft-office/access/access-downloads/access-examples/example-security/vppc-toolkit-for-2007

---

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

Answer 13

Regarding vPPC, to save you some searching, see: http://hitechcoach.com/index.php/microsoft-office/access/access-downloads/access-examples/example-security/vppc-toolkit-for-2007

---

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

Thanks, Daniel. I wish we could resurrect the old discussion thread which led to the creation of the demo.

Cheers!

Answer 14

It might be going a bit far to suggest that Access is altogether the "wrong tool" if one is serious about security.  It is certainly the wrong database to use as a back-end if security is an issue and perhaps that is what you meant.  However an Access front end paired with a SQL server database can be quite secure.  

It appears that the OP has already built a security infrastructure of sorts with users and roles into the application in question, mimicking to some degree facilities that are already present in SQL server.  It shouldn't be a massive undertaking to create those roles and users in SQL although rewriting the code on the Access side to take advantage of it might be a bit of a chore.

-Bruce

Answer 15

Not much luck, the original threads seem to be gone for some reason.  I did uncover:

http://www.utteraccess.com/forum/vPPC-Security-Toolkit-VS-t1540366.html

and

http://www.utteraccess.com/forum/lofiversion/index.php/t1976268.html

---

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

Answer 16

The link above from DBGuy is very insightful! With all the information everyone has provided in this thread, I think any developer can implement a solution to securing their data if they are willing to put in the effort.

I am going to use some of the Access approaches suggested in this thread and the links within. For the short term due to time restrictions. Then I am going to integrate SQL Server security from the database side into that soon after(I hope) just to be sure.

Thanks so much! You guys are great.

Answer 17

Hi. We're glad we could assist. Good luck with your project.