security related question

  • Hi All,

    Have a question regarding database roles in SQL Server. While assigning the database role for a database user, we have roles like

    db_datareader & db_writer roles.

    Similarly, we have db_denydatareader & db_denydatawriter roles.

    My question is, what is the use of these 2 deny roles. If I don't want to a user to have SELECT , INS, UPD, DEL permissions, then I can simply uncheck or don't give  db_datareader, db_writer roles to that user. What is use of db_denydatareader, db_denydatawriter roles. Is there any specific use case scenarios for these 2 db roles?

    Thanks,

    Sam

  • Deny is a trump over all other security. So, you may have a complex set up where a given user, because they're in a group, has access to a table or tables. But, you don't want them to see it or write to it, regardless of that group affiliation. You can use the deny as a final trump to be sure they don't have it.

    But yeah, let's say there's nothing complex, at all, in any  way, just a single login, no groups, no affiliations, if you never grant them access, you won't need to deny them anything.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • Grant Fritchey wrote:

    Deny is a trump over all other security. So, you may have a complex set up where a given user, because they're in a group, has access to a table or tables. But, you don't want them to see it or write to it, regardless of that group affiliation. You can use the deny as a final trump to be sure they don't have it.

    But yeah, let's say there's nothing complex, at all, in any  way, just a single login, no groups, no affiliations, if you never grant them access, you won't need to deny them anything.

     

    Thank you Sir. Got it.

  • One example would be when you want to allow select to a 'tableA' from the accountingGroup. Then you realise that there is a member of that group who you would rather not have access to that data yet (say they are a new/junior member of the team). You still want that junior member to be a part of the accounting group so she/he can Select from the other tables accountingGroup has access to (just not tableA). So the solution here is add a DENY on tableA to the junior member.

     

     

    ----------------------------------------------------

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply