MDB 'remembers' old MDW

[Guest]

I have an Access 2003 front-end database that was originally linked to
'Security.mdw'. It was decided to change how the security groups were setup
so a new 'Security2003.mdw' was created, the user details migrated into this
and the new groups assigned.

This has all works fine, but occasionally when we build and publish a new
mde front-end we get permisssion problems. When we look at the front-end mdb
it appears to be linked to the correct 'Security2003.mdw', but when we look
at the user and group permissions, the groups from the old 'Security.mdw' are
listed. Going through the process of relinking to 'Security2003.mdw' seems to
fix the problems, although there is one temporary table (created via a make
table query) that has to have its permissions reset. The MDE file has the new
workgroup file specified in its launch string.

I don't understand how the mdb can 'remember' the old security groups. The
problem is that I am unable to reproduce it consistently - I have tried
opening Access first, checking the workgroup file, then opening the mdb;
opening the mdb from Windows Explorer so that it starts Acceess. There must
be some reference in the front-end.mdb to this old mdw that I need to remove.
I cannot delete the old 'Security.mdw' as there are other databases that use
it still (It is rare for me to open one of these old databases, but when I do
I am careful to check which mdw file is being used when I open one of the new
databases).

Any ideas appreciated.?

Thanks

Peter

 

[Joan Wild]

Databases do not 'remember', nor are they absolutely tied to any workgroup
file.

The workgroup file is used for a session of Access - any number of databases
can be used during that session.

In addition it is possible for a database to be used while joined to any one
of a number of mdw files.

The mdw defines the usernames/passwords and group memberships. The
permissions are stored in the mdb file with the objects.

It is possible, and from your description likely, that there are identical
users in both mdw files. Therefore, either mdw can be used with the mdb,
except for those instances where a permission in the mdb has been removed
for a user/group. In other words, they may be able to use it, but hit a
permission error on one object only.

The fact that you 'migrated the user details' from the original mdw to the
new one, suggests that the two mdw files have a lot in common.

If you wanted them separate, then you should have created new Group
names/pids in the new mdw and assigned users to those groups. Permissions
are more easily managed if assigned to groups rather than users, which may
be part of your problem here.

I would ensure that the Security2003.mdw doesn't have any Group names in
common with Security.mdw. Change any Group names, and reassign users into
those new groups.

 

[david epsom dot com dot au]

An MDW is a database, which you use to look up Security ID's.

If you can see data from the 'old' workgroup database, that
means you are using the 'old' workgroup database. Specifically,
if you can see 'Groups' from an old workgroup database, you
are looking at the old workgroup database.

Another way to look in an MDW is to open the MDW in Access,
and look in the tables.

You don't say what you mean by 'appears to be linked',
but if, when you look in any database, you see unexpected
data, then it means you are looking in the wrong database.

And when you use the security menu to look in your workgroup
database and see unexpected groups, it means you are using
the wrong workgroup database.

(david)

 

[Guest]

Dear David/Joan

Thank you both for your responses.

There were 2 reasons that I thought that the mdb had a link to the mdw:
1) When you looked at the Workgroup Administrator dialog it displayed
'Security2003.mdw' as the workgroup file, but when one looked at the User and
Group permissions the old groups from 'Security.mdw' were displayed.
2) With Access closed double-clicking on the mdb file to launch Access and
open the file occasional gives an error message saying that it 'cannot find
the mdw file' and displaying a path that is incorrect so instead of being:
"C:\Databases\Security\Security2003.mdw" It lists
"C:\Databases\Security\Security\Security2003.mdw"

If I am understanding you correctly Access 'doesn't care' what the name of
the mdw file is, it purely looks up the username/pid in whichever one it
happens to be linked to at the time, and if it finds a match looks at the
assigned permissions and applies them to whichever objects are in the current
mdb. This was how I had always thought it was supposed to work, it was just
when the above started to happen that I began doubting if I was correct in
this (and my sanity!)

I always set object permissons at Group level and then assign users to
groups. All of the Groups and all of the database objects were renamed and do
not overlap with the old ones (my predecssor didn't following naming
conventions!). The only object that seems to 'lose' its permissions is a
temporary table that is created with a make table query. This query contains
the 'WITH OWNERACCESS OPTION'.

I think the best way for me to proceed is:
1) to create shortcuts to open the mdbs which specifiy which mdw to use so
that I can be sure the correct one is being opened.
2) Double-check the permissions on my temporary table each time I build and
release and mde.

I have never been a fan of Access' security implementation and I am glad
that a different approach is being taken in Access 12 (2007). I have been
beta testing this for several months now. We are currently in the process of
migrating all of our back-end databases to SQL Server 2005 (Express). I much
prefer applying access rights at the table (or column) level as you can be
absolutely certain that users cannot get at that data via the Access
front-end (and the performance is far superior).

Thanks again for your advice.
Regards

--
Peter Schmidt
Ross-on-Wye, UK

Databases do not 'remember', nor are they absolutely tied to any workgroup
file.

The workgroup file is used for a session of Access - any number of databases
can be used during that session.

In addition it is possible for a database to be used while joined to any one
of a number of mdw files.

The mdw defines the usernames/passwords and group memberships. The
permissions are stored in the mdb file with the objects.

It is possible, and from your description likely, that there are identical
users in both mdw files. Therefore, either mdw can be used with the mdb,
except for those instances where a permission in the mdb has been removed
for a user/group. In other words, they may be able to use it, but hit a
permission error on one object only.

The fact that you 'migrated the user details' from the original mdw to the
new one, suggests that the two mdw files have a lot in common.

If you wanted them separate, then you should have created new Group
names/pids in the new mdw and assigned users to those groups. Permissions
are more easily managed if assigned to groups rather than users, which may
be part of your problem here.

I would ensure that the Security2003.mdw doesn't have any Group names in
common with Security.mdw. Change any Group names, and reassign users into
those new groups.

 

[Joan Wild]

There were 2 reasons that I thought that the mdb had a link to the
mdw: 1) When you looked at the Workgroup Administrator dialog it
displayed 'Security2003.mdw' as the workgroup file, but when one
looked at the User and Group permissions the old groups from
'Security.mdw' were displayed.

The Workgroup Administrator tells you what your default mdw is set to, which
isn't necessarily the one currently in use. If you want to see what is
currently in use, open Access and hit Ctrl-G and type
?dbengine.systemdb
That will tell you the one currently being used in the current session.

 

[david epsom dot com dot au]

front-end (and the performance is far superior).

This is an interesting observation. How are you achieving that?

(david)

Dear David/Joan

Thank you both for your responses.

There were 2 reasons that I thought that the mdb had a link to the mdw:
1) When you looked at the Workgroup Administrator dialog it displayed
'Security2003.mdw' as the workgroup file, but when one looked at the User
and
Group permissions the old groups from 'Security.mdw' were displayed.
2) With Access closed double-clicking on the mdb file to launch Access and
open the file occasional gives an error message saying that it 'cannot
find
the mdw file' and displaying a path that is incorrect so instead of being:
"C:\Databases\Security\Security2003.mdw" It lists
"C:\Databases\Security\Security\Security2003.mdw"

If I am understanding you correctly Access 'doesn't care' what the name of
the mdw file is, it purely looks up the username/pid in whichever one it
happens to be linked to at the time, and if it finds a match looks at the
assigned permissions and applies them to whichever objects are in the
current
mdb. This was how I had always thought it was supposed to work, it was
just
when the above started to happen that I began doubting if I was correct in
this (and my sanity!)

I always set object permissons at Group level and then assign users to
groups. All of the Groups and all of the database objects were renamed and
do
not overlap with the old ones (my predecssor didn't following naming
conventions!). The only object that seems to 'lose' its permissions is a
temporary table that is created with a make table query. This query
contains
the 'WITH OWNERACCESS OPTION'.

I think the best way for me to proceed is:
1) to create shortcuts to open the mdbs which specifiy which mdw to use so
that I can be sure the correct one is being opened.
2) Double-check the permissions on my temporary table each time I build
and
release and mde.

I have never been a fan of Access' security implementation and I am glad
that a different approach is being taken in Access 12 (2007). I have been
beta testing this for several months now. We are currently in the process
of
migrating all of our back-end databases to SQL Server 2005 (Express). I
much
prefer applying access rights at the table (or column) level as you can be
absolutely certain that users cannot get at that data via the Access
front-end (and the performance is far superior).

Thanks again for your advice.
Regards

 

[Guest]

Thank you Joan. I think that explains my confusion.

I must say that it is not obvious from the wording of the Workgroup
Administrator dialog that the mdw displayed there is the default one, rather
that the one currently in use. It would have been sensible for it to have
displayed both. A quick look in the registry shows that the default mdw is
specified in a key under Access>Jet>Engines and this is where Access
'Remembers' it from.

Thank you both for your help