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

Stored Procedures Execute Permissions Expand / Collapse
Author
Message
Posted Monday, April 22, 2013 6:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:25 AM
Points: 7,070, Visits: 12,523
crmitchell (4/22/2013)
Eric M Russell (4/18/2013)
Dree Vasquez (4/18/2013)
Hi All

Thanks so much for all the suggestions. I really appreciate it.

I asked the Global developer and they said their procedures cannot be changed. And they also said that we should not limit the rights of the Developers/App Support Team to the databases. In my office, the developer and app support are the same team and I don't think it is correct to grant everyone db_owner privileges.

Do we really give developers the rights to execute stored procedures on the Test and the Production Systems. (In my office, Change Management is required on the Test and Production Systems) and granting such privileges will mean no one needs to raise an CR in the first place.

If you're a member of sysadmin, then you can grant or deny whatever privillages you know are really required. You can even edit and recompile the stored procedures too. Don't let some 3rd party tell you how to manage your database.


As a counter to that you should remember that the code whether stored procedures or otherwise should have been tested and signed off before being put LIVE. This testing may and change control may be governed by statutary requirements and so altering it without a full retest may have legal implications. As such you should not be changing the stored procs but instead requiring the developers to do so.

It depends on the shop. Some DBAs have carte blanche to change code when it makes sense, i.e. when the changes relate to database security or performance. Other times those tasks fall to a developer.

On the other hand it is reasonable to require the developers to produce the code such that it will work correctly on a server with much more restrictive permissions.
As a developer I would expect very liberal rights on the develpment server - definitely DBO and probably SysAdmin. I would expect TEST to be more restricted - no SYSADMIN, maybe DBO but probably only DBREADER, DBWriter and appropriate EXECUTE permissions and minimal rights on the LIVE box.

You gave them dbo?


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1444940
Posted Monday, April 22, 2013 6:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:25 AM
Points: 173, Visits: 647
opc.three (4/22/2013)
crmitchell (4/22/2013)
Eric M Russell (4/18/2013)
Dree Vasquez (4/18/2013)
Hi All


If you're a member of sysadmin, then you can grant or deny whatever privillages you know are really required. You can even edit and recompile the stored procedures too. Don't let some 3rd party tell you how to manage your database.


As a counter to that you should remember that the code whether stored procedures or otherwise should have been tested and signed off before being put LIVE. This testing may and change control may be governed by statutary requirements and so altering it without a full retest may have legal implications. As such you should not be changing the stored procs but instead requiring the developers to do so.

It depends on the shop. Some DBAs have carte blanche to change code when it makes sense, i.e. when the changes relate to database security or performance. Other times those tasks fall to a developer.

On the other hand it is reasonable to require the developers to produce the code such that it will work correctly on a server with much more restrictive permissions.
As a developer I would expect very liberal rights on the develpment server - definitely DBO and probably SysAdmin. I would expect TEST to be more restricted - no SYSADMIN, maybe DBO but probably only DBREADER, DBWriter and appropriate EXECUTE permissions and minimal rights on the LIVE box.

You gave them dbo?


Well I'm a developer but as I said I would expect that on DEV and would expect to need to justify it in TEST - it may be needed when promoting to TEST but normally I would not normally expect it after that stage is completed. Older code may still require it but it should be the exception rather than the rule. It should not be expected for LIVE.
The only thing I would want on LIVE over and above the basic access everyone would have would be the ability to view but not alter server settings and the database objects to aid with diagnosing problems. In some cases the sensitivity of LIVE data may mean that even that level of access would not be appropriate.
Ideally for LIVE the code should be set up to run with execute permissions on specific stored procs only and no general DB access.
Post #1444949
Posted Monday, April 22, 2013 7:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:25 AM
Points: 7,070, Visits: 12,523
crmitchell (4/22/2013)
opc.three (4/22/2013)
crmitchell (4/22/2013)
Eric M Russell (4/18/2013)
Dree Vasquez (4/18/2013)
Hi All


If you're a member of sysadmin, then you can grant or deny whatever privillages you know are really required. You can even edit and recompile the stored procedures too. Don't let some 3rd party tell you how to manage your database.


As a counter to that you should remember that the code whether stored procedures or otherwise should have been tested and signed off before being put LIVE. This testing may and change control may be governed by statutary requirements and so altering it without a full retest may have legal implications. As such you should not be changing the stored procs but instead requiring the developers to do so.

It depends on the shop. Some DBAs have carte blanche to change code when it makes sense, i.e. when the changes relate to database security or performance. Other times those tasks fall to a developer.

On the other hand it is reasonable to require the developers to produce the code such that it will work correctly on a server with much more restrictive permissions.
As a developer I would expect very liberal rights on the develpment server - definitely DBO and probably SysAdmin. I would expect TEST to be more restricted - no SYSADMIN, maybe DBO but probably only DBREADER, DBWriter and appropriate EXECUTE permissions and minimal rights on the LIVE box.

You gave them dbo?


Well I'm a developer but as I said I would expect that on DEV and would expect to need to justify it in TEST - it may be needed when promoting to TEST but normally I would not normally expect it after that stage is completed. Older code may still require it but it should be the exception rather than the rule. It should not be expected for LIVE.
The only thing I would want on LIVE over and above the basic access everyone would have would be the ability to view but not alter server settings and the database objects to aid with diagnosing problems. In some cases the sensitivity of LIVE data may mean that even that level of access would not be appropriate.
Ideally for LIVE the code should be set up to run with execute permissions on specific stored procs only and no general DB access.

Judging by your response I thought I would point out that You gave them dbo? was a link to a blog post, not really a question or comment directed at you. I consider myself a Developer first, and a DBA second. In some shops your expectations will not be met. You may be granted db_owner membership in DEV, read-only and VIEW DEFINITION in TEST, and nothing in LIVE, because sometimes you will not be allowed to see transactional data in LIVE and non-LIVE environments will be scrubbed of PII. At the end of the day if the code is untested in DEV it should not even make it to TEST, let alone LIVE. By the time it makes it to LIVE developers should not need to worry. Of course this is best case scenario, but that's how it goes in some shops. In some extreme cases access to LIVE can be granted temporarily to troubleshoot an issue that cannot be recreated in DEV or TEST, but that would require an approval for access to PII. You can see how this could motivate developers to produce fully-tested code that can be deployed in a repeatable way such that it runs in LIVE the same way it runs in DEV, a nice ancillary benefit to having a locked down LIVE environment.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1444989
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse