controlling access to a column RRS feed

  • Question

  • I read in the latest security paper for SQL Server that I can deny read to a column. In Oracle, I see this as label based accessed control without the views .. so it's nice. I didn't know I could do this in SQL Server. Is this a replacement for LBAC or more of a workaround. What am I losing here vs a full out LBAC solution since the SS paper notes that SS doesn't support LBAC natively.

    If I did a SELECT * -- and I was denied a read to a column, would I just get the columns I can read back, or would it be an authorization error?
    Tuesday, December 30, 2008 3:53 PM

All replies

  • To restrict access to a column


    1. select the table or view
    2. open the "properties" dialog
    3. choose the "permissions" page
    4. If they are not already there add the users and/or roles that you want to restrict
    5. choose "select", "Update", or "References" in the permission list (these are the only ones that support column level permissions)
    6. click on "Column Permissions" near the bottom
    7. choose the permissions that you want
    8. click "OK"

    Using T-SQL (an example):

    DENY SELECT ON MySchema].MyTable (Column1, Column2) TO MyRestrictedRole;<BR> 

    If you deny SELECT to a user/role for a column then "SELECT * FROM MyTable" will produce a permission denied error.

    For more information see: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/c32d1e01-9ee9-4665-a516-fcfece58078e.htm


    Tuesday, December 30, 2008 5:23 PM
  • The "Column Permissions" button is grayed-out.  any idea what's wrong?
    Thursday, March 12, 2009 6:12 PM
  • If you do SELECT *, you will get an error. As for the disabled button, my guess is that you need to select a permissions for which you can go column level (i.e., click on SELECT and the button is enabled, and click on INSERT and it is disabled).
    Tibor Karaszi
    Thursday, March 12, 2009 6:22 PM