Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cross Database Ownership Chaining


Cross Database Ownership Chaining

Author
Message
DarylAndDaryl
DarylAndDaryl
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 96

Can cross database ownership chaining work when using application roles and if so, how?

My company has a two tier application that is utilizing application roles. Once the application role has been started, there is stored procedures that have to select data from other database and even launch stored procedures in other databases. It would be nice if database chaining would work instead of granting individual logins access to the other database or granting guest to the other database.





K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6816 Visits: 1917
Yes, it can work, but the guest user must be enabled in the second database.

When you use application roles, you lose all previous server login and database user information. Your application role becomes the only relevant security "account" (principal would be a better word) left. Of course, application roles are only applicable to the original database.

If you've maintained the owner between objects in two databases (the login maps correctly), it is possible for cross-database ownership chaining to work. However, one of the big catches is that the user must have access to the second database. Keep in mind what I said above... the only security context you have is with reference to the first database. You no longer have a login -> user mapping. Therefore, the only way to gain access to the second database is through the guest user. If a login has a context via no other means, but the guest account is activated, that's what gets used.

That's actually why the guest user is required for both the master and tempdb databases. Both of those databases have cross-database ownership chaining turned on and the reason you can use temp tables and the sp_ system stored procedures is because the guest user and cross-database ownership chaining.

K. Brian Kelley
@‌kbriankelley
DarylAndDaryl
DarylAndDaryl
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 96

I tested the following scenario:

Created Db1 and DB2. Owner of Db1 and DB2 is sa, meaning all objects will be owned by dbo. I created application role Db1..AppRole1. I also created Db2..Tble and add 1 row of data into it. I granted login UserA connection ONLY to Db1 and DB2. Granted guest connection to DB2 and enabled cross database ownership chaining to Db2. Using Query Analyzer, I set the Db1..AppRole1 and performed "SELECT * FROM Db2..Tbl", which failed with lack of permissions. So, I granted guest SELECT on Db2..Tbl and executed the "SELECT * FROM Db2..Tbl" again, which was successful. Then I disabled cross database ownership chaining to Db2 (guest SELECT permissions to Db2..Tbl remain in tact) and executed the "SELECT * FROM Db2..Tbl", which was successful.

From the scenario above, I conclude that if I have to grant guest SELECT on an object, I do not need cross database ownership chaining enabled. What I was hoping to accomplish was to use cross database ownership chaining without granting object permissions to guest or any other users. Is this possible?





K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6816 Visits: 1917
You are correct that if you granted SELECT you don't need cross-database ownership chaining. Understandably, you don't want to grant the SELECT.

Keep in mind an ownership chain only forms when one database object refers to another database object. For instance, a view or stored procedure referencing a table. In the example you gave, you aren't accessing an object in DB1 that references DB2. You are accessing the object in DB2 directly. Therefore, this is no ownership chain across databases (a chain connects two objects and you only have one here). Now, if you did something akin to:

USE DB1
GO

CREATE VIEW dbo.vw_MyViewOnTable
AS
SELECT * FROM DB2..Tbl
GO

GRANT SELECT ON dbo.vw_MyViewOnTable TO AppRole1
GO

And then you issued a query:

SELECT * FROM dbo.vw_MyViewOnTable

You'd be in business.

K. Brian Kelley
@‌kbriankelley
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search