SQL Row Level Security

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

I have read the MSDN article 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
    LVL 22

    Accepted Solution

    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

    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

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

    Expert Comment

    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

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now