I have read the MSDN article http://technet.microsoft.com/en-au/library/cc966395.aspx
on this topic and they seem to skip alot on the design, so might need someone to help out a bit and maybe suggest some changes or another way to do what we require.
We are doing an in house HR database for various reasons and obvioulsy security and levels of access are very important.
Can someone advise the best way to achieve;
Each Employee will have several types of information.
We require a granular way of restricting access to this information. Each user/group/role etc can either have no access, read or change.
Now most of the articles I have read say add an extra integer column to the employees table for the managers_id, and then do a view with a where managers_id = current_user_id (or something to that effect).
Issue is that more than one person has to be able to read and/or change this information.
Happy to clarify info...
I was looking at using SQL Roles, but there may be a need to add a specific user to say read the "DirectorsComments' on a particular employee, so we would end up with too many custom roles. And i would like to build a security interface to allow the HR Manager to set the security access.
So this leaves tables with permission mappings.
we have a table called tblEmployees for all thier basic details
we have a table called tblUsers for user logins, only a few and maps to windows username.
we have a table tblPayDetails for employees pay history
we have tables tblSupervisorComments and tblDirectorsComments
Tables I am thinking about for controlling access are:
ID EmployeeID UserID PermissionID
1 5 1 1
2 5 1 2
3 5 1 3
4 5 1 4
5 7 2 1
6 7 3 1
7 7 5 1
8 7 5 5
But not sure how to put it together to make it work.
I gues it would be different views so that when doing a "Select * from tblEmployees Where xxxxx" so that it is evaluated and only a list of employees who the current user has say BasicDetailsRead permission is returned.
The other issue I see here is if the user leaves and a new user is created, all the permissions would have to be set again. But by doing it in roles I would need too many...
Any help is much appreciated.