Is db_datareader and db_datawriter *typically* all that's needed?

  • edflecko

    SSC Enthusiast

    Points: 162

    We have several SQL Server databases in our organization and most of them are a requirement for a specific application.

    Most often, either the end-user will request to have them be a member of the db_owner role or the application will automatically make them a member of db_owner.

    I know there are exceptions, specific circumstances, etc., etc., but as a general rule of thumb, *most* users would not need to be any more than a db_datareader and db_datawriter, would they? For the average user, it seems like gross overkill to make them a member of db_owner, or am I wrong? I'm trying to adhere to the principle of "least privilege".

    Thank you,
    Ed

  • ZZartin

    SSC-Dedicated

    Points: 30310

    That entirely depends on the database and what you consider the average user, you are right in thinking that defaulting to db_owner for everyone is likely very wrong though.  For a production database db_reader would likely be the most the "average" user should have if any, preferably the average non IT user would have some kind of application they use to get to the database without any direct database access.

    Defaulting everyone to db_owner is usually the result of either not understanding access privileges or simply administrators/application devs who either don't want to take the time to learn what people are really trying to do or don't have the spine to tell people no.

  • Jeff Moden

    SSC Guru

    Points: 993860

    IMHO, the "Gold Standard" would be to not allow any privs to user or apps other than "PUBLIC" and privs to run certain stored procedures.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • edflecko

    SSC Enthusiast

    Points: 162

    Thank you ZZartin!

    🙂

    I guess another way of asking my question might be...for those people who DO need to both read and write data only, they don't need, nor should they be given, anything more than those two roles. To just arbitrarily give them db_owner, db_securityadmin, db_accessadmin, db_backupoperator or db_ddladmin is plain dumb from a security perspective, isn't it?    

    As a side note, their primary database assignment should be the database they need to interact with and not "Master" which is the default, unless you manually select otherwise, correct?

    Ed

  • Lynn Pettis

    SSC Guru

    Points: 442116

    Yes, db_datareader and db_datawriter could be considered the minimum.  The others are not typically granted to typical users.  As Jeff also said, typical users should also only access the database through an application that helps limit the access that users have to the database.  As he also stated this could be public access the execute access to stored procedures used by the application to access the data in the database (be that reading or writing).

    As for the default database being master, I have found leaving that the best option.  If you make a given user database the default, and that database gets deleted for any reason the default database for those users changes and it may not change to an appropriate database and could result in that user not being able to access the server even after the deleted database is restored.

  • Lynn Pettis

    SSC Guru

    Points: 442116

    To add to this, you may actually want to look at custom database roles.  Not every user needs access to every table in a database.  Think about confidential data that is stored in HR or Financial systems, or health data (HIPPA) as well.  Security is an import part of any application and should not be an afterthought.

  • Jeff Moden

    SSC Guru

    Points: 993860

    Lynn Pettis - Thursday, February 22, 2018 10:54 AM

    Yes, db_datareader and db_datawriter could be considered the minimum.  The others are not typically granted to typical users.  As Jeff also said, typical users should also only access the database through an application that helps limit the access that users have to the database.  As he also stated this could be public access the execute access to stored procedures used by the application to access the data in the database (be that reading or writing).

    As for the default database being master, I have found leaving that the best option.  If you make a given user database the default, and that database gets deleted for any reason the default database for those users changes and it may not change to an appropriate database and could result in that user not being able to access the server even after the deleted database is restored.

    Heh... I look at it the other way... db_datareader and db_datawriter should be considered to be the maximum rather than the minimum along with possibly being given privs to execute a set of stored procedures.

    Totally agreed on the "default database" thing you mention except that I set it to TempDB for non-DBA users.   No real need to do that, though.  It's just my personal preference.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Eric M Russell

    SSC Guru

    Points: 124963

    The way it should work is that the users indicate what level of access they need functionally, and then you decide which role(s) to put them in. The user shouldn't simply say: "Add me to the DB_OWNER role.". If a user swears what all they intend to do is query tables and occasionally run ad-hoc inserts, then put them in the DB_READER and DB_WRITER roles and then swear that's all they really need.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • edflecko

    SSC Enthusiast

    Points: 162

    Thank you Lynn!...I appreciate your clarity.

    Could you explain, "typical users should also only access the database through an application that helps limit the access that users have to the database."? In other words, does this mean that when I have a User that's accessing SQL through their application, they DO need an account created under (using SSMS) <database_name> > Security > Users but they would NO T need a Logon Account that's created under Security > Logins ? Is that right?

    If I understand it correctly, an account created under the Security > Logins allows them to log into SSMS (which the *average* user probably does not need?) where the Account created under <database_name> > Security > Users would allow their application to connect to SQL and function the way they expect but yet not give them too much access. Do I have that correct?

    🙂

    Ed

  • lptech

    Hall of Fame

    Points: 3188

    Even db_datareader and datawriter could be too much in today's environment. Do 'most users' really need to be able to read or update every table in the database? Not that there may be any malicious intent, but with all of the hacker activity going on in the world, one compromised workstation could do an incredible amount of damage. Better to set up application roles with only the required privileges.

  • ZZartin

    SSC-Dedicated

    Points: 30310

    edflecko - Thursday, February 22, 2018 10:28 AM

    Thank you ZZartin!

    🙂

    I guess another way of asking my question might be...for those people who DO need to both read and write data only, they don't need, nor should they be given, anything more than those two roles. To just arbitrarily give them db_owner, db_securityadmin, db_accessadmin, db_backupoperator or db_ddladmin is plain dumb from a security perspective, isn't it?    

    As a side note, their primary database assignment should be the database they need to interact with and not "Master" which is the default, unless you manually select otherwise, correct?

    Ed

    Make sure you understand what the users really need to do, keep in mind the reader and writer roles give access to everything.  You don't necessarily want someone just needs to see addresses to be able to see everyone's credit card and SSN numbers or someone who just needs to modify order statuses to be able to delete every transaction record in the system.

  • Jeff Moden

    SSC Guru

    Points: 993860

    lptech - Thursday, February 22, 2018 11:31 AM

    Even db_datareader and datawriter could be too much in today's environment. Do 'most users' really need to be able to read or update every table in the database? Not that there may be any malicious intent, but with all of the hacker activity going on in the world, one compromised workstation could do an incredible amount of damage. Better to set up application roles with only the required privileges.

    +1000.  PUBLIC and the privs to execute certain stored procedures should be enough, especially App logins.  Of course, that's the "Gold Standard" that most would never be happy with.  There is some good merit to apps being able to do C.R.U.D. without having to gen a bazillion C.R.U.D. procs.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Lynn Pettis

    SSC Guru

    Points: 442116

    edflecko - Thursday, February 22, 2018 11:26 AM

    Thank you Lynn!...I appreciate your clarity.

    Could you explain, "typical users should also only access the database through an application that helps limit the access that users have to the database."? In other words, does this mean that when I have a User that's accessing SQL through their application, they DO need an account created under (using SSMS) <database_name> > Security > Users but they would NO T need a Logon Account that's created under Security > Logins ? Is that right?

    If I understand it correctly, an account created under the Security > Logins allows them to log into SSMS (which the *average* user probably does not need?) where the Account created under <database_name> > Security > Users would allow their application to connect to SQL and function the way they expect but yet not give them too much access. Do I have that correct?

    🙂

    Ed

    Understand the difference between users and logins.  Without logins, no one would be able to access the database server.  Logins provide authentication to SQL server (instance).  Users are used to grant users access to one or more databases that reside on SQL Server (an instance).  How this is down is dependent on the application for typical users.  Some applications manage the entire security of the system, such as PeopleSoft for one example.  In this case only the application needs to connect to SQL Server and its database(s) and the users need no direct access to SQL Server.

    And yes, typical users would NOT need SSMS.  SSMS is typically reserved for Database Developers and Administrators.  Even they may have different levels of access based on the environments they access as well as the projects they may be assigned.

Viewing 13 posts - 1 through 13 (of 13 total)

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