Limit Data that Users see in Database

[katkth7533]

Hi. I am creating an Action Item Database that will be located on a Sharepoint portal so multiple users can access and I'd like each user to have a login/ password to 1) keep unauthorized users out of the database and 2) limit what users can see. This login/ security should ideally work so that user "Bob Smith" logs in with his assigned login/ password and he will only view/edit/ run reports for those Action Items that he has generated (field named "GeneratedBy") or have been assigned to complete ("ResponsibleLead") which are both fields in table "tbl_ActionItems". I can not tie the security levels to a users network login; this has to be security within the database. Any suggestions as to how to do this? I don't necessarily need specific code at this point so much as I need a logical starting point for setting this up. Any pointers/ suggestions are greatly appreciated. Thank you.

[orange]

You should research Role Based Access Control.
Do not assign privileges directly to people. A person's role may change and you want the access control to follow/be synchronized with that change in roles.

Generally:
Eg.
Role
---Reader is allowed to read any record in the tables, but can not create,change or delete
---Editor is allowed to read and change any record but can not create or delete
---Owner is allowed to create, change but can not delete records for which he/she is owner
---other various roles
---Administrator - is allowed to create, change or delete records, AND can modify role assignment

People ---->PeopleRole<----Role

More info here in ppt slides

[katkth7533]

Thank you for the reply Orange. I will look into this but I am not sure if it will do what i need. There could be 1,000 entries into the database and we want people to see ONLY those entries that apply to them. Not sure how the Role Based Control can determine which entries belong to which person since multiple people will be assigned as "editor", etc. Again, I only want the user to see those items assigned to them. But thanks.

[orange]

You have to determine Who can do what to What under which conditions.
The only other way I can think of quickly is to have a person's initials or code number on each record they own/create or whatever. Then when ever a query or form or report is accessed, that query,form, report must have code/criteria to restrict the data to ONLY those records matching the person/login id.

It seems to me you would need a master account if an "administrator type person" wanted to see records from all or many people.

Search the forums for loginId and restricted access to records or similar

[katkth7533]

I think you are right in that approach. I think I need to start with a big pot of coffee then dig in! Appreciate the response. I will keep searching

[orange]

NOTE:
With any kind of access control there has to be discipline. That is, you can not give a full copy of MSAccess to people who could edit/delete/create tables and records as they wish. With any access control, users must login through approved means and only get access to objects or routines via controlled and managed processes.

Think of it bit like a bank they don't just give everybody money or access to all customer files. You have a PIN and an account(s). Your account number(s) and your PIN allow you to "use" your account(s). Some bank agents can see transactions(readOnly) but their actions are monitored( and recorded) to ensure no misuse of privileges.
In many banking processes, 2 or more people have to "OK/authorize" certain actions.

and yada yada yada.... you know this stuff..

[katkth7533]

Thank you. I am going to try to map it out on paper and use this approach.