SharePoint - Secure/Restrict list access by a field on that list

I have a Sharepoint list that I need to secure based on a field in the list - "person assigned".  Example:  Everyone needs to see their own records, a few people need to see 3 or 4 people's records, and a couple of people need to see all of the records.

The related solution below seems close, but it only restricts the view, not the actual access to the list.  I have people entering records using an MS Access front-end with tables linking to the Sharepoint list.


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

If you separated your columns to external lists and had your main list "look up" these other lists to get it's values, could you then apply security on this other lists.
If that makes sense!
Dale FyeOwner, Developing Solutions LLCCommented:
Do your users actually know where the list is in Sharepoint?  Although my applications users have access to Sharepoint, I've never pointed them to that location of the list, and hide those details from them in the application.

For those people that need access to 3 or 4 peoples records, what is their relation to those that they need access to?  Are they the peoples boss?  Are the people they need access to all in the same department?  If so, would more than one person from this group of people need to be able to access the same individuals records?

I use two tables (tbl_roles, and tbl_User_Roles) to assign this type of permissions to people.  Generally, people don't need to be assigned a role to access their own record.  In other cases, people could be assigned to the Team Leader role, and be assigned to a specific team (or possibly multiple teams).

So, tbl_User_Roles might look like:

UserID    Role     Role_Value
Dale        Team   Div1
Steve      Team   Div2
David      Admin

Then. in Access, you would use a query that looks like:

Select * FROM yourTable
WHERE [UserID] = "Dale"
OR [Division] IN (SELECT Role_Value FROM tbl_User_Roles WHERE UserID = "Dale" AND [Role] = "Team")
OR DCOUNT("UserID", "tbl_User_Roles", "[UserID] = 'Dale' AND [Role] = 'Admin'"

This would let me (Dale), look at my record, or those records where the [Division] field = 'Div1' or where if I have admin rights.

As long as you have prevented your users from accessing the Sharepoint list directly, and have locked down your application so that the user cannot get to the database window, then this technique should work.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
One way that you could change the permissions on an item is to fire a workflow when an item is added which then changes the permissions for the item.

I don't think there will be a simple method of achieving this.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

MethosAuthor Commented:
This is a timesheet database - everyone needs to create and edit their own time records.  The people who need to see multiple people are managers who need to be able to see and edit their people's records.  Then I need the HR department to be able to see all of the records.

Users don't know about the Sharepoint itself.  I haven't rolled the app out or explained that part yet.

I had considered controlling access using Access queries vaguely like who you outlined above, but figured it wasn't as secure as actually controlling access up on the Sharepoint.  What's the best way to hide the details within Access?  Hide the linked tables and queries - and lock it up as an MDE?

I'm heading out for the night - but I'll be back online looking at this and trying a few things tomorrow.

- Methos
Either the workflow option proposed by Arduk or a custom event handler can achieve this. It's not provided out of the box.
MethosAuthor Commented:
I should have mentioned at the beginning... I'm on a shared Sharepoint and am not currently allowed to add custom code.  

I would rather have he security on Sharepoint itself - but I'm new to Sharepoint and don't fully understand workflows and how to use them or how it solves my particular issue.  Can someone give me the big picture view of that?
MethosAuthor Commented:
I'm pretty close to doing it the way you talked about.  I don't think there's a way for my people not to learn about the Sharepoint itself -- when you log in from the Access file, it shows the Sharepoint path.  So I setup views like the EE question I referenced - and I'm linking to that view rather than the whole list.

I think it will work for me -- and I appreciate your help.
Dale FyeOwner, Developing Solutions LLCCommented:
Unfortunately, to give your users the ability to update the info in SharePoint, they must have read/write permissions for the Lists.  I have my lists built in an out-of-the way "folder" on SharePoint, one that they are not likely to get to.

If your users can see the tables in the Database Window (or NavPane) I would strongly recommend that you hide that window.  If they can see the linked tables (lists), then they can open them and view the data directly.

Hiding the database window or navpane will make it just a little more difficult (still relatively easy) to bypass this limited attempt at security.
MethosAuthor Commented:
I read some info yesterday about how to hide tables using VB code (beyond just pressing "hide tables" which is super easy to unhide) and planned to use that VB code.  I'm not sure how to hide the table window itself - but I would love that.

I don't want them to update using Sharepoint itself - I just figured they're smart enough to get themselves there and figured I should try to secure it as much as possible with a View.  If I don't give them read/write to the list, they won't be able to do anything with the data - even linked in Access, correct?

I really do appreciate all of your time and effort in helping me figure this out.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.