Access tables

[Bob]

We use a large MS SQL database that stores observations from the field.
Teams go out and input their observations through a web front end. However
if they go somewhere that doesn't have web access they use the same database
but in a standalone MS Access to collect the information. The data is
entered into Access through the same web front end as the main DB by running
IIS on the notebook. We then "pull" it into the main DB when they return.

In Access the tables that store the passwords and userids are easy to look
into and gain the information. If we were to loose a notebook in the filed
that person would have access to both the Access and the Web enabled SQL
database to include DBA rights.

How can I protect those tables from being compromised? Is their away to
protect those user tables from direct view and allow the application to still
use them?

--

 

[Scott McDaniel]

Are your users and passwords stored in an Access table on the frontend (the
one on the laptop)? Are these the user/pass combos used to log into the SQL
Server? If so then you've got a pretty huge security hole, as you have
noticed.

Do field users store data in an Access db on the laptop when they can't
connect to the Internet? Of do the laptops have SQL Server or MSDE
installed?

 

[Bob]

Yes, the passwords are stored on the laptop and these are the same as the SQL
Server.

Yes, the data is collected in Access when they don't have internet access.
We run the web application portion by installing IIS on the notebook along
with the application.

I'm writing a policy now to keep the two types of passwords different, but I
don't trust them to live up to that. These notebooks go places like Iraq and
Afghanistan unfortunately.

 

[Scott McDaniel]

Your best bet would be to implement Access's builtin User Level Security for
the frontend and backend. Access uses Workgroup files (.mdw files) to store
the Users and Groups; the actual permissions are stored in each database
(since one workgroup can secure more than one Access database). Access ULS
is tricky, and can take a bit of time to fully understand and correctly
implement.

The "bible" is the whitepaper here:
http://support.microsoft.com/Defaul...crosoft.com/support/access/content/secfaq.asp

There are other resources - this newsgroup, for one, and a few other sites:

Joan Wild's site: http://www.jmwild.com/Accesssecurity.htm (includes links
to Lynn Trapp's site, and other security-related items)
Gerry Robinson's Book: www.vb123.com/map

Make sure to "practice" security on a copy of your database. Look into Run
With Owner Permission queries (the FAQ goes into some detail regarding this)
if you want to completely remove permissions from your tables.