MDW Deployment Problem

[croy]

Using Access 2002, we've split and secured a database that
is used in two locations. Updates to the front-ends are
working fine.

The problem comes with both offices aquiring new users on
occasion. I've never been able to get things set up so that
the user at the other office can add new users, without
including the manager there in the admins group, which my
boss doesn't want. I've seen something in the past about
doing such a thing, but the amount of code involved sent me
running--I can't imagine my meager skills getting that much
code all dialed in to our db and running.

Is there another solution?

 

[Joan Wild]

You don't need to use code. You can use two separate mdw files - one for your use when you develop, and one for production use.

Since the production Admins Group isn't the same as the development Admins Group, you can safely add a key person to the Admins Group in the production mdw - they'll be able to add/delete users, but not have 'administer rights' in the mdb.

There is more detail in the security FAQ:
http://support.microsoft.com/?id=207793

 

[croy]

You don't need to use code. You can use two separate mdw files - one for your use when you develop, and one for production use.

Since the production Admins Group isn't the same as the development Admins Group, you can safely add a key person to the Admins Group in the production mdw - they'll be able to add/delete users, but not have 'administer rights' in the mdb.

There is more detail in the security FAQ:
http://support.microsoft.com/?id=207793

Thanks Joan. I *think* I'm getting on with this... ;-)

 

[croy]

You don't need to use code. You can use two separate mdw files - one for your use when you develop, and one for production use.

Since the production Admins Group isn't the same as the development Admins Group, you can safely add a key person to the Admins Group in the production mdw - they'll be able to add/delete users, but not have 'administer rights' in the mdb.

There is more detail in the security FAQ:
http://support.microsoft.com/?id=207793

One oddity I'm finding: I set up a shortcut for
development, and a one for production, each pointing to a
particular MDW file.

After working in the development MDW, I close Access
completely, and then open the MDB using the production
shortcut. I immediately go to the Workgroup Manager, and it
says that I'm using the development MDW! How can this be?

 

[croy]

One oddity I'm finding: I set up a shortcut for
development, and a one for production, each pointing to a
particular MDW file.

Well, I wasn't very concise there. Should be: "each
pointing to its own, unique (according to the instructions
in SecFAQ paragraph 33) MDW file.

After working in the development MDW, I close Access
completely, and then open the MDB using the production
shortcut. I immediately go to the Workgroup Manager, and it
says that I'm using the development MDW! How can this be?

Those statements and the question still stand.

 

[croy]

You don't need to use code. You can use two separate mdw files - one for your use when you develop, and one for production use.

Since the production Admins Group isn't the same as the development Admins Group, you can safely add a key person to the Admins Group in the production mdw - they'll be able to add/delete users, but not have 'administer rights' in the mdb.

There is more detail in the security FAQ:
http://support.microsoft.com/?id=207793

Thanks Joan.

Ok, I'm sure screwing something up. For reference, the FAQ
gives these instructions:

33. I want to create a remote site administrator able
to administer the database and add user accounts but
not alter permissions on database objects

You can set this up by using two different workgroup information files: one for development, and one for distribution. You secure your application using the development file, assigning permissions to custom groups (implicit permissions) so that all permissions are inherited through group membership. You use a second workgroup information file to distribute your application. By entering identical group names and PIDs in both workgroup information files, permissions to objects in the database are inherited from the developer workgroup information file for those specific groups. You can then create a site administrator account in the distribution workgroup file and add it to the Admins group. The site administrator account will be able to create users and move them in and out of groups, and will be able to create and delete users and groups, but will not be able to modify permissions to database objects or even to open objects unless the site administrator inherits those permissions

by being a member of other groups. The reason this works is that the remote site administrator is not present in the Admins group of the workgroup file used to secure the database; therefore, this account cannot administer permissions on objects in the database. Unlike the Users group, which is the same across all workgroup files, the Admins group is unique, its SID being encrypted from the strings used to create the workgroup file. The Admins group has Administer permissions that cannot be revoked, but only the Admins group can be used to secure the database. Therefore, the remote site administrator can manipulate user and group information (stored in the workgroup file), but not permissions on database objects (which are stored in the database, which "sees" only the correct Admins group as having the necessary permissions).

This technique works equally well in all versions of Microsoft Access. In Microsoft Access 2.0, work group databases have the extension, MDA; in Microsoft Access 95 and 97, the extension is MDW.

QUESTIONS: How about Access 2002 (this doc. seems to be
written prior to that version)? Does it matter if the
option is set to save in 2000 format?

Here are the steps to follow:

1. Using the Workgroup Administrator, create a workgroup database (DEVELOP.MDA/MDW), which will be the developer workgroup.

QUESTIONS: Do this to an *unsecured* database, or does it
matter? Do this while the database in question is open, or
does it matter?

2. Log on using DEVELOP.MDA/MDW and take all necessary steps to secure your database.

QUESTION: Including running the ULS Wizard, even if this db
has already been secured, sometime in the past?

3. When you create your own custom groups, make sure that you write down the exact names of these groups (case-sensitive) and the Personal ID (PIDs) you use to create them. You will need these strings later.

4. Assign the appropriate permissions to these groups, making sure that you don't grant Administer permissions to any of these groups to any of the objects in your database. Also, make sure that all permissions are removed from the Users group and Admin user.

5. Using the Workgroup Administrator, create another workgroup database (USER.MDA/MDW)- this is the one you will distribute with your application. Make sure you use different strings for the Name, Company Name, and Workgroup ID than the ones you used for DEVELOP.MDA.

QUESTION: Again, do this while the database in question is
open? Or does it matter?

6. In USER.MDA/MDW, create the exact same group names that exist in DEVELOP.MDA/MDW by using the identical case-sensitive names and PIDs you defined in DEVELOP.

7. Create a user account (SiteAdmin) and add it to the Admins group of USER.MDA/MDW. Remove the Admin user from the Admins group. This will make SiteAdmin the administrator for the USER.MDA/MDW workgroup.

QUESTION: "SiteAdmin" is not some sort of a special,
"must-use" name, is it?

8. Put a password on the Admin user, which will force the logon dialog box to appear.

9. Distribute USER.MDA/MDW with your application, not DEVELOP.MDA/MDW. The SiteAdmin account will be able to administer user and group accounts, but will not be able to alter permissions on your database. You should instruct the individual entrusted with the SiteAdmin ID and password not to delete any of the custom groups that you created or your application will "break" and users will not be able to access the objects in the database. If you have to revise permissions, you must log on using the DEVELOP.MDA/MDW as the secured Admins user to make the changes. Just make sure that the users or groups in the User.mda/mdw don't have design permissions on any database object or the database itself.

QUESTION: Is this all compatible with FAQ paragraphs 13 and
14, for the "production" SiteAdmin being able to use the
"Linked Table Manager"?

Appologies for asking so many questions, but like I say, I'm
sure doing something wrong!

 

[Joan Wild]

The workgroup manager tells you what your default is set to - that isn't necessarily the one currently in use. If you don't use a shortcut (that specifies a mdw to use), then it will use your default.

You can verify the mdw in use by hitting Ctrl-G and typing
?DBEngine.SystemDB

 

[Joan Wild]

QUESTIONS: How about Access 2002 (this doc. seems to be
written prior to that version)? Does it matter if the
option is set to save in 2000 format?

No the technique works in 2002 and 2000

QUESTIONS: Do this to an *unsecured* database, or does it
matter? Do this while the database in question is open, or
does it matter?

Well in your case, just use your existing secure mdw, since you've already secured it. Make it the development mdw.

QUESTION: Again, do this while the database in question is
open? Or does it matter?

It doesn't matter. These things you are doing at this point have nothing to do with a mdb file.

QUESTION: "SiteAdmin" is not some sort of a special,
"must-use" name, is it?
No.


QUESTION: Is this all compatible with FAQ paragraphs 13 and
14, for the "production" SiteAdmin being able to use the
"Linked Table Manager"?

It should be. You'll need to test relinking using your SiteAdmin (or whatever username). Just remember to assign permissions to groups, not users (while using your development mdw). The permissions are stored in the mdb file. As long as it finds the exact same group(s) in the production mdw, the permissions will be honoured.

 

[croy]

No the technique works in 2002 and 2000


Well in your case, just use your existing secure mdw, since you've already secured it. Make it the development mdw.


It doesn't matter. These things you are doing at this point have nothing to do with a mdb file.


It should be. You'll need to test relinking using your SiteAdmin (or whatever username). Just remember to assign permissions to groups, not users (while using your development mdw). The permissions are stored in the mdb file. As long as it finds the exact same group(s) in the production mdw, the permissions will be honoured.

Well, it took me the usual 78 turns around the fire-pit, but
I think I've actually got it working! The last five turns
were due to not *realizing* that it *was* working.

Time for my nap.

Many thanks, Joan.

 

[croy]

On Tue, 5 Feb 2008 12:22:48 -0500, "Joan Wild"

It should be. You'll need to test relinking using your SiteAdmin (or whatever username). Just remember to assign permissions to groups, not users (while using your development mdw). The permissions are stored in the mdb file. As long as it finds the exact same group(s) in the production mdw, the permissions will be honoured.

Another question about the FAQ:

9. [paragraph 33] <...> not to delete any of the custom groups that you created, or your application will "break" and users will not be able to access the objects in the database. <...>

How about unused groups? Would deleting an unused group
(from one, or both, MDWs) ruin anything?

 

[Joan Wild]

9. [paragraph 33] <...> not to delete any of the custom groups that you created, or your application will "break" and users will not be able to access the objects in the database. <...>

How about unused groups? Would deleting an unused group
(from one, or both, MDWs) ruin anything?

No it wouldn't. If you're worried (or perhaps might have overlooked some permission for this group), you can backup the development mdw, and then delete the group.