Understanding SQL Server Ownership Chaining (2023)

By: K. Brian Kelley |Comments | Related: > Security

Problem

I’ve heard of asecurity feature within Microsoft SQL Server called ownershipchaining. However, I don’t understand what it is or how it works. When isit applied? What does it affect?

Solution

Ownership chaining is a security feature in SQL Server which occurs when allof the following conditions are true:

• A user (which could be an app through a login/service account) tries toaccess an object that makes a reference to another object. For instance, theuser tries to execute a stored procedure that accesses other objects or a SELECTfrom a view that accesses other tables.
• The user has access to the first object, such as EXECUTE rights on the storedprocedure or SELECT rights on the view.
• Both objects have the same owner.

In this case, SQL Server will see the chain between the object the user calledand the object being referenced. SQL Server will also determine that the owner forboth objects is the same. When those conditions are met, SQL Server will createthe ownership chain.

SQL Server Ownership Chaining Example

When there’s an ownership chain, security is ignored on the object beingreferenced. For instance, let’s say we have the following situation:

We have a stored procedure, Inventory.RestockItem, referringto a table, Inventory.ItemQuantity. If both the stored procedureand the table have the same owner, SQL Server will form an ownership chain whena user tries to execute the stored procedure. In this case, as long as the userhas EXECUTE permissions on the stored procedure, the stored procedure will be ableto reference the table. The user doesn’t have to have any permissions on thetable at all. In fact, any permissions the user does have will be ignored.

Let’s set this up using T-SQL to create the objects. This will make thingseasier to understand:

CREATE DATABASE TestDB;USE TestDB;GO CREATE SCHEMA Inventory;GO CREATE TABLE Inventory.ItemQuantity ( ItemID int NOT NULL, ItemsInStock int NOT NULL, CONSTRAINT PK_ItemQuantity PRIMARY KEY CLUSTERED (ItemID));GO INSERT INTO Inventory.ItemQuantity (ItemID, ItemsInStock) VALUES (1, 5);INSERT INTO Inventory.ItemQuantity (ItemID, ItemsInStock) VALUES (2, 15);INSERT INTO Inventory.ItemQuantity (ItemID, ItemsInStock) VALUES (3, 25);INSERT INTO Inventory.ItemQuantity (ItemID, ItemsInStock) VALUES (4, 10);GO CREATE PROC Inventory.RestockItem @ItemID int, @QuantityToAdd intASBEGIN UPDATE Inventory.ItemQuantity SET ItemsInStock = ItemsInStock + @QuantityToAdd WHERE ItemID = @ItemID;END;GO 

And now let’s create a user to apply security to and test how ownershipchaining works.

USE TestDB;GO CREATE USER JohnDoe WITHOUT LOGIN;GO CREATE ROLE RestockClerk;GO ALTER ROLE RestockClerkADD MEMBER JohnDoe;GO GRANT EXEC ON SCHEMA::Inventory TO RestockClerk;GO 

What we’ve done here is create a user who only exists in the database,John Doe, a role for restocking, and granted the role permissions to execute anystored procedure in the Inventory schema.

Now we can use EXECUTE AS USER to test how the security works:

USE TestDB;GO /* Reset inventory back to 15 if neededUPDATE Inventory.ItemQuantitySET ItemsInStock = 15WHERE ItemID = 2;GO */EXECUTE AS USER = 'JohnDoe';GO EXEC Inventory.RestockItem @ItemID = 2, @QuantityToAdd = 10;GO UPDATE Inventory.ItemQuantitySET ItemsInStock = ItemsInStock + 10WHERE ItemID = 2;GO REVERT;GO 

When we do so, we’ll see the following:

Note that when JohnDoe attempted to execute the stored procedure, no error wasreturned. However, when JohnDoe attempted to execute an UPDATE statement, the verysame operation the stored procedure performed, we received an error, actually twoof them. Why do we also see the error on the SELECT? The reason is documented inBooks Online:

UPDATE permissions are required on the target table. SELECT permissionsare also required for the table being updated if the UPDATE statement contains aWHERE clause, or if expression in the SET clause uses a column in the table.

When we specified the creation of both the table and the stored procedure, weput them in the same schema. By default, these objects are created without an owner.That means SQL Server will look at the ownership at the schema level. Since theyare both in the same schema, they have the same owner. We can verify like so:

USE TestDB;GO -- Show owners for objects-- NULL means no owners have been specified and will default to schema ownerSELECT o.name, o.principal_idFROM sys.objects AS O JOIN sys.schemas AS S ON O.schema_id = S.schema_idWHERE s.name = 'Inventory';GO -- Let's example the schema's ownerSELECT name, principal_idFROM sys.schemasWHERE name = 'Inventory';

And we will see that the objects don’t have owners but the schema does.

Breaking SQL Server Ownership Chaining

I said that when an object is created that no owner is assigned by default. However,we can change that.

Let’s intentionally create a separate user and make that user the ownerof the stored procedure.

USE TestDB;GOCREATE USER BrokenChain WITHOUT LOGIN;GO ALTER AUTHORIZATION ON Inventory.RestockItem TO BrokenChain;GO-- Verify ownership changeSELECT o.name, o.principal_idFROM sys.objects AS O JOIN sys.schemas AS S ON O.schema_id = S.schema_idWHERE s.name = 'Inventory';GO

And our verification should show a value other than NULL:

When SQL Server goes to evaluate the security on the objects, it will see thatthe referring stored procedure and the referred to table have different owners.As a result, SQL Server will look to see if JohnDoe has permissions against thetable. He doesn’t. Because he doesn’t, we get the same error as whenJohnDoe attempted the UPDATE statement directly.

Let’s re-test, but only execute the stored procedure:

USE TestDB;GO EXECUTE AS USER = 'JohnDoe';GO EXEC Inventory.RestockItem @ItemID = 2, @QuantityToAdd = 10;GO REVERT;GO 

And we’ll see we get the exact same error, but this time the error messagetells us it came from the stored procedure:

SQL Server Ownership Chaining Across Schemas

While schemas are used to group objects and, when used for that purpose, cansimplify our security setup, they don’t play a role in ownership chainingexcept for helping determine who the de facto owner of an object is. We have seenwhere we can break that within a particular schema. So it shouldn’t surpriseus that an ownership chain can occur for objects across schemas as long as the owneris the same.

Let’s see this with another example:

USE TestDB;GO-- Reset ownership because we're going to call the stored procedure-- From another schemaALTER AUTHORIZATION ON Inventory.RestockItem TO SCHEMA OWNER;GO -- New schemaCREATE SCHEMA Accounts;GO -- New table to track when we've back-ordered somethingCREATE TABLE Accounts.BackStockItems( ItemID INT, QuantityOrdered INT, CONSTRAINT PK_Accounts_OrderedItems PRIMARY KEY CLUSTERED (ItemID));GO INSERT INTO Accounts.BackStockItems (ItemID, QuantityOrdered) VALUES (1, 25);INSERT INTO Accounts.BackStockItems (ItemID, QuantityOrdered) VALUES (2, 15);GO CREATE PROC Accounts.ItemReceived @ItemID INT ASBEGIN BEGIN TRAN; DECLARE @Quantity INT; SET @Quantity = (SELECT QuantityOrdered FROM Accounts.BackStockItems WHERE ItemID = @ItemID); EXEC Inventory.RestockItem @ItemID = @ItemID, @QuantityToAdd = @Quantity; DELETE FROM Accounts.BackStockItems WHERE ItemID = @ItemID; COMMIT TRAN;END;GO 

Note that the stored procedure in Accounts won’t just update a table inAccounts, it will also call the RestockItem stored procedure from Inventory. Thisis why we need to revert the ownership of that stored procedure back to the schemaowner. In this way the two schemas will have the same owner and as long as the userhas the permissions to execute the stored procedure in Accounts, the ownership chainwill carry over to the stored procedure in Inventory which refers to a table inInventory. Again, another link in the ownership chain.

To test this, we will need a different user with different permissions.

USE TestDB;GO CREATE USER JaneDoe WITHOUT LOGIN;GO CREATE ROLE Accountant;GO GRANT EXECUTE ON SCHEMA::Accounts TO Accountant;GO ALTER ROLE AccountantADD MEMBER JaneDoe;GO 

Now to execute the test as JaneDoe, who has no permissions in Inventory:

USE TestDB;GO EXECUTE AS USER = 'JaneDoe';GO EXEC Accounts.ItemReceived @ItemID = 2;GO REVERT;GO 

And we see that the command executed without issue and given that we get twoseparate “1 row affected” messages, we know two separate commands haveoccurred:

You could go a step further and query both tables and you’ll see the changesreflected accordingly:

Concluding Thoughts

Ownership chaining is a powerful tool in the hands of a SQL Server developerwho wants to control how data is accessed. It gives us options where we can ensuredirect table access doesn’t occur. It allows to restrict how many rows areaffected and what rules apply, all from the database layer. Therefore, understandingwhat it can and can’t do is important for building secure solutions againstSQL Server. We’ll explore what else we can do with ownership chaining in afuture article, but hopefully this has given you enough of a start to begin playingwith them on your own.

Next Steps

• Understand whydynamic SQL can break ownership chaining.
• Be able to handleaudits with the implicit permissions that ownership chaining allow.
• Learn how it’s possible forownership chaining to occur across databases.
• All of the MSSQLTips.comSQLServer Security Tips.

About the author