Friday, March 9, 2012

Ownership of Stored Procedures/Functions By Role Other Than dbo

I would like to enforce the following security policy. This policy is used in our other db systems (Rdb and Oracle), and I'm thinking about how to implement this in SQL Server. (Yes, I'm a DBA.)

(1) Developers are not allowed to create/alter/delete tables owned
by dbo. To prevent this, no developers will be granted role db_owner.
Developers should only be creating/modifying stored procedures/functions.

(2) All tables will be owned by dbo. DBA's (who have role db_owner
and server privileges) will be creating/modifying table definitions. DBA's will also be granting individual table priv's to the developers (most likely through the role "dco" below).

(3) All stored procedures/functions will be owned by a new role "dco"
(database code owner). All developers will be granted role "dco". No tables should be created in "dco", so role "dco" will be DENY-ed the privilege CREATE TABLE. (I'm also thinking about restricting view creation to dbo by DENY-ing CREATE VIEW.) DBA's will implicitly get access to dco procs/funcs from server privileges.

Does anybody see any possible problems with this approach? Have you tried anything like this? I've read about "broken ownership chains", but as long as the DBA grants the object privs on the tables to the developers I don't see a problem.

Thanks in advance for any input.

JeffWho will be the owner of what the DCO's will create?|||Role "dco" will own the stored procedures/functions, ie:

Create Procedure mydb.dco.myproc ...
Create Function mydb.dco.myfunc ...

Jeff

No comments:

Post a Comment