Multi-user security in Access 2003 with Windows 7

[daro]

Hi,

Googled a lot for my problem, but couldn't find the answer.

I have a DB in .mdb file created with Access 2003 (one file only, no F/E and B/E). I created new workgroup information file, added new users, set permissions etc. It works fine as long as I am using shortcut like this one:

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "S:\YourSystem.mdb" /WRKGRP "S:\YourWorkgroup.mdw"

or open my .mdb file directly.



When I open my .mdb file using different user account in Windows, it uses default workgroup information file, and no security is applied.

I am no security expert, but I can easily bypass multi-user security by deleting workgroup information file. I think I am missing something here...

I guess I have to distribute the shortcut among users. How should I secure .mdw and .mdb file in system level, so users can only use provided shortcut?

[khalid]

The best and tested way is to split the database in fe/be (more secured with password protected be linked tables)
Then distribute it to your users, create userTable with usernames and passwords, provide login panel to users so they can put their passwords and usernames.
In this way you can record each users activity, their machine numbers, login/logout times, in a logtable and also can control each user with the specific group to certain panel/entry etc.

using access default security could make hurdles and limited control over your users.
I am controlling all my users as described above.

[daro]

If I understand correctly, there is needed extra programming in VBA, because Access multi-user security doesn't work?

So, when the DB is opened, I should run a macro asking user to log in, check the user name and password with userTable and limit user permissions accordingly to the login provided. Is that correct?

[khalid]

as far as multi-user security, I am not sure and not tested. however the idea of creating usertable is more controllable, where you can add remove blocked users etc.

Yeah the idea is to provide the user Login Form connected to the users table with their username and passwords, and you can also create groups and assign each users to them (as I have one database with multi Applications connected to it with different user/group bunch), so whenever a user is login the application will atomatically decide that this user belongs to which group and what application he can open to use.
This need lot of programing in VBA, but time being you can avoid groups if you have only one application.
you can control your users with UserType bit in the usertable so they will be controlled with their Types for each tasks.

hth.

[daro]

Thanks for your answer.

And I thought that limiting access to certain objects in my DB was easy.

[daro]

One more thing.

I disabled all permissions for default Admin account, and threw this account out of Admins group. Whenever I open my .mdb using different workgroup information file with none but default Admin account (logging on different Windows account) I get full access to DB.

According to this:

http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx

it shouldn't work.

Group Users has no privileges, default Admin account is no longer in Admins group, Admin account has no privileges.

When I try to login to the default Admin account using correct (with access restrictions) workgroup information file, I get the message:

This database is in an unexpected stat;Microsoft Access can't open it.

This database has been converted froma prior version of Microsoft Access  by using the DAO CompactDatabase method instead of the Convert Database  command on the Tools menu (Database Utilities submenu). This has left  the database in a partially converted state.

If you have a copy of the database in its original format, use the  Convert Database command on the Tools menu to convert it. IF the  original database is no longer available, create a new database and  import your tables and queries to preserve your data. Your other  database objects can't be recovered."

Why it is possible to login with administrative rights using default Admin account, with different workgroup information file, while all privileges have been revoked from this account?


Edit:
I created new DB using new account with administrative rights, so I became owner of the whole DB. It solved the problem and I get 'Access denied' while logging in with default Admin account. Works just fine But when I open my .mdb file using different Windows account (and different workgroup file) default Admin account is used and full access granted :/ Hate this multi-user pseudo security! I guess only way to set up this correctly is to force users to use the shortcut with some OS settings.

[khalid]

I am not using Ms-Access security, this is more confusing and useless...