• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 991
  • Last Modified:

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.


1 Solution
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 FyeCommented:
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.

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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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 FyeCommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now