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

20 Oct 2005 11:33 PM

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

21 Oct 2005 1:10 AM

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

21 Oct 2005 3:05 PM

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

Show quoteHide quote

"TC" wrote:

> 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
>
>

21 Oct 2005 3:49 PM

Joan Wild

Sue wrote:

> 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).

--
Joan Wild
Microsoft Access MVP

21 Oct 2005 4:16 PM

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

Show quoteHide quote

"Joan Wild" wrote:

> Sue wrote:
> > 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).
>
>
> --
> Joan Wild
> Microsoft Access MVP
>
>
>

21 Oct 2005 5:25 PM

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.

--
Joan Wild
Microsoft Access MVP

Sue wrote:

Show quoteHide quote

> 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" wrote:
>
>> Sue wrote:
>>> 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).
>>
>>
>> --
>> Joan Wild
>> Microsoft Access MVP