CODEWITHSUNDEEP
sql-serverstored-procedurespermissions
FDavidov
FDavidov

Reputation: 3675

Permission cascading/inheritance in SQL Server 2012

I have a system with three layers: A Web client, a Servlet (TOMCAT) and a SQL Server with data and logic.

The client sends request towards the Servlet who forwards it to a single Stored Procedure (let's call it DISPATCHER). The DISPATCHER examines the nature of the request and invokes another stored procedure aimed to handle the specific request received.

The Serlet's connection takes place using a specific set of credentials (let's refer to it as Web_Client) and EXECUTE permission for the DISPATCHER had been granted to it, while no access was defined for any other element (procedure, table, view, etc.).

To my surprise, though the Web_Client is entitled to execute the DISPATCHER, an error message is received when it invokes SOME inner procedures (while others do not generate any error).

What could be the reason for this strange behavior?

Upvotes: 1

Views: 1673

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82504

To me, it seems like an ownership chain problem.

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 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.

The basic concept of ownership chain is that when you are granted access to a database object - a stored procedure in your case - your permissions are not checked for any other database object that the stored procedure uses if it is owned by the same login that owns the original stored procedure.
If the original stored procedure use some database object that is not owned by the owner of the stored procedure, then you will have to have permissions to use this object.

Please note that the user that execute the first object in the chain doesn't need to be that object's owner, just to have execute permissions to it.

So in this case, it seems that the inner procedures that throws the execute permission denied error simply belongs to another login.

Upvotes: 0

sepupic
sepupic

Reputation: 8687

What you are talking about is ownership chaining.

You can read more on it here: Ownership Chains

In a few words, the permissions are NOT checked if there is unbroken ownership chain, i.e. your outer proc and your inner procs have the same owner and all the objects within them have the same owner.

Ownership chain is broken in case of different owners, in case of dynamic sql or different databases.

So what you need now is to read the error message with attention and check the owner of the object on which there is no permission, it's there where the chain is broken.

About DENY. Deny would not provoke this error if the ownership chain is unbroken, it's because the permissions are not checked in this case, so DENY is not check either.

Upvotes: 1

Related Questions