[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Workgroup security

Posted on 2005-04-10
21
Medium Priority
?
600 Views
Last Modified: 2010-08-05
Hello,

I want to completely restrict access to my tables, and restrict ability to modify forms/queries/reports by my users.

I tried the Workgroup Administrator feature but I got lost trying to create the users, I was never able to locate the users ?!?!?

What are the proper steps to accomplishing my objective.  Also, I am not using a server, the users access the database via a shared folder on my computer, all XP pro machines.

HELP!!! before my users revolt and delete all the tables!!!
0
Comment
Question by:onestopfinancial
  • 11
  • 4
  • 3
  • +2
21 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13748474
>and restrict ability to modify forms/queries/reports by my users.
Creating an .mde file will not allow forms/reports/macros/modules to be opened, but still allows tables/queries to be modified.

>I tried the Workgroup Administrator feature but I got lost trying to create the users, I was never able to locate the users ?!?!?
Suggested process:  Create groups first, and assign rights to groups.  Then create users and assign users to your groups.  Your users will inherit the rights of the group they're in, and you won't have to do anything when users need to be added/deleted other than add/delete the user name to the group.

Another thought here would be to move your tables to a secure folder, or even better SQL Server, then link these tables to the app that goes out to the user.  

Hope this helps.
-Jim
0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 900 total points
ID: 13748483
0
 

Author Comment

by:onestopfinancial
ID: 13748507
ideally, id like the users to not even be able to view the objects.....i am beginning to develop some Switchboards.... i figured out how to open a switchboard on startup under the Tools>Startup menu and how to hide the database object window, but how do i remove the Unhide option under the Window menu bar because a user could just Unhide the object window? if i can grey out Unhide that would be great, somehting to that effect....if so do i really need Workgroup Administrator?

anyway, while i was writing this comment i made a bad move....... i unchecked all the boxes in the Tools>Startup menu and now I can't even Unhide the object window, what do I do!?!?  i am stuck with an unfinished switchboard, basically a useless database now.  (i do have a backup but it would be nice to know how to reverse this mistake)

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:onestopfinancial
ID: 13748530
jimhorn, where are the users created? on my machine where the DB resides, or on the local user machines? because the DB is in a shared folder on my computer
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13748552
On my XP box...

Tools: Security: User and Group Accounts...
On the Users tabl click the 'New' button, and enter a UserId and password.
0
 
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 200 total points
ID: 13748554
First, you better get this free tool to bring you (back) in control:

  C2DbProperties
  http://www.colbyconsulting.com/main.htm

Then, if you don't have much time for experimenting and learning by trial and error, get Garry's book on the topic:

  http://www.vb123.com/map/

/gustav
0
 
LVL 85
ID: 13748647
Users, Groups, Group memberships, and passwords are stored in the workgroup information file (the .mdw file). Access ships with a default workgroup file named "system.mdw", however, you don't want to use that to secure your database. Instead you would build a new workgroup file and add your Users and Groups to that workgroup. When you deploy your database, you would then also deploy the .mdw file you created, and place it in a public folder (like the same folder you use to store the backend data tables). You normally would provide your users with a desktop shortcut in order for them to open the db with your workgroup file.

Access User Level Security is very tricky and can be difficult to master. Properly done and paired with OS security, it will do all you want. The links provided by jim and gustav are the most often given, and will provide you with enough information to properly secure your db.

Make sure to store a copy of your database off-machine when you are experimenting with ULS.
0
 
LVL 11

Expert Comment

by:Stephen_Perrett
ID: 13749007
Hi onestopfinancial,

As others have said User Level Security implementation does take a bit of time to get the hang of and is tricky to work with.

I would think that using an MDE file for the front end, hiding the linked tables at startup and performing a backup of the backend db at regular intervals will be a relatively secure system to work with in the short term at least.

If a user figures out how to unhide the db window of the front end and delete a table object they will only delete a link which is not serious. Of course someone could open a table and delete all items in a table which would be a bit of a disaster but this would be highly unlikely to come about by accident. If a backup is done on a daily basis then only a days data could be lost. There are many things a malicious person might do and it is probably impossible to safeguard against all scenarios e.g. the computer where backend resides could be stolen.

Hope this helps with your decision making

Steve
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13749639
>Make sure to store a copy of your database off-machine when you are experimenting with ULS.
An extremely good idea.  Even Access developers can get hit by a bus / win the lottery / switch jobs, and have the next person not know where the 'Admin' password is.
0
 

Author Comment

by:onestopfinancial
ID: 13752215
cactus data....i installed the C2DbProperties Add-in, i was able to change the properties but when I clicked Write Properties and tried to reopen the "locked down" database the changes did not take effect...any ideas....is it because im using Access 2003 and not access 2000
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 13752241
I don't use these features myself except for some Access 97 apps so I don't know.
But write John about it. He will certainly be interested to learn about any incompatibility issue.

/gustav
0
 

Author Comment

by:onestopfinancial
ID: 13752380
LSMConsulting....i have split 2 copies of the database to create a development and live fe/be setup (http://experts-exchange.com/Databases/MS_Access/Q_21382211.html) as stephen suggested.....

now i want to implement the Workgroup Informaiton file on the development database before i go live...will i be able to change the database the workgroup file is associated with, or will i have to go through the same process on the live side once i get it right on the development side?
0
 

Author Comment

by:onestopfinancial
ID: 13752781
ok experts.....i created the workgroup information file, i created the users, assigned the permissions to the User group, assigned the users to the User group....how do i get the users a password to log on with?

i went to one of the user machines and "joined" the workgroup by finding the WIFile on my machine where the DB resides....
0
 

Author Comment

by:onestopfinancial
ID: 13753207
nevermind, i read through jimhorn's FAQ, i got all the users setup with passwords....i dont understand item #10 in quesiton 1 in the FAQ, what does that mean?
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 900 total points
ID: 13753297
This is the last step in properly securing your database; failing to do so could allow a malicious user to open the database, which you of course don't want to do.

You would simple (a) log onto Access as a member of the Admins group and (b) import all your objects to that new file. This would be done AFTER running the security wizard but BEFORE you set object permissions, etc ... since you'll have to re-do it if you've already done so.

Also, you'll probaby be better off providing your endusers with a desktop shortcut that points them to the correct workgroup. Build a desktop shortcut and set this as the Target:

"Full path to MSACESS.exe" "Full path to your db" /wrkgrp "Full path to your workgroup"

Note you can use UNC paths here ...
0
 

Author Comment

by:onestopfinancial
ID: 13753646
LSMconsulting....part (b) of your last comment....does it matter if i have already split the db (fe/be)?
0
 

Author Comment

by:onestopfinancial
ID: 13753727
seems like i should have secured the DB first, then split the database into fe/be.....does the order matter between security and splitting?
0
 
LVL 85
ID: 13753950
No, just probably easier to secure and then split ... just do the same thing to both frontend and backend.
0
 

Author Comment

by:onestopfinancial
ID: 13754466
i dont understand why i would create a blank database (which hasnt been secured) and import all the objects from the security enhanced database created by the security wizard (in an effort to resolve item # 10 in question #1 in the following link http://support.microsoft.com/default.aspx?scid=%2Fsupport%2Faccess%2Fcontent%2Fsecfaq.asp) into the new database?!?!

do i then have to go through the security wizard again on the new database?
0
 

Author Comment

by:onestopfinancial
ID: 13754479
or do i just join the workgroup through the workgroup administrator in the new database?
0
 

Author Comment

by:onestopfinancial
ID: 13754968
IT WORKS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

thanks for all the responses, gonna split points
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question