My approach was to create a UDF (User Defined Object) with properties: FName, LName, Level, EmplNo, IsActive, LoginID (from their Windows network login) and whatever else you can think of as appropriate. Even a custom method can be associated with such an object (.RefreshUserData). Any of these attributes can be called upon from anywhere at any time. For example, if you want to know the empl no of who modified a record, you write dbUser.EmplNo to that table record. You'd be amazed at how much you can get from this object without having to do DLookups or execute sql statements. As for which forms to allow a user to access, a switchboard form can hide access to anything by setting the visible property of controls that access them based on dbUser.Level upon opening the switchboard.
There is a limit of what you can do to secure an Access db, subject to the level of determination and skill possessed by anyone who seeks to thwart it. IMHO, the minimum (not necessarily in this order) is to:
- split the db into BE and FE
- make the fe an accde or mde as the case warrants (what some call an 'executable' but it's not really) with the knowledge of what protection that offers
- disable the shift bypass key but have an Easter egg (the software type!) on an About Form for enabling or re-disabling it.
- use a password when linking the be tables - good idea to share this with somebody (like IT person or management) although it is discoverable if they are ODBC; not sure about when they're not.
-validate that the fe is accessing only the authorized tables in case someone grabs a copy of both and puts them in their personal folder. While this might seem like a data integrity thing, what happens if they think they have their own copy of the tables and from the fe, start deleting data from those tables? Guess where they're linked to. Of course, this would mean they defeated the shift bypass or you forgot to disable it after distributing a revised production version. For this reason, invoking the Easter egg colored a label background red when the bypass was enabled.
- have user data tables and use that information to limit access to table members
- hide sensitive macros and tables (can be seen in design view in accde) and queries, which can be seen in sql view.
That's it for now.
Epilogue
If you're reading this and are a moderator, if forum doesn't have a treatise on what can be done to secure an Access db, there ought to be one. One or more forum mods should have sole rights to amend suggestions in order to preserve its content and limit it to one post. If there is one, wish I had thought of it before now...
Last edited by Micron; 10-26-2016 at 03:51 PM.
Reason: grammar
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.