Relink tables in a secured DB - create table permission required?

[Sue]

I have a secured database that is used by multiple users. Because I don't
want users to clutter up the database with their own tables and queries, I
used the sample code from the Access Security FAQ to turn off permissions to
create tables and queries.

Now, here's my problem. My database uses linked tables, and I need to give
users the ability to relink these tables. The linked tables have the
following permissions in my front-end DB: Read Design, Modify Design, Read
Data, Update Data, Insert Data, and Delete Data. I added some relinking code
that updates the connection property of each linked tabledef, and then
refreshes the link. This code works perfectly when I'm logged in as the
administrator. Unfortunately, when I open the application as a "regular"
user and attempt to run the relink procedure, I get a permissions error
("Could not create; no modify design permission for table or query '|'"). If
I turn the ability to create tables and queries back on, the relinking code
will run without error, so it appears that create permissions are required to
relink a linked table. This strikes me as odd, since, technically, I'm not
creating a table - I'm modifying the design of an existing table.

Am I missing something with my security? Or is it just not possible to
allow users to relink linked tables, and prevent them from creating tables.

Thanks!

Sue

 

[TC]

I'm fairly sure (but have not checked) that the exact permissions
required in order to relink tables, are stated in the Access Security
FAQ - often referenced in this newsgroup. Have you checked there?

HTH,
TC

 

[Sue]

Yup, I did, but after going back and rereading it again, I discovered that I
had misinterpreted the "No Permissions necessary" section in the
documentation on permissions for updating table links. It turns out that
permission to create tables is required to relink tables. This sure doesn't
make sense to me, since one of the main reasons to secure a database is to
prevent users from changing or creating objects. Oh well, I guess I'll have
to cross my fingers and hope that the users don't clutter up my database too
much...

Thanks,

Sue

 

[Joan Wild]

Oh well, I guess I'll have to cross my fingers and hope
that the users don't clutter up my database too much...

Why do your users have access to the database window? You can do a lot to
lock it down...

Create custom menus/toolbars for use throughout your application.
Create a startup form (a main menu form if you have one) that is opened on
startup.
Use the features in Tools, Startup to
set the startup form
set your default menu (the custom one you made)
disable all the checkboxes about allowing built in menus, toolbars,
changes etc.
hide the db window (ensure the custom menu you create does not
include the Windows, Unhide item)
Click on the Advanced button and uncheck the allow special keys
(this will disable the F11 key, among others)

If you need to bypass these startup features, you can hold the shift key
down while you open the db. If you feel that your users may use this to
bypass your settings, you can disable the shift key bypass - there's an
example in help for doing this(look for AllowBypassKey) or at
http://www.mvps.org/access/modules/mdl0011.htm
and
http://www.mvps.org/access/general/gen0040.htm

You can also create a MDE from your database, which will prevent changes to
forms, reports and modules (If you do this, be certain to keep your original
mdb in case you need to make changes).

 

[Sue]

Well, the database is essentially an ad hoc reporting application. It
contains several predefined report queries, but the users also need to be
able to do their own "quick and dirty" querying. Basically, the requirement
was to allow users to create their own queries, but not to save them (i.e.,
one-shot queries). Yeah, I know it sounds odd, but we do have a need for
this.

Thanks for the suggestions, though.

Sue

 

[Joan Wild]

For that kind of adhoq querying, give them a separate frontend, so that they
don't mess up anything in the production frontend.