Being a DBA or DBE, understanding how Ownership Chains work and how impersonate another principal for changing the execution context are really important. This topic was discussed while I was conducting the course 6232A on last Saturday, thought to blog on it as it would be useful to you all.
What is Ownership Chain?
When we perform an action with set of statements, objects used are accessed sequentially and this sequence is called as a Chain. Once the chain is established, permissions on objects in the chain are evaluated differently, not the way it checks permissions when they are separately accessed. If the objects accessed in the chain are owned by a same owner, Ownership Chain is established. Within the ownership chain, SQL Server checks the permission for the first object and skips checking on others. This is the advantage of Ownership Chain because it offers slight performance benefit skipping permission checks on other objects. If SQL Server finds a broken ownership chain, it starts evaluating permission on them.
What is Impersonation?
SQL Server Impersonation means, allowing to change the execution context with another principal. Simply, perform some actions (execute set of queries) using someone else privileges. This can be done explicitly by using the stand-alone EXECUTE AS statement or implicitly by using the EXECUTE AS clause on modules.
Let’s first check with Ownership Chaining and then go for Impersonation. I did this in a TestDatabase, you can do the same. The first code snippet creates two logins and two users. Note that SalesPerson is added to db_owner role.
-- Create two logins
CREATE LOGIN SalesPerson WITH PASSWORD = '123'
, CHECK_POLICY = OFF
GO
CREATE LOGIN MarketingPerson WITH PASSWORD = '123'
, CHECK_POLICY = OFF
GO
-- Create users for logins.
USE TestDatabase
GO
CREATE USER SalesPerson FROM LOGIN SalesPerson
GO
sp_addrolemember 'db_owner', 'SalesPerson'
GO
CREATE USER MarketingPerson FROM LOGIN MarketingPerson
GO
With next code you can see that Impersonation has been done explicitly using EXECUTE AS. What I do here is, impersonate SalesPerson and then create a schema and a table. Note that SalesPerson becomes the owner of these objects.
EXECUTE AS USER = 'SalesPerson'
GO
CREATE SCHEMA Sales
GO
CREATE TABLE Sales.Sales
(
Date date
, Amount money
)
GO
REVERT
GO
Next code impersonates MarketingPerson and sees whether he can access the table Sales.Sales. Since he has no permission on this table, error is thrown for SELECT query.
“The SELECT permission was denied on the object 'Sales', database 'TestDatabase', schema 'Sales'.”
EXECUTE AS USER = 'MarketingPerson'
GO
SELECT * FROM Sales.Sales
REVERT
GO
Let’s check Ownership Chaining. With next code, SalesPerson creates a stored procedure that returns data from Sales.Sales, and he grants EXECUTE permission to MarketingPerson.
EXECUTE AS USER = 'SalesPerson'
GO
CREATE PROC Sales.GetSales
AS
SELECT * FROM Sales.Sales
GO
GRANT EXECUTE ON Sales.GetSales TO MarketingPerson
REVERT
GO
We know that MarketingPerson has no read permission on Sales.Sales table but with next code, you can see that he can read the table through Sales.GetSales procedure.
EXECUTE AS USER = 'MarketingPerson'
GO
-- MarketingPerson can access the table
-- through the SP
EXEC Sales.GetSales
-- This throws the same error
SELECT * FROM Sales.Sales
REVERT
GO
The reason for this is, the Ownership Chain. As MarketingPerson has EXECUTE permission, he can execute the SP. Although he has no permission on Sales.Sales, SQL Server does not evaluate permission on it for MarketingPerson because it is owned by the same owner who owns Sales.GetSales. Note that we have not explicitly set owners for both Sales.GetSales and Sales.Sales, hence owners of them are considered as Sales schema’s owner; SalesPerson. Below code shows it;
SELECT USER_NAME( OBJECTPROPERTY(OBJECT_ID('Sales.Sales'), 'OwnerId'))
If the SP contains an object owned by someone else, it breaks Ownership Chain. Let’s test this too. Below code adds a new table, note that no impersonation is established, so, it is created by dbo. Since it is under Sales schema too, the owner of the schema (SalesPerson) becomes the owner of the new table.
CREATE TABLE Sales.SalesCommisions
(
SaleNumber int
, Amount money
)
GO
Next, the code of the SP is altered by SalesPerson. Now it contains both tables but still MarketingPerson can execute the SP without any issues even though he has no permission on new table.
EXECUTE AS USER = 'SalesPerson'
GO
ALTER PROC Sales.GetSales
AS
BEGIN
SELECT * FROM Sales.Sales
SELECT * FROM Sales.SalesCommisions
END
GO
REVERT
GO
EXECUTE AS USER = 'MarketingPerson'
GO
-- Can see both tables
EXEC Sales.GetSales
-- Both statement throw errors
SELECT * FROM Sales.Sales
SELECT * FROM Sales.SalesCommisions
REVERT
GO
Still the Ownership Chain works. Let’s break it. Let’s change the owner of the new table Sales.SalesCommisions as dbo.
ALTER AUTHORIZATION ON OBJECT::Sales.SalesCommisions TO dbo
Now if MarketingPerson executes the SP, he gets an error for Sales.SalesCommisions table:
”The SELECT permission was denied on the object 'SalesCommisions', database 'TestDatabase', schema 'Sales'.”
The reason is, the second object, Sales.SaleCommisions is not owned by the same person who owns the SP, hence SQL Server checks and sees whether MarketingPerson has permission on Sales.SaleCommissions.
How can we let MarketingPerson to execute the SP without giving explicit permission on Sales.SaleCommissions? Impersonation is the answer. We can use EXECUTE AS explicitly with the stored procedure, instructing SQL Server to execute the stored procedure with someone else privileges without using CALLER’s privileges. See the code below;
EXECUTE AS USER = 'SalesPerson'
GO
ALTER PROC Sales.GetSales
WITH EXECUTE AS 'SalesPerson'
AS
BEGIN
SELECT * FROM Sales.Sales
SELECT * FROM Sales.SalesCommisions
END
GO
REVERT
GO
Now if MarketingPerson executes the SP, he will see the result without seeing an error because the execution context for the SP’s execution is set with SalesPerson not with MarketingPerson.
