September 30, 2010 at 12:47 pm
Hello. Is there an easy way to allow a SQL user to run a view in 1 DB that accesses a bunch of tables in another DB without letting them have access to the underlying tables? I would think this is a common problem but I don't see much discussion on it.
We've been using a loopback linked server to do this where the linked server is mapped back to this DB and the login is made under a specific security context that has elevated rights. But for some reason we're seeing perform problems relating to the linked server.
So the goal is:
User1 has readonly access to DB_1
User1 has no access to DB_2
In DB_1
Create View vwReadData As Select * from DB_2.TestTable
User1 can access view within DB_1 but cannot get out DB_2 on their own.
TIA,
Ken
September 30, 2010 at 1:05 pm
Seems like you are trying to answer the wrong question, really.
But yes, Linked Server Performance is known to be poor. Honestly I don't understand all the underlying reasons but here is a good "nutshell" to sum up why -
September 30, 2010 at 2:39 pm
I know I've seen this article before but maybe didn't grasp the significance of it. The security context the linked server runs as cannot execute DBCC SHOW_STATISTICS. I may try a Profiler trace to see if it's being attempted and failing when users are hitting it.
Thanks,
Ken
September 30, 2010 at 2:52 pm
If the login you are using does not have permissions to get statistics, then what is likely happening is the connection from db_1 tells db_2 to basically return all possible data that the query might possibly need, "across the wire" to db_1, where db_1 will take that dataset and join it to whatever other datasets you need from db_1. So you would be causing db_2 to possibly retrieve an entire table (with the same impact as if you selected * from table with no where clause) to join to a dataset in db_1.
That query would be pretty rough on the resources as you can imagine. Which is why I kinda thought maybe you were asking the wrong question in this particular case - if the user needs data from db_2, and you are willing to write something, anything so that that user can get it, why not set up a login with minimal permissions to access just what they need and save your db server from having to send an entire table to itself to complete a query?
That may be possible in your use case, I'm just warning that if you do decide that a linked server approach is indeed necessary, it will likely perform pretty poorly.
September 30, 2010 at 3:24 pm
Yeah, wow. Everything, and I mean everything, is on DB_2 in this simulation. Our DB_1 is really a type of accessor DB. All it has are views. Every table referenced from any view exists in DB_2; the idea being that users only hit this database of views which neatly summarize all the data.
But I'm starting to wonder if it's worth getting killed on performance this way. :crying:
October 1, 2010 at 9:36 am
Probably not worth the performance impact, but have you considered an alternative idea of using schemas to accomplish something similar? As in, create a login, lock the login down to its own schema, and populate that schema with views to access the tables that way?
This option would be similar to what you are after, but without the linked server performance overhead.
October 1, 2010 at 2:47 pm
You mean something like:
use db_1
create user ROuser1 for login ROuser1 --with default_schema = user1schema
create schema user1schema authorization ROuser1
create view user1schema.vwTest as select * from db_2.dbo.test
Seems that ROuser1 still needs access to the tables in db_2 to do this.
October 5, 2010 at 12:10 pm
Yes, this is doable using cross database ownership chaining. If you've got ownership chaining down, this is an extension of that. Except instead of the owner being evaluated as a user in the database, it goes up to the login level. More here (disregard the example, it's not right):
Understanding Cross-Database Ownership Chaining in SQL Server
K. Brian Kelley
@kbriankelley
October 5, 2010 at 2:08 pm
Brian, thanks a lot for these articles. So we bump the permissions of the guest account so that under the hood a connection can be made from calling DB to called DB. It's a user with no login; meaning no one can login to a DB as "guest".
Just for the heck of it I turned cross db ownership off in both DBs and I still able to get this scenario to work; no linked servers.
use db_1
sp_configure 'cross db ownership chaining', 0
create view someView as select * from db_2.someTable
grant select on someView to ROuser
use db_2
sp_configure 'cross db ownership chaining', 0
grant connect to guest
grant select on someTable to guest
--ROuser does not exist in this DB
use db_1
select * from someView
Ken
October 5, 2010 at 2:44 pm
ken.trock (10/5/2010)
Brian, thanks a lot for these articles. So we bump the permissions of the guest account so that under the hood a connection can be made from calling DB to called DB. It's a user with no login; meaning no one can login to a DB as "guest".Just for the heck of it I turned cross db ownership off in both DBs and I still able to get this scenario to work; no linked servers.
use db_1
sp_configure 'cross db ownership chaining', 0
create view someView as select * from db_2.someTable
grant select on someView to ROuser
use db_2
sp_configure 'cross db ownership chaining', 0
grant connect to guest
grant select on someTable to guest
--ROuser does not exist in this DB
use db_1
select * from someView
Ken
The example is wrong... I didn't write it. You're could be using ownership chaining here across databases, but the example has the grant against the table, and that shouldn't be necessary (it'll defeat what you want to do). Basically, here's what you want to do:
1) Turn cross db ownership chaining on in both databases.
2) Make sure the owners (since this a 2008 forum, check the owners of the schema unless you've explicitly set at the object level) map to the same login.
3) Explicitly grant the appropriate Windows groups the ability to connect to the second database using CREATE USER (don't use guest).
K. Brian Kelley
@kbriankelley
October 7, 2010 at 1:15 pm
If the DB you are accessing is on the same server, I don't understand why you use a linked server.
I have an inner facing DB with data that is accessed by a web facing interface. We created a read-only user in the production DB, then created another DB with views to the production data. The only login on that DB is the same one as the read-only user in the production DB.
The views work great and no one using the web-facing is having difficulty accessing the views.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply