restricted user level security

[Guest]

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?

Thank you.

 

[Joan Wild]

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.

 

[Guest]

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]

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?

 

[Guest]

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]

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.

Just so I'm clear here, is a user the same as a supervisor? i.e. you have a
supervisor Wild with a User of jwild.
jwild is in the User field, and jwild is the user's access login name, and
Wild is in the Supervisor field.

Are there any users that aren't supervisors?

(by the way, it's not a good idea to use 'User' as a field name since it is
a reserved word - could confuse Access, but not the issue you're having
right now).

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.

Did you give read data permission (as a minimum) on the RWOP query that is
the recordsource of the Add form?
I find it odd that it is saying no read permission on *Main table* since the
recordsource is no longer the Main table.

The only thing that comes to mind, if you've set the permissions on the RWOP
query correctly, is that there is some other control on the Add form that is
using the Main table - a listbox, combobox, subform, code? Change these as
well to refer to the RWOP query.

Another tip once you get this working. Create a RWOP query for each table
in your database. Then you can base all recordsources, other queries, code,
etc on these RWOP queries rather than the tables. Then you don't have to
create a RWOP query for everything.

 

[Guest]

Good morning,
I have made some progress, but I am currently having some issues with the
queries.

Just so I'm clear here, is a user the same as a supervisor? i.e. you have a
supervisor Wild with a User of jwild.
jwild is in the User field, and jwild is the user's access login name, and
Wild is in the Supervisor field.

Are there any users that aren't supervisors?

That's correct, the users and the supervisors are one in the same. One of
the users is a manager, and I will give him admin priveleges anyway
eventually. Jane Doe is in the Username (changed title) field and Doe is in
the Supervisor field.

The Add form works fine now, with the exception of the Username field, which
is automatically linked to the current user. I can't get the field to fill
into the Main table. For example, when I enter something for Jane Doe, it
doesn't fill into the table under Username, it's just blank.

The main problem now is the queries behind the Change form and all the
reports are not working. Each supervisor has their own query that pulls
their work items from the Main table. Each of these six queries is identical
and includes all of the fields from the Main table. For Jane's query, a
criteria of "CurrentUser" is included under the Username field, and a
criteria of "Doe" is included under Supervisor. All of these queries have
Owner's run permissions. However, once I log in as Jane Doe, the Change
forms and the reports built on this query come up blank. If I remove the
CurrentUser criteria, the query runs fine, but then there is nothing to
prevent Jane from entering another Supervisor's main form, running their
queries, and looking at their work list. I hope you can help me sort this
out.

Thank you.

 

[Joan Wild]

That's correct, the users and the supervisors are one in the same.
One of the users is a manager, and I will give him admin priveleges
anyway eventually. Jane Doe is in the Username (changed title) field
and Doe is in the Supervisor field.

OK, that information is probably redundant, but I don't want to muddy the
waters by bringing up design.

The Add form works fine now, with the exception of the Username
field, which is automatically linked to the current user. I can't
get the field to fill into the Main table. For example, when I enter
something for Jane Doe, it doesn't fill into the table under
Username, it's just blank.

On your form, set the default value for the textbox control that is bound to
the Username field to CurrentUser()

The main problem now is the queries behind the Change form and all the
reports are not working. Each supervisor has their own query that
pulls their work items from the Main table.

By using RWOP, and making use of the CurrentUser() function you should only
need one query.

SELECT * from SomeTable WHERE [Username] = CurrentUser()

Each of these six

queries is identical and includes all of the fields from the Main
table. For Jane's query, a criteria of "CurrentUser" is included
under the Username field, and a criteria of "Doe" is included under
Supervisor.

Why do you need both these criteria? CurrentUser() = Jane Doe should be
sufficient. i.e.
....WHERE [username] = CurrentUser() when Jane Doe is logged in will pull all
the records where the Username is Jane Doe (and those will have Doe in the
Supervisor so you don't need to set a criteria on it.

All of these queries have Owner's run permissions.

However, once I log in as Jane Doe, the Change forms and the reports
built on this query come up blank.

How about you post the SQL statement behind your query for the Change
form(Query design, view SQL, copy and paste the statement).

If I remove the CurrentUser

criteria, the query runs fine, but then there is nothing to prevent
Jane from entering another Supervisor's main form,

Why is Jane entering any Supervisor's name (even her own)? The Main form
should also be restricted so that only the Current User's records *can* be
retrieved.

running their

 

[Guest]

OK, I realize now I have a lot of redundancies in my design, but I will
address those later. At least it is functional now. Thank you.

That's correct, the users and the supervisors are one in the same.
One of the users is a manager, and I will give him admin priveleges
anyway eventually. Jane Doe is in the Username (changed title) field
and Doe is in the Supervisor field.

OK, that information is probably redundant, but I don't want to muddy the
waters by bringing up design.

The Add form works fine now, with the exception of the Username
field, which is automatically linked to the current user. I can't
get the field to fill into the Main table. For example, when I enter
something for Jane Doe, it doesn't fill into the table under
Username, it's just blank.

On your form, set the default value for the textbox control that is bound to
the Username field to CurrentUser()

The main problem now is the queries behind the Change form and all the
reports are not working. Each supervisor has their own query that
pulls their work items from the Main table.

By using RWOP, and making use of the CurrentUser() function you should only
need one query.

SELECT * from SomeTable WHERE [Username] = CurrentUser()

Each of these six

queries is identical and includes all of the fields from the Main
table. For Jane's query, a criteria of "CurrentUser" is included
under the Username field, and a criteria of "Doe" is included under
Supervisor.

Why do you need both these criteria? CurrentUser() = Jane Doe should be
sufficient. i.e.
....WHERE [username] = CurrentUser() when Jane Doe is logged in will pull all
the records where the Username is Jane Doe (and those will have Doe in the
Supervisor so you don't need to set a criteria on it.

All of these queries have Owner's run permissions.

However, once I log in as Jane Doe, the Change forms and the reports
built on this query come up blank.

How about you post the SQL statement behind your query for the Change
form(Query design, view SQL, copy and paste the statement).

If I remove the CurrentUser

criteria, the query runs fine, but then there is nothing to prevent
Jane from entering another Supervisor's main form,

Why is Jane entering any Supervisor's name (even her own)? The Main form
should also be restricted so that only the Current User's records *can* be
retrieved.

running their

queries, and looking at their work list. I hope you can help me sort
this out.

 

[Joan Wild]

Glad it's working.

--
Joan Wild
Microsoft Access MVP

OK, I realize now I have a lot of redundancies in my design, but I
will address those later. At least it is functional now. Thank you.

That's correct, the users and the supervisors are one in the same.
One of the users is a manager, and I will give him admin priveleges
anyway eventually. Jane Doe is in the Username (changed title)
field and Doe is in the Supervisor field.

OK, that information is probably redundant, but I don't want to
muddy the waters by bringing up design.

The Add form works fine now, with the exception of the Username
field, which is automatically linked to the current user. I can't
get the field to fill into the Main table. For example, when I
enter something for Jane Doe, it doesn't fill into the table under
Username, it's just blank.

On your form, set the default value for the textbox control that is
bound to the Username field to CurrentUser()

The main problem now is the queries behind the Change form and all
the reports are not working. Each supervisor has their own query
that pulls their work items from the Main table.

By using RWOP, and making use of the CurrentUser() function you
should only need one query.

SELECT * from SomeTable WHERE [Username] = CurrentUser()

Each of these six

queries is identical and includes all of the fields from the Main
table. For Jane's query, a criteria of "CurrentUser" is included
under the Username field, and a criteria of "Doe" is included under
Supervisor.

Why do you need both these criteria? CurrentUser() = Jane Doe
should be sufficient. i.e.
....WHERE [username] = CurrentUser() when Jane Doe is logged in will
pull all the records where the Username is Jane Doe (and those will
have Doe in the Supervisor so you don't need to set a criteria on it.

All of these queries have Owner's run permissions.

However, once I log in as Jane Doe, the Change forms and the reports
built on this query come up blank.

How about you post the SQL statement behind your query for the Change
form(Query design, view SQL, copy and paste the statement).

If I remove the CurrentUser

criteria, the query runs fine, but then there is nothing to prevent
Jane from entering another Supervisor's main form,

Why is Jane entering any Supervisor's name (even her own)? The Main
form should also be restricted so that only the Current User's
records *can* be retrieved.

running their

queries, and looking at their work list. I hope you can help me
sort this out.