How to create .mdb file for multiple users to view only tables ? 1

[mickeync]

Hi all,
I need to link tables from SQL Server to MS Access says .... MYDATA.mdb and allow 30-40 users VIEW TABLE ONLY, but able to create their own queries. However how do they prevent others from updating or changing their queries ???

I have read some help online about creating "NEW WORKGROUP". I am nervous when changing the system file and prefer NOT TO TOUCH! Does anyone have any ideas to help me with this project.

Anyhelp would be greatly appreciated.
M.

 

[alfalf]

Hello.
There's no other way to do what You want but to work with workgruop premissions. Also, You don't have to change system.mdw, simply create new workgroup. You can easily do it via Start -> Run -> type in wrkgadm.exe and create new workgroup system file. Make sure to write down PID and location and to give it different name then system.mdw. Once created, You can easily join it, setup different access to different users (user groups), and You can easily switch from that new mdw to system to gain control over Your dbs & objects.

Since by default, if no User passwords are provided, Access opens db for last logged user, in question is user Admin.

The trick is:

to add (for You) another name & password (that is other than 'Admin'), and to remove existing Admin User from Admins group.

I wrote about MS Access security a lot. The best way to do this is to follow bellow instructions and to apply those solutions step by step for one user, and then to apply Your expirience with that to all other users.

Sorry for late reply and Post if You come into some missunderstanding.

[red]MAKE SURE TO MAKE BACKUP OF YOUR SYSTEM.MDW AND YOUR DATABASES![/RED]

Access 97/2000 Security Step by Step

1. Go to Start, Run and type wrkgadm.exe This will open the workgroup administrator and show you the workgroup you are currently joined to by default (write down the path; you’ll need it later).
2. Click on Create and enter a Name, Organization, and Workgroup ID. Write down the exact strings you use; you’ll need these should you ever need to recreate the workgroup.
3. Choose a suitable location and name for your new workgroup file. Do not give it the same name as the standard workgroup (system.mdw). Once you click on OK, you’ll be presented with a dialog to confirm the information.
4. Once you’ve confirmed the information, the workgroup will be created and you’ll be joined by default to this new workgroup. Click Exit.
5. Open Access, canceling the opening dialog.
6. Go to Tools, Security, User and Group Accounts.
7. On the Users tab click on New to create a new user (one that will own all objects and have full permissions on your database). Enter the name and PID for this user (suggest you write down this information).
8. Add the Admins Group to this user.
9. Choose the Admin user in the dropdown list and remove them from the Admins Group.
10. Go to the Change Logon Password tab and enter a password for the Admin user. Click on OK.
11. Close Access.
12. Open Access and log in as the user you created in step 7 (the password will be blank). Cancel the opening dialog.
13. Go to Tools, Security, User and Group Accounts. Click on the Change Logon Password tab and enter a password for this user. Click on OK.

At this point, you can either use the security wizard or secure it manually. If you are using Access 97, use the wizard. If you are using version 2000, do not use the security wizard - secure it manually.

Using the Wizard:
14. Open the database you want to secure. Go to Tools, Security, User Level Security Wizard.
15. Choose all objects and click on OK.
16. Choose a suitable location and name for your secure mdb and click on Save.
17. The wizard will create the new secure mdb, and your original mdb will not be changed. You’ll get a confirmation message when it is completed. Your new user will be the owner of the secure mdb and all its objects. The Users Group will have no permission on anything.
18. When the wizard is completed, close the database window. Open your new secure mdb.
19. Go to Tools, Security, User and Group Permissions.
20. Click on the List Groups option, and then select the Users Group in the list.
21. Beside Object Type choose Database, and uncheck the Open/Run permission, and click OK.
22. Proceed to Final Steps, below.

Secure it manually:
14. Create a new database, choosing a suitable location and name.
15. Go to File, Get External Data, Import
16. Locate your database. Click on each tab and click Select All. If your database has custom menus and/or import/export specs, click on Options and ensure you include those. Click OK.
17. Go to Tools, Security, User and Group Permissions.
18. Click on the List Groups options, and then select the Users Group in the list.
19. Beside Object Type choose Database, and uncheck all permissions.
20. Choose Object Type Tables, select all items under Object Name. Click on Read Design once to remove all permissions and click on Apply. Repeat for the Object Type Queries.
21. Choose Object Type Forms, select all items under Object Name. Click on Read Design and on Open/Run to remove all permissions and click on Apply. Repeat for the Object Type Reports.
22. Choose Object Type Macros, select all items under Object Name. Click on Open/Run once to remove all permissions and click on Apply.
23. Choose Object Type Modules, select all items under Object Name. Click on Read Design once to remove all permissions and click on Apply.
24. Proceed to Final Steps, below.

Final Steps
1. Close Access.
2. Go to Start, Run and type wrkgadm.exe This will open the workgroup administrator and show that you are still joined by default to your secure mdw.
3. Click on Join and locate the original workgroup you were joined to (you wrote down the path).
4. Create a desktop shortcut that has the following as the target:
“path to msaccess.exe” “path to mdb” /wrkgrp “path to secure mdw”
This will ensure that you are joined to the standard system.mdw for all sessions of Access. When you want to use your secure database, you’ll start it using the desktop shortcut.

As a final test, open Windows Explorer, locate your secure mdb and double-click it. You should not be able to open the database at all.

Not Done Yet
So far you have ensured that only by using the secured mdw can someone open your secure database. You haven’t created any groups or users other than the one.
1. Open your database using the shortcut, and login with the username/password you created. Go to Tools, Security, User and Group Accounts.
2. Click on the Groups tab and create the groups you need. Be sure you write down the names and PIDs you enter.
3. Close the dialog.
4. Go to Tools, Security, User and Group Permissions.
5. Click on the Groups option beside List.
6. Select a group that you want to apply permissions to.
7. Go through all the objects in your database assigning the appropriate permissions to this group. Repeat for other groups you have created. Ensure that you don’t assign any permissions to the Users Group.
8. You do not need to assign permissions to individual users. Although it’s possible, it isn’t necessary and will make administration harder.
9. Go to Tools, Security, User and Group Accounts.
10. Click on the Users tab and create users.
11. Once you've created a user, you can assign them membership in one or more groups. That user will automatically inherit the permissions assigned to the group.

 

[tpauldin]

Hey Alfalf,

Thanks for your post to this question. I followed your instructions thoroughly until I reached "Final Steps". Could you give me a little more detail on how to create the desktop shortcut. I greatly appreciate it.

 

[alfalf]

Here's shortcut (Put proper Folder names):

"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "C:\folder\Your.mdb" "C:\windows\system32\system.mdw"

That's it.

I have to add some conclusions here:
There are two main things here that will provide security on distributed mdbs (or mdes):
1. Eliminating Admin user from Admins group and setting other user (which is in Admins gruop) to be owner of all objects;
2. Storing that information in custom mdw (Your custom mdw) that will not be distributed arround network (information about ownership is 'unknown' to other mashines/users).
Consequence: Any User is logged as Admin by default, when starting Access. By completing upper steps, You provide that, but then, Admin user (or any other) will not be able to bypass premissions settings.

This is fairly the best way to properly secure Access data. With such soluton, in cases where Access can, by automation, replace ownership to default values of an mdb (installations of newer versions of Access do that), You are protected even when this happens! Also, with such solution, there's no tool on market today that can reset premissions if there's no original workgroup info file (mdb) present. All alterations to premissions stops, tested! So, to conclude this part, in no situation distribute Your custom mdw file. Owner's name and pwd are distributed together with mdbs anyway. Keep it safe, no matter only You know password for owner of objects. Because if someone gets Your custom mdw, one can use tools to reset premissions.

Another thing is more technical from above. When You finnish all those steps above, remove password from Admin user (that's missing up there), that way You'll avoid Access Logon dialog to popup.

Also, this solution proves to be excellent if one project is consisted of mde application and additional mdb(s) that stores data. From application, in code, one should open global workspace defined with owner's name and password, and then under that workspace, open connections to mdb(s). With such connections, user can access data only via Application Forms and dialogues. Ultimatelly, convert application to mde. From this point, one can make applications with no limits!

In addition, avoid links to tables over network. This is not only because of security, but performance reasons also. In such case, use data accessing as described in paragraph above (workspace).

Hope I helped.

 

[JonFer]

What happens if you use the wizard to secure an Access 2000 database? Are there bugs with it?

John

http://www.interautomateconsulting.com