How to go from 2 secure MDB’s with 1 workgroup to 2 workgroups

[Hank]

I have two secure front-end databases (e.g. secureapp1.mdb & secureapp2.mdb)
that reside on each user’s desktop pc and one secure back-end database (e.g.
securedata.mdb) that resides on the server and is linked to the front-end
databases. All 3 database files were created and secured using the same
workgroup file (e.g. system.mdw) which also resides on the server in the same
folder with the back-end database.

My manager now wants to separate the user accounts for secureapp1.mdb &
secureapp2.mdb so that he has the flexibility to assign different user
permissions in each of the two applications. For example Joe Blow may have
admin permissions in secureapp1.mdb and no permissions in secureapp2.mdb

What is the best way to transition to dedicated workgroup files for each
front-end database without loosing my custom groups and corresponding
permissions?

I’ve tried using the User-Level Security Wizard to create a new workgroup
file for secureapp1.mdb however, when I do that I loose my custom groups and
permissions.

Any help would be greatly appreciated.

 

[Rick Brandt]

I have two secure front-end databases (e.g. secureapp1.mdb &
secureapp2.mdb) that reside on each user's desktop pc and one secure
back-end database (e.g. securedata.mdb) that resides on the server
and is linked to the front-end databases. All 3 database files were
created and secured using the same workgroup file (e.g. system.mdw)
which also resides on the server in the same folder with the back-end
database.

My manager now wants to separate the user accounts for secureapp1.mdb
& secureapp2.mdb so that he has the flexibility to assign different
user permissions in each of the two applications. For example Joe
Blow may have admin permissions in secureapp1.mdb and no permissions
in secureapp2.mdb

What is the best way to transition to dedicated workgroup files for
each front-end database without loosing my custom groups and
corresponding permissions?

I've tried using the User-Level Security Wizard to create a new
workgroup file for secureapp1.mdb however, when I do that I loose my
custom groups and permissions.

Any help would be greatly appreciated.

You don't need separate workgroups just to have different permissions. Just
create new groups inside the one workgroup file, give those groups different
permission settings and then move some of the existing users into the new
groups.

 

[Hank]

Rick,

Thanks for your reply.

Unfortunately I don't think this will solve my problem. For example if Joe
Blow has admin permissions in secureapp1.mdb and no permissions in
secureapp2.mdb, what would prevent Joe from assigning himself to an admin
group in secureapp2.mdb? Since you're suggesting creating new groups within
the same workgroup file I don't see how you can prevent an admin user in one
application from assigning himself admin permissions in the other
application. Unless I'm missing something?

Thank You,

Jeff

 

[Rick Brandt]

Rick,

Thanks for your reply.

Unfortunately I don't think this will solve my problem. For example
if Joe Blow has admin permissions in secureapp1.mdb and no
permissions in secureapp2.mdb, what would prevent Joe from assigning
himself to an admin group in secureapp2.mdb? Since you're suggesting
creating new groups within the same workgroup file I don't see how
you can prevent an admin user in one application from assigning
himself admin permissions in the other application. Unless I'm
missing something?

Groups, Users, and which Users are members of which Groups is stored in the
workgroup. Permissions are stored in the MDB. So... one group (in one
workgroup file) can have administrative permissions in MDB_1 and yet not
have administrative permissions in MDB_2.

This does mean that each MDB will have to use different groups for
administrative purposes, but that is still a lot easier than using two
different workgroups.

 

[Hank]

Rick,

Thanks for your input. I understand everything you're saying. The problem
I'm running into is that the users who have admin permissions are given
access to the "UserAndGroupAccounts" window. This to to allow them to create
new user accounts and assign them to the appropriate group (for permissions).


Given they have access the the "UserAndGroupAccounts" window and a common
workgroup file for both applications, means they can assign any available
group to any user. Since we want to limit the permissions they can assign
based on the application they have admin permissions in (i.e. admin for
secureap1.mdb only has access to custom groups dedicated secureap1.mdb and
secureap2.mdb only has access to custom groups dedicated to secureap2.mdb),
I'm back to my original problem. Do you see my dilema? I'm starting to
wonder if this is going to be too complicated to implement.

Any other suggestions or insight would be greatly appreciated.

Thanks again!

Jeff

 

[Rick Brandt]

Rick,

Thanks for your input. I understand everything you're saying. The
problem I'm running into is that the users who have admin permissions
are given access to the "UserAndGroupAccounts" window. This to to
allow them to create new user accounts and assign them to the
appropriate group (for permissions).


Given they have access the the "UserAndGroupAccounts" window and a
common workgroup file for both applications, means they can assign
any available group to any user. Since we want to limit the
permissions they can assign based on the application they have admin
permissions in (i.e. admin for secureap1.mdb only has access to
custom groups dedicated secureap1.mdb and secureap2.mdb only has
access to custom groups dedicated to secureap2.mdb), I'm back to my
original problem. Do you see my dilema? I'm starting to wonder if
this is going to be too complicated to implement.

Any other suggestions or insight would be greatly appreciated.

While I haven't ever been in the exact scenario you are describing I believe
that even though "they have access the the UserAndGroupAccounts window" this
does not mean they can assign permissions to users in a particular MDB if
you deny them administrator rights for that MDB.

 

[Joan Wild]

While I haven't ever been in the exact scenario you are describing I believe
that even though "they have access the the UserAndGroupAccounts window" this
does not mean they can assign permissions to users in a particular MDB if
you deny them administrator rights for that MDB.

But Rick, he's not having these users assign permissions, he's allowing them to create users and assign them to groups. I don't see any way around this except to use two workgroups.

Hank, you'd start by unsecuring one of the databases, then resecure it using a different mdw file. You will have to recreate the groups and permissions (you wouldn't want to keep the same group SIDs). You can use Jeff Conrad's utility to document the permissions as they exist now, to ease recreating the groups/permissions in the new mdw.
http://www.accessmvp.com/JConrad/accessjunkie/csdtools.html

 

[Hank]

Hi Joan,

Thank you so much for the information. Now I'm wondering when I create 2
workgroup files if I'm going to have trouble editing data in the common
backend database that each of the frontend mdb's are linked to (since it will
be secured with a different workgroup file than one of the frontends)?

In other words I'll have secureapp1.mdb secured with system1.mdw and
secureapp2.mdb secured with system2.mdw and securedata.mdb (common back end
database) secured with system1.mdw. When I link the tables from
securedata.mdb to secureapp2.mdb will users of secureapp2.mdb be able to
edit, delete & append data to those linked tables when they reside in a
database secured with a different workgroup file?

I'd appreciate any insight and suggestions you or anyone else may have.

 

[Joan Wild]

You can use RWOP queries for all data interaction, and remove all permissions on the tables in the BE. See www.jmwild.com/RWOP.htm for more information.