Re: Yukon schemas
- From: "Laurentiu Cristofor [MSFT]" <laur@xxxxxxxxxx>
- Date: Wed, 15 Feb 2006 11:54:55 -0800
ALTER on a schema is a very powerful permission and should not be granted
lightly. This permission, combined with others, allows the creation of
objects in a schema that will be owned by the schema owner, not by the
principal who created them.
Although ALTER on the schema will not allow the creation of new tables, it
will allow modifying existing tables.
I recommend to have tables stored in a special schema and the procedures,
views, etc that developers can change should be created in a different
schema. The schemas should have different owners to prevent ownership
chaining and you could have the schema owners be dummy users that are not
used for anything else (create them using CREATE USER WITHOUT LOGIN).
Having everyone use the dbo schema is not a good idea at all as this schema
is owned by dbo and granting ALTER on it will allow the grantees to create
objects owned by dbo. This is not good security.
Thanks
--
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Resquegal" <Resquegal@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E1792F17-6939-4F5E-8D3E-43D1871712BC@xxxxxxxxxxxxxxxx
I am in the process of setting up security on our Yukon development
servers.
Thus far, I have added logins and created users within databases. Each
user
was given the db_datareader and db_datawriter roles. Each user also has
'dbo'
as the default schema. I created a database role in each database called
m_developer and I granted the role to each user. Then I granted 'create
procedure' and 'create view' to the role m_developer.
In SQL 2000, this was enough permissions to allow developers to add/update
data and to create and alter stored procedures and views that they owned.
In Yukon, however, this apparently is not enough permissions.
In order to allow developers to create stored procedures, I ended up
executing the following command:
Grant Alter on schema::dbo to m_developer
However, I believe that this now gives developers permission to not only
create and alter stored procedures and views but also tables etc... and
that
is what I was trying to prevent.
Is there a better way of doing this? Is there a way to grant 'Create
procedure' to a user or role and have it apply to any/all schemas without
having to grant permissions directly to the schema?
We'd like to stick to having developers use 'dbo' as the default schema.
Thanks for your advice!
.
- Prev by Date: Re: Yukon schemas
- Next by Date: Re: Audit log that SA cannot modify
- Previous by thread: Re: Yukon schemas
- Index(es):
Relevant Pages
|
