Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


SQL Row Level Security

Posted on 2011-04-19
Medium Priority
Last Modified: 2012-05-11

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.
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  Permission
1   BasicDetailsRead
2   BasicDetailsChange
3   PayDetailsRead
4   PayDetailsChange
5   SupervisorCommentsRead
6   SupervisorCommentsChange
7   DirectorsCommentsRead
8   DirectorsCommentsChange

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.

Question by:businessits
  • 2
LVL 22

Accepted Solution

8080_Diver earned 2000 total points
ID: 35433145
A simple control may still involve the use of that integer column (I'd make it a BigInt, though ;-).

However, I would change your Permissions coding as shown below.

 ID   Permission
  0   No Access
  1   BasicDetailsRead
  2   BasicDetailsChange
  4   PayDetailsRead
  8   PayDetailsChange
 16   SupervisorCommentsRead
 32   SupervisorCommentsChange
 64   DirectorsCommentsRead
128   DirectorsCommentsChange

Open in new window

By doing so, you can, for instance, change your Security Mapping as follows:
ID   EmployeeID   UserID    Permissions
1    5                    1          15
2    7                    2           1
3    7                    3           1
4    7                    5           1
5    7                    5          17

Open in new window

Then, after you get the user logged in, you can get their HR Access Permissions in one value (e.g. 15 for EID 5).  Once you have that Permissions Code, if the user tries to access, for instance, the Cirector's comments in any way, you AND the codes for the Directors Comments access being requested (e.g. for the Director COmments Read access, you would check Permission Code AND Diretor Comment Read Code or, in this example, 15 AND 64) which would yield a result of 0, meaning that the user does not have access to that data.

Simlarly, though, if EID 5 is trying to modify the Pay Details, their Permission Code of 15, when ANDed with the Permission Code 4 for changing the Pay Details would yield a 4, indicating that the user has permission to do that.

Author Closing Comment

ID: 35442186
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.
LVL 10

Expert Comment

ID: 35442270
...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..
LVL 22

Expert Comment

ID: 35442415
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
FROM   tblSecurityMapping
WHERE  (Permissions & @SelectedPermissions) <> 0;

Open in new window


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Loops Section Overview
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question