May 4, 2010 at 7:57 am
Scenario:
Single Database having hundreds of tables and thousands of other objects (sp,view,functions,synonyms etc)
Almost all of these objects are created in schema dbo.
A database user having dbowner rights exists.
Every object belongs to some module. Naming convention exists to identify modules specific objects.
For Eg. if module name is XYZ then objects follow naming conventions as follows Tables: dbo.XYZ_TableName, SP: dbo.XYZ_ProcedureName and so on.
Cross module integration exists, i.e. joins for cross module objects exists in sp's, functions, views etc
Requirement:
Creation of admin roles per module which will have all rights only for that module.
Creation of admin users per module and granting them admin role for that module.
Problem:
Admin roles per module is granted "control" permissions to all objects of that module. admin user per module is assigned the admin role for that module.
Problem is existing module objects for eg. stored procedures having references to another module objects execute successfully and does not give insufficient permissions error. How to avoid such implicit permissions.
Ex:
Tables :
dbo.ABC_Table1 (table belonging to module ABC)
dbo.XYZ_Table1 (table belonging to module XYZ)
StoredProcedure:
CREATE PROCEDURE dbo.ABC_GetTable1Data
AS
BEGIN
SELECT * FROM dbo.ABC_Table1
END
CREATE PROCEDURE dbo.XYZ_GetABCTableData
AS
BEGIN
SELECT
X.Column1, A.Column1
FROM
dbo.XYZ_Table1 X
JOIN dbo.ABC_Table1 ON X.Column2 = A.Column2
END
All the above objects are existing objects
Database Roles:
ABCAdminRole (granted control permissions on dbo.ABC_Table1 and dbo.ABC_GetTable1Data)
XYZAdminRole (granted control permissions on dbo.XYZ_Table1 and dbo.XYZ_GetABCTableData)
Database Users:
ABCAdmin (role member of ABCAdminRole)
XYZAdmin (role member of XYZAdminRole)
Now when XYZAdmin executes the procedure "dbo.XYZ_GetABCTableData" it executes successfully and gives the output.
Requirement is the procedure should give permission denied error.
May 4, 2010 at 9:12 am
You can't turn off ownership chaining. So you can't turn off the implicit permissions. But because you posted this in a SQL Server 2005 forum, let's look at what you can do. In the case of:
dbo.objectname
The dbo now represents the schema and not the owner. Each object can still have an owner. By default, objects don't. Therefore, they default to the owner of the schema. That's why dbo.Proc1 which calls dbo.Proc2 works. The stored procedures dbo.Proc1 and dbo.Proc2 belong to the dbo schema, do not have a specified owner, and default the owner of the dbo schema. That means they have the same owner. And that means the ownership chain forms.
Now, you CAN change the owner of an object while leaving the schema alone. If you use ALTER AUTHORIZATION on a given object, you can change who its owner is. So in the example you've given...
CREATE USER Owner2 WITHOUT LOGIN;
GO
ALTER AUTHORIZATION ON OBJECT::dbo.ABC_GetTable1Data TO Owner2;
GO
This causes the owners to be different and breaks the ownership chain.
K. Brian Kelley
@kbriankelley
May 4, 2010 at 9:16 am
yet another day where i learned something I didn't know.
Thansk for this post, Brian. Good to know!
Lowell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply