How to use an existing workgroup file with a new FE/DB database

[Debbie]

Hello everyone,
In as much as I've read about security, I can't seem to find a scenario that
addresses the same one we have. So any ideas are greatly appreciated.

I have created a new front and and backend database. We want to utilize a
workgroup file that has been created for another Access system. I was
successful in getting the front end database to use the security and
permissions that currently exist, however, I did join the same workgroup file
in the BE database but I don't seem to be getting a list of the current user
groups that are a part of the existing workgroup file. I did notice that the
owner of the BE database is a user with full admin right but there is no
owner listed for the BE database. My questions are this:
1. Do I join the existing workgroup file to both the FE and BE database?
2. How do I set up the permissions on the BE?
3. In many cases, I use recordsets that use a CONNECT statement in ADO.
Would I just need to add the userid and password of the BE database?
4. Why doesn't the user get prompted for a user name and password when you
start the database from a file listing rather than a shortcut?

If someone could give me an overview of the steps, that would be great. I
know Access is not forgiving in security and I'm too worried about making
changes without knowing exactly what I'm doing. Thanks so much.
Debbie

 

[TC]

Debbie wrote:

1. Do I join the existing workgroup file to both the FE and BE database?

I think you might be misunderstanding what happens when you join a
workgroup file.

Every time that Access starts - before it even opens a database - it
selects which workgroup file to use for that run. If you start Access
by using a shortcut that uses the /wrkgrp switch in the target
shortcut, Access will use whatever workgroup file is specified by that
switch. In *all other cases*, Access will use the default workgroup
file for that PC. So we need to know (a) what are "all other cases",
and (b) what is the default workgroup file for a PC?

(a) "All other cases", in this context, means: starting Access from a
shortcut *without* the /wrkgrp switch; or, starting it directly
(without using a shortcut at all); or, double-clicking an MDB file
directly (which has the affect of starting Access first, and then,
opening that database).

(b) The default workgroup file for a PC, is initially, the file that is
installed with Access (ie. system.mdw). When you use the workgroup
administrator program or function, you are simply changing which
workgroup file is considered to be the default file. But whether that
file is *used*, or not, for any particular database, is determined by
the other factors described above.

In summary, you do not join a workgroup file "to a database". It would
be better if you thought of "joinging" a workgroup file, as, "changing
the default workgroup file for the PC'. That is what it actually does.
But you can easily use a *different* workgroup file, for one or more
databases n that PC, by starting those databases by using shortcuts
with the /wrkgrp switch.

4. Why doesn't the user get prompted for a user name and password when you
start the database from a file listing rather than a shortcut?

The user will be prompted for a username/password, if any *only* if the
Admin user of the current workgroup file, has a password. When you
start the database "from a file listing", ie. by double-clicking it,
you are using the defaulkt workgroup file, as described previously. So
if you do not get the username/password prompt, this shows that the
default workgroup file's Admin user does not have a password. That's
the way it normally should be. If you *do* get the prompt when the
database is started from a shortcut, this means that the shortcut uses
the /wrkgrp switch to select some other workgroup file, & that other
workgroup file *does* have a password on its Admin user.

Although the Admin user is identical across all workgroup files in all
installations of Access, that user might *have* a password in certain
workgroup files, but *not* have a password in certain other workgroup
files!

It's all fairly complex until you get your head around it. Hopefully
the above will help.

Cheers,
TC

 

[Debbie]

TC,
Thanks so much for that great explanation. I think I see what you are
talking about. I need to figure out all the ways in which the system could
be started and then plan for each of those scenarios.

I am still not getting one thing (I hate this feeling of not getting it),
but, how do you handle the front and back end scenario? I mean, if I set up
the short cut to point the the new workgroup file, that shortcut will open
the front end database and redirect Access to our workgroup file and not the
default. However, what kind of security should I put on the backend? I'm
thinking that I need to do the following on the back end:
- add a password to admin and remove admin from the admin group
- create a user with admin rights and add it to the admins group
- create a regular user in the user groups with read/update/delete
permissions.

Then, in my front end in my CONNECT statement, I use the userid and password
of the regular user.

Am I on the right track? Thanks again, I really appreciate your expertise.
Debbie

 

[TC]

TC,
Thanks so much for that great explanation.

No probs, glad that it helped!

I think I see what you are
talking about. I need to figure out all the ways in which the system could
be started and then plan for each of those scenarios.

The normal way, is this. Have a /new/ workgroup file for securing the
database(s). Do not modify the default file (system.mdw). Do not use
the workgroup administrator program to join the new file. Leave the PC
joined to the standard (default) workgroup file. Provide a shortcut
using the /wrkgrp switch to open the database with the new workgroup
file.

Then, if people use the shortcut to open the database, it will open ok.
But if they use /any other method/ to open the database, it will fail
with a permissions error. (Here, "any other method" would include,
double-clicking the mdb file, or starting Access and then doing a File
: Open.) This all assumes, of course, that you have set up the
permissions & other things correctly in the various files.

I am still not getting one thing (I hate this feeling of not getting it),
but, how do you handle the front and back end scenario? I mean, if I set up
the short cut to point the the new workgroup file, that shortcut will open
the front end database and redirect Access to our workgroup file and not the
default. However, what kind of security should I put on the backend?

Normally you would secure both databases against the same workgroup
file. The first step in doing this is to create a suitable user (in the
new workgroup file) to be the owner of both databases & each object
within them. I suspect that you have not done that step correctly. What
you have written next is correct - but definitely not the whole story:

I'm thinking that I need to do the following on the back end:
- add a password to admin and remove admin from the admin group
- create a user with admin rights and add it to the admins group
- create a regular user in the user groups with read/update/delete
permissions.

You mentioned before, that the owner of one database was shown as
"unknown". That is a clear sign that you have missed some basic steps
or done them wrongly. This is not a criticism - it's friggin' hard to
do it properly!

The secret to doing it properly, is to follow a written list of
specific instructions: adding & omitting nothing. For example, the
Access Security FAQ, often referenced in this newsgroup:

http://support.microsoft.com/?id=207793

However, Joan Wild has some good stuff, I'm sure she won't mind me
giving her link:

http://www.jmwild.com/Accesssecurity.htm

I suggest that you start again. Rejoin the default system.mdw (if you
are not joined to it already). Remove the password (if any) from the
Admin user of that workgroup file. Create a new database, and import
all the objects (tables, queries, forms, reports, macros & modules)
from your backup copy of the unsplit database. Then use Joan's
instructions to secure the /unsplit/ database. Then use her
instructions to split the secured (unsplit) database into the FE & BE
parts.

Sorry it's so complicated! I'd give you all the steps myself, but I do
not keep them written down, & there's no way I will do them from
memory.

Good luck!
TC

 

[Debbie]

TC,
You are as bad as me late on a weekend! Thanks SO MUCH for all your help.
I will try the steps you recommended. I feel as though I've got good
information to go with. You are right - this is so friggin hard! I'm
determined to get it now. Thanks again for really taking time to explain.
Debbie

 

[TC]

No probs, best of luck!

TC

 

[Joan Wild]

However, Joan Wild has some good stuff, I'm sure she won't mind me
giving her link:

http://www.jmwild.com/Accesssecurity.htm

It wouldn't be on the internet, if I didn't want people to see it

 

[TC]

Perhaps you should offer an affiliate program!

TC