locked
Hide data for specific column from a user RRS feed

  • Question

  • Greetings,

    I have a problem. I would like to restrict showing specific users (or roles or schemas) data in a column. First I thought DENY option will do it but figured out that it won't. DENY denies the column being in the SELECT query. I would still like to call the column but if access to the data in that column is restricted it would show only null values and not the real ones.

    So... I would like call the next query from user TestUser:

    SELECT ID,Name,Address

    FROM Employee

    But if access to Address is restricted for TestUser I would get results like:

    ID Name Address

    1 John Smith NULL

    2 Rick Ross NULL

    ...

    Is it possible to do something like that in SQL Server 2005/2008? I was thinking of storing that kind of information in a table (for which tables, which columns for which users access is restricted) and then manipulate it on the application site but my programmers tell me that they use components which always call the full table (like SELECT *). 

    Thank you for your answer.

    Monday, August 23, 2010 12:16 PM

Answers

  • You can though estrict the access to the data to be accessible through a view only. With this technique, you don´t need to change yur application logic for implemening encryption / decryption. On the other side this will also let other users not see the column as well. There might be also another option for you like Row level security by implementing a meta data table where access is determined and joined to the actual data. This of course needs additional maintaince work as you will have to keep the security dataup-to-date.

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    Wednesday, August 25, 2010 11:51 AM
  • Thank you for both replys.

    I created an application that can manage this stuff. I'm using a meta data table, also because of the application logic I need to rename a table and create a view with the old table name. The view then checks if user has permission to see the data. Also some triggers are needed. 

    Encryption was another way to go, but than users would be a bit confused :) I really wanted to have those columns set to null value.

    Thanks again,

    Dejan Podbregar

    Wednesday, August 25, 2010 12:23 PM

All replies

  • Or if there is anyway of catching queries like SELECT * FROM employee and changing it to SELECT ID,Name FROM employee if DENY SELECT (Address) is in effect? 

    Best Regards,

    Dejan Podbregar

    Monday, August 23, 2010 12:23 PM
  • Hi Dejan,

     

    According to your description, you don't want to display some sensitive information to the specified user. If I have misunderstood, please feel free to let me know.

     

    Based on my research, we can use REVOKE function to revoke permission on a column in a table. However, if we use SELECT * to query all fields of a table, it will throw an error.

     

    In this case, we recommend that you can use encryption algorithm to encrypt a column, and then protect or hide such sensitive data. Using encryption can make the data useless without the corresponding decryption key or password.

     

    For more information, please visit the following links:

    SQL Server Encryption: http://msdn.microsoft.com/en-us/library/bb510663.aspx

    How to: Encrypt a Column of Data: http://msdn.microsoft.com/en-us/library/ms179331.aspx

    REVOKE Object Permissions: http://msdn.microsoft.com/en-us/library/ms187719.aspx

     

    If you have any more questions, please feel free to let me know.

     

    Thanks,

    Yoyo Yu

    Wednesday, August 25, 2010 8:18 AM
  • You can though estrict the access to the data to be accessible through a view only. With this technique, you don´t need to change yur application logic for implemening encryption / decryption. On the other side this will also let other users not see the column as well. There might be also another option for you like Row level security by implementing a meta data table where access is determined and joined to the actual data. This of course needs additional maintaince work as you will have to keep the security dataup-to-date.

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    Wednesday, August 25, 2010 11:51 AM
  • Thank you for both replys.

    I created an application that can manage this stuff. I'm using a meta data table, also because of the application logic I need to rename a table and create a view with the old table name. The view then checks if user has permission to see the data. Also some triggers are needed. 

    Encryption was another way to go, but than users would be a bit confused :) I really wanted to have those columns set to null value.

    Thanks again,

    Dejan Podbregar

    Wednesday, August 25, 2010 12:23 PM