db owner

  • We have many in house or vendor based applications and their databases.

    I have a question about the dbowner, usually the vendor production they have a specific login and that login has db_ower role in the database.

    And our in house application database, we usually create a database role for a database that has read and write, and permissions to excute a lot of functions and procedures.

    I found out each time we add a procedure we need to grant permission to this database role. If missed, there will be errors.

    But my question is why bother to grant all these permssions, but just grant this account db_owner role. Becaue it pretty much need all the permissions: read, write, and excute.

    Is it a common practice or should I always to grant specific permissions?

  • Someone needs to have permission on the stored procedure otherwise only sa or someone in the db_owner role will be able to run it. The usual procedure is to grant execute permission to a Database Role or user which is mapped to and AD group representing users, or a single user if the application uses a specific SQL user to access the data. This allows greater control over who is allowed to read or modify data.

  • It depends on your security expectations. Typically, a login from an application doesn't have read/write, they ONLY have execute permissions to procedures, and inherit what they need from the schema owners.

    The reason for this is tiered security, and comes from the following line of thinking. If a hacker cracks your login for the website (Either SQLLogin or AD), they have exactly as much ability to affect the database as they did before from the website... unless the website was sanitizing inputs for dynamic SQL, then the hacked login can pretty much do whatever the hell it wants.

    DBO for the app owners is a convenience thing. It means they don't have to give sysadmins a separate SQL Script for upgrades and the like and their application can send down the DDL and DML during the installation with its already pre-built security logins. It's bad form, usually.

    And to answer your question, yes, every one of my procs has a tail that looks like this:

    GRANT EXECUTE ON <schema>.<procname> TO <DBRole>

    GO


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • sqlfriends (8/8/2014)


    But my question is why bother to grant all these permssions, but just grant this account db_owner role. Becaue it pretty much need all the permissions: read, write, and excute.

    And permission to change the schema, drop tables, add users, add permissions, drop the database. None of which your application probably needs.

    Principle of Lease Privilege. An app's login should have the minimum permissions it needs to do it's job and nothing else.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (8/9/2014)


    sqlfriends (8/8/2014)


    But my question is why bother to grant all these permssions, but just grant this account db_owner role. Becaue it pretty much need all the permissions: read, write, and excute.

    And permission to change the schema, drop tables, add users, add permissions, drop the database. None of which your application probably needs.

    Principle of Lease Privilege. An app's login should have the minimum permissions it needs to do it's job and nothing else.

    For me, that means that the app login has no privs other than PUBLIC and the privs to execute certain stored procedures.

    Of course, in real life, we never reach that particular bit of security Nirvana. Instead, we end up catering to people that don't understand and spending proverbial mega-bucks on things like penetration testing. :pinch:

    --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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (8/9/2014)


    GilaMonster (8/9/2014)


    sqlfriends (8/8/2014)


    But my question is why bother to grant all these permssions, but just grant this account db_owner role. Becaue it pretty much need all the permissions: read, write, and excute.

    And permission to change the schema, drop tables, add users, add permissions, drop the database. None of which your application probably needs.

    Principle of Lease Privilege. An app's login should have the minimum permissions it needs to do it's job and nothing else.

    For me, that means that the app login has no privs other than PUBLIC and the privs to execute certain stored procedures.

    Of course, in real life, we never reach that particular bit of security Nirvana. Instead, we end up catering to people that don't understand and spending proverbial mega-bucks on things like penetration testing. :pinch:

    Like applications like PeopleSoft. Requires sys admin (not necessarily the sa login itself) rights (never tried to see if it could get away with db_owner) and handles user security in the application itself.

  • So it looks like for in house developed application and databases, all read and write to database should go through by using stored procedure instead of granting db reader and db writer role, correct?

    Then grant execte to each procedure.

    Thanks for clearing out for application logins it should keep minimum permissions like just excute stored procedures.

    I also sometimes have to grant some developers dbowenr for some databases, for they not only need to read,write,execute, they sometimes do need to create ddls.

    Thanks,

  • You don't have to grant each procedure separately, you can grant execute on the schema (or for the entire db, no matter what the schema, if you prefer). The user then automatically can execute new procs as well, without another command being issued.

    One really big issue with db_owner is that it can DROP the database! If you get a hack or some bad in there, are you really ready for that to happen?!

    The apps that require db_owner are, frankly, poorly designed. Yes, the app may even need db_securityadmin (so that all access can be administered through the app), but it never should require db_owner.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • I returned to this, since I think I am not very clear about below and want to verify.

    Many posts above mentioned to grant execute to stored procedures, but did not mention to grant db reader and writer to the database, or select to databases.

    Does that mean all the read and write should go through stored procedures?

    for example even select from a table need to write a procedure to achieve?

    I think this is a more developer question, or perhaps both dba and developer question?

    Thanks

  • There is no right or wrong answer to this question.

    The advantage of accessing data through storied procedure only include

    1) Security. If a user is in db_datareader role and the knows how to use other tools that can access SQL, like Excel, Microsoft access, they will be able to query anything in the database, or write bad queries that may cause performance issues. Maybe they need this access to the data and this is fine, and maybe they don't. By using exclusively stored procedures for data access security can be designed to users will only have access to the data and queries designed in the stored procedures. There is principle of minimal permissions that says users should have rights to do what's required for their jobs and nothing else.

    2) Stored procedures are useful when application and database developers are separate people with separate skill sets. A .NET developer may not know much about SQL queries and vice-versa. Stored procedures allow a SQL developer to worry about SQL queries and interface developers to focus on the interface and much of the application logic.

    On the other hand...

    Some applications contain reporting or querying functions that give a lot of flexibility to users to define what they want to see. Giving users more access to the database allows users to get what they want easily without making every new query a development project requiring the cooperation of a team of people.

    So it really depends on the environment, the needs of users and security needs.

  • sqlfriends (8/12/2014)


    Does that mean all the read and write should go through stored procedures?

    for example even select from a table need to write a procedure to achieve?

    That would be nice. Pity we don't live in a perfect world. 😀

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • sqlfriends (8/12/2014)


    Does that mean all the read and write should go through stored procedures?

    for example even select from a table need to write a procedure to achieve?

    I think this is a more developer question, or perhaps both dba and developer question?

    Typically, yes. You'll see it acronymed as CRUD, or CRUD procs. Create, Read, Update, Delete. There's a number of automation tools out there that can setup the standard set for you or you can build your own pretty simply. It's usually the first thing you do after setting up your schema before you let the devs loose against the system.

    I'm not saying that these are the best tactic, as many of them won't be and you'll end up replacing them. But it's a quick and dirty starting point so you can get to work and figure out where you need stronger development.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Thanks, what are the tools, can you tell me a couple of them?

    Thanks

  • Embarcadero has a suite of tools that can do that.

    http://www.embarcadero.com/downloads

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Is CodeSmith one of them?

    Thanks

Viewing 15 posts - 1 through 15 (of 18 total)

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