none
security control on rows RRS feed

  • Question

  • Hi Experts, I have a table like this:

    col1        col2       col3       Owner       Col4...

    1           2             3        domain\a       4

    5          6              7        domain\b       8 ...

    .............

    I want to ensure that only the owner can update/delete their records.

    For example: only domain\a can update/delete the first row

    Please share your ideas thank you!

    Thursday, February 2, 2012 2:59 PM

All replies

  • Well, use a filter in your where clause

     

    UPDATE <table>
    SET Col1 = <New Val>, Col2 = <NewVale>
    WHERE OWner = @Owner

     

    Same for the the delete

    Ideally, you need to create stored procedure and pass owner as parameter

    e.g.

    DELETE FROM Table WHERE Owner = @Owner AND <otherColumn> = 'Criteria for other column' etc.

    • Edited by Chirag Shah Thursday, February 2, 2012 3:06 PM
    • Proposed as answer by Manish_Kaushik Thursday, February 2, 2012 3:08 PM
    Thursday, February 2, 2012 3:03 PM
  • I cannot control the user of this DB. I mean to tell them using a where clause just like telling them do not update other members' data, right? They have read/write permission on this DB. They can update/delete the data at will. I just want to ensure no matter how they play this DB, they can only play their own data.

    Thursday, February 2, 2012 3:08 PM
  • It looks like you are talking about implementing row level security... AFAIK SQL server does not have such feature implemented yet...

     

    You may want to read

    Implementing Row- and Cell-Level Security in Classified Databases Using SQL Server 2005

    http://technet.microsoft.com/en-us/library/cc966395.aspx

     

    Othewise as suggested do not give user direct access to the table  and  use a view and restrict the use to update and delete through view.



    • Edited by Chirag Shah Thursday, February 2, 2012 3:18 PM
    • Marked as answer by SSAS_user Thursday, February 2, 2012 3:22 PM
    • Unmarked as answer by SSAS_user Thursday, February 2, 2012 3:48 PM
    Thursday, February 2, 2012 3:16 PM
  • Hi,

    Try to check with the current user.

    Before update check the user. If current user & DB user are same then update else return you value.


    Cheers,
    Rey Singh
    Thursday, February 2, 2012 3:19 PM
  • I just found using a view can achieve this.

    @REY -Singh, it seems you are talking a different way, could you give me a sample? Thank you!

    Thursday, February 2, 2012 3:24 PM
  • Hi Chirag,

    One question is: after deny access to the table, does that mean the user cannot update/delete on base tables?

    Thursday, February 2, 2012 3:51 PM
  • >>One question is: after deny access to the table, does that mean the user cannot update/delete on base tables?

    Right, you do not want to give user direct permission on the base table.  Create view and give user permission through (select, update, delete) etc. on view. 


    Thursday, February 2, 2012 4:49 PM
  • But the problem is that they have to read/modify the base table. And they should be able to do that.
    Sunday, February 5, 2012 7:27 AM
  • Any progress?

    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES

    Friday, February 10, 2012 2:50 PM
    Moderator