I created a workgroup for security purposes in Access and uses security wizard to create users ans groups. However, it seems that the security applies to every database I open, not a particular database only. When I did the security wizard, I only open the database I want to secure. Does anyone know how to attach a workgroup to a particular database only, so it does not apply to all databases opened in Access ? Thanks.
Thanks for the reply. If I want to automate attaching the particular database to a workgroup, how do I do that instead of typing "msaccess.exe ... wrkgrp ..." everytime I wanna open the database ? What I wanna achieve is whenever users click on my database in windows explorer or using File->Open in Access, that database is automatically attached to the workgroup. So, I don't want them to type in ""msaccess.exe ... wrkgrp ...". Because if I put my database in the network for example, the workgroup must be automatically attached, otherwise, people will ignore the security. I used join before from wrkgadm.exe, but it seems it attach all databases to the join workgroup, not a particular only.
you can create shortcuts to the database for your users. The target of the shortcut is "msaccess ... /wkrgrp ...". So the user has to double-click the shortcut to run your secured database.
File/Open doesn't work because Access checks security before it opens the database. That's the reason why
1) every database requires a logon if you run Access with a secured workgroup database
2) there is no need to authenticate to a new database if you are logged on.
Thanks for the reply. What if I wanna share my mdb in network ? I use linked tables and create copies of my mdb in local machines, so the query runs in local machines and access data in mdb in the server rather that running all in the server. So, in this case, how does it work with security ? Should I copy mdw to and create shortcut in every local machine ? What I did previously was put the mdb, shortcut as well as mdw in network. But, it seems it works so slow when users try to access it because of transferring everything through network.
What I did previously was put the mdb, shortcut as well as mdw in network.
Probably the following example is what you did:
There is a network share 'accessdb' to directory \\Server1\Database\AccessDB. Every user has mapped the share as drive X.
Content of directory AccessDB are your secure.mdw and data.mdb file.
Every user has a local frontend.mdb too. Let's assume it's stored in H:\DB.
Create a shortcut with the following target:
"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" H:\DB\frontend.mdb /WRKGRP X:\secure.mdw
Copy this shortcut either to H:\DB or to X:\. I suggest to set the readonly flag for this shortcut.
Users can double-click this shortcut, log on and that's it.
But, it seems it works so slow when users try to access it because of transferring everything through network.
If the frontend.mdb is stored locally you have network traffic for the data only. To reduce this traffic try some hints from the Access help. Look for "Optimize performance" and "Improve performance of linked tables". Or use a server database instead of Access for the data.
In the network drive I put:
Shortcut : "C:\...\MSACCESS.EXE" "C:\...\LinkedDB.mdb" /wrkgrp "N:\... \Secure.mdw"
C --> Local drive
N --> Network Drive
So basically, each local machine has its own copy of LinkedDB which links to OriginalDB. What I am not sure is whether to put the shortcut only in network (that means only 1 shortcut for all users) or put shortcut in each local machine ? I would say one shortcut in network drive is better because only users that are connected to network can access it, otherwise, if I put the shortcut in the every local machine and users are not connected and click on the shortcut, a message telling where the OriginalDB located will come up.
Another concern of putting LinkedDB in each local machine is user will rather click on LinkedDB, hence bypass the security, than click on the shortcut. Is it possible to protect LinkedDB so users cannot click on it ?
if the shortcut is in a network drive make sure that Access is installed in the same directory (e.g. C:\Program Files\Microsoft Office\Office) for every user.
But your main problem seems that your databases are not really secured if users can bypass security by clicking the LinkedDB.
I assume that you created your databases with the default system.mdw. But you have to create a secured database with the secure.mdw!
To secure your databases do the following:
1. Set secure.mdw as workgroup database.
2. Start Access, logon as Admin.
3. Security advice (it is not necessary but I recommend it)
a) Create a new user (e.g. NewAdmin) and assign them to group Admins.
b) Quit Access, restart it and logon as NewAdmin.
c) Remove user Admin from group Admins and remove all permissions for this user.
-> Now user NewAdmin is the only database administrator (dba). User Admin has no permissions so nobody can logon with this well known user. But don't remove the password from user Admin. It's necessary for the logon process.
d) Set password for user NewAdmin.
4. Create a new database (e.g. NewOriginalDB).
-> The owner of the new database is user NewAdmin. This database is secure because it is created with security data from the secure.mdw.
5. Import your tables etc. from your OriginalDB.
6. Create a new database (e.g. NewLinkedDB).
7. Import your forms etc. from your LinkedDB.
8. Link your tables from NewOriginalDB to NewLinkedDB.
9. Quit Access and distribute the databases.
Now you have secured databases. I guess for NewOriginalDB and NewLinkedDB you will use your original database names.
If a user click the new LinkedDB he get the message "You do not have the necessary permission to use the 'LinkedDB.mdb' object..." because he tries to open the LinkedDB.mdb with the default system.mdw as workgroup database.
But the necessary workgroup database is secure.mdw. The user MUST use the shortcut.
I had already done what you told me, but maybe it's clearer if I just tell you what I did in details.
1. I was developing a database (called myDB), creating tables,forms etc.
2. Then, my users want me to create user-level security
3. So, I create a workgroup (called myMDW) using wrkgadm.exe
4. Then, I open Access (no mdb is opened) and change Admin password
5. I create a new user called milan and make it a member of Admins group
6. I exit Access and logon as milan
7. I remove the Admin user from Admins group
8. Then, I assign password to milan
9. Then, I open myDB and run security wizard
10. In the security wizard I: secure all objects, create groups (e.g. Read-Only Users etc), remove all rights from Users group, create users and assign password, assign users to groups
11. I exit Access and click on myDB or other mdb. What I find is Access asks for username and password for every database I open, which is not what I want, I only want it to happen if click on myDB.
12. Then, I learn from you about the shortcut
13. So, I use wrkgadm.exe to join to SYSTEM.MDW. Now, the username-password only prompts if I click on the shortcut. It seems that using join in wrkgadm.exe basically joins access with .mdw, so all DB opened in my Access will use the .mdw
14. So, does myMDW only apply to myDB ? Because I am still in development stage, I usually copy myDB and save as myDB1 or myDB2 etc. Is that OK ? When I use the shortcut to open myDB/myDB1 etc using myMDW, it works fine I guess and the owner becomes me, not Admin.
15. However, if I click on myDB/myDB1 etc, I can do whatever I want, there is no security at all, which is not supposed to happen, right ?
Can you see anything wrong with my process ? Should I just redo it from the beginning if the mistake is fatal ? When we create security, what security information is saved in .mdw and .mdb ?
Also, how do I let user change password without using user-group account ? Do we need to use VBA for this ?
Does myDB has to be a new one ? In my case, the idea of securing DB comes in the middle of developing myDB. I think what I am still not sure is does one mdw only applies to one mdb ? Because when I use security wizard, the objects listed that I want to secure is only the ones with the database I open.
Peter, thanks a lot for the help, I learn a lot from this experience.
steps 1 to 13 are all right. And myMDW applies only to myMDB.
Access stores groups and users in the mdw file. You can check this if you create groups & users in the system.mdw and open or create an unsecured mdb. With system.mdw you see the new groups & users. If you open the unsecured mdb with myMDW you see your groups & users.
All permissions are stored in the mdb file. And that may be the problem. Please check that group Users has no permission to open the current database. That could be the reason why users can open myMDB without myMDW. It has nothing to do with permissions for tables or forms. It's only the permission to open a database.
If users should change their password you can create a user-defined menu and add the menu item 'User and Group Accounts'. Unfortunately they see also all users and groups. Another way is using VBA. You can create a form (e.g. MyForm) with a form field (e.g. txtNewPwd) that contains the new password and a command button with the following code for the OnClick method (written in DAO):
Dim db As Database
Dim us As User
Set db = CurrentDb
Set db.Users(CurrentUser).NewPassword = MyForm!txtNewPwd
It's just a simple example. You should add additional fields for old password and for re-entering the new password. I hope you can fix the problem.
Thanks. I already check the User group permission, in the database (or others), nothing is checked. I think if we use security wizard, there is an option that will takes all Users group's permissions away. I am really confused why this occurs. If I use the shortcut and login as a Read-Only user, it works as the permission says, so works fine. Only when I click on myDB, it's causing headache
I read through the Security FAQ. In point no 10, it says, "The Access 2000 Security Wizard removes permissions to the point where they are not visible on the security menus, but testing has revealed that in Access 2000 it is possible to open a database by using the default workgroup information file regardless of the menu settings.". This seems the problem, though, not sure.
If I import all objects from myDB to a new DB, would the permission be copied as well ? What if I copy myDB and save it as myDB1, would the permission be copied as well ? If I can't solve the problem, I am thinking of re-doing security from the beginning. So, first, I import myDB's objects to a new DB and create a workgroup using wrkgadm.exe etc (like step 1 - 13). Is a MDW usually only for one MDB ?
In your code, where do you use us object (Dim us as user) ?
The statement in the FAQ is correct if group Users has the permission to open the database. I checked this in a sample database once again. If this permission is given the user can open a secured database with the default mdw. If I remove this permission I get the message "You do not have the necessary permission ...". There is no other explanation.
If you open a database with the default mdw file:
1. Access checks authentication (stored in mdw) -> with default mdw no logon is necessary
2. Access checks permissions (stored in mdb) -> if group Users has the permission Open/Run for the database everybody can open the database because everyone is member of this group.
3. Access checks permissions for objects (tables, forms, etc.) -> ...
The only solution from my point of view is to remove this permission:
- menu Tools/Security/User and group permissions
- choose Groups from List, Users from User/Group Name and Database from Object Type
The group Users should not have the Open/Run permission for the current database, your myMDB.
> In your code, where do you use us object (Dim us as user) ?