MDW locks, cant add/modify users

[Doug Bemis]

We have been using this database on terminal server for a long time, but
recently had to move it to a different server.

It used to run on Server 2000 with Access 2000.

It is now running on Server 2003 EE with Access 2003. We converted the MDW
and MDBs to 2002/2003. Since getting on the new server, the mdw constantly
locks up, and we cannot make user changes (adding/removing/editing).
When I say locked, it is not locked with a LDB, I can only see the locks
using Unlocker.

The error I get when trying to modify/add/del users is: Cannot Update.
Database or object is read-only.

If I use unlocker to unlock the MDW, then log in, I can (until another use
logs in, or at least I think that is what locks the file up) make user
changes.

The structure we use for people accessing the datbase is:
-Each user has the same share, Q: which points to a share on the same
server, which stores the MDW. The permissions on this folder and file are
full read/write for everyone.
-Each user had their own MDB on the same server in their own folder

I think I hit all the main details, if you need more information let me know

Thanks,
Doug Bemis

 

[Joan Wild]

Read/write permissions isn't sufficient; you need read/write/create/delete
permissions.

It is generally recommended that you unsecure, convert, and resecure. In
your case, it wasn't necessary to convert the 2000 mdw, since 2003 could
have used it just fine - it's possible the conversion has caused a problem.

I assume that there is a backend mdb file in the same folder as the mdw -
ensure these two files do not have the same name.

 

[Guest]

Since you can add/modify/delete users by adding/modify/deleting
their private share (which contains the mdb), you might re-consider
the use of the mdw. Unless you need the Access Login Name for
the record locking reports, you could let everyone login to their
own mdw as 'admin' or 'user'. When you give them their private
share, and private mdb, you could give them a private mdw with
the appropriate permissions.

To change their permissions, just swap their mdw. If all of your
permissions are controlled by groups, and you only have a couple
of groups, then you only need a couple of standard mdw's, and
you can give each user the group mdw that they need.

mdw's are a user control system that actually predates windows
security, and a lot of the functionality has been re-created in
windows now, so sometimes you find on examination that you
can use the (Active Directory) database for the user identification
tasks in conjunction with your mdb, and just use the (Workgroup)
database as the permission mask.

BTW, I've only ever had lock problems on mdw when doing
design changes. Do you have users doing design changes (creating
new queries?) in their private mdb's?

(david)

 

[Doug Bemis]

Sorry, I meant to say they have full permissions on the share

I only converted the MDW to 2002/2003 after it hadnt been working (i have a
backup of the 2000 one) to see if it would help with the issue.

We do keep a mdb in this folder too, which is where we copy it from to
redistribute it out. It has a completely different name

Read/write permissions isn't sufficient; you need read/write/create/delete
permissions.

It is generally recommended that you unsecure, convert, and resecure. In
your case, it wasn't necessary to convert the 2000 mdw, since 2003 could
have used it just fine - it's possible the conversion has caused a problem.

I assume that there is a backend mdb file in the same folder as the mdw -
ensure these two files do not have the same name.

No one ever uses (or has access to) design changes on the server. We design
on dev machines, then move over the mdb to the server when it's ready.

We have tossed around the idea of giving everyone their own mdw, but there
was something (I cannot recall now what it was) that made us decide against
it. I'll have to ask again what the reasoning was behind it.

Since you can add/modify/delete users by adding/modify/deleting
their private share (which contains the mdb), you might re-consider
the use of the mdw. Unless you need the Access Login Name for
the record locking reports, you could let everyone login to their
own mdw as 'admin' or 'user'. When you give them their private
share, and private mdb, you could give them a private mdw with
the appropriate permissions.

To change their permissions, just swap their mdw. If all of your
permissions are controlled by groups, and you only have a couple
of groups, then you only need a couple of standard mdw's, and
you can give each user the group mdw that they need.

mdw's are a user control system that actually predates windows
security, and a lot of the functionality has been re-created in
windows now, so sometimes you find on examination that you
can use the (Active Directory) database for the user identification
tasks in conjunction with your mdb, and just use the (Workgroup)
database as the permission mask.

BTW, I've only ever had lock problems on mdw when doing
design changes. Do you have users doing design changes (creating
new queries?) in their private mdb's?

(david)

Thank you two for ideas/information! Keep it coming

 

[Doug Bemis]

ALright well we want to go to using straight active directory and rid
ourselves of the mdw. Any tips/resources for doing this? I am going to
search around, but I want to see if there is anything already known to be
good for this

Thanks
Doug Bemis

 

[Guest]

Can you see which user has the file locked? Or is it just
the file cache that has it locked?

TS 2003 is supposed to be able to handle shared files.
(Note that the shared file bug only applies to 2000
http://support.microsoft.com/kb/294816/#appliesto),
but I don't know anyone using Server 2003 EE with
Access 2003.

You already are running AD :~). The point is, that since
AD handles the user authentication, there is no longer a
need for an Access/Jet process to do that.

Using ADO, you can query against active directory,
using SQL or ldap_dialect:

SELECT
ADsPath, cn
FROM
'LDAP://OU=Sales, DC=Fabrikam,DC=COM'
WHERE
objectCategory='person' AND objectClass ='user'

http://msdn.microsoft.com/library/en-us/adsi/adsi/ldap_dialect.asp
http://msdn.microsoft.com/library/en-us/adsi/adsi/searching_active_directory.asp

Unfortunately, there is no DAO or ODBC connector,
so you can't do joins or views, and binding forms to
ADO recordsets is notoriously flaky.

(david)

 

[Doug Bemis]

Nope, I cannot see who has it locked. When I check locks, it just says
"System", not the username.

We use AD yes, but how would we use it for group permissions in access? For
example, we currently use three main groups, admins, data entry, and advanced
data entry. Adv Data Entry has access to a little more than data entry.

Would we have to set table/field permissions for groups in sql?

Sorry if I ask some basic questions...I am still fairly new with access, but
I am the one working on fixing these issues P

Thanks,
Doug

 

[Guest]

You can build your own permission system, using tables. If you
want, you can put those tables into AD. However, that is not
what I was suggesting. What I was suggesting was that you use
AD for user authentication, and MDW only for group permissions.

Imagine that MDW is used only for group permissions. What does
MDW need to contain? Needs to contain Group ID value, used
by MDB to match permissions. Needs to contain User Name in
Group, to establish group membership. Does NOT need to contain
User Name authentication. Hence, does not need to contain
Password, and does not need to contain unique user name.

So, MDW can have just one user name (admin), as long as that
user name is in the correct group, only in the correct group, and
not in the Admins group.

But that means user must have correct MDW. because wrong
MDW would give user wrong group, hence wrong group permissions.

So how many different MDW are required? One for each group.
So 3 groups means three different MDW. Bad. But now you don't
have to manage MDW. They are static. Just like a key. You give
key to user: user uses key to access database. Authentication is
static: user has key, user can use database.

So how to handle dynamic authentication? Control access to key.
Use AD to control access to key. Key is MDW is file, so use AD
to control access to file. Control of file access is normal network
admin. Some users get access to Group A mdw. Some users get
access to Admin Group mdw. Some to Guest Group mdw.

But mdw are static, like a key. does not contain any unique user
information. never needs to be updated. So you can give each user
separate key. never needs to be updated. Put key in private user
folder for each user. Access to private user folder is controlled
by AD. When you log in, using standard windows authentication,
you get access to your private mdw file, which has the group id
for your group.

Of course, sometimes you have to change the locks and change
all the keys. If user makes copy of private mdw, and gives copy
(key) to other user, permission mask is compromised. If user
uses other user windows login, and steals private mdw (key),
permission mask is compromised. Not solution for all permission
mask problems. Access User Level Security not solution for
any real security problem, too badly compromised already. But
use of AD (windows) for user authentication, and MDW only
for permission mask, solution to network fault problem with shared
mdw and dynamic user authentication/group membership problem.

(david)