Re: restricted user level security

Thank you for your response. I want to tell you the steps I took, and
hopefully you can help me understand why I'm not getting the correct results.

On the Main table, I added a field called User (FirstName LastName),
corresponding with the access user name, to go along with Supervisor
(LastName). All of my queries and macros are built off the Supervisor name,
so I didn't want to change that. Here are the steps I took to try to give
each Supervisor access to only their own work items:

1) I removed all of the Supervisors' permissions from the Main table where
everyone's records are stored.
2) I added a text box where the User automatically fills in on the Add form
(for adding new records) and the Change form (for changing existing records).
3) I changed the queries of all six Supervisors to include the User field
and the criteria of =CurrentUser
4) In the record source of the Add form, I replaced the Main table with a
query, the query includes all of the fields and the CurrentUser() criteria.
I changed the Run Permissions to Owner.

I exited and logged in under the name of user Jane Doe to test. When I went
to Jane's opening form and tried to enter the Add form, it said "Records
cannot be read, no read permission on Main table" and did not open the form.
When I opened the Change form, it did open, but Jane's eight records were no
where to be seen. I hope you can help me sort this out. Thank you.

"Joan Wild" wrote:

You can read more about RWOP in the security white paper, and also at
http://www.jmwild.com/RWOP.htm

Instead of having the user choose their name from a list, just set the
form's recordsource to a query that uses a criteria of CurrentUser().

If you need more help on this, please tell me how the current 'last name'
looks up their records? Is their Access username the same as their last
name?


--
Joan Wild
Microsoft Access MVP

brem219 wrote:
Security is now in place, and each user has their own password. The
opening form asks each user to type in their last name and leads them
to their own form, where they can view a report of their work or add
and change work items. Still, their needs to be a feature added to
prevent a user from entering another's name and looking at their
work. I'm unclear what you mean by a RWOP query.

Also, how do you adjust the permissions so the users can add and
change items in tables, but cannot view them? Thanks.

"Joan Wild" wrote:

brem219 wrote:
Good afternoon,
I am creating a database designed to monitor an organization's
current workflow. I want to create secure access for a group of
six people and myself. The workflow is stored in a table and
organized by name. The forms are designed so that each member can
view and make changes to their own work, and view reports. I want
the security aspect to limit the database so each user will only be
able to view their own work and are unable to view or change anyone
else's. However, the manager has a special form where he has the
ability to view his own work and that of the five others. Can I
accomplish this without having to create separate workflow tables
and queries for each user?

Yes you can. You indicate that your workflow table has a 'name'
field in it. By the way, that's not a good name for a field -
conflicts with an Access property name.

Anyway, you can set up user level security.
Study up on it and practice on a copy of your mdb, until you get it
right. Security FAQ
http://support.microsoft.com/?id=207793

Security Whitepaper
http://support.microsoft.com/?id=148555

I've also outlined the detailed steps at
www.jmwild.com/AccessSecurity.htm

Since you already have a field for the 'owner' of the record, set up
the usernames in security to match the name you used in this field.

You can use the CurrentUser() function to retrieve the Access
username.

What you'd do is implement security, and remove all permissions from
the tables. Use a RWOP (run with owner permissions) query for your
form. In this query you can set a criteria under the 'name' field
of CurrentUser(), and only that person's records will appear.


--
Joan Wild
Microsoft Access MVP



.

Relevant Pages

  • Re: Search for accounts based on advanced security permissions
    ... to query the security attributes and to change permissions and security ... Search for accounts based on advanced security permissions ... don't think there is any easy way to query for all users. ...
    (microsoft.public.windows.server.active_directory)
  • RE: Permissions causing error on export?
    ... The Access security permissions might limit a user if they con't have ... I assume they can run the query fine within Access? ... Do MDW security settings affect VBA programming in using export/import ...
    (microsoft.public.access.security)
  • Re: How to apply security to a column in a table?
    ... You need to implement user-level security. ... You can deny all permissions to the tables, and instead use 'run with owner ... permissions' queries. ... the underlying tables based on the query owner's permissions on the tables. ...
    (microsoft.public.access.security)
  • Re: Make linked tables read only?
    ... >> You can accomplish this by implementing security and giving minimal ... >> permissions on the tables. ... Just to add an easier method:- you could show your users MS Query. ... You can combine the two if you like, with user level security protecting ...
    (microsoft.public.access.tablesdbdesign)
  • lame server messages in named.log
    ... Mar 30 05:42:30.526 security: info: client 202.52.250.176#1052: ... query (cache) denied ...
    (RedHat)