Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Ownership Chaining Expand / Collapse
Author
Message
Posted Thursday, August 1, 2013 2:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 9:02 AM
Points: 18, Visits: 532
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?

Post #1480126
Posted Thursday, August 1, 2013 4:15 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 9:35 AM
Points: 811, Visits: 738
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



Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1480202
Posted Thursday, August 1, 2013 4:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 9:02 AM
Points: 18, Visits: 532
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!
Post #1480205
Posted Friday, August 2, 2013 1:11 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 9:35 AM
Points: 811, Visits: 738
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!


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1480273
Posted Friday, August 2, 2013 10:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 9:02 AM
Points: 18, Visits: 532
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.
Post #1480494
Posted Friday, August 2, 2013 11:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 9:02 AM
Points: 18, Visits: 532
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.
Post #1480538
Posted Friday, August 2, 2013 1:37 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 9:35 AM
Points: 811, Visits: 738
Thanks for the update. Yes, that is definitely a bug. Good to hear that MS has a fix.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1480589
Posted Friday, August 2, 2013 3:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 9:02 AM
Points: 18, Visits: 532
Confirmed; CU5 fixed this issue.

Thanks!!
Post #1480622
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse