Data Security - ODBC to Access

[AnthonyGerrard]

Security Question.

I have 2010 Access Front end to SQL db.

How do I protect my data? The advice I have seen is to use Windows Authentification (surely SQL server would be better), and I use a ODBC.

All users pretty much need all read/write/ etc permissions on all tables or through views. What they can do is limited in the FE access Application

However the ODBC is sat there – and they could just link a new balnk FE and update / delete all data?

How do I stop that ? (PS I notice in many a place I work – that accunts systems like sage are frequently open to the same problem?)

Is there an idiots guide to basic data security in this set up?

Cheers

 

[mdlueck]

Big topic...

If you have a SQL BE DB that all of the data resides in, then I would suggest utilizing Stored Procedures (SP's) and not create ANY linked table objects to the BE DB. That way all BE DB access is controlled by VBA execution of SP's... if people go nosing around the FE DB, there are no linked table objects to open and start snooping around.

Client/Server Architecture
http://www.access-programmers.co.uk/forums/showpost.php?p=1110794&postcount=5

As for the ID to connect to the SQL BE DB in the first place... my application runs on a single account. The FE DB authenticates with that account. No one who uses the application knows the account to the SQL BE DB. I have a password scramble algorithm so that the password is not stored in readable text, and like I said I do not advertize where the ID/pw is stored on the client workstations.

I do not compile (hide the source code) in my application. So it would be possible to open the VBA editor and inject code in the Immediate Window. I am willing to operate with known risk on that point.

 

[AnthonyGerrard]

Thanks Michael - to change all my linked tables would be a big job, too big to be viable and remain in access at thi spoint I think.

Maybe a move to VB VB.net is the way forward for it.

Cheers