asked on

SQL Row Level Security

Hi,

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.
1. BasicDetails
2. PayDetails
3. ReviewComments
4. DirectorsComments

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.
i.e.
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:

tblReources
---------------
ID Permission
1 BasicDetailsRead
2 BasicDetailsChange
3 PayDetailsRead
4 PayDetailsChange
5 SupervisorCommentsRead
6 SupervisorCommentsChange
7 DirectorsCommentsRead
8 DirectorsCommentsChange

tblSecurityMapping
-----------------------
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.

TIA

ASKER CERTIFIED SOLUTION

8080_Diver

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

businessits

ASKER

Thanks for answering that. That gives me another learning curve to find out the best way to implement that.
Have you an idea for the best way to say check the permissions on a Select statement for all employees and only show ones with permission. Just a bit lost on how the AND comparison would fit in..
i.e. Select * from tblEmployees (would change to a view through with user having no table access) where user has access (not sure how to get it in there).

Coming from a more vb background, I would think of getting all employee id's into an array, and for each value doing a lookup on permisson table, then AND it to see if user can access, then build a new array with employee id's they have access to, then perform a select statement from that.
Have I lost you? I think i lost myself in that.

GlobaLevel

...if you want to do this on the client ...which is what I would do(if so what language are u using java, asp.net?)...or do you want to run this on the sql server..probably have it run through a nested sproc...the reason I prefer client side..is bc..less hits on the db..but it depends how your system is setup..

8080_Diver

If you are checking for a specific permission (or set of permissions), it is actually failry simple.

SQL-1, attaChed should provide a selection of EID's with a given permission or permission set.

NOTE: This is based upon the sample tblSecurityMapping information I presented.

SELECT  EmployeeID
       ,UserID
       ,Permissions
FROM   tblSecurityMapping
WHERE  (Permissions & @SelectedPermissions) <> 0;

Open in new window