Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Using Cross-Database ownership chaining

Posted on 2006-06-01
14
Medium Priority
?
1,523 Views
Last Modified: 2007-12-19
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?
0
Comment
Question by:Joeisanerd
  • 8
  • 6
14 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16812872
>> is there something better?<<
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.
0
 
LVL 9

Author Comment

by:Joeisanerd
ID: 16812971
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16812989
>>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 :)
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16812993
>>  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.
0
 
LVL 9

Author Comment

by:Joeisanerd
ID: 16813268
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?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16820845
>>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.
0
 
LVL 9

Author Comment

by:Joeisanerd
ID: 16836707
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16839385
>>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.
0
 
LVL 9

Author Comment

by:Joeisanerd
ID: 16839828
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.

0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1500 total points
ID: 16841850
Sorry, you are right I forgot to include that as well, they:
Execute a stored procedure on the "home" database that SELECTs data from the "central" database.
Execute a stored procedure on the "home" database that executes a UDF that SELECTs data from the "central" database.

I think I have covered all the scenarios, but let me know if I have missed anything.

Users belong to the Public role, as well as user defined role that only has Execute permissions to the relevant stored procedures in the "home" database as well as the "central" database.
0
 
LVL 9

Author Comment

by:Joeisanerd
ID: 16843926
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16848590
>>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.
0
 
LVL 9

Author Comment

by:Joeisanerd
ID: 16849022
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16851571
>>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.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question