Prevent table linking

[Ian]

I have a simple database application all the objects are
in a single .mdb file.

Is there any way to prevent people linking to the tables
in there? As far as I know, anyone who can open the
database can also link tables.

 

[Jeff Conrad]

I have a simple database application all the objects are
in a single .mdb file.

Is there any way to prevent people linking to the tables in there?
Yes.

As far as I know, anyone who can open the database can also link tables.

Correct, given your current setup.

The ONLY way to prevent people from directly linking to your data tables is to implement full-blown
Access User Level Security. This is a very daunting task to say the least when you do this for the
first time! When done properly you can set up RWOP queries for all access to the data tables and
deny all permissions to the tables themselves. Only then will this prevent direct linking from
another database file.

Setting up User Level Security *properly* will take a LOT of time depending upon the size and
complexity of your database. This is not something you will be able to set up in just a few minutes
if this is your first time. It is best to experiment with User Level Security on backup copies of
your database files in case you mess something up. To study up on this subject I suggest the
following reading material:

Access User-Level Security:

Security FAQ (the Security Bible):
http://support.microsoft.com/?kbid=207793

Jack Macdonald's Security Document:
http://www.geocities.com/jacksonmacd/AccessSecurity.html

Lynn Trapp's Ten Security Steps:
http://www.ltcomputerdesigns.com/Security.htm

Joan Wild's Tips:
http://www.jmwild.com/security02.htm

Good luck,

 

[Ian]

Yes i'm not a beginner.I've been programming in Access
since Access 2 came out. I believe that I have fully
secured the database.

For simplicity, I have removed the Admin password and
given the Admin user basic permissions to run the
database. But no matter how I set things up. Anyone can
create a new database with their User ID and link into my
database.

This idea of using RWOP queries sounds interesting.
However, this will require a lot of rewriting because I
use a lot of DAO to manipulate the data retrieved from the
tables.

 

[Lynn Trapp]

One thing you might consider doing is placing your database in a hidden
folder. It's highly unlikely that they will ever be able to locate it and
create a link.

By all means, explore the option of using RWOP queries. In most cases your
code probably won't have to be changed if you do that.

 

[Jeff Conrad]

Yes I'm not a beginner.I've been programming in Access
since Access 2 came out. I believe that I have fully
secured the database.

No offense was intended.
It was not completely clear if you were using just a database password or if you had secured the
database with a MDW file. I wanted to make sure you had all the necessary information. Apologies for
any misunderstanding.

For simplicity, I have removed the Admin password and
given the Admin user basic permissions to run the
database. But no matter how I set things up. Anyone can
create a new database with their User ID and link into my
database.

This is true, to a point.
In addition to Lynn's suggestion, I'll offer another alternative.
You can use code that will deny all groups and users the ability to create new databases. This will
prevent a user from joining the secured MDW file, creating a new database, and then linking to the
secured database file. I believe the security FAQ has code that shows how do this. I've used the
code in the ADH book for this.

In order to really make this effective you will need to lock down the database tight with custom
menu bars/toolbars, disabling the shift key bypass with the DDL argument, etc.

This idea of using RWOP queries sounds interesting.
However, this will require a lot of rewriting because I
use a lot of DAO to manipulate the data retrieved from the tables.

The RWOP queries are just one of the many steps needed to lock a database file down REAL tight. Yes,
it will take lots of work, but it does sound like you may need to head in this direction.

A comment if I may (again, please no disrespect intended!)
It would seem you have users that are pretty Access-savvy AND like to go places they shouldn't be!
This almost sounds like we're getting into a personnel issue here. Perhaps a written policy should
be drawn up with disciplinary actions in place. Just a thought.

 

[Ian]

It would seem you have users that are pretty Access-savvy

AND like to go places they shouldn't be!
This almost sounds like we're getting into a personnel
issue here. Perhaps a written policy should
be drawn up with disciplinary actions in place. Just a
thought.

No actually i've been asked to produce a database of multi
choice aeronautical exam questions to be distributed
nationally within the UK. The idea is that an Access
Report will pluck questions randomly from the database and
print an exam paper. Currently there are just four exam
papers. So it doesn't take the candidates much homework to
learn what the questions are.

The reason I want to lock it down is because I have no
idea as to the capabilities of the people it will be
distibuted to. Presumably, some of them may turn out to be
Access developers!

I will investigate your suggestion to prevent users
creating new databases. I think I might also look at
implementing my own regime for encrypting the table data.

 

[Jeff Conrad]

Hi Ian,

Well based on the additional information you have provided I definitely recommend pulling out all
the stops and locking this thing down tight! This will take time and most likely require some coding
changes but you at least should do the following:

1. Set up RWOP queries and deny all permissions to the tables themselves.
2. Create custom menu bars and toolbars with only the things you need.
3. Disable the shift key bypass using the *correct* code found here:

http://www.mvps.org/access/general/gen0040.htm

4. Use code to deny all other groups and users the ability to create new databases.
5. Use one MDW file for development purposes, but use another for distribution purposes. This will
further safeguard intruders. Details are in the security FAQ.
6. Make an MDE file out of your MDB and keep several back-up copies.
7. Encrypting is an option, but it will slow things down a bit.

Good luck with your project,