April 21, 2011 at 11:40 am
User user1 has no access to the table dbo.Table1, but the user has access to vw.vTable1 which refers to dbo.Table1. The user cannot run the code SELECT TOP 1 * FROM dbo.Table1 (Msg 229, Level 14, State 5, Line 1 The SELECT permission was denied on the object ...).
But the user can create a procedure (CREATE PROCEDURE dev.Test AS SELECT TOP 1 * FROM dbo.Table1) and execute the procedure dev.Test and get results.
I would like to be able to limit the users create procedure, view, function against the objects they have access and if they don't I want it to fail at security level.
Is there anyway this can be implemented?
Thank you
April 21, 2011 at 12:06 pm
The dev and dbo schemas share the same owner. That's why the ownership chaining is working. There are two ways of attacking this:
1) By the use of schema. Segregate objects based on schema and have the schema owned by different owners. This assumes you can segregate the objects nicely without breaking something. This is not very likely, so...
2) By default, objects in SQL Server 2005/2008 do not have owners. They therefore use the owner of the schema they are in. You can assign an owner to an object that is different than the owner of the schema. Obviously if one object refers to another and they have different owners, there is no ownership chain. This would mean granting explicit permissions to the objects which you "break" in this way.
K. Brian Kelley
@kbriankelley
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply