locked
SECURITY ISSUE RRS feed

  • Question

  • I'm having an issue when trying to revoke privileges from a SQL group.  The idea is to prevent certain columns being altered by specific users.

    The commands I'm running are as below :

    REVOKE  SELECT ON TABLEA (COLUMNA) FROM db_DenyCCInfo

    GO

    REVOKE UPDATE ON TABLEA (COLUMNA) FROM db_DenyCCInfo 

    GO

    REVOKE INSERT ON TABLEA (COLUMNA) FROM db_DenyCCInfo

    GO

    REVOKE DELETE ON TABLEA (COLUMNA) FROM db_DenyCCInfo

    GO

    Now, the first 2 commands will work, while the last 2 give an error :

    Msg 1020, Level 15, State 1, Line 0

    Sub-entity lists (such as column or security expressions) cannot be specified for entity-level permissions.

    Has anyone seen this, and got a way around it?  Technically I COULD simply revoke all permissions for the entire table, but I'm trying to leave as much open as possible.

    Thanks

    Regards


    Andy

    Tuesday, November 29, 2011 10:54 AM

Answers

  • Have answered my own question (Through messing around with SSMS).

    It appears that you cannot revoke INSERT/DELETE permissions for a particular column, which makes sense when you think about it.

    You are able to revoke SELECT and UPDATE from individual columns, because actions on these two commands can be filtered to only affect an individual column.

    DELETE and INSERT cannot be filtered this granularly, so permissions need to be on the entire table.

    I have therefore done the following :

    DENY SELECT ON [dbo].[TABLEA ] ([COLUMNA]) TO [db_DenyCCInfo]

    DENY UPDATE ON [dbo].[TABLEA ] ([COLUMNA]) TO [db_DenyCCInfo]

    DENY INSERT ON [dbo].[TABLEA ] TO [db_DenyCCInfo]

    DENY DELETE ON [dbo].[TABLEA ] TO [db_DenyCCInfo]

     

    • Marked as answer by AndyB1978 Tuesday, November 29, 2011 11:33 AM
    Tuesday, November 29, 2011 11:33 AM

All replies

  • I forgot to mention, we're running SQL 2008 R2 standard x64 on Windows 2008 R2 standard.
    Tuesday, November 29, 2011 10:55 AM
  • Have answered my own question (Through messing around with SSMS).

    It appears that you cannot revoke INSERT/DELETE permissions for a particular column, which makes sense when you think about it.

    You are able to revoke SELECT and UPDATE from individual columns, because actions on these two commands can be filtered to only affect an individual column.

    DELETE and INSERT cannot be filtered this granularly, so permissions need to be on the entire table.

    I have therefore done the following :

    DENY SELECT ON [dbo].[TABLEA ] ([COLUMNA]) TO [db_DenyCCInfo]

    DENY UPDATE ON [dbo].[TABLEA ] ([COLUMNA]) TO [db_DenyCCInfo]

    DENY INSERT ON [dbo].[TABLEA ] TO [db_DenyCCInfo]

    DENY DELETE ON [dbo].[TABLEA ] TO [db_DenyCCInfo]

     

    • Marked as answer by AndyB1978 Tuesday, November 29, 2011 11:33 AM
    Tuesday, November 29, 2011 11:33 AM
  • hah, hah....   indeed, it is obvious in reality...    I just so much don't want them to blank out or null those columns!  :)
    Monday, June 15, 2015 3:07 PM
  • A DENY prohibits access. A REVOKE removes a permission statement from the system. It doesn't prohibit access. So if you GRANT a permission, REVOKE removes the GRANT, leaving the user or login at a neutral state able to use any permission that are available. If you DENY permissions, then REVOKE removes the DENY and leaves the user or login at a neutral state able to use any permission that are available.

    So you solved your problem by changing to DENY instead of REVOKE, because REVOKE doesn't do anything if a privilege was not previously either granted or denied.

    You can see this behavior in the system tables sys.database_permissions or sys.server_permissions where you can see entries added (for GRANT and DENY) and then see the entries removed by REVOKE.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, June 15, 2015 3:46 PM