"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
>
>
>

> 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

"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
>
>
>

> 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:

> brem219 wrote:
> > 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.
>
> --
> Joan Wild
> Microsoft Access MVP
>
>
>

"Joan Wild" wrote:

> brem219 wrote:
> >
> > 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
> 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.
>
> "Joan Wild" wrote:
>
>> brem219 wrote:
>>>
>>> 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
>> Microsoft Access MVP