Security Question

[Garridon]

On a suspense database I inherited, my boss has asked if the following can be done:

Some individuals will have access to full editing of each record. Others, she wants them to be able look at records and be able to edit say, the status field, but not the Closed field. Meaning that they would have permission to update the status field, but they wouldn't have permission to update the closed field. Can this be done?

The database currently has VBA code that runs passwords that restricts access of departments (that is, Department A can't look at Department B's records). And I'm pretty confused about the Microsoft Security and how to make it work.



Linda Adams (Garridon@aol.com)
"The Importance of Being Grammarian," published in The Toastmaster, March 2001

http://www.geocities.com/hallona_2000

http://www.david-hedison.com

 

[ghubbell]

Hi Linda,
This is an example and you'll have to adjust it carefully for your needs. Leave all of your users on their regular "System.Mdw".

Make one shortcut and paste it on each of their desktops and its Target line should read something like this:

"\\ThisComputer\C\Program Files\Microsoft Office 97\Office\MSAccess.exe" /excl /wrkgrp " Z:\Somefolder\Linda.mdw" "Z:\SomeFolder\LindaDB.mdb"

No kidding, you have to be really careful with spaces etc. but the point here is the path forces the user through the specified workgroup only when using the shortcut. Remove the "excl" part if you open for shard use. Good luck. It might take a couple of trys but it will work! Gord
ghubbell@total.net

 

[ghubbell]

Linda! Sorry about that! I slapped that last post into the wrong thread...also named Security... (not that the info above isn,t too far off security issues)!
For yourself, I would highly recommend using the Tools-Security-User Level Security Wizard. This is a wonderful tool with easy step by step instructions about groups and users. Try it with a copy of your database (or two) until you feel comfortable. If you set it up nicely, you can rip the VB code out and let Access do all the work for you!
Gord
ghubbell@total.net

 

[Garridon]

Thanks! I'll give it a try. Maybe I'll have better luck than the last time I tried it. It worked on my computer, but when I put it up into the network directory, all the permissions disappeared!

Linda Adams (Garridon@aol.com)
"The Importance of Being Grammarian," published in The Toastmaster, March 2001

http://www.geocities.com/hallona_2000

http://www.david-hedison.com

 

[RickSpr]

Linda,

You'll most likely benefit from using User-Level Security, but it's not enough for the situation you describe. The problem is that permissions are granted to the entire table or query, not to a field.

To control access to a field, you'll have to do one of two things:
1. Create different tables for fields with different permissions, giving the tables the same keys and creating one-to-one relationships between them. Write queries that join the tables, and use those instead of the original table everywhere else (queries, forms, reports, macros, and modules). Since your database is already built, this would probably be very messy.
2. Store your field-level permissions in a protected table (only accessible to the administrator), and write code to look up the current user's permissions. Based on that, you can set the Locked property of fields the user can't update. You could even set Visible to False if there are fields a user shouldn't even see. You'll also have to create a field-permissions form for the administrator, or at least document how you've implemented it so he/she can update the field permissions table directly.

Of course, you could hard-code the user id's all over, but I assume you've already seen how impossible that is to maintain, with your department-level security code. Rick Sprague

 

[ghubbell]

Hi Linda,

I am in full agreement with Rick now that I have my specs on and have found the right thread! Do you have an employees or users table you could work from? These are commonly tables that are locked out to all but administrators, and all it really takes is one additional field: True/False and a bit of code to check its state. Let us know if you'd like to do something like this as I do personally use a similar arrangement for field level security in an ISO certified system. In such a system the controls are also sequence sensitive so you'd have lots of code to choose from! Gord
ghubbell@total.net

 

[Garridon]

Yes, it already has tables with users in each department). But if you know anything about Access, you can bypass the protections the code supposedly provides by pressing F11!

My boss is having me downgrade an existing database that I worked on and put Microsoft permissions on it to use instead. My plan is to put the field on the data entry form but lock it so that it can't be edited. Then a button will take the user to a form where it can be edited--but I'll set the permissions so not everyone can use it, if I'm understanding how this works properly. What I'm currently have trouble with is testing it. I entered a test user into one of the groups, but I can't add a password for the user name. According to Microsoft, you can supposedly type in the user name and log on with a blank password, then go in and update the password--but it tells me I'm not authorized when I do this. How do I work with this?

Linda Adams (Garridon@aol.com)
"The Importance of Being Grammarian," published in The Toastmaster, March 2001

http://www.geocities.com/hallona_2000

http://www.david-hedison.com

 

[ghubbell]

Step 1: Backup the entire Db in the event you lock yourself out!
Do you have the wizard available to you to set up the security? Best way I can describe this to you is to try and copy the database while "working through" the System.Mdw which is hopefully still "stock". Let the wizard set this up for you and make sure-sure you are in as the owner or an administrator, and have every permission available before continuing. You don't have to be fancy here if you plan to go to field level security, but you certainly need access to all the goods. If you don't want to try this you'll have to see if you can get to the user groups~permissions stuff and try to change the owner to yourself or Admin. Write back while I dig out some nifty code for you to use for the other stuff...(F11 and shift on start are easily removed...) Gord
ghubbell@total.net

 

[HenryAnthony]

Gord,

You slapped in this code:

"\\ThisComputer\C\Program Files\Microsoft Office 97\Office\MSAccess.exe" /excl /wrkgrp " Z:\Somefolder\Linda.mdw" "Z:\SomeFolder\LindaDB.mdb"

So what does it do?

Best regards,

Henry

 

[ghubbell]

Ya, blind Gord was supposed to put that in Thread181-65145 ! Gord
ghubbell@total.net