locked
DENY UPDATE/DELETE/INSERT on specific columns to ALL users RRS feed

  • Question

  • Hello,

    I would like to deny the above operations on specific columns in a table to ALL users (not specific).

    how can I accomplish this?

    Regards

    Monday, August 29, 2016 2:09 PM

Answers

  • What is your definition of "DELETE a column"? DENY only works for SELECT and UPDATE. So this maybe already all you need. Otherwise you need a trigger to control an INSERT statement.

    Another approach - which I prefer - would be splitting your table into two. One where you don't need these permissions and one where you need these permissions on these columns. The it is sufficient to set the permissions on the table.

    Can you give use more information about your concrete use-case?

    Monday, August 29, 2016 2:25 PM
  • It is possible to apply column level permissions to specific columns on a table, as mentioned in this Article: -

    https://www.mssqltips.com/sqlservertip/2124/filtering-sql-server-columns-using-column-level-permissions/

    Another way is If this a specific set of columns then you could move this data into a new Table, construct a FK relationship between the two tables, and deny permissions on this new Table; this approach is more Dynamic as if you want to add more columns and still deny users permissions; the permission set would already be set and you won't have to alter various Roles..  This however would need reworking on the Application as well.  Best approach would be to have a chat with the developers.


    Please click "Mark As Answer" if my post helped. Tony C.


    Tuesday, August 30, 2016 12:26 PM
  • If you were logged in as sysadmin, there was no permission check performed, so the DENY did not bite.

    An trigger sounds like a better bet:

    CREATE TRIGGER evil_trigger ON dbo.Person AFTER UPDATE AS
    IF UPDATE (Age) OR UPDATE(Salary)
    BEGIN
       RAISERROR('Hands off! You may not update Age or Salary!', 16, 1)
       ROLLBACK TRANSACTION
    END

    • Marked as answer by ITForums Saturday, October 15, 2016 7:47 PM
    Friday, October 14, 2016 10:12 PM

All replies

  • What is your definition of "DELETE a column"? DENY only works for SELECT and UPDATE. So this maybe already all you need. Otherwise you need a trigger to control an INSERT statement.

    Another approach - which I prefer - would be splitting your table into two. One where you don't need these permissions and one where you need these permissions on these columns. The it is sufficient to set the permissions on the table.

    Can you give use more information about your concrete use-case?

    Monday, August 29, 2016 2:25 PM
  • Hello,

    I would like to deny the above operations on specific columns in a table to ALL users (not specific).

    how can I accomplish this?

    Regards

    You can do this per user/role

    DENY SELECT ON dbo.TABLE(col1, col2, ...coln) TO user/role.

    With all Options you have for the GRANT statement afaik.

    EDIT: I have to correct myself, subentity-level permissions logically can't

    work for entitylevel permissions (ALTER, CONTROL DELETE, INSERT,

    TAKE OWNERSHIP, VIEW DEFINITION, VIEW change tracking). Further you can't do

    this with special database roles

    Regards



    Tuesday, August 30, 2016 11:16 AM
  • It is possible to apply column level permissions to specific columns on a table, as mentioned in this Article: -

    https://www.mssqltips.com/sqlservertip/2124/filtering-sql-server-columns-using-column-level-permissions/

    Another way is If this a specific set of columns then you could move this data into a new Table, construct a FK relationship between the two tables, and deny permissions on this new Table; this approach is more Dynamic as if you want to add more columns and still deny users permissions; the permission set would already be set and you won't have to alter various Roles..  This however would need reworking on the Application as well.  Best approach would be to have a chat with the developers.


    Please click "Mark As Answer" if my post helped. Tony C.


    Tuesday, August 30, 2016 12:26 PM
  • thanks for the reply,

    My subject was not correct.

    I meant deny DELETE/UPDATE on specific columns.

    my use-case is an archive table with specific columns that should not be updated/deleted.

    I've tried the:

    DENY UPDATE ON dbo.Person (Age, Salary) TO SampleRole

    I have message that says, row affected.

    but when testing it by updating these specific columns of rows, I have no restrictions and the UPDATE/DELETE succeedes


    • Edited by ITForums Friday, October 14, 2016 10:02 PM
    Friday, October 14, 2016 9:41 PM
  • If you were logged in as sysadmin, there was no permission check performed, so the DENY did not bite.

    An trigger sounds like a better bet:

    CREATE TRIGGER evil_trigger ON dbo.Person AFTER UPDATE AS
    IF UPDATE (Age) OR UPDATE(Salary)
    BEGIN
       RAISERROR('Hands off! You may not update Age or Salary!', 16, 1)
       ROLLBACK TRANSACTION
    END

    • Marked as answer by ITForums Saturday, October 15, 2016 7:47 PM
    Friday, October 14, 2016 10:12 PM
  • why not just put this user in the db_datareader role only?
    Friday, October 14, 2016 10:29 PM
  • why not just put this user in the db_datareader role only?

    My understanding of ITForums that he/she/it/they want to prevent updates across the board - after all the title of the topic says "ALL users".

    Saturday, October 15, 2016 9:42 AM
  • Thank you,

    it was really a test with user with sysadmin permissions.

    tried with "db writer" permissions -> WORKED!

    I'll test the trigger later.

    Thanks again

    Saturday, October 15, 2016 7:47 PM