Multiple Databases - One Workgroup

[Guest]

The questions:
1. Is it possible to create one (1) workgroup that is used by more that one
database with each database have different permissions for different grouping
of users?
(The shortcut method, while an option will get to be a big hassle to
maintain as more and more databases are added. I would prefer not to go
there if possible)

2. If it is possible, how can it be done if there are existing databases,
specifically, two with security (using their own mdw files), and one without
security (using the system mdw file)?

The full story:

The company I work for has a client that has created (or had created by a
third party) three Access (2003) databases:
One is unsecured, is used (and abused) buy most of the staff (25), and
uses the system.mdw
The second is secured, is used by just a few of the staff (6), and uses
its own workgroup file.
The third is secured, is used by just a few of the staff (6), and uses its
own workgroup file. It has not deployed…yet.

The third one I was asked to secure as I'm the only employee who admits to
having used Access - that was 4 years ago (2000 was the last version).
After several days of researching security and experimenting with setting up
security on a copy of the client database at my office, I was ready to go to
the client and setup the security.

All went well. Well, not quite. Ended up with dueling workgroups.
Retreated to the office for further research and experimenting.

I know the shortcut with the workgroup method will work, but as more
databases are added, with more and more users, that would end up being a
maintenance nightmare at some point. As I'm going to be the guy doing the
maintenance, I'm looking for alternatives.

My research seems to indicate it is possible to create one workgroup used by
all the databases, current and new. Unfortunately, have not found a clear
description of how to do that, and absolutely nothing on how to do that when
there are already existing databases.

 

[Joan Wild]

The questions:
1. Is it possible to create one (1) workgroup that is used by more
that one database with each database have different permissions for
different grouping of users?
Yes

2. If it is possible, how can it be done if there are existing
databases, specifically, two with security (using their own mdw
files), and one without security (using the system mdw file)?

For the two secured mdb files. Log in using a username with sufficient
permissions. Unsecure those databases by
-give Users group full permissions on all objects
-rejoin system.mdw
-open Access and create a new mdb and import objects from the 'secure' mdb
- you now have unsecured mdb files.

Now join your one mdw that you want to secure all databases with. Login as
the user you want to own all objects. Open each database and proceed to
secure it.

You might find it handy to script out the permissions before you unsecure,
so it is easy to reapply them when you re-secure. See
Dbutilities 1.0 at http://www.daiglenet.com/MSAccess.htm is the tool to
use.

Remember that users, groups, memberships, passwords are stored in the mdw.
Permissions on objects are stored with the object in the mdb.

You'll want to plan carefully, to ensure that you have the groups set up to
accomodate your needs for all databases.

 

[Guest]

After importing the objects and securing my first DB, I assume I will need to
close and reopen Access Before securing the next DB, correct?

Will I have to create a new DB and import the objects for the second DB to
secure it?

When I need to secure a new database (next week/month), will all I need to
do is just join to the "Secure" mdw file, then setup the user and group
security?

What about adding/deleting users for specific DBs, or all databases.
Anything special when doing that?


Thanks for your help, I really appreciate it.

Have been consulting with this company since April and they would like me to
join them as a real employee. Solving their client's problems will certainly
help as we negotiate.

Jim

Always the Crystal Guy, sometimes the the SQL Guy and now (reluctantly), the
Access Guy

 

[Joan Wild]

After importing the objects and securing my first DB, I assume I will
need to close and reopen Access Before securing the next DB, correct?

No. The workgroup is in effect for the session of Access - it's not tied to
the mdb, but to the Access session, so you don't need to close and reopen.

Will I have to create a new DB and import the objects for the second
DB to secure it?

Yes repeat the same steps for each mdb.

When I need to secure a new database (next week/month), will all I
need to do is just join to the "Secure" mdw file, then setup the user
and group security?

Yes, but what you're really doing is applying permissions to the groups in
your secure mdw - not necessarily setting up new users/groups.

What about adding/deleting users for specific DBs, or all databases.
Anything special when doing that?

No. Users are stored in the mdw, not the mdb. That is why you need to
carefully consider the groups that you need (regardless of the mdb).

 

[Guest]

Thank you so much for your help.

Now to see if I can do it for my test databases.

Jim

 

[Guest]

Am not having any success when joining my preciously secured db, but now
unsecured, to (what I hoped to be) the company-wide security group.

Here is what I did: (not on the first try though – stumbled several times
trying to get it all sync'd up)

1. Created a database "SecurityDB", that was connected to the system.mdw.
2. In the "SecurityDB" database, created the company-wide securitygroup mdw
file. Saved as "CompanySecurityGroup.mdw" in network folder.
3. Created a password for the admin user, added a new user – NewAdmin –
added new user to the Admins group, and removed the admin user from the
Admins group. Closed DB.
4. Logged on as the NewAdmin and created a password. Closed DB.
5. Logged on as NewAdmin using new password.
6. Using the Wizard, selected all groups assigning IDs to each group. No
permissions for user-group. Added one new user – Newuser1 with password and
PID (not default) – and placed in admins group. Verified NewAdmin was also
in Admins group. Closed DB
7. Removed Security from database – "OlderDB" - that had its own mdw file by:
a. Logging on as OlderDBAdmin
b. Gave the users group full permissions on all objects
c. Gave the Admin user full permissions
d. Put admin user back in admins group
e. Removed admin password
f. Rejoined system.mdw
g. Closed database
8. Next, I created a new database "ImportOlderDB", connected to the
system.mdw and imported all of the objects from the "OlderDB". Closed DB
9. Opened "ImportOlderDB"
10. Joined the "CompanySecurityGroup.mdw". Closed DB
11. Logged on as NewAdmin (using password) created in step 3.
12. Now the issues start
a. The "ImportOlderDB" Owner is the Admin user and cannot be changed.
b. Cannot give NewAdmin or NewUser1 any database permissions. Other
objects, yes, but not the database.
c. Cannot give the Admins group or any other group any permissions on the
database.

Am extremely frustrated at this point.

Is there anyone who can help me create one workgroup for all of the
company's Access databases, new ones, and databases that have already been
created with their own security workgroup?

 

[Joan Wild]

Here is what I did: (not on the first try though - stumbled several
times trying to get it all sync'd up)

1. Created a database "SecurityDB", that was connected to the
system.mdw.

Databases are NOT connected to a mdw. I hope what you mean is that you
created a database SecurityDB.mdb while using the standard system.mdw (with
no login). Actually I don't see why you need to create this mdb at all.

2. In the "SecurityDB" database, created the company-wide
securitygroup mdw file. Saved as "CompanySecurityGroup.mdw" in
network folder.

Again, it doesn't matter what database you have open (in fact you don't need
one open at all). I assume you used the workgroup administrator to create a
mdw named CompanySecurityGroup.mdw.

3. Created a password for the admin user, added a new user - NewAdmin
- added new user to the Admins group, and removed the admin user from
the Admins group. Closed DB.

Nope. You were still using system.mdw. You should have joined your new mdw
file; closed Access and reopened it. To be certain you are using the
correct mdw, hit Ctrl-G and type
?dbengine.systemdb
It should return the full path to CompanySecurityGroup.mdw
Then create a password for the Admin user, create a new user - NewAdmin and
put them in the Admins Group, and remove Admin from Admins Group.

4. Logged on as the NewAdmin and created a password. Closed DB.

You don't need any DB open to do this.

5. Logged on as NewAdmin using new password.

And opened your unsecured mdb, right?

6. Using the Wizard, selected all groups assigning IDs to each group.
No permissions for user-group. Added one new user - Newuser1 with
password and PID (not default) - and placed in admins group.
Verified NewAdmin was also in Admins group. Closed DB
7. Removed Security from database - "OlderDB" - that had its own mdw
file by: a. Logging on as OlderDBAdmin

Well how did you logon? Perhaps at this point your are still joined to
CompanySecurityGroup.mdw. You'd need to use a desktop shortcut to open
OlderDB....
"path to msaccess.exe" "path to OlderDB" /wrkgrp "path to secure.mdw"
secure.mdw is the workgroup that OlderDB was secured with!

b. Gave the users group full permissions on all objects
c. Gave the Admin user full permissions

Not necessary

d. Put admin user back in admins group

Not necessary

e. Removed admin password

Not necessary

f. Rejoined system.mdw
g. Closed database

And Access. Then start Access, and...

8. Next, I created a new database "ImportOlderDB", connected to the
system.mdw and imported all of the objects from the "OlderDB".

10. Joined the "CompanySecurityGroup.mdw". Closed DB

And Access. Then start Access and...

11. Logged on as NewAdmin (using password) created in step 3.
12. Now the issues start
a. The "ImportOlderDB" Owner is the Admin user and cannot be changed.

Correct. It is now in a unsecured state. At this point you are using
CompanySecurityGroup.mdw. You can run the wizard with this mdb open and go
through the steps of securing it (choose to modify existing mdw).

I think you should do things in the following order.

Unsecure all your secure mdb files. You log in to each one using the
appropriate mdw file (you don't have to 'join' the workgroup, just use a
desktop shortcut as shown above) and then...

Grant all permissions on all objects (don't forget the database object) to
the Users Group.
Close them.
Open Access, and verify you are joined to the standard system.mdw workgroup
(no login) by hitting Ctrl-G and typing
?dbengine.systemdb
If it doesn't return the path to system.mdw, then use the workgroup
administrator to join system.mdw (close Access and reopen it and verify).

For each of your 'secure' mdb files, create a new database and import all
objects from them.

At this point you have a bunch of unsecured mdb files - as though they'd
never been secured before!

Open Access, and use the workgroup administrator to create a new workgroup
CompanySecurityGroup.mdw.

Now you can create a desktop shortcut with the following as the target....
"path to msaccess.exe" /wrkgrp "path to CompanySecurityGroup.mdw"

Double click the target and go to Tools, Security, Accounts and create a
password for the Admin user, create a new user - NewAdmin and put them in
the Admins Group, and remove Admin from Admins Group.

Close Access. Double-click your shortcut and login as NewAdmin. Set a
password for this user, if one doesn't exist yet.

Now open each of your unsecured mdb files and run the security wizard
(choosing to modify the existing workgroup; and create desktop shortcuts).