Monday, March 21, 2011

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

Friday, March 4, 2011

Agile methodology v/s My School days

Don’t take this as too serious. This just my effort to compare my Agile software development experience with my school days.

As you know Agile is comparatively new methodology in the software industry and everybody is rushing towards that.Its well suited for small projects where all the members can collaborate with out much efforts.I am working using Agile methodology for the last 4 years and I love it when applied to small projects.As its incremental process which cares more about individuals and frequent customer deliveries ,it feels to me funny sometimes and similar to my school days.Lets look what are the similarities?

When I was failing in the subjects,I usually receives advices that “At least you need to pass in all the examinations.Its very shame to us” Developer: We cannot complete this project with in 2 weeks.
Manager : Guys we are using Agile. I know its hard.But at least deliver the UI to the users.
When I managed to pass in all the subjects,the advice changes “These marks are not enough to a job.Get first class with 80%.” Developer: You have asked only for the UI, not the functionality.So you can’t expect right behavior on button click.
Manager: No no.When we give a release it should have the functionality,otherwise what the customer can do with this application.
When I forgot my games for studies and got first class they said “You need to strive for perfection.See the other guys who got university Ranks” When developers stretched whole nights and made the application functional
Manager:This application has lot of memory leaks and performance issues.You should have considered this while developing.
When I got 98 out of 100,They shouted “Where are the 2 marks?” When the developers forgot their families and fixed all the memory leaks and performance issues.
Manager : What a useless code is this? This code doesn’t have any comments and not following our coding guidelines.