Problem
I’ve heard of a security feature within Microsoft SQL Server called ownership chaining. However, I don’t understand what it is or how it works. When is it applied? What does it affect?
Solution
Ownership chaining is a security feature in SQL Server which occurs when all of the following conditions are true:
- A user (which could be an app through a login/service account) tries to access an object that makes a reference to another object. For instance, the user tries to execute a stored procedure that accesses other objects or a SELECT from a view that accesses other tables.
- The user has access to the first object, such as EXECUTE rights on the stored procedure 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 called and the object being referenced. SQL Server will also determine that the owner for both objects is the same. When those conditions are met, SQL Server will create the ownership chain.
SQL Server Ownership Chaining Example
When there’s an ownership chain, security is ignored on the object being referenced. For instance, let’s say we have the following situation:
We have a stored procedure, Inventory.RestockItem, referring to a table, Inventory.ItemQuantity. If both the stored procedure and the table have the same owner, SQL Server will form an ownership chain when a user tries to execute the stored procedure. In this case, as long as the user has EXECUTE permissions on the stored procedure, the stored procedure will be able to reference the table. The user doesn’t have to have any permissions on the table 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 things easier 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 int
AS
BEGIN
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 ownership chaining works.
USE TestDB;
GO
CREATE USER JohnDoe WITHOUT LOGIN;
GO
CREATE ROLE RestockClerk;
GO
ALTER ROLE RestockClerk
ADD 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 any stored 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 needed
UPDATE Inventory.ItemQuantity
SET ItemsInStock = 15
WHERE ItemID = 2;
GO
*/
EXECUTE AS USER = 'JohnDoe';
GO
EXEC Inventory.RestockItem @ItemID = 2, @QuantityToAdd = 10;
GO
UPDATE Inventory.ItemQuantity
SET ItemsInStock = ItemsInStock + 10
WHERE 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 was returned. However, when JohnDoe attempted to execute an UPDATE statement, the very same operation the stored procedure performed, we received an error, actually two of them. Why do we also see the error on the SELECT? The reason is documented in Books Online:
UPDATE permissions are required on the target table. SELECT permissions are also required for the table being updated if the UPDATE statement contains a WHERE 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, we put 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 they are 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 owner
SELECT o.name, o.principal_id
FROM sys.objects AS O
JOIN sys.schemas AS S
ON O.schema_id = S.schema_id
WHERE s.name = 'Inventory';
GO
-- Let's example the schema's owner
SELECT name, principal_id
FROM sys.schemas
WHERE 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 owner of the stored procedure.
USE TestDB;
GO
CREATE USER BrokenChain WITHOUT LOGIN;
GO
ALTER AUTHORIZATION ON Inventory.RestockItem TO BrokenChain;
GO
-- Verify ownership change
SELECT o.name, o.principal_id
FROM sys.objects AS O
JOIN sys.schemas AS S
ON O.schema_id = S.schema_id
WHERE 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 that the 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 the table. He doesn’t. Because he doesn’t, we get the same error as when JohnDoe 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 message tells 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, can simplify our security setup, they don’t play a role in ownership chaining except for helping determine who the de facto owner of an object is. We have seen where we can break that within a particular schema. So it shouldn’t surprise us that an ownership chain can occur for objects across schemas as long as the owner is 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 schema
ALTER AUTHORIZATION ON Inventory.RestockItem TO SCHEMA OWNER;
GO
-- New schema
CREATE SCHEMA Accounts;
GO
-- New table to track when we've back-ordered something
CREATE 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
AS
BEGIN
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 in Accounts, it will also call the RestockItem stored procedure from Inventory. This is why we need to revert the ownership of that stored procedure back to the schema owner. In this way the two schemas will have the same owner and as long as the user has the permissions to execute the stored procedure in Accounts, the ownership chain will carry over to the stored procedure in Inventory which refers to a table in Inventory. 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 Accountant
ADD 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 two separate “1 row affected” messages, we know two separate commands have occurred:
You could go a step further and query both tables and you’ll see the changes reflected accordingly:
Concluding Thoughts
Ownership chaining is a powerful tool in the hands of a SQL Server developer who wants to control how data is accessed. It gives us options where we can ensure direct table access doesn’t occur. It allows to restrict how many rows are affected and what rules apply, all from the database layer. Therefore, understanding what it can and can’t do is important for building secure solutions against SQL Server. We’ll explore what else we can do with ownership chaining in a future article, but hopefully this has given you enough of a start to begin playing with them on your own.
Next Steps
- Understand why dynamic SQL can break ownership chaining.
- Be able to handle audits with the implicit permissions that ownership chaining allow.
- Learn how it’s possible for ownership chaining to occur across databases.
- All of the MSSQLTips.com SQL Server Security Tips.
