SQL server ownership chaining

Now a days it becomes a habit to blog, if I learn something.Otherwise I am not feeling it completed.After a long time recently I got a big chance to learn some database related things due to the project which I am doing for my current company.Let me start with SQL server ownership chaining.

What is Database ownership chain?

We all know that what is database object.Everything we create in database is a database object eg:stored procedures,tables ,views etc…When we invoke or access one database object there are chances that it may access another object.This sequence is called the ownership chain.

To understand the scenario better lets take one example.There is a Order table and Order_Jan2011 view.To access the data from the view we have a StoredProcedure usp_GetOrders .When we access or call the sp that sp internally uses the view and that view uses the table.This is ownership chains.If all these objects are owned by one user and the identity which calls the sp  is the same user,there is nothing special.But what if these objects are owned by different users and the user accessing is somebody else? (User here refers to the database user.)

Bypassing security check based on ownership

Lets assume that there are 2 users DBUser and the AppUser.DBUser owns all the DB objects such as tables, views and SPs, where the AppUser has access to only the SPs.When the AppUser calls the SP to retrieve some data the database allows it because the SP and Table used by that are owned by single user that is DBUser.

Lets take one example to understand it clearly.Order Table ,Orders_Jan2011 & usp_GetOrders are owned by the DBUser and the AppUser has access to the SP.

User	Relation	DBObject
DBUser	Owns	Order ,Orders_Jan2011,usp_GetOrders
AppUser	Has Access	usp_GetOrders

When the AppUser calls the usp_GetOrders which calls the view it works.ie the data has retrieved from Orders_Jan2011even though the AppUser dont have permission on Orders_Jan2011.

Try out yourself

Get ready with SQLServer Management Studio (SSMS.exe) to execute the below queries.There are mainly 2 phases in creation.

Create The Users with required permission

create user DBUser without login
Exec sp_addrolemember @RoleName = 'db_owner', @MemberName = 'DBuser'
create user AppUser without login

We use login less users.Hope everybody knows who is login less user.Make the DBUser as db_owner to get the table,view & SP creation permission.

Create DBObjects in the context of DBUser

execute as user = 'DBUser'

--Create Table and insert 2 sample rows----
create table [Order] (Id int,OrderDate date,Details varchar(20))
insert into [Order] values (1,'1/1/2011','5 Laptops')
insert into [Order] values (2,'2/2/2011','2 Desktops') 

---Create view that holds orders for the month Jan2011
go
create view Orders_Jan2011
as
select * from [Order] where YEAR(OrderDate)=2011 and MONTH(OrderDate)=1

--Create SP to get the orders and give permission to AppUser
go
create proc usp_GetOrders
as
--Parameterize and add Logic to call the correct view
select * from Orders_Jan2011
grant execute on usp_GetOrders to AppUser
revert

-----Checking the permissions in the context of AppUser------
execute as user = 'AppUser'
go
exec usp_GetOrders
--Executing the below queries will result in error --
--select * from [Order]
--select * from [Orders_Jan2011]
revert

Inserted 2 rows into the Order table.Now creation is over check accessing the DBObjects in the context of AppUser.

execute as user = 'AppUser'
exec usp_GetOrders
--Executing the below queries will result in error --
select * from [Order]
select * from [Orders_Jan2011]
revert

As said earlier the first line ie the SP call will work.Rest will result in error because AppUser don’t have access to those objects.Interesting thing here is sp internally uses the same view and table.

Hope everybody knows DB user impersonation.ie execute as user.The full script can be downloaded from here.

Is this good or bad

It depends own your scenario.There are support for cross database ownership chains.So choose it carefully.For more details see below link.

http://msdn.microsoft.com/en-us/library/ms188676.aspx