ownership chain

Question

Post reply

ownership chain

sqlfriend

SSC Guru

Points: 52781
More actions
January 11, 2022 at 6:37 pm
Go to Answer
#3972092
I am reading microsoft doc about owner chain, it says :
quotes:
```
quotes:
---
USE AdventureWorks2016;
GO

CREATE USER [DP300User1] WITH PASSWORD = 'Pa55.w.rd';
GO

CREATE ROLE [SalesReader];
GO

ALTER ROLE [SalesReader] ADD MEMBER [DP300User1];
GO

GRANT SELECT, EXECUTE ON SCHEMA::Sales TO [SalesReader];
GO

CREATE OR ALTER PROCEDURE Sales.DemoProc
AS
SELECT P.Name,
SUM(SOD.LineTotal) AS TotalSales,
SOH.OrderDate
FROM Production.Product P
INNER JOIN Sales.SalesOrderDetail SOD ON (SOD.ProductID = P.ProductID)
INNER JOIN Sales.SalesOrderHeader SOH ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY P.Name,
SOH.OrderDate
ORDER BY TotalSales DESC;

GO

EXECUTE AS USER = 'DP300User1';

SELECT P.Name,
SUM(SOD.LineTotal) AS TotalSales,
SOH.OrderDate
FROM Production.Product P
INNER JOIN Sales.SalesOrderDetail SOD ON (SOD.ProductID = P.ProductID)
INNER JOIN Sales.SalesOrderHeader SOH ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY P.Name,
SOH.OrderDate
ORDER BY TotalSales DESC;
```
The above query results in an error that the user DP300User1 does not have SELECT permission, because the role that the user belongs to does not have any privileges in the Production schema. Now we can try to execute the stored procedure:
SQL
```
EXECUTE AS USER = 'DP300User1';

EXECUTE Sales.DemoProc;
```
The DP300User1 has EXECUTE permission on the stored procedure in the Sales schema, because the role the user belongs to has EXECUTE permission on the Sales schema. Because the table has the same owner as the procedure, we have an unbroken ownership chain, and the execution will succeed and results will be returned.
----- quotes done.
I am not sure I fully understand this, esp, the last sentence above. it says the table has the same owner as the procedure, but I see the owner of product table is production, and the procedure owner is sales, why it says it is the same owner?
and Does the EXECUTE Sales.DemoProc really returns success?
Thanks
- This topic was modified 2 years, 10 months ago by Steve Jones - SSC Editor. Reason: format code
Andy sql

SSCrazy Eights

Points: 9496
More actions
January 17, 2022 at 5:24 pm
Answer
#3973949

If a User has the EXECUTE permissions on a SP, they are automagically granted the rights to perform whatever the SP can do.
The User, when creating the SP, requires the relevant permissions on all the relevant objects quoted by the SP [Caution: I am not totally convinced this is correct; I couldn't immediately find an accurate source to confirm].
After creation, the Ownership Chain determines if the SP can perform the tasks; if the Ownership Chain is broken, then permissions of the User (calling the EXEC on the SP) come into play.
I think, in the background: with a normal database setup, all objects within a Schema are owned by the Schema Owner; and all Schemas are owned by the database owner (might be db_owner or defaulted to [dbo]). This means the ownership chain from the SP, up to the dbo, and down to the object (eg. a table in a different schema) is unbroken.
If the User, who created the SP, is later denied access; the existing SP continues unaffected. Unless the User was also the owner of the SP or Schema!
This is a bit clunky, and really hard to audit. And as already mentioned, cross-database-chaining is another layer of complexity (and a key reason why [sa] should never be the owner of any database!). However, by restricting EXEC permissions to a limited number of SPs, it is easy to control what happens in the Schema/Database.
You can change the Schema Owner, or the owner of an individual SP, using "ALTER AUTHORIZATION ON". I suggest you play with these scenarios and see what you can find out.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 2

You've been here for more than 12 years. Please learn how to post code in a code window so that it's easier to read. Here's the key when you're creating a post. Click where indicated below and then paste into the code window that appears. And try to avoid the over-use of GO. It just interrupts the flow of code when used unnecessarily. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ken McKelvey SSCoach Points: 18842 More actions · Answer 3

I presume you want to know why, when running under the DP300User1 context, the SP works but the query on it's own does not.

When a SP is created by default it has the permissions, within the current database, of the user, presumably a sysadmin or dbowner, who created it. DP300User1 has the EXEC permission for the schema so can run the SP which then executes with the permissions of the user who created the SP.

When DP300User1 just runs the query it has SELECT permission for the Sales schema but cannot access the Production schema so the query fails.

Things become more complicated when doing cross database queries. Normally queries are then done via SPs which are signed with certificates:

https://www.sommarskog.se/grantperm.html

ScottPletcher SSC Guru Points: 101095 More actions · Answer 4

Ken McKelvey wrote:

I presume you want to know why, when running under the DP300User1 context, the SP works but the query on it's own does not.
When a SP is created by default it has the permissions, within the current database, of the user, presumably a sysadmin or dbowner, who created it. DP300User1 has the EXEC permission for the schema so can run the SP which then executes with the permissions of the user who created the SP.

I don't think that's true. The default is "EXECUTE AS CALLER", not "AS OWNER". That is, the proc should run under the context of the user running the proc, not under the proc owner (the proc creator is actually irrelevant, it's who owns it that matters).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

sqlfriend SSC Guru Points: 52781 More actions · Answer 5

Yes, I totally understand the user cannot run the select query, because he doesnot have permissions on Production schema.

It is the stored procedure permissions that confused me. But Andy's answer: If a User has the EXECUTE permissions on a SP, they are automagically granted the rights to perform whatever the SP can do.

That makes sense to me and it is a clear answer.

Thanks,