October 28, 2004 at 7:53 am
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.
October 28, 2004 at 7:35 pm
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
October 29, 2004 at 10:58 am
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?
October 29, 2004 at 2:25 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply