Ownership Chaining

Question

Ownership Chaining

caddywonkus

Right there with Babe

Points: 763
More actions
August 1, 2013 at 2:04 pm

#296209

Have there been any changes, even minor ones, to ownership chaining in SQL 2012?
I have a problem with one database, migrated from 2008 R2 to 2012. The scenario is:
* schema1.ViewA ... selects from schema2.ViewB
* schema2.ViewB ... selects from dbo.ScalarFunctionC
* dbo.ScalarFunctionC does not, itself, reference any objects at all, just internal calculations
* User1 has SELECT permissions on schema1 -only- ... no other permissions on any schema or object in the database at all.
All schemas are owned by dbo, and the database is owned by "sa". SQL 2012 has SP1 and CU4 applied.
Problem:
* On 2008 R2 the above is working just fine. User1 gets data from schema1.ViewA, but cannot directly call the other view or function.
* On 2012 User1 gets "access denied" on the function. Not the view in the middle of the dependency chain... the function at the very end of it.
Now to be fair... I tried a simple, rudimentary test of the above scenario... and it worked fine on 2012. Even used the same schemas, just much simpler objects (e.g. each view only returns a column or two; the function just returns 1 and doesn't do anything else). User1 can read from the top-most view, can't directly call the other view or the function... just as it should be.
I similarly tried making dummy copies of the "real" much-more-complex objects involved, i.e. changed the names of the objects themselves, changed the objects to use the changed names where applicable, and deployed them all as "new" objects to the same schemas. And... I get the same problem - the same user calling the renamed view gets an error on the renamed function in 2012... but in 2008 R2 it's fine. Exact same code, new objects, and only me as a sysadmin deploying them... access denied in 2012, but fine in 2008.
So, yes... I know part of it is the code itself in those more complex objects. But with no changes to that code, not even redeploying it, just literally a restored copy of the database (from a native to-disk backup), it works in 2008 R2 and fails in 2012. So... 2012 is part of the problem, too. What I don't know is if it's a bug or some subtle change to permissions and/or ownership chaining I simply don't know about yet.
Has anyone seen something like this?

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

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

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

Without a repro it is difficult to say what might be going on. There are no changes in this area that I know of. But check that the function is actually owned by dbo.

This repro works for me:

CREATE USER pelle WITHOUT LOGIN

go

CREATE SCHEMA schema1;

go

CREATE SCHEMA schema2

go

CREATE FUNCTION dbo.somefunc(@d float) RETURNS float WITH SCHEMABINDING AS

BEGIN

RETURN log(@d)

END

go

CREATE VIEW schema2.viewB AS SELECT dbo.somefunc(1E0) AS col

go

CREATE VIEW schema1.viewA AS SELECT col FROM schema2.viewB

go

GRANT SELECT ON SCHEMA::schema1 TO pelle

go

EXECUTE AS USER = 'pelle'

go

SELECT col FROM schema1.viewA

go

REVERT

go

DROP USER pelle

DROP VIEW schema1.viewA

DROP VIEW schema2.viewB

DROP FUNCTION dbo.somefunc

DROP SCHEMA schema1

DROP SCHEMA schema2

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

caddywonkus Right there with Babe Points: 763 More actions · Answer 2

Yeah, I tried something simple like that, too... and it worked fine. So I know part of the problem is the "real" code. It's a lot more complex, and given the sensitive nature of it I can't really post the specific code here. Never mind that I'd probably get yelled at for doing so - it's huge (and it ain't mine).

The curious thing is that the exact same code... only changing the object names, and where applicable, references to them... yields the same thing. Works fine in 2008, access denied to the function in 2012. So... it's a combination of the code and something going on in 2012.

Our development team is going to open a case with Microsoft on it. Developers and DBA's here are well-separated in their duties (i.e. it would take me a while to study it to even guess what it's actually doing from a business-logic perspective)... and the key here is clearly in the code itself, triggering "something unknown." It's only happening in 2012, but it's also only happening with that code, as we've both shown the fundamentals of ownership chains haven't changed. We're coordinating efforts but at this time development is on point. Something changed; by design or a bug, something changed. If we find an answer one way or another, I'll post it here.

Thanks!

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 3

Yeah, given that it is not easily reproducible, and only appears in one specific context, I think opening a case with Microsoft is the right way. Particularly when you cannot share the code publicly. Microsoft will most likely require to get copy of a the code or the database, but that's a completely different level of confidentiality. From what you say, it smells like a bug.

If you would find the time to report back when the issue have been resolved, I would appreciate it!

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

caddywonkus Right there with Babe Points: 763 More actions · Answer 4

We don't have an official answer from MS yet, but it is looking like a bug. And I just figured out a big key to it: Common Table Expressions.

Let me update the scenario:

* schema1.ViewA ... selects from schema2.ViewB ... using a CTE

* schema2.ViewB ... selects from dbo.ScalarFunctionC ... using a CTE (and the function call is inside that CTE)

* dbo.ScalarFunctionC does not, itself, reference any objects at all, just internal calculations

My (and your) rudimentary tests yesterday didn't include CTEs. I just typed out a new simple test today, but made sure both views also have CTEs, and the "middle" one calls the function. And I get the same error when a user that only has permissions on schema1.ViewA tries to call it - access denied on the function.

CREATE DATABASE TestDB;

GO

IF SUSER_ID('user1') IS NULL

CREATE LOGIN user1 WITH PASSWORD = 'abc', CHECK_POLICY = OFF;

USE TestDB;

GO

CREATE SCHEMA schema1 AUTHORIZATION dbo;

GO

CREATE SCHEMA schema2 AUTHORIZATION dbo;

GO

CREATE USER user1 FOR LOGIN user1 WITH DEFAULT_SCHEMA = schema1;

GO

CREATE TABLE dbo.things (id int);

INSERT INTO dbo.things (id) SELECT 1;

GO

CREATE FUNCTION dbo.f_thing (@x int) RETURNS INT AS

BEGIN

RETURN 1;

END

GO

CREATE VIEW schema2.vGetThings AS

WITH CTE AS (SELECT id, dbo.f_thing(1) as f_thing FROM dbo.things) SELECT id, f_thing FROM CTE;

GO

CREATE VIEW schema1.vGetAllThings AS

WITH CTE AS (SELECT id, f_thing as the_thing FROM schema2.vGetThings) SELECT id, the_thing FROM CTE;

GO

GRANT SELECT ON SCHEMA::schema1 TO user1;

EXECUTE AS USER = 'user1';

PRINT 'User: ' + USER_NAME();

SELECT * FROM schema1.vGetAllThings;

REVERT;

/* cleanup

USE master;

DROP DATABASE TestDB;

DROP LOGIN user1;

*/

Result:

(1 row(s) affected)

User: user1

Msg 229, Level 14, State 5, Line 6

The EXECUTE permission was denied on the object 'f_thing', database 'TestDB', schema 'dbo'.

The exact same code, on 2008 (and just for fun I tried it on 2005 as well)... works as expected; user1 can read from the view without any error (though can't call the sub-objects directly).

Edit:

This goes just a tad deeper than the CTE's. The view using them are fine - so long as the CTE doesn't call the function. In fact, if the function call is still in the view (schema2.vGetThings), but not inside the CTE, just in the SELECT from it... it works fine.

caddywonkus Right there with Babe Points: 763 More actions · Answer 5

Forgive the double-post here, but I wanted to make sure this was highlighted separately. MS has confirmed to us it's a bug. They claim it's fixed in CU5, though the KB article indicates symptoms we didn't see and doesn't mention ownership chaining. Nonetheless the article is very similar and addresses "CTE calling a function," so... good chance CU5 does happen to fix this problem, too.

Here's the article: http://support.microsoft.com/kb/2861387

We're testing it right now.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 6

Thanks for the update. Yes, that is definitely a bug. Good to hear that MS has a fix.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

caddywonkus Right there with Babe Points: 763 More actions · Answer 7

caddywonkus

Right there with Babe

Points: 763

August 2, 2013 at 3:02 pm

#1638362

Confirmed; CU5 fixed this issue.

Thanks!!