locked
Help me to implement a hierarchy Raw Level Security RRS feed

  • Question

  • Hi all

    I am newbie on administer and security SQL Server 2014 and my first task surpasses me so I need your help.

    I need to implement a system security based in a hierarchical permissions. I have four groups (e.g. : group A: Sellers (they can see only your data),
    group B.Supervisors (they can see data from their vendors), group C. Area supervisors (they can see the data from ther supervisors) and finally group A.the heads 
    (obviously they can see all).

    all users are authenticated through the Windows Active Directory within a Citrix platform.

    At the first moment they access data using excel and pivot tables, nobody will connect directly through SMS.

    I have read a CLS whitepaper and try to implement with the label policy designer but for any reason the tblUniqueLabel table doesn´t populute so when I create the view I find an empty table.

    So at this momment I unable to implement this type of solution, so I don´t now if I can combine nested roles with RLS.

    In short, I need someone to light me 

    Regards

    Friday, February 5, 2016 11:46 AM

Answers

  • Are you referring to the following toolkit?

    http://sqlserverlst.codeplex.com/releases/view/83460

    It should work on SQL Server 2012 & 2014 using the old model (i.e. creating views), but if you run on SQL Server 2016, it should make use of RLS.

    If you are having trouble, please let us know on this forum or on the codeplex discussion page (http://sqlserverlst.codeplex.com/discussions).

    You can also try running the toolkit version 1.0 and see if that helps you.

    -Raul Garcia

     SQL Security


    This posting is provided "AS IS" with no warranties, and confers no rights.

    Friday, February 5, 2016 7:04 PM

All replies

  • Are you familiar with hierarchyID-Datatype?

    If not I would suggest to dive into that one as it can be the foundation of your concept.

    The security-part depends on the version you are on. In SQL 2016 you will be able to use the "Row Level Security"-Feature to speed up development by far.

    Otherwise you will need to build your own framework using functions and views.


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

    Friday, February 5, 2016 2:45 PM
  • Sergio

    Please explain what do you mean by (they can see data from their vendors),?  Is there a table vendors>

    Read this article I think gives you an idea

    http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Friday, February 5, 2016 4:19 PM
  • Sergio

    Please explain what do you mean by (they can see data from their vendors),?  Is there a table vendors>

    Read this article I think gives you an idea

    http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Hi Uri

    I will try to explain as well as I can.

    I have a table with all the sales made by sellers. Inside the table there are also sales made by Chief sellers and so on. 

    There is a Hierachy like this

                A

       B      C

    D E F G

    H

    A (the boss) must be see all data, B must be see data from D,E and H and so with others.

    I have to implement this solution in SQL Server 2014 and I try with Label security toolkit but It seem I do something wrong because I unable to finish sucessfully.

    I have read that this toolkit doesn´t  work  for sql server 2012 and 2014, however other people say opposite ....

    Regards



    Security

    Friday, February 5, 2016 6:50 PM
  • Are you referring to the following toolkit?

    http://sqlserverlst.codeplex.com/releases/view/83460

    It should work on SQL Server 2012 & 2014 using the old model (i.e. creating views), but if you run on SQL Server 2016, it should make use of RLS.

    If you are having trouble, please let us know on this forum or on the codeplex discussion page (http://sqlserverlst.codeplex.com/discussions).

    You can also try running the toolkit version 1.0 and see if that helps you.

    -Raul Garcia

     SQL Security


    This posting is provided "AS IS" with no warranties, and confers no rights.

    Friday, February 5, 2016 7:04 PM
  • Are you referring to the following toolkit?

    http://sqlserverlst.codeplex.com/releases/view/83460

    It should work on SQL Server 2012 & 2014 using the old model (i.e. creating views), but if you run on SQL Server 2016, it should make use of RLS.

    If you are having trouble, please let us know on this forum or on the codeplex discussion page (http://sqlserverlst.codeplex.com/discussions).

    You can also try running the toolkit version 1.0 and see if that helps you.

    -Raul Garcia

     SQL Security


    This posting is provided "AS IS" with no warranties, and confers no rights.

    Hi Raul

    Yes, that it is. 

    As you  know the toolkit generates six tables, all of them have data (the data I have filled in the tool) except tblUniqueLabelMarking and tblUniqueLabel  which have no data at all.

    My t-sql is not good enough to find the fix.

    Thanks for the help.

    Regards


    Security

    Friday, February 5, 2016 8:27 PM