Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

db owner Expand / Collapse
Author
Message
Posted Friday, August 8, 2014 11:55 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:57 AM
Points: 1,775, Visits: 3,219
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?
Post #1601293
Posted Friday, August 8, 2014 3:22 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 11:28 AM
Points: 553, Visits: 1,618
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.
Post #1601341
Posted Friday, August 8, 2014 3:31 PM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:18 AM
Points: 6,258, Visits: 7,445
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
Post #1601342
Posted Saturday, August 9, 2014 5:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:19 AM
Points: 43,026, Visits: 36,192
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 2008, MVP
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

Post #1601391
Posted Saturday, August 9, 2014 10:50 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:00 AM
Points: 37,099, Visits: 31,650
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.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1601444
Posted Saturday, August 9, 2014 4:16 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:04 AM
Points: 23,397, Visits: 32,242
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.


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.



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)
Post #1601463
Posted Monday, August 11, 2014 9:47 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:57 AM
Points: 1,775, Visits: 3,219
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,

Post #1601946
Posted Monday, August 11, 2014 11:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 2,088, Visits: 3,139
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)

"While in these days of quiet desperation /
As I wander through the world in which I live /
I search everywhere for some new inspiration /
But it's more than cold reality can give /
If I need a cause for celebration /
Or a comfort I can use to ease my mind /
I rely on my imagination /
And I dream of an imaginary time" : the inimitable Mr. Billy Joel
Post #1601989
Posted Tuesday, August 12, 2014 1:51 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:57 AM
Points: 1,775, Visits: 3,219
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
Post #1602453
Posted Tuesday, August 12, 2014 3:36 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 11:28 AM
Points: 553, Visits: 1,618
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.
Post #1602505
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse