April 24, 2015 at 10:54 am
First, I'm not a MS Access developer. However I've got a new job and have to do some MS Access development. I'll be working with another developer who has experience at this; at least more than I have.
One thing he showed me is that users will get into this MS Access application, which goes into the forms, do whatever it is they do there and then bang out of the forms application to get direct access to the tables of the database. (The Access application is a front end to a SQL Server 2005 database.) Since the end users have direct access to the SQL tables through MS Access, well you can just imagine what sort of mischief they can get into. (The Access application was written by a contractor who left before finishing the application.)
So my question is this: how can we prevent end users from getting out of the Access application to directly interact with the SQL tables? I would think this is possible, but like I said I'm not an Office developer so I've no idea how it would be done, nor even what sort of things I'd look for.
The Access application is written in MS Access 2007.
Kindest Regards, Rod Connect with me on LinkedIn.
April 24, 2015 at 10:58 am
If the users have access to the tables, you can't prevent this. You could make it hard, perhaps with some trigger that looks for Access directly manipulating things by using APP_NAME, but not sure that your Access application changes this.
The best solution is to use stored procs in the Access app to work with data, but that might not be practical. Perhaps having regular backups and a good administrative policy (like firing people) might work best.
April 27, 2015 at 3:52 pm
You can also prevent folks from "jumping out" of the app.
There are a few tricks which could make it tough for someone to get to the linked tables, some documented, some not, to include:
- not showing the DB window so they can't get to the tables,
- creating "pass through" queries as the linked tables, which are by nature read-only. Updates would be done via code calling stored procedures using a service account.
- "hiding" the tables in a reference DB acting as a "forms library", which can be linked in via a startup module. The forms library can be compiled into an MDE file, so any user "breaking in would see NO "offically developed" objects other than a module which has been compiled and not readable.
1) and 3) are least invasive, but really are just designed to slow a user down (they don't really "fix" the problem, since the user still has direct access to the tables). If you were to implement access security you oculd actually make it fairly painful to "break in", but still as Steve mentioned - you would still need to have those "admin sanctions" in place (i.e. not only is it painful to break in to the app, but if I am caught doing it - I will get counselled and/or fired), as well as removing direct user privs and setting up some form of a service account.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply