locked
My implementation of Label Based security works but ... RRS feed

  • Question

  • Hi All

    This is my first post, so bear with me :)
    I have implemented row based security based on the article "Implementing Row- and Cell-Level Security in Classified Databases" and it works for all normal users. I have one category and 9 labels, non hieracial.

    My problem is that if you have the server role "sysadmin" , then your resulting security label will be corrupt = "<Label />" and you wont se any data in the application, even not those you create yourself! They get hideen (or more accurate forbidden, when they are security tagged by the insert trigger)

    I have tested with both normal Windows users and the AD security groups and SQL users with different combination of role memeberships - but same result every time.

    I cant see any restrictions in the script genereated by the "Security Label Toolkit" that could point in the direction that sysadmin arent allowed to run certain funcions og procedures.

    Anyone who recognises the problem?

    Best Regards

    Kim Ortvald
    Software Developer and Team Lead
    University Of Copenhagen
    Denmark

    Friday, December 2, 2011 8:42 AM

Answers

  • I cant see any restrictions in the script genereated by the "Security Label Toolkit" that could point in the direction that sysadmin arent allowed to run certain funcions og procedures.


    I took a cursory look at the usp_GetUserLabel proc and it uses EXECUTE AS USER and IS_MEMBER to determine if the specified user is a member of a database role.  I suspect the issue may be that 'dbo' is passed for sysadmin role members (who are automatically 'dbo' in all databases).  Since the dbo user cannot belong to database roles, IS_MEMBER will always return zero when 'dbo' is passed as the @username,

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Kim Ortvald Wednesday, December 7, 2011 12:21 PM
    Tuesday, December 6, 2011 2:16 AM

All replies

  • That article is quite long, and I have not read it. I assume that the trigger you talk about is the INSTEAD OF trigger in appendix A. I cannot see any labels being created in the trigger code itself. I get the impression that this happens in the procedure usp_GetUserLabel, but the code for that procedure is not included in the article.

    So it is quite difficult to tell what is going on. Any chance you could post to that procedure?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, December 2, 2011 10:58 PM
  • The Label Security Toolkit implements all the procedures and tables required. That implementation is scriptable and I have the script, but its quite large so i link it here


    Best regards Kim Ortvald Software Developer and Team Lead University of Copenhagen Denmark
    Sunday, December 4, 2011 7:59 PM
  • The Label Security Toolkit implements all the procedures and tables required. That implementation is scriptable and I have the script, but its quite large so i link ithere <http://www.k31k0.dk/sql_script.zip>

    So the idea is that I or someone else should download and install all that, and then debug our ways through it?

    I don't know how big this is, since I have not worked with it. But either it is fairly simple to isolate the particular problem you are talking about, and in that case you could do that part of the work yourself, and only post the pertinent part. Or, the toolkit is really so vast that finding all the relevant code is a daunting task for you. The question is then whether it's a tall order for everyone else here...

    We'll see if anyone else bites. Right now, I'm trying to get a few other things out the door, so I pass for now.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, December 5, 2011 9:45 PM
  • I cant see any restrictions in the script genereated by the "Security Label Toolkit" that could point in the direction that sysadmin arent allowed to run certain funcions og procedures.


    I took a cursory look at the usp_GetUserLabel proc and it uses EXECUTE AS USER and IS_MEMBER to determine if the specified user is a member of a database role.  I suspect the issue may be that 'dbo' is passed for sysadmin role members (who are automatically 'dbo' in all databases).  Since the dbo user cannot belong to database roles, IS_MEMBER will always return zero when 'dbo' is passed as the @username,

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Kim Ortvald Wednesday, December 7, 2011 12:21 PM
    Tuesday, December 6, 2011 2:16 AM
  • Thank a lot. It explains the odd behavior.
    Best regards Kim Ortvald Software Developer and Team Lead University of Copenhagen Denmark
    Wednesday, December 7, 2011 12:21 PM