none
How to prevent users deleteing rows in sql 2012 RC0

    Question

  • I want some users to be able to update information in a row but not add or delete rows.

    In Denali there was a work around that you could give update rights on attribute groups and read only on the entity.

    In 2012 RC0 you can no longer apply (CRUD) security to attribute and you need to give update access at the entity level or they can't update the data. I have restricted the leaf name and code field to read only, but users can still delete the row.

    Any idea if this is possible now?


    David Roseneder

    Friday, February 10, 2012 6:11 AM

Answers

  • Hi David,

    In 2008 R2, the attribute group security would cascade down the attribute level.  In SQL 2012 MDS, you need to directly apply permissions to the attributes if you want certain attribute columns to be updateable while keeping the overall entity at a read-only level.

    In your case, you should make the entity Read-Only and assign Update permission to the specific attributes the users should edit.  If you do this, the users will be able to update selected attributes of existing members, but they will not be able to add or delete members.

     

    Regards,

    Val Lovicz

    Profisee

     

    Online MDS training: http://profisee.com/services/tabid/296/Default.aspx (SQL 2012 edition coming soon)

    • Proposed as answer by Val Lovicz Tuesday, February 14, 2012 10:02 PM
    • Marked as answer by David Roseneder Thursday, February 16, 2012 12:16 AM
    Tuesday, February 14, 2012 10:02 PM
  • Hello David,

    We had a similar requirement of having users be able to add and update members but not delete. This is in the 2008 R2 version, so I'm not sure if it works in the 2012 version which I haven't had a chance to work with yet.

    We added a self referencing domain based attribute to each entity that we wanted to prevent deletions on. This attribute was then set to always equal the code of the member through a business rule. Users are denied permissions on this attribute, so when they try to delete a member they are unable to because of the self referencing attribute. They should get a message similar to "Unable to deactivate Account 100 because it is currently used by Account 100". Our particular requirements require that nothing be deleted, so this works for us currently. 

    Hope this is somewhat helpful,

    Isaac



    • Edited by ialtis Tuesday, February 21, 2012 6:05 PM fixed typos
    • Marked as answer by David Roseneder Thursday, March 01, 2012 6:36 AM
    Tuesday, February 21, 2012 6:05 PM

All replies

  • Hi David,

    In 2008 R2, the attribute group security would cascade down the attribute level.  In SQL 2012 MDS, you need to directly apply permissions to the attributes if you want certain attribute columns to be updateable while keeping the overall entity at a read-only level.

    In your case, you should make the entity Read-Only and assign Update permission to the specific attributes the users should edit.  If you do this, the users will be able to update selected attributes of existing members, but they will not be able to add or delete members.

     

    Regards,

    Val Lovicz

    Profisee

     

    Online MDS training: http://profisee.com/services/tabid/296/Default.aspx (SQL 2012 edition coming soon)

    • Proposed as answer by Val Lovicz Tuesday, February 14, 2012 10:02 PM
    • Marked as answer by David Roseneder Thursday, February 16, 2012 12:16 AM
    Tuesday, February 14, 2012 10:02 PM
  • Thanks Val

    I had tried this combination earlier and did not get the desired result. Have retried it and now works like a charm. Users can only edit the selected fields and can’t add or delete rows.

    I had run the dpSecurityMemberProcessRebuildModel after making both changes. But, This time I waited a good half day before testing it. Suspect that there must be some additional caching occurring in the Excel addin that delays the full effect of security changes. Can live with this behaviour as the speed improvements in the Excel Addin are worth it and security changes only done infrequently during development.

    Would also like to set up security to enable a user to add, edit rows but not delete them. But I think this is not possible in RC0.  

    regards

    DAVID ROSENEDER


    David Roseneder

    Thursday, February 16, 2012 12:16 AM
  • Hello David,

    We had a similar requirement of having users be able to add and update members but not delete. This is in the 2008 R2 version, so I'm not sure if it works in the 2012 version which I haven't had a chance to work with yet.

    We added a self referencing domain based attribute to each entity that we wanted to prevent deletions on. This attribute was then set to always equal the code of the member through a business rule. Users are denied permissions on this attribute, so when they try to delete a member they are unable to because of the self referencing attribute. They should get a message similar to "Unable to deactivate Account 100 because it is currently used by Account 100". Our particular requirements require that nothing be deleted, so this works for us currently. 

    Hope this is somewhat helpful,

    Isaac



    • Edited by ialtis Tuesday, February 21, 2012 6:05 PM fixed typos
    • Marked as answer by David Roseneder Thursday, March 01, 2012 6:36 AM
    Tuesday, February 21, 2012 6:05 PM
  • Thanks Isaac,

    Your trick works in 2012 RC0 as well.


    David Roseneder

    Thursday, March 01, 2012 6:37 AM