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

Cross Database Ownership Chaining Expand / Collapse
Author
Message
Posted Thursday, October 28, 2004 7:53 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:50 AM
Points: 87, Visits: 45

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.




Post #143866
Posted Thursday, October 28, 2004 7:35 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, September 26, 2014 7:52 AM
Points: 6,624, Visits: 1,873
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #143947
Posted Friday, October 29, 2004 10:58 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:50 AM
Points: 87, Visits: 45

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?




Post #144026
Posted Friday, October 29, 2004 2:25 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, September 26, 2014 7:52 AM
Points: 6,624, Visits: 1,873
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #144056
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse