Securing an Access Database or ADP
Posted on 2009-04-30
In summary, I am trying to create a MS Access multi-user front-end app that I can continue to develop, possess the capacity to remotely disable outdated applications, and assign user rights that only allow the execution/opening of forms/reports, and NEVER allow direct viewing/designing of tables or queries, all while hosting the back-end data tables remotely.
I am reliant on MS Access 2007 for developing a front-end application that stores and organizes multiple clients' data across a number of tables (customers, insurance policies, invoices, etc). Each front-end application is assigned a unique ID through a macro. I have created a decent, albeit easily crackable system of macros that automatically check with the main back-end server to determine if that front-end application is active, or if it has been deactivated, in which case the Access database quits. Of course, this is circumvented by holding down SHIFT when opening the access file.
If a user holds down shift when opening the Access file, they can view *everything* in the linked source tables, a very bad thing given that the tables hold data for a number of clients. One could also modify queries that are designed on a per-application basis to filter only the data that pertains to them. I don't want that happening.
I am heavily dependent on Access because of the GUI and relative straightforwardness in comparison with other "databases" that are more script and language-intensive, so I would like to keep Access so as to continue developing a working model. But I need to secure the data and assign user rights easily with regard to the linked tables. For example, a standard user should *never* be able to directly open or execute ANY table or query. Only forms/reports within the front-end Access application should display the data and read/write/edit the respective tables.
I wish I could keep everything in Access. But if a back-end SQL Server were *required,* I have a godaddy.com hosting account with MySql and SQL Server support, and it also supports Direct Database Connections and DSN, but I am clueless as to how to set this up with a front-end Access database. I can't even seem to get Access 2007 to point to a valid SQL Server.
Thanks in advance for the help.