User permissions and distributing an Access solution

  • Thread starter Thread starter Raj Parulekar
  • Start date Start date
R

Raj Parulekar

I am running into a problem with my app, which I am sure, others have
experienced as well but am not sure how to proceed at this time. I have
developed an Access 2000 app and split it into a FE (Front End) and a BE
(Back End). The FE will be on different user's machines within different
divisions of my company. The admin of each division will then be able to set
their division specific user permissions on their own FEs and then deploy
them to their division's users. My question is this - when I modify the code
on my dev version (for bug fixes, enhancements, etc.), and since the
permissions are stored in the FE, when I send my updated FE to all the
Division admins, all their custom permissions get wiped out and they default
to the permissions which were on my dev version. How do I get around this
problem? How can I distribute just the code changes such that their custom
permissions are still retained? Is there a way to do this?

Thanks for your help!!
 
Hi Raj

Are you talking about NTFS permissions (at a file level) or Access
permissions (at a database object level)?

If you are seeing your Access permissions getting "wiped out", it probably
means that you have not secured your database correctly.

Have you read the Access Security FAQ? If not, then I *strongly* recommend
you go to http://support.microsoft.com/?id=207793 where you can download it
or read it on line. It lists the steps required to properly secure the
database.

All the steps are important, but there are three which are particularly
important to your case:
1. Your database must be created using a custom workgroup (MDW) file
2. You must be logged in as a user other than Admin when you create it
3. ALL permissions should be revoked from the user Admin and the group Users
for ALL objects

I suspect that the users are opening the FE database using the default MDE
and user (Admin) and are happily stepping over your very low barbed wire
fence :-)

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.
 
(snip)
when I send my updated FE to all the Division admins,
all their custom permissions get wiped out and they default
to the permissions which were on my dev version.
Click to expand...

This will certainly happen - and can certainly be avoided! - depending on
how you established the security of your database.

(1) Did you create a new workgroup file for your application?

(2) Do you use that workgroup file (or a different one) when you develop
changes?

(3) Have you assigned permissions to *groups*, or directly to users?

(4) Does your application let them create new users or groups?

TC
 
The whole issue is that Access stores the users and groups in the mdw file
but it seems like it stores the actual permissions for these users and
groups in the front end /MDB file
Click to expand...

That is 100% accurate.

The workgroup file stores:
- the name of each user;
- the name of each group;
- the cross-reference between users & groups (ie. what users are in which
groups and vice-versa), and
- a unique identifier called the Security Identifier (SID) for each user and
group. That is, each user & group has their own unique SID.

The database file stores a cross-reference showing which SIDs can access
which objects, & what level of access they have. Conceptually:
OBJECT SID PERMISSIONS
MyTable 111 read, add
MyTable 222 read, add, edit
MyTable 333 read, add, edit, delete
MyQuery etc.

When a user logs-on to a database, he gets the permissions for his own SID,
*and*, the permissions for any *other* SIDs that identify groups of which he
is a member.

This information may help you work out how to handle your problem.

Raj, I notice you replied to my last post within an hour or so of my making
it. I am now off for the rest of the day, & I will not be around at all,
tomorrow. So if you reply to this, I will not see it for at least 2 days.

Cheers,
TC
 
Thanks for your response. Please see my answeres:

1) Did you create a new workgroup file for your application?
Ans: Yes

2) Do you use that workgroup file (or a different one) when you develop
changes?
Ans: I use the same workgrroup file, but after a while my workgroup file
will become different
from the other admin's workgroup file since they will be creating their own
groups and users

3) Have you assigned permissions to *groups*, or directly to users?
Ans: Only to groups, but the admins will be able to assign their own
permissions to groups and/or users
as I have enabled them to do so they have the flexibility to create their
own security.

4) Does your application let them create new users or groups?
Ans: Yes (per point 3 above).

Additionally, when I created the database I made sure that it was not
created using the Admin user and that it was connected to the MDW file that
my app currently
uses.

The whole issue is that Access stores the users and groups in the mdw file
but it seems like it stores the actual permissions for these users and
groups in the front end /MDB file, that is why when the admin makes any
permission changes he has to "push" his front end to all users
in his division.

I think the only way to solve my problem is to use some kind of a utility
that can import the code objects only (e.g. Forms, Modules, Macros, etc.)
from my dev version into the admins front ends, so their custom defined
permissions do not get overwritten - is this a right assumption? if so is
there a utility that allows that?

Thanks...

Raj
 
Hello Graham, Thank you for your response. Please see my response to TC's
message - he had similar type of questions and I have answered them all in
my reply to him. I am still looking for a solution. Thanks.

Raj

Graham Mandeno said:
Hi Raj

Are you talking about NTFS permissions (at a file level) or Access
permissions (at a database object level)?

If you are seeing your Access permissions getting "wiped out", it probably
means that you have not secured your database correctly.

Have you read the Access Security FAQ? If not, then I *strongly* recommend
you go to http://support.microsoft.com/?id=207793 where you can download it
or read it on line. It lists the steps required to properly secure the
database.

All the steps are important, but there are three which are particularly
important to your case:
1. Your database must be created using a custom workgroup (MDW) file
2. You must be logged in as a user other than Admin when you create it
3. ALL permissions should be revoked from the user Admin and the group Users
for ALL objects

I suspect that the users are opening the FE database using the default MDE
and user (Admin) and are happily stepping over your very low barbed wire
fence :-)

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

Raj Parulekar said:
I am running into a problem with my app, which I am sure, others have
experienced as well but am not sure how to proceed at this time. I have
developed an Access 2000 app and split it into a FE (Front End) and a BE
(Back End). The FE will be on different user's machines within different
divisions of my company. The admin of each division will then be able to set
their division specific user permissions on their own FEs and then deploy
them to their division's users. My question is this - when I modify the code
on my dev version (for bug fixes, enhancements, etc.), and since the
permissions are stored in the FE, when I send my updated FE to all the
Division admins, all their custom permissions get wiped out and they default
to the permissions which were on my dev version. How do I get around this
problem? How can I distribute just the code changes such that their custom
permissions are still retained? Is there a way to do this?

Thanks for your help!!
Click to expand...
Click to expand...
 
Hello again Raj
3) Have you assigned permissions to *groups*, or directly to users?
Ans: Only to groups, but the admins will be able to assign their own
permissions to groups and/or users
as I have enabled them to do so they have the flexibility to create their
own security.
Click to expand...

I think this is the essence of the problem. It is up to you, as the
developer, to ascertain what combinations of permissions should be made
available, and to create groups to satisfy those requirements. It is then
up to the Admins users to create users and add or remove them from those
pre-defined groups.

This latter function (adding/removing users and adding/removing them from
groups) affects only the MDW file. Changing permissions affects the MDB
file, and you should not allow that. In particular, changing permissions on
objects in the front-end will affect the front-end MDB, and these changes
will, as you have observed, be "wiped out" when you install a new front-end.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.
 
Graham Mandeno said:
Hello again Raj


I think this is the essence of the problem. It is up to you, as the
developer, to ascertain what combinations of permissions should be made
available, and to create groups to satisfy those requirements. It is then
up to the Admins users to create users and add or remove them from those
pre-defined groups.

This latter function (adding/removing users and adding/removing them from
groups) affects only the MDW file. Changing permissions affects the MDB
file, and you should not allow that. In particular, changing permissions on
objects in the front-end will affect the front-end MDB, and these changes
will, as you have observed, be "wiped out" when you install a new
Click to expand...
front-end.


In thinking about this, it seems to me that each user & group's permissions
should be established primarily by permissions in the BE database - not the
FE database. Maybe all users & groups could be given full access to the FE
tables, but limited access to the BE tables. Then, when you distribute a new
FE, nothing would change.

TC
 
Thanks TC - but I also needed to control permissions to the FE objects such
as Forms, Queries and Macros.
 
Ok, understood. Maybe you could write some code to store the current
security settings to (or retrieve them from) a flat file. Then, the existing
FE would store its settings, the new FE would be copied over the old one
(somehow), then the new FE would restore the settings from the saved values?

HTH,
TC
 
Back
Top