How can I lock down my MS-SQL DB from my users and yet still access it through ODBC?

Question

I've got an ms-access application that's accessing and ms-sql db through an ODBC connection. I'm trying to force my users to update the data only through the application portion, but I don't care if they read the data directly or through their own custom ms-access db (they use it for creating ad hoc reports).

What I'm looking for is a way to make the data only editable if they are using the compiled .mde file I distribute to them. I know I can make the data read only for the general population, and editable for select users.

Is there a way I can get ms-sql to make the data editable only if they are accessing it through the my canned mde?

Thought, is there a way to get ms-access to log into the database as a different user (or change the login once connected)?

Answer 1

There is a way to do this that is effective with internal users, but can be hacked. You create two IDs for each user. One is a reporting ID that has read-only access. This is they ID that the user knows about: Fred / mypassword

The second is an ID that can do updates. That id is Fred_app / mypassword_mangled. They log on to your app with Fred. When your application accesses data, it uses the application id.

This can be sniffed, but for many applications it is sufficient.

Answer 2

Does you app allow for linked table updates or does it go through forms? Sounds like your idea of using a centralized user with distinct roles is the way to go. Yes, you could change users but I that may introduce more coding and once you start adding more and more code other solutions (stored procedures, etc) may sound more inviting.

Answer 3

@Jake,
Yes, it's using forms. What I'm looking to do is just have it switch users once when I have my launchpad/mainmenu form pop up.

@Peter,
That is indeed the direction I'm headed. What I haven't determined was how to go about switching to that second ID. I'm not so worried about the password being sniffed, the users are all internal, and on an internal LAN. If they can sniff that password, they can certainly sniff the one for my privileged ID.

@no one in general,
Right now its security by obscurity. I've given the uses a special .mdb for doing reporting that will let them read data, but not update it. They don't know about relinking to the tables through the ODBC connection. A slightly more ms-access/DB literate user could by pass what I've done in seconds - and there a few who imagine themselves to be DBA, so they will figure it out eventually.