Access Security Do-Over Wanted 2

[autosol]

Is it possible to undo the security on my Access 2000 database in order to start over?

My problem is this. I have a frontend and a backend in use on only two computers for now, both using Win XP Pro. The fe is being used for development and testing. For months, it was possible to use the Linked Table Manager to link from the fe to the be from time to time to test or enter data.

Sometime after installing Office 2000 updates (SR2 and SP3) I discovered that it was no longer possible to link from fe to be. The error message received is: "The MS Jet database engine cannot open the file... It is already opened exclusively by another user or you need permission to view its data."

Nor is it possible to copy either the fe or be from one machine to the other. That message is "Cannot copy filename: Access is denied."

There are currently only two users, both are in Admins group.

I have reviewed much information re Access Security and tried many things. Most notably, perhaps, I have created new db files by importing from the fe and be files. When I place these new files on the be and fe computers and rename them to the original file names,it is possible to both link to and copy them until, it seems, one of the users actually changes some data. Then the errors return.

Does this suggest any obvious remedy that I have missed?

Is it possible to unsecure the files and, if so, would this be a good approach to take?

Thanks, in advance, to anyone who might have a suggestion.

 

[MoLaker]

It doesn't sounds like a database security issue. Just a guess, but take a look at the directory permissions for the users. It might be the locking file [dbname].ldb is not being deleted when they exit. The user should have Delete permissions as well as Read & Write permissions.

 

[autosol]

Thanks for responding. I won't be back in the office until Wed afternoon, at which time I'll check this out.

 

[hickeyc]

The error message received is: "The MS Jet database engine cannot open the file... It is already opened exclusively by another user or you need permission to view its data." "

Just got this error message today and found an article that said the mdb file cannot have the same name as the security file if it's in the same directory. If they are the same name then you get this error.

 

[autosol]

Thanks for the response. hickeyc. Interesting info but doesn't apply in this case.

 

[autosol]

To MoLaker

I have checked for undeleted ldb files in the directory in which the mdb's reside and there aren't any. As far as the Delete permissions you mention, I'm not aware of specific delete permissions in Win XP -- all I'm aware of for security and sharing is the "Allow network users to change my files" option.

What do you make of my being able to copy and link to the new mdb files, created by importing the old mdb files, until the other user opens them up and actually changes some data?

Also, the backend user, who actually uses the application, is a member of the Admins group but when I look at his permissions the permissions granted to Admins are not checked off. Should hey be? When I look at my own permissions the Admins permissions are checked.

 

[MichaelRed]

permissions should (generally) be assigned at the group level, not individually.



MichaelRed

 

[MoLaker]

The Delete permissions I referred to are NTFS permissions. If your system is using FAT32 rather than NTFS, this would not apply. On the other hand, if your file system is NTFS you still might want to look at the permissions for the user in question. Right click the directory where the DB is located and select 'Properties'. Take a look under the 'Security' tab.

Since you say you did not find any .LDB files, this might be moot, but if a user leaves a database ungracefully or for whatever reason, leaves a locking file hanging, it can leave a connection to the database open. If that user has opened the DB 'exclusively', it may prevent you from doing anything with it - such as copying or linking to it.

But, in readdressing your configuration, you say you have a 'frontend' and 'backend' then you refer to your 'backend user'. I don't think I know what that means. In typical Access parlance, a frontend/backend database refers to a frontend database where all the forms, reports, etc. exist and a backend database where most all of the tables exist. So, when you refer to a 'backend user', that leads me to believe this user is accessing the backend direct and not using the frontend. So, just what is you configuration?

As for the permissions of your 'backend user', if he belongs to the Admin group, his actual permissions need not reflect it - UNLESS he has something denied. Anything marked as Deny will override any group permissions. You (your user) has all Admin permission because that user is probably the Owner of the system.

 

[autosol]

To MoLaker

I can see where my description re frontend and backend was confusing. I have a split db. The frontend does contain the forms, reports, etc and, the backend, the live production data tables. The fe is linked to the be tables. The user opens the fe via a shortcut that points to the correct mdw file, in the correct directory. The same mdw file is on both computers. The other computer, the development system, has the same setup - the program mdb is linked to the data mdb, both of which reside on the local hard drive for development and testing. Occasionally, I need to link to the production computer's data mdb from the development computer. In the future, other users in our small workgroup will also be using the backend mdb for production purposes. And, occasionally, I refresh my test data mdb by copying the most current be production mdb to the development computer. As stated in my original post, I have recreated the fe and be mdb's, replaced the current ones on both computers with the recreated ones and, UNTIL the user changes some data, I can link to and copy the recreated files on the production computer. Both users, from both computers, can open and close the production be mdb without causing the problem, as long as no data is changed or added.

I hope that's clearer. Does this suggest anything?

It confuses me that a non-Access function (trying to copy the files from one computer to the other) fails, also. It suggests something outside of Access Security is involved. I am sure there are no ldb files. This scenario used to work fine. I don't recall making any changes to security but I did install Office 2000 service releases 2 and 3.

For lack of a better idea at this point, in my original post, I wondered if there is a way to get back to square one before these mdb's were secured.

Thanks for your help.

 

[autosol]

To MichaelRed

I believe the permissions were given to the Admins group and then the users were assigned to that group. I am in the Admins group and am the owner. When I look at my user permissions, they all appear as checked off. The other user was also assigned to the Admins group but for some reason when I look at his permissions, they aren't checked off. Is that the way it is supposed to be? Shouldn't they be checked off for both users in the Admins group?

Thanks for your reply.

 

[MichaelRed]

It is correct as is. object 'owners' (should) always have 'full' permissions, when objects are created.

One issue which you mentioned is a bit (actually a lot) out of the ordinary. Duplacate security (.MDW) files are not normal. The generic / suggested method is to have the single one in a common resource area which all users are able to access. I am not sure this would generally cause your problem, but it is certainly a suspect in my case book.





MichaelRed

 

[MoLaker]

Bear with me here, but where precisely is the production BE located? Is it on a workstation that other users are allowed to attach to or is it on a network server? What is the operating system for the machine where the BE exists? Can either computer cause the problem by changing data or is it just one? Once the condition develops, does it clear or change if the computer is rebooted?

The reason I'm asking this is because there is more at play than just Access when a database is opened. Files are also locked by the O/S - has nothing to do with whether or not it is a database. I'm certainly no expert on this subject, but there must be some file system setting at the root of this problem. The fact that you cannot copy the database file pretty much screams "file system!" But, why this only occurs when a user makes data changes, I don't know - yet.

 

[easyit]

Hi,

You can only open a db exclusively when you'er logged in as an Administrator. The message implies that one (or more) of the users is an admin. So, what you should do is remove the users from the adminsgroup. This will solve your problem.
Create a new group for them (with less rights than admin). Do not use the default groups!
Check thread181-903863 for the (15) steps involved in securing a DB.
Also, I wouldn't use more than one MDW. Don´t you have a networkdrive somewhere?

Maarten

 

[autosol]

Sorry for the long delay in responding to this topic but an emergency arose, which diverted me from this Access problem and I only spend 1-2 days in the office with the Access problem presented in this thread. I don't know if this thread can be resurrected but I will work on the suggestions made to date and see. Thanks to everyone for their help so far.

 

[autosol]

To MoLaker

The live production be is located on one of the computers on our peer-to-peer workgroup. A copy of the fe is also located on that computer, while development continues on a second computer (I'll call it the development computer) utilizing its own copies of the fe and be mdb files. Occasionally the I want to link to the be file on the production computer from the development computer. Both computers are running Win XP Pro. I believe that changing the data from either computer causes the condition. And, no, the condition, once it develops, does not clear on a reboot.

By the way, I have split the Northwind sample mdb and linked to it (and copied it) from and to both computers without problem.

Don

 

[autosol]

To MichaelRed

Regarding the use of duplicate .MDW files, is your concern that they will not be administered properly over time (ie, kept exactly the same) or something else?

 

[autosol]

To easyit

Regarding "Open Exclusive", I have been the only user trying to use the be mdb on the production computer when trying to link to it from my development computer. In other words, the other user, who is in the Admins group, who works on the data, has never been using it when I've tried to link to it. Does that change anything in your analysis?

Don

 

[MoLaker]

Okay, you say the production database is on one machine and the development is on another in a peer-to-peer network. So, it doesn't sound like there is any great security issue at the present time - especially since both of you are in the ADMINS group. So, let's address the last question of your 1st posting - that is reverting back to no security.

Unless you have done something tricky to security, removing security should not be a problem. First make and secure backups of your production FE & BE databases. Next, open your production back-end and give ADMIN administrative permissions. Do the same for your front-end. Make sure both databases can be administered by the user ADMIN. Then join the system.mdw workgroup (assuming you have not messed with the permissions of the default workgroup file). Next, create 2 new blank databases. Import into one new database all the objects of your FE and import all the objects of your production BE into the other. Open the new FE and relink to the tables in the new BE. Rename your new DB's to whatever. This should remove all security from the new copies of your database and you can start all over - security wise. Since the new BE has the production data, you should be able to copy it to the production machine along with a copy of the new FE.

 

[MichaelRed]

The basic concern / issue starts w/ the sync of the two, however when (if) this becomes an issue, it may lead to other issues. A simplistic example might have a copy (version) where a Password is changed, but the original is not. Now, the user has two seperate login's. What happens when the seperate accounts are used ("simultaneously") to edit records? Is any conflict noted? Does the locking (.ldb) have two seperate entries? What about permissions chenged in one security file but not the other? Which one is checked for those permissions at run time?

More, but it is to early for old brains to consider (wait till coffee is injested and caffine adsorbed).




MichaelRed