advice for protecting Access frontend

Question

I have a fairly large Access database that I am bringing into the 21st century. The backend has been converted to SQL Server instead of MDB, and I am considering how to proceed with the frontend, which is also in MDB. I am quite comfortable with user-level security, and use it extensively to manage permissions on the data and on the frontend elements. This is my first development with ACE/Accdb, and since it does not support user-level security, what is the best method for protecting forms, queries, reports, etc? Is there anything in addition to a compiled frontend?

Answer 1

In my experience MS Access "security" is a mirage. Much better to use Windows security and use several different compiled front ends each one protected.

@June7 is right this is not a question suited to SO.

Answer 2

Well, user level security (ULS) as as general rule is used to figure out what user can open what form or what report. However, ULS not really for the purpose of "locking down" the application. They are two very different goals. Now to be fair, ULS can help in locking things down - but it more intended to control what users can open or use what parts of the application.

And in most cases, ULS will not say lock out use of the Access UI bits and parts. So, your application will "work" or "seek" to keep the built in Access parts hidden. This is in fact where a compiled accDB can help enormously. (since the compiled application has the source code stripped out, and ALSO disables the ability to open forms/reports and code in design mode.

The other big advantage of a accDE? Well, any un-handled code error does NOT re-set local or global variables. (so you get quite a nice increase in reliability). The other advantage of the compiled accDE is of course is using the free runtime. That means that workstations and users don't need a paid copy of office that includes Access.

So the runtime + a compiled accDE has quite a few benefits. (and hiding and locking down the Access UI is one such benefit). In fact, running as runtime means that built in ribbons and menus can't be used. And as noted, no source code or no design abilities against code or form's/reports keeps things locked down.

So the above quite much means that ULS is "more" intended for controlling who can use what parts of the application - not really that of locking down parts from users in terms of changing or modifying the application (as I stated - two different goals).

So without any ULS security, you can well lock down the application part as tight as a drum.

However, while loss of ULS in terms of locking down changes and prying eyes to the application is no big deal? it is MOST certainly a big loss in a control system for who can do what in the application. And since you have no ULS, then you can't use ULS for both control of who can do what, and also that of preventing users from doing things like design mode etc. So, this now quite much suggests that you need to write code to prevent some users from opening forms - and you have to write code for that.

I would certainly follow the classic model of users, and those users then belonging to a given group. As you know, if you did this correct in Access, then you could take a copy of the FE (front end application part) off site. They could on site change what user belongs to what security group. As long as they NEVER assigned a user to a given object (form/report etc.), then you could update + deploy a new version of your software and all of the security stuff or even adding some users to the "sales group" would continue to work - even if you updated and deployed a new FE. But, the instant you assign users to anything OTHER then a security group - then the whole process breaks down.

Having said above? You have to roll your own. You can likely create a SINGLE function that returns the security information. You can then use the forms on-open (not on-load). And if you set cancel = true, then the form in question will not load and not display. So, you could well retro-fit a security model based on this single function in the form (or report) on-open event. And in most cases little to none existing code will be in that event anyway (on-open event does not allow modifying of bound controls on the form - you have to wait and place such code in the on-load event.

So, you have to roll your own. And since you in VBA code can't really "prevent" a user from opening a form in design mode? Then you quite much have to adopt a compiled accDE if you want at least "some" reasonable level of security.