May 28, 2008 at 3:57 am
This is the senario...
I have databaseA and databaseB. I have a stored proc or view in databaseA that selects from a table in databaseB. If I add a user on DatabaseA and on databaseB with public role nothing else, the user has execute on the stored proc only no select on the table. Now I get a permission denied when the stored proc runs, but goes away when I give select permission on the underlying table in databaseB. This defeats the object of security because the user does not have to use the stored proc and go and do a select * from the table, which in my mind is security breach as you have no way of controling what the user can see.
However this works fine when the stored proc queries a table in the same database then I don't have to give select on the underlying table and only execute on the stored proc and nothing else. The user in this senario only is a member of the public role. But as I mentioned above does not work the same when the proc queries tables in another database.
I ask this because if you have to give user select or read access to the underlying table then a clever user can just as well go and do a select * and get all the information even if some columns have sensitive data in it. I know that you can go to very grandular level in table, but I mean why is this so. Surely security should be governed at the proc of view level and not have to then go and give select on each object the is referenced in the view or proc.
Now I'm sure there must be a way of doing this when quering from another database to where the proc or view sits.
Can anyone give an explanation, as to how to accomplish this so that a user can only see what is referenced in view or stored proc. Even provide an article that I can read up on how to manage this.
Thanks:D
May 28, 2008 at 10:33 am
The way this can be done is to use something called cross-database ownership chaining. It's a security "feature" which is turned off by default because of the risk if one isn't careful with it. Let's cover the basics.
First rule, never turn it on server-wide. Always turn it on at the database level. If you do some investigation on the topic in Books Online and on your SQL Server, you'll notice the master, msdb, and tempdb databases all have cross-database ownership chaining turned on. This is required and in the case of master and tempdb you can't undo it.
Second rule, cross-database ownership chaining must be turned on for any databases participating in the ownership chaining. So if you are going from DatabaseA to DatabaseB, both must have it turned on. One draw back is that once it is turned on for a database on a server, any other database on the server for which this option is turned on can participate in a cross-database ownership chain. So if you had DatabaseC and DatabaseD also activated, you couldn't say DatabaseB could only participate with DatabaseA. All four databases could participate with each other.
Third rule, when determining ownership chaining across databases, the login is used. If you're used to ownership chaining within the database (say a stored proc hits a table within the same database), it's the same concept, just extended. Within the database, the owner of the objects (SQL Server 2000) or the owner of the schemas to which the object belongs (SQL Server 2005) is compared. If they are the same, all is well. When crossing databases, SQL Server looks at the owner and maps that up to the corresponding login at the server level. If the owners match, an ownership chain can be formed. If the objects are owned by dbo (or if the schema is owned by dbo), then the login is whoever owns the respective database.
Fourth rule, the login will still need access to the second database. You granted them access and they are a member of the public role. That is enough. If the ownership chaining takes effect, then that's all the security that's needed.
HTH.
K. Brian Kelley
@kbriankelley
May 29, 2008 at 1:24 am
Thank you very much for your response, now that I know what term to research I can do so. I was planning to buy the cheating security of 2005 from amazon to see if it deals with these types issues. I think I will still do that as security is very important and often gets neglected just because its easier to just give everyone read access at the database level.
Anyway thanks again much appreciated.
May 31, 2008 at 3:16 pm
I don't have my copy handy (visiting family), but if I remember right, cross database ownership chaining isn't covered in a whole lot of detail. It's generally frowned upon, and for good reason, because it means having to stay on top of another layer for security. However, it does solve some security issues that can be solved in any other way. I need to write an article on it.
K. Brian Kelley
@kbriankelley
June 26, 2008 at 9:25 am
brendanc,
I had exactly the same scenario as you described, and I knew I had seen this issue before, but couldn't remember how I had resolved it. I had cross-db chaining on (set on for the participating database AND on the server in the hope of rectifying the issue!!).
It was when I read K.Brian Kelley's quote
If the objects are owned by dbo (or if the schema is owned by dbo), then the login is whoever owns the respective database.
that it all became clear...
The issue was on our dev database, and when I checked the OWNERS of the databases I found one was 'sa' and one was 'Domain/admin', so when I thought 'dbo' should have matched against 'dbo', it was actually comparing 'sa' to 'Domain\admin' and of course, denied the permission!!!
I remember thinking the first time I came across this that I wouldn't forget it, but it happens so infrequently that I just plain forgot!
Maybe now I'll remember next time.......
Kev
June 27, 2008 at 6:23 am
I was unable to get ownership chaining to do what I needed. I finally took the cowards way out and simply moved the tables from the "other" database to the database that needed them.
June 27, 2008 at 7:12 am
Jim Russell (6/27/2008)
I was unable to get ownership chaining to do what I needed. I finally took the cowards way out and simply moved the tables from the "other" database to the database that needed them.
Jim - if you post up the scenario you had, I'm sure someone here can help.
Kev
June 27, 2008 at 7:29 am
Truth is, I'm happy with just moving the tables to the DB that needed them, in my case it made sense.
In the primary database, security is role based. So a view V1 would be included as a securable in a role SeeV1, which would list as members one or more of the "functional" roles (e.g. Auditors, Officers, etc.), which in turn, included all the individuals/logins which comprise those functions.
When V1 accessed a table in a second DB, I could not find a way to avoid having redundant (and perhaps inconsistent) role definitions in the second DB. Since the object schema was "DBO" in both databases, I had hoped that Ownership chaining would avoid that problem. But I was not smart enough to figure out how.
But thanks, I'd like to know for the next time I have a similar challenge.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply