Wise man or Wise guy? You Decide

Just another WordPress.com weblog

What’s ownership chaining?

Posted by sqlwiseguy on March 25, 2009

Edit: 04-27-2009 K. Brian Kelley corrrectly pointed out in a comment that objects can still be owned by users in 2005 and later.  See his blog post to see how.

Edit:  While the code section is not as nice as I would like it is better.  Thanks for sticking with me.

I recently answered a question on SSC about how you granting execute rights on a stored procedure allows you to access tables without having any permissions granted on the tables.  The way that SQL Server implements this it through ownership chaining.  Here is the BOL (SQL Server 2005) definition of Ownership Chains:

When multiple database objects access each other sequentially, the sequence is known as a chain. Although such chains do not independently exist, when SQL Server 2005 traverses the links in a chain, SQL Server evaluates permissions on the constituent objects differently than it would if it were accessing the objects separately. These differences have important implications for managing security.
Ownership chaining enables managing access to multiple objects, such as multiple tables, by setting permissions on one object, such as a view. Ownership chaining also offers a slight performance advantage in scenarios that allow for skipping permission checks.

How Permissions Are Checked in a Chain

When an object is accessed through a chain, SQL Server first compares the owner of the object to the owner of the calling object. This is the previous link in the chain. If both objects have the same owner, permissions on the referenced object are not evaluated.

Prior to SQL Server 2005, objects were owned by users, and, in most cases, designers and developers used the dbo user to own common objects.  In my experience it was very rare for objects to be owned by a “real” user. Thus if the customers table and orders table are both owned by dbo, then you can create a customer_orders_get stored procedure owned by dbo, grant user Joe execute permissions on the stored procedure, and Joe would be able to successfully use the stored procedure to query the customers and orders tables without having select permissions on the tables.  This is pretty easy to understand, as long all the objects are dbo.object_name I can access them using ownership chaining.
SQL Server 2005 introduced schemas which removed the use of users as owners.  Now objects belong to schemas, not users.  From BOL:

Beginning in SQL Server 2005, each object belongs to a database schema. A database schema is a distinct namespace that is separate from a database user. You can think of a schema as a container of objects. Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.

The question now is, how does ownership chaining work when using schemas?  As the last line of the BOL quote states, a schema is owned as well, and ownership chaining in SQL Server 2005 and greater is based on schema ownership.  So if schema A is owned by dbo then you would be able to use ownership chaining to access objects in dbo from schema A and vice versa.  I think the best way to explain is with an example:

USE [master]
GO

-- drop the DB if it exists
IF EXISTS ( SELECT
name
FROM
sys.databases
WHERE
name = N'oc_test' )
DROP DATABASE [oc_test]
GO

USE [master]
GO

-- create the test database
CREATE DATABASE [oc_test] ON PRIMARY
(
NAME = N'oc_test',
FILENAME = N'C:\Data\oc_test.mdf' ,
SIZE = 194560KB,
MAXSIZE = 1024000KB,
FILEGROWTH = 10240KB
)
LOG ON
(
NAME = N'oc_test_log',
FILENAME = N'C:\Logs\oc_test_log.ldf',
SIZE = 204800KB,
MAXSIZE = 204800KB,
FILEGROWTH = 10240KB
)
GO

-- switch to the new databse
USE oc_test ;

GO

-- drop the user if it exists
IF EXISTS ( SELECT
1
FROM
sys.database_principals AS DP
WHERE
DP.[name] = 'test_user' )
BEGIN
DROP USER test_user ;
END

-- create our test user
CREATE USER test_user WITHOUT LOGIN
WITH DEFAULT_SCHEMA = dbo ;
GO

-- drop proc and schema
IF OBJECT_ID('ownership_chaining.table_one_ins')
IS NOT NULL
BEGIN
DROP PROCEDURE ownership_chaining.table_one_ins ;
END

GO

IF SCHEMA_ID('ownership_chaining') IS NOT NULL
BEGIN
DROP SCHEMA ownership_chaining ;
END
GO

-- create our test schema, notice it is "owned" by dbo
CREATE SCHEMA ownership_chaining AUTHORIZATION dbo ;

GO

-- drop our test table if it exists
IF OBJECT_ID('dbo.table_one') IS NOT NULL
BEGIN
DROP TABLE dbo.table_one ;
END

/*
create a test table NOTICE it is in the dbo
schema which is owned by dbo
*/
CREATE TABLE dbo.table_one
(
column_one INT IDENTITY(1, 1),
column_two VARCHAR(10) NOT NULL
) ;

GO

/*
Create the test procedure in the
ownership_chaining schema which
is owned by dbo
*/
CREATE PROCEDURE ownership_chaining.table_one_ins
@column_two VARCHAR(10)
AS
INSERT INTO
dbo.table_one (column_two)
VALUES
(@column_two) ;

RETURN ;

GO

/*
give out test user execut permissions on the
procedure
*/
GRANT EXECUTE ON ownership_chaining.table_one_ins
TO test_user ;

GO

-- change user context
EXECUTE AS USER = 'test_user' ;

-- execute the procedure
EXEC ownership_chaining.table_one_ins
@column_two = 'Test 1' ;

-- go back to my login
Revert ;

-- verify that it worked which it should
SELECT
*
FROM
dbo.table_one AS _TO ;

/*
Now change the owner of the ownership_chaining
schema so that it is no longer owned by dbo
thus breaking the chain
*/
ALTER AUTHORIZATION ON SCHEMA::ownership_chaining
TO db_owner ;

-- change user context
EXECUTE AS USER = 'test_user' ;

/*
using try - catch so I get the revert as
I expect failure
*/
BEGIN TRY
EXEC ownership_chaining.table_one_ins
@column_two = 'Test 1' ;
END TRY
BEGIN CATCH
-- show the error message
SELECT
ERROR_MESSAGE() AS error ;

-- return to my user
revert ;
END CATCH

-- verify data
SELECT
*
FROM
dbo.table_one AS _TO ;
I hope this helps clear up what I found to be a somewhat confusing topic.
Advertisements

6 Responses to “What’s ownership chaining?”

  1. RBarryYoung said

    Ouch! That code did not format well. Nice post though, Jack.

  2. Jack said

    Wow, are you right about the formatting. This is my first post with code using Live Writer and it is not what I expected at all. I’ll work on fixing the formatting this evening.Thanks for the positive feedback on the content.

  3. Orlando said

    I think ownership chaining is great and am glad the concept happily co-exists with separation of users and schemas introduced in 2005! It certainly simplifies security management and allows one to treat schemas as what they are in their simplest form, namespaces, ignoring the granularity of permissions they offer. I like that the option for granular permission management with schemas is available, however for my simpler environments that granularity is not forced upon me.Question, given the performance advantages ownership chaining brings to the table, do you have any metrics on what that advantage really is in practice in non-ownership chaining scenarios? For example where “execute as” is specified at the beginning of every stored proc and ownership chaining is not taken advantage of.

  4. Jack said

    Orlando,Thanks for the comment. A simple answer to your question is, “I don’t know”. I have not tested it out and, honestly, have never used “Execute As” or in 2005/2008 signed stored procedures. Certainly ownership chaining is the simplest and, in my opinion, the best way to handle it. If you are using views and stored procedures for data access then there is no reason NOT to have all schemas “owned” by dbo so ownership chaining will work across schemas.

  5. kbriankelley said

    I know I’m late to the party, but objects in 2005/2008 can still be owned by users:

    http://www.sqlservercentral.com/blogs/brian_kelley/archive/2009/03/04/owning-an-object-in-sql-server-2005-2008.aspx

  6. Jack said

    Thanks for the clarification Brian. I am adding the link to your blog post to my post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: