Joeisanerd
asked on
Using Cross-Database ownership chaining
Okay most sites think cross-database ownership chaining is bad, but so far in testing those claims don't hold up.
So here is the problem I have. I have multiple databases on a one server (that's the way it's going to be and it's not changing). Basically one database houses common information and the others are like projects that utilitized by Web Applications. What I need to do is be able to create an sql user account that calls views and stored procedures in the one database, but those views and stored procedures could be accessing the other databases.
Currently the way this is done is to have an account that has select and execute access in all of the needed databases and then use that account for each app. It would also have update, delete, insert in the databases that had applications that require that. The database that houses the common information is used for reporting, so no updating by applications is done to it.
What I have found as a way to get what I want is to turn on the cross-database ownership chaining for the databases that need it, then make sure I have a SQL user account that has the necessary select and execute access in the different databases.
Example:
masterDB - contains the reporting crap
- has a user account (MISTER_READ) that has select access on the tables, nobody logs in with this account, doesn't need to.
app1DB - custom application database that might lookup something in the masterDB
- has it's own SQL user account (app1_user)
- contains views and/or stored procs. that lookup masterDB stuff
- make MISTER_READ the owner of those views and stored procs that access the masterDB and grant the app user account (app1_user) select/execute on those items
Then you turn on the cross-database ownership chaining on and it works! so where is the security flaw? I might be missing something here....
The articles I have looked at indicate that this would been that if MISTER_READ account was a database owner then that user account could create views in database's that it should have acces to. Prove it! You would have to be the owner of those objects in order to do that and when the cross-database ownership chaining is on it doesn't work for objects makes with dbo as the owner.
So if you decided to read though this lengthy blah, blah, blah will it work or is there something better?
So here is the problem I have. I have multiple databases on a one server (that's the way it's going to be and it's not changing). Basically one database houses common information and the others are like projects that utilitized by Web Applications. What I need to do is be able to create an sql user account that calls views and stored procedures in the one database, but those views and stored procedures could be accessing the other databases.
Currently the way this is done is to have an account that has select and execute access in all of the needed databases and then use that account for each app. It would also have update, delete, insert in the databases that had applications that require that. The database that houses the common information is used for reporting, so no updating by applications is done to it.
What I have found as a way to get what I want is to turn on the cross-database ownership chaining for the databases that need it, then make sure I have a SQL user account that has the necessary select and execute access in the different databases.
Example:
masterDB - contains the reporting crap
- has a user account (MISTER_READ) that has select access on the tables, nobody logs in with this account, doesn't need to.
app1DB - custom application database that might lookup something in the masterDB
- has it's own SQL user account (app1_user)
- contains views and/or stored procs. that lookup masterDB stuff
- make MISTER_READ the owner of those views and stored procs that access the masterDB and grant the app user account (app1_user) select/execute on those items
Then you turn on the cross-database ownership chaining on and it works! so where is the security flaw? I might be missing something here....
The articles I have looked at indicate that this would been that if MISTER_READ account was a database owner then that user account could create views in database's that it should have acces to. Prove it! You would have to be the owner of those objects in order to do that and when the cross-database ownership chaining is on it doesn't work for objects makes with dbo as the owner.
So if you decided to read though this lengthy blah, blah, blah will it work or is there something better?
ASKER
Okay have you tried that when working with mulitple databases?
For instance a stored procedure in database1 that accesses to tables, one from database 1 and the other from database 2? The user account executing the stored proc has to have select access on the the table in database 2.
Crossing databases breaks that wonderful myth of just giving exec on the stored procedure and you're be just fine. It might work with integrated security, I haven't tried, but I know it doesn't work with regular SQL accounts.
For instance a stored procedure in database1 that accesses to tables, one from database 1 and the other from database 2? The user account executing the stored proc has to have select access on the the table in database 2.
Crossing databases breaks that wonderful myth of just giving exec on the stored procedure and you're be just fine. It might work with integrated security, I haven't tried, but I know it doesn't work with regular SQL accounts.
>>Okay have you tried that when working with mulitple databases?<<
Sure. We do it all the time.
>>The user account executing the stored proc has to have select access on the the table in database 2.<<
Nope. That is only true if you are using dynamic SQL and that is "verboten" at our shop.
>>It might work with integrated security, I haven't tried, but I know it doesn't work with regular SQL accounts.<<
Sure it does. Try it. You will be impressed :)
Sure. We do it all the time.
>>The user account executing the stored proc has to have select access on the the table in database 2.<<
Nope. That is only true if you are using dynamic SQL and that is "verboten" at our shop.
>>It might work with integrated security, I haven't tried, but I know it doesn't work with regular SQL accounts.<<
Sure it does. Try it. You will be impressed :)
>> That is only true if you are using dynamic SQL and that is "verboten" at our shop.<<
In case that was not clear, I mean we do not allow Dynamic SQL where I work. It is simply not worth it.
In case that was not clear, I mean we do not allow Dynamic SQL where I work. It is simply not worth it.
ASKER
are you using sql accounts? I am using SQL 2000 with sql accounts and there are udf's views, stored procs. and if I have a db that has a stored proc that accesses thoses things in a different database, it doesn't work.
so how does your shop do it?
so how does your shop do it?
>>are you using sql accounts? <<
Yes.
>>it doesn't work.<<
I am sorry about. Try posting an example of a typical stored procedure that "doesn't work" and this time tell us what "doesn't work" means. Do you get:
A. An error message, if so what is it.
B. The wrong results, if so what are they and what do you expect.
This is an imperfect communication medium at best and quite frankly "it doesn't work" doesn't work for me.
Yes.
>>it doesn't work.<<
I am sorry about. Try posting an example of a typical stored procedure that "doesn't work" and this time tell us what "doesn't work" means. Do you get:
A. An error message, if so what is it.
B. The wrong results, if so what are they and what do you expect.
This is an imperfect communication medium at best and quite frankly "it doesn't work" doesn't work for me.
ASKER
The error messages that I would get are user account on database "" table "" doesn't ahve select access. That's the kind of error messages I get when trying to execute a stored procudure that accesses information in another database. So if you are able to make this work, how? What special configuration is needed? Becuase according to Microsoft that is not how security works SQL server 2000.
Again we are not using Integrated Security, because you can't. We have SQL server 2000 with SP3. I know that if everything is in one database then you can create a stored procedure and grant a user account with only access to the stored proc and it will work, but only if the stored procudure accesses data in just that database.
Again we are not using Integrated Security, because you can't. We have SQL server 2000 with SP3. I know that if everything is in one database then you can create a stored procedure and grant a user account with only access to the stored proc and it will work, but only if the stored procudure accesses data in just that database.
>>The error messages that I would get are user account on database "" table "" doesn't ahve select access. <<
If it is the error that I think it is (I can only guess from your description) than one possible cause is that you are using Dynamic SQL. As I stated earlier that is a potential problem, but you would also get that error message if you were not SELECTing across databases.
On the subject of the type of logins, let me state so that it is totally clear and we can get beyond that point once and for all: Our users access a website that uses a SQL Server user that has ***SQL Server Authentication ***. EOS
They typically connect to one database and access that database as well as one other central database. Here are some of the ways that happens from there "home" database they may:
Execute a stored procedure on the "central" database.
Execute a stored procedure on their "home" database that executes a stored procedure on the "central" database.
Execute a stored procedure on their "home" database that executes a UDF on the "central" database.
Execute a stored procedure on their "home" database that Joins tables from both "home" and "central" database.
I trust this puts this subject to rest.
If you still have questions, than post the Stored Procedure that is causing the error and the specific error message as displayed in SQL Query Analyzer.
If it is the error that I think it is (I can only guess from your description) than one possible cause is that you are using Dynamic SQL. As I stated earlier that is a potential problem, but you would also get that error message if you were not SELECTing across databases.
On the subject of the type of logins, let me state so that it is totally clear and we can get beyond that point once and for all: Our users access a website that uses a SQL Server user that has ***SQL Server Authentication ***. EOS
They typically connect to one database and access that database as well as one other central database. Here are some of the ways that happens from there "home" database they may:
Execute a stored procedure on the "central" database.
Execute a stored procedure on their "home" database that executes a stored procedure on the "central" database.
Execute a stored procedure on their "home" database that executes a UDF on the "central" database.
Execute a stored procedure on their "home" database that Joins tables from both "home" and "central" database.
I trust this puts this subject to rest.
If you still have questions, than post the Stored Procedure that is causing the error and the specific error message as displayed in SQL Query Analyzer.
ASKER
It sounds like you have stored procedures that execute other stored procedures as opposed to a stored proc that simple returns data from another database. When I get the machine with the database I can give actual errors.
ex:
in "home" db
create proc dbo.RetrieveSomeStuff
as
select * from central.dbo.tblSomeStuff
In my tests I was not able to just give a user account exec access to that RetrieveSomeStuff proc in "home". It would give an error message that the account didn't have select access to tblSomeStuff in the "central" database with owner "dbo".
Is this what your users do and are you sure you are able to just create a new user right now that is granted only exec access on the proc and be able to login to Query Analyzer and exec that one stored procedure without error? Or maybe you system configured with public access to execute those stored procedures.
As stated above views and stored procedures, not Dynamica SQL.
ex:
in "home" db
create proc dbo.RetrieveSomeStuff
as
select * from central.dbo.tblSomeStuff
In my tests I was not able to just give a user account exec access to that RetrieveSomeStuff proc in "home". It would give an error message that the account didn't have select access to tblSomeStuff in the "central" database with owner "dbo".
Is this what your users do and are you sure you are able to just create a new user right now that is granted only exec access on the proc and be able to login to Query Analyzer and exec that one stored procedure without error? Or maybe you system configured with public access to execute those stored procedures.
As stated above views and stored procedures, not Dynamica SQL.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Okay I have made some progress with what you have said. UDF's and Stored Procedures are "compiled" and act as a security barrier for the user. So if I create a UDF that selects the information I need in Database2 then a stored procedure in Database1 that selects on the UDF will execute just fine if the user account as EXEC access on the stored procudure in Database1 and select access on the UDF in Database2.
This does not work however, if the you tried to select on a view or table from Database2 because you would have to have select access on the table.
was this what you were trying to get accross, because using UDF's and SP works accross databases if the user has access to both.
This does not work however, if the you tried to select on a view or table from Database2 because you would have to have select access on the table.
was this what you were trying to get accross, because using UDF's and SP works accross databases if the user has access to both.
>>was this what you were trying to get accross<<
Sure. Of course you cannot select any table in any database (let alone across databases) unless you have SELECT permissions. But again, we do not allow that, all access is through stored procedures, in which case all you need is EXECUTE permissions to the stored procedure in any database that you have enabled cross-database ownership chaining in both the "target" and "source" database.
Let me repeat my original comment, because it encapsulates everything else I have said:
"Only allow access through stored procedure and then there is no need to give any more permissions than Execute permissions on the Stored Procedures themselves." This is true for a single database and also true for databases with the appropriate cross-database ownership chaining.
I don't know how else to explain this. Unfortunately you have yet to post one single stored procedure that fails in your environment. So until you have produced one scenario that fails, I will have to bow out. Hopefully someone can step up to the plate and convince you, what I have evidentally been unable to do.
Sure. Of course you cannot select any table in any database (let alone across databases) unless you have SELECT permissions. But again, we do not allow that, all access is through stored procedures, in which case all you need is EXECUTE permissions to the stored procedure in any database that you have enabled cross-database ownership chaining in both the "target" and "source" database.
Let me repeat my original comment, because it encapsulates everything else I have said:
"Only allow access through stored procedure and then there is no need to give any more permissions than Execute permissions on the Stored Procedures themselves." This is true for a single database and also true for databases with the appropriate cross-database ownership chaining.
I don't know how else to explain this. Unfortunately you have yet to post one single stored procedure that fails in your environment. So until you have produced one scenario that fails, I will have to bow out. Hopefully someone can step up to the plate and convince you, what I have evidentally been unable to do.
ASKER
I gave you an example of what doesn't work, but whatever... with that said your persistence shown me that if I turn off cross-database chaining I can achieve what I want as long as a stored proc or UDF is called via the other database.
With dbo as the owner you cannot select on a table in another db through a stored procedure (like the a example above), it only works on "compiled" objects (sp, udf) that act act as a wrapper to the tables and views.
ex:
create proc dbo.RetrieveSomeStuff
as
-- doesn't work (user doesn't have access to this table)
select * from central.dbo.tblSomeStuff
change to
create proc dbo.RetrieveSomeStuff
as
--DOES work
-- call udf in the other db, current user has select access on this udf
select * from central.dbo.udfSomeStuff()
the point is to restrict user from certain fields, tables, etc.
hopefully this can those many people that have had this problem as evident from our friend google.
GO
With dbo as the owner you cannot select on a table in another db through a stored procedure (like the a example above), it only works on "compiled" objects (sp, udf) that act act as a wrapper to the tables and views.
ex:
create proc dbo.RetrieveSomeStuff
as
-- doesn't work (user doesn't have access to this table)
select * from central.dbo.tblSomeStuff
change to
create proc dbo.RetrieveSomeStuff
as
--DOES work
-- call udf in the other db, current user has select access on this udf
select * from central.dbo.udfSomeStuff()
the point is to restrict user from certain fields, tables, etc.
hopefully this can those many people that have had this problem as evident from our friend google.
GO
>>create proc dbo.RetrieveSomeStuff
as
-- doesn't work (user doesn't have access to this table)
select * from central.dbo.tblSomeStuff
<<
It works fine for us. We have been using it just like that for years.
But whatever works for you ..
Good luck and thanks for the grade.
as
-- doesn't work (user doesn't have access to this table)
select * from central.dbo.tblSomeStuff
<<
It works fine for us. We have been using it just like that for years.
But whatever works for you ..
Good luck and thanks for the grade.
Yes. Only allow access through stored procedure and then there is no need to give any more permissions than Execute permissions on the Stored Procedures themselves.