Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


db owner


db owner

Author
Message
sqlfriends
sqlfriends
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2047 Visits: 3863
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?
dan-572483
dan-572483
SSChasing Mays
SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)

Group: General Forum Members
Points: 643 Visits: 1958
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.
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5699 Visits: 7660
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47261 Visits: 44392
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


Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45121 Visits: 39919
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24207 Visits: 37976
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
sqlfriends
sqlfriends
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2047 Visits: 3863
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,
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3946 Visits: 6686
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)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
sqlfriends
sqlfriends
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2047 Visits: 3863
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
dan-572483
dan-572483
SSChasing Mays
SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)

Group: General Forum Members
Points: 643 Visits: 1958
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search