May 9, 2008 at 2:00 pm
Linked server
Connects to DATABASEA as userx
Some Views ---selects data from TABLE in DATABASEB
Both users set up in DATABASEA and DATABASEB - with select permission only on the table in DATABASEB so that the data can be returned.
All works great.
If the User sets up the Linked Server and does not put in the CATALOG and leaves this blank.
When looking at the catalogs he /she can see
DATABASEA
DATABASEB (Userx is here as it is needed to return the data for the view in DATABASEA)
If Linked server Catalog is set up only sees DATABASEA.
There is no way i could tell if the linked server set up on another site that the CATALOG must
exist .
So the question is
--- i cannot allow DATABASEB to be seen by the user because the user can now do select * from this table............hence the reason why i created a view to filter
I not sure how to fix.
Its almost like i need to run the view as a hidden user on DATABASEB
May 10, 2008 at 6:32 am
This is one of those rare cases where cross database ownership chaining may be useful. Are you working in SQL Server 2000 or SQL Server 2005?
K. Brian Kelley
@kbriankelley
May 10, 2008 at 7:44 am
SQL 2005
i put together some test examples
http://www.stretchunlimited.com/LINKED.DOC
Scratching my head how to fix.
If the catalog is entered everything is fine........but the other parts of organisation may not put this catalog in .........(then the entire table is exposed of all transactions of all organisations which is not so good).
Any guidance is greatly appreciated.
May 11, 2008 at 8:59 pm
Here's how I see it:
1. you have defined a user Linked_ASRC on DISB to have access to a table
2. you have configured the linked server on DISA to use the DISB\Linked_ASRC login
for any login that requires access to DISB.
Therefore, whenever anybody accesses the DISB table, they are actually using the Linked_ASRC login (transparently). Therefore, they get whatever rights you have granted to the Linked_ASRC login.
Ensure the table & view have the same owner (e.g. dbo) and grant SELECT on the VIEW (instead of the table) to Linked_ASRC.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
May 12, 2008 at 6:37 am
Here's how I see it:
1. you have defined a user Linked_ASRC on DISB to have access to a table
Yes
2. you have configured the linked server on DISA to use the DISB\Linked_ASRC login for any login that requires access to DISB.
(IF Catalog is blank the user Linked_Login which is on DISA
(I trying to disguise the fact the Linked_Login is a login for DISB but username is Linked_ASRC) SO that when catalog is blank it will not show DB DISB) --Didn't do trick
Therefore, whenever anybody accesses the DISB table, they are actually using the Linked_ASRC login (transparently). Therefore, they get whatever rights you have granted to the Linked_ASRC login.
(I am trying not to show the DISB to the user)
Ensure the table & view have the same owner (e.g. dbo) and grant SELECT on the VIEW (instead of the table) to Linked_ASRC.
(View has the select on this....but in order to get the data from DISB i gave rights to Table for select)
May 17, 2008 at 10:31 am
Anyone got anything else on this i cannot fix this.
May 17, 2008 at 12:57 pm
I apologize, I was out of pocket for most of the week.
Here's what should work.
- Ensure the database owner for DISA and DISB are the same.
- Activate cross-database ownership chaining on for both databases
ALTER DATABASE DISA SET DB_CHAINING ON;
GO
ALTER DATABASE DISB SET DB_CHAINING ON;
GO
- Revoke access to the table CUSTOMER_TRANSACTIONS from the login coming in.
Because the view and the table are both in the dbo schema (and I assume that schema is owned by dbo), then as long as the database owners are the same for both databases, ownership chaining is possible between the view and the table. The catch then is to ensure the databases are configured for cross-database ownership chaining. Once that is done, no permissions are required against the table, only against the view. You'll want to leave the user with access to the second database, DISB, because it still needs the ability to access the database.
K. Brian Kelley
@kbriankelley
May 17, 2008 at 3:39 pm
That worked on my server here where the view is dbo, tables are all dbo.
When catalog is left blank i do see DISA and DISB however as the select is not against the table customer transactions DISB they cannot be seen the table so that is good.
Let me see what happens on the other test server where the SCHEMA are difference.
View is owned by one schema and the table in DISB is a different schema.
--This may infact save me setting up individual roles in DISB for all my customers.
Right now i have ROLES
CUSTA has TABLEA, B, C
CUSTB has TABLE D, E F
about 20 of these roles which is getting hard to manage especially
as it a replicated database.
If i enable the cross database all i need to do is make sure the login is set up give no access to any tables.
How does it work then the view --is it passing in dbo rights...
Any other ramifications that i need be aware off..............by enabling this in production.
May 17, 2008 at 5:54 pm
As long as the schema owners are the same, you're in good shape. That's what is actually used to determine cross-database ownership chaining in SQL Server 2005.
K. Brian Kelley
@kbriankelley
May 18, 2008 at 6:09 am
Thanks for the advice...........
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply