June 9, 2011 at 1:47 pm
Hi guys,
We did some changes to our production SQL server instance a couple of days ago.
After the changes were applied, it turned out that a specific application failed and we had to recover the databases that we changed.
Here are some facts:
OS: Windows 2008
SQL server version: 2008 version 10.00.4000 Enterprise Edition (SP2)
We migrated from SQL Server 2005 4 months ago.
Current situation is:
Many of our schema's are owned by user 'sys' - this has been inherited from SQL Server 2005.
In 2008, it seems that it is no longer possible to create new schema's owned by 'sys'.
'Cross database ownership chaining' is turned off for all user db's (which is the default).
What do we want to achieve:
We wish to create new schema's within existing databases and to create views that joins tables from different databases (cross join). The most important thing is, that we want to be able to grant SELECT permissions ONLY against the views - the end users should not have SELECT permissions against the underlying tables.
What did we try:
In order to benefit from 'Cross Database Ownership Chaining', our current and future schema's have to be owned by the same user. So we decided to change the ownership of existing schema's to 'dbo' user and regrant all permisssions
( ALTER AUTHORIZATION ON SCHEMA::[schema_name] TO [dbo]
GRANT SELECT ON ::schema_name to user
etc ).
We also activated 2 databases for 'cross database ownership chaining'
( ALTER DATABASE db_name SET DB_CHAINING ON ).
Just to prepare for using this option.
The set-up is like this:
User logs in via an AD user (being a member of one or more AD groups).
The application is written in Visual Basic using ado.net and it connects to database DBX via ODBC.
Under DBX there is a synonym pointing at a view in schema A under another database DBY.
The view refers a table in the same schema and database (DBY.A).
SELECT permissions against the synonym in DBX and the view in DBY have been granted to the AD group(s) via roles.
What happened:
After doing the described changes on database DBY and DBZ, the application returned an error saying that user didn't have select permissions against the table in schema A under DBY.
We tried to login to SSMS with the same AD user and to our surprise the same SQL now ran without errors !
After restoring the 2 databases that we changed, the application worked again.
Today we have tried to reproduce the error in our development and test environments - but without any luck :crying:
Can anybody give me a clue about what went wrong here ?
To me it looks like an ownership chaining problem in schema A between the view and the underlying table. But why did it only occur at the prod db - and why didn't we see the error when same user connected to SSMS and ran the query ?
The most important thing for us is to find a solution that definitely will solve the problem.
Since we can not reproduce the problem in test, we have to be pretty sure that we find the correct cure before we do another attempt at the production system 🙂
Best regards
Carsten
June 9, 2011 at 4:50 pm
carsten.jorgensen (6/9/2011)
Hi guys,We did some changes to our production SQL server instance a couple of days ago.
After the changes were applied, it turned out that a specific application failed and we had to recover the databases that we changed.
Here are some facts:
OS: Windows 2008
SQL server version: 2008 version 10.00.4000 Enterprise Edition (SP2)
We migrated from SQL Server 2005 4 months ago.
Current situation is:
Many of our schema's are owned by user 'sys' - this has been inherited from SQL Server 2005.
In 2008, it seems that it is no longer possible to create new schema's owned by 'sys'.
'Cross database ownership chaining' is turned off for all user db's (which is the default).
What do we want to achieve:
We wish to create new schema's within existing databases and to create views that joins tables from different databases (cross join). The most important thing is, that we want to be able to grant SELECT permissions ONLY against the views - the end users should not have SELECT permissions against the underlying tables.
What did we try:
In order to benefit from 'Cross Database Ownership Chaining', our current and future schema's have to be owned by the same user. So we decided to change the ownership of existing schema's to 'dbo' user and regrant all permisssions
( ALTER AUTHORIZATION ON SCHEMA::[schema_name] TO [dbo]
GRANT SELECT ON ::schema_name to user
etc ).
We also activated 2 databases for 'cross database ownership chaining'
( ALTER DATABASE db_name SET DB_CHAINING ON ).
Just to prepare for using this option.
Which 2, only DBY and DBZ right? It sounds like you need it turned on in all 3, DBX, DBY and DBZ.
The set-up is like this:
User logs in via an AD user (being a member of one or more AD groups).
The application is written in Visual Basic using ado.net and it connects to database DBX via ODBC.
Under DBX there is a synonym pointing at a view in schema A under another database DBY.
The view refers a table in the same schema and database (DBY.A).
Is the schema the synonym is in also authorized by dbo? Looking for a broken ownership chain...
SELECT permissions against the synonym in DBX and the view in DBY have been granted to the AD group(s) via roles.
What happened:
After doing the described changes on database DBY and DBZ, the application returned an error saying that user didn't have select permissions against the table in schema A under DBY.
The fact that the app connects to DBX is why I think DB Chaining needs to be on here too.
We tried to login to SSMS with the same AD user and to our surprise the same SQL now ran without errors !
After restoring the 2 databases that we changed, the application worked again.
Today we have tried to reproduce the error in our development and test environments - but without any luck :crying:
Can anybody give me a clue about what went wrong here ?
To me it looks like an ownership chaining problem in schema A between the view and the underlying table. But why did it only occur at the prod db - and why didn't we see the error when same user connected to SSMS and ran the query ?
The most important thing for us is to find a solution that definitely will solve the problem.
Since we can not reproduce the problem in test, we have to be pretty sure that we find the correct cure before we do another attempt at the production system 🙂
Best regards
Carsten
The scenario is large by a forum's standards so sorry I have more questions than answers. Mostly I'm just poking around at this point...hopefully some of what I picked out as points of interest will help lead to a solution.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 10, 2011 at 1:40 am
Thank you for your suggestions.
Yes - The synonym in db DBX is in a schema owned/authorized by dbo.
And I do understand why you are suggesting that even DBX should be activated for 'cross database ownership chaining'.
But the case is that this existing unchanged application doesn't make use of cross database ownership chaining. It worked before we considered doing this change and it works again after db restore (because SELECT permissions are granted against the synonym in DBX as well as against the view in DBY).
June 10, 2011 at 8:29 am
carsten.jorgensen (6/10/2011)
Thank you for your suggestions.Yes - The synonym in db DBX is in a schema owned/authorized by dbo.
And I do understand why you are suggesting that even DBX should be activated for 'cross database ownership chaining'.
But the case is that this existing unchanged application doesn't make use of cross database ownership chaining. It worked before we considered doing this change and it works again after db restore (because SELECT permissions are granted against the synonym in DBX as well as against the view in DBY).
I think this is precisely the issue...because now it needs to.
I do not know the exact scenario you had with the upgrade from 2005 and how that affected the sys schema, but I suspect that your users were implicitly permitted to select from tables in schemas owned by sys, either by way of a grant or by way of object authorization that is no longer present now that things are authorized by dbo. This masking effect is exposed now that your schemas are owned by dbo so you must explicitly provide an ownership chain.
Regarding the differences between DEV and PROD, have you checked to make sure the owners of the databases themselves are the same across environments? Database ownership can mask granular permission deficiencies.
Just some random thoughts...I hope some of my ramblings help.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 10, 2011 at 9:02 am
In fact, we tried to reproduce the problem at our test environment by restoring a backup of both databases involved. We used a fresh backup of DBX (the unchanged db) and a backup that we took of DBY right before we decided to restore. And yes, I have checked both database and schema ownership.
The select permissions are explicitly granted against the synonym and the view to the AD group via roles. And all these permissions were regranted after change of schema ownership from sys to dbo.
A few of the users on the application seemed to be running ok - the difference is that these users have general select permissions against the entire A schema at DBY via another AD Group. So of course this could explain why these users were not affected.
If I could go back in time, I believe that I would try to alter the view in order to check, if that would solve a possible ownership chaining issue between the view and the table.
I didn't think of that while we had the problem 🙁
Any comments on whether an alter of the view may have solved the problem ?
It is also possible that our experiences could be caused by a SQL server bug.
And in that case it is important to find the right work around.
June 10, 2011 at 10:55 am
carsten.jorgensen (6/10/2011)
In fact, we tried to reproduce the problem at our test environment by restoring a backup of both databases involved. We used a fresh backup of DBX (the unchanged db) and a backup that we took of DBY right before we decided to restore. And yes, I have checked both database and schema ownership.The select permissions are explicitly granted against the synonym and the view to the AD group via roles. And all these permissions were regranted after change of schema ownership from sys to dbo.
A few of the users on the application seemed to be running ok - the difference is that these users have general select permissions against the entire A schema at DBY via another AD Group. So of course this could explain why these users were not affected.
If I could go back in time, I believe that I would try to alter the view in order to check, if that would solve a possible ownership chaining issue between the view and the table.
I didn't think of that while we had the problem 🙁
Any comments on whether an alter of the view may have solved the problem ?
It is also possible that our experiences could be caused by a SQL server bug.
And in that case it is important to find the right work around.
Sorry, I did not pick up on how you were planning to alter the view. What were you planning there?
For the sake of focusing solely on PROD I am going to shelve the differences you saw in DEV as an environment-diff-issue for now since that could be any number of things.
I think that you have decided that enabling DBX for cross database ownership chaining is unimportant because the user has SELECT permissions on the VIEW in DBY therefore giving them SELECT rights on the underlying table. I think this would be true if they were selecting directly from the view in DBY, however they are using the synonym in DBX first. I keep coming back to the fact that you may need it. Sorry if I'm being hard-headed but here's why I say that:
The ownership chain starts with the first link in the chain, the synonym, but it sounds like you are wanting it or are expecting that it can to start with the second link in the chain, the view. Once an ownership chain is broken however all permissions on subsequent objects are explicitly checked. It is only when the ownership chain is completely unbroken where permission checks are bypassed.
To recap, this is how I am understanding it:
1) AppUser connects to server, sets DB context to DBX
----> login's AD group is authorized to connect to server and is mapped to DBX, all is well
2) AppUser attempts to select data from DBX.UnimportantSchemaName.synonym for which AppUser has explicit SELECT permission
----> explicit SELECT granted, beginning of ownership chain
3) DBX.UnimportantSchemaName.synonym is resolved to DBY.A.View for which AppUser also has explicit SELECT permission
----> ownership chain was broken because DBX cannot participate in cross-database ownership chaining, permissions will now be explicitly checked for all objects, explicit SELECT was granted so user can select from VIEW, all is still well but we lost our ownership chain
4) DBY.A.View is selecting data from DBY.A.SomeTable. AppUser has explicit SELECT permission on the view, but does not have select permission on the table
----> ownership chain was already broken, permissions are now being explicitly checked on all objects, explicit SELECT was not granted on DBY.A.SomeTable so user receives error message
I could totally be off my rocker but please let me know if this matches up to anything close to reality in PROD.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 10, 2011 at 4:33 pm
Thank you for a very detailed walk through of all the steps.
The set-up is exactly as you describe it.
And I totally agree, that the ownership chain breaks when going from [DBX].[UnimportantSchemaName].[synonym] to [DBY].[A].[view] since it is another db - and because DBX db is not configured for Cross Database Ownership Chaining.
I may be wrong, but I would assume that a 'new ownership chain' starts at DBY db. And since the view and table is in the same schema (A), permissions should not be checked against the table (isn't it the owner of the schema that would determine the owner of the table and view ?).
At least this is the behaviour we experience with the configuration we had before we applied the change.
Pls also check out step 5 in Microsoft doc abt ownership chaining:
It says,
"Because the owner of this table differs from the owner of the previous object (Joe, not Sam), full information about permissions on this table is retrieved."
Note that ownership chaining was already broken in step 4.
The description indicates that if the owner doesn't differ from the previous one, no permission check will be done.
Based on those assumptions, I suspected that some (hidden) information regarding ownership on the view could have been incorrect in the database leading to an unexpected broken ownership chaining followed by a permission check against the table.
That is why I suggested to do an ALTER on the view - simply by scripting the ALTER VIEW via SSMS and executing it. If any hidden ownership information regarding the view was kept in the database, I would believe that the ALTER VIEW would update it.
It still doesn't explain why the user could successfully execute the SQL via SSMS - while the SQL failed when same user connected through the application.
June 10, 2011 at 8:26 pm
carsten.jorgensen (6/10/2011)
Thank you for a very detailed walk through of all the steps.The set-up is exactly as you describe it.
And I totally agree, that the ownership chain breaks when going from [DBX].[UnimportantSchemaName].[synonym] to [DBY].[A].[view] since it is another db - and because DBX db is not configured for Cross Database Ownership Chaining.
I now see why you did not think it was important to enable cross-database chaining on DBX and I now agree with you (see below).
I may be wrong, but I would assume that a 'new ownership chain' starts at DBY db. And since the view and table is in the same schema (A), permissions should not be checked against the table (isn't it the owner of the schema that would determine the owner of the table and view ?).
At least this is the behaviour we experience with the configuration we had before we applied the change.
Pls also check out step 5 in Microsoft doc abt ownership chaining:
It says,
"Because the owner of this table differs from the owner of the previous object (Joe, not Sam), full information about permissions on this table is retrieved."
Note that ownership chaining was already broken in step 4.
The description indicates that if the owner doesn't differ from the previous one, no permission check will be done.
I agree with you. I did not realize that a new chain could be started once the initial chain had been broken. I tested it locally and was able to restart a chain after the initial one was broken so I learned something here. Thank you. I had never considered it nor have I gone this deep on the topic to explicitly encounter the scenario.
In testing the scenario and reviewing the article I see the wording you're calling out and how it implies that you can start a new chain. It's a subtle implication IMO but I definitely see it now nonetheless 🙂
Based on those assumptions, I suspected that some (hidden) information regarding ownership on the view could have been incorrect in the database leading to an unexpected broken ownership chaining followed by a permission check against the table.
That is why I suggested to do an ALTER on the view - simply by scripting the ALTER VIEW via SSMS and executing it. If any hidden ownership information regarding the view was kept in the database, I would believe that the ALTER VIEW would update it.
I am doubtful about why performing an ALTER VIEW might help the situation but please let me know if it works. Can you cite an article giving you reason to believe it will help? I am doubtful because you have granted SELECT on the VIEW to SomeUser and to my knowledge altering a VIEW has no effect on permissions (or the object owner).
What is more interesting to me is, who is the owner of A.SomeTable?
SELECT dp.name AS table_owner
FROM sys.tables t
JOIN sys.database_principals dp ON t.principal_id = dp.principal_id
WHERE OBJECT_SCHEMA_NAME(t.object_id) = N'A'
AND t.name = N'SomeTable' ;
I ask because the only way I was able to recreate what you are seeing was to explicitly change the owner of DBY.A.SomeTable like this:
ALTER AUTHORIZATION ON OBJECT::[A].[SomeTable] TO [SomeOtherUser]
It still doesn't explain why the user could successfully execute the SQL via SSMS - while the SQL failed when same user connected through the application.
I have been leaving that point aside thus far since I have no ideas on why that might have occurred or how it relates to the issue. I hope it becomes clear at some point however.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 10, 2011 at 8:28 pm
PS I meant to attach my two proof-of-concept scripts. Maybe if you try something, or figure something out you can alter these scripts and shoot them back to me so we're looking at the same thing.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 12, 2011 at 5:11 pm
You are asking about the owner of A.SomeTable and you sent a SQL for retrieval of that information.
The result of that SQL is 0 rows.
And the reason is that the values of principal_id in sys.tables and sys.views are NULL for all objects in schema A.
This applies for current production db (where owner of schema A is sys),
the test db (where owner of schema A has been changed to dbo) and
even at the backup we took of production db right before our restore (where owner of schema is dbo).
What if changing the ownership of schema A messed up something on the view (e.g. setting the principal id wrongly or perhaps more likely failing to invalidate schema ownership information kept in memory) - we are talking a SQL server bug here. If that was the case, then doing an ALTER on the view might have fixed the situation. Or perhaps stop/start of SQL server would have fixed the problem. But of course, this is only pure speculation - I have not been able to find any article supporting my thoughts.
June 13, 2011 at 9:56 am
carsten.jorgensen (6/12/2011)
You are asking about the owner of A.SomeTable and you sent a SQL for retrieval of that information.The result of that SQL is 0 rows.
And the reason is that the values of principal_id in sys.tables and sys.views are NULL for all objects in schema A.
This applies for current production db (where owner of schema A is sys),
the test db (where owner of schema A has been changed to dbo) and
even at the backup we took of production db right before our restore (where owner of schema is dbo).
That could be an issue, but not necessarily. Here is a more reliable way to check...what does this return in test and prod?
SELECT USER_NAME(OBJECTPROPERTY(OBJECT_ID(N'A.SomeTable'), 'ownerid')) AS 'table_owner',
USER_NAME(OBJECTPROPERTY(OBJECT_ID(N'A.SomeView'), 'ownerid')) AS 'view_owner' ;
What if changing the ownership of schema A messed up something on the view (e.g. setting the principal id wrongly or perhaps more likely failing to invalidate schema ownership information kept in memory) - we are talking a SQL server bug here. If that was the case, then doing an ALTER on the view might have fixed the situation. Or perhaps stop/start of SQL server would have fixed the problem. But of course, this is only pure speculation - I have not been able to find any article supporting my thoughts.
Going way back...was this database upgraded from SQL2000 to SQL2005?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 13, 2011 at 2:04 pm
Actually, the view contains data from 2 tables. But they are both in schema A - so it shouldn't make any difference.
Here are the results of the query:
Prod:
table1_ownertable2_ownerview_owner
sys sys sys
Test:
table1_ownertable2_ownerview_owner
dbo dbo dbo
Backup from prod (taken right before prod restore) and restored at Test:
table1_ownertable2_ownerview_owner
dbo dbo dbo
The database has never been upgraded from 2000 - it was born as 2005.
The upgrade from 2005 to 2008 was done as 'Side by Side Upgrade' to another server.
June 13, 2011 at 2:10 pm
I am officially stumped.
Have you been able to recreate the issue in any environment?
If you can recreate it in a non-prod environment I would recommend getting Microsoft Support involved.
I am VERY interested in hearing about a resolution. If you have a moment please post back if you get to the bottom of it, or at the very least arrive at a stable production environment after replacing sys to know what was done.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 13, 2011 at 2:59 pm
In fact, we are already considering getting help from Microsoft Support.
But as I mentioned, we are not able to reproduce the case in our dev/test environments.
Our only other option will be to redo the prod changes once again (in small steps), checking the application between each step and if the problem arises again, prepare for investigation and interventions like checking ownership (like we just did), trying altering the view, maybe restarting SQL server and finally, if nothing else works, grant access to the tables as well.
Thank you so much for your help - I will keep you updated.
June 13, 2011 at 4:03 pm
Thanks Carsten, I appreciate the post. Good luck!
- Orlando
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply