Oracle linked server shows all tables from all schemas in sql server management studio

linked-serveroracle-12cssms

I have created a linked server to an oracle database ( 12c ) in sql server management studio. I am mapping the local user to a special remote user in the oracle db. Connection is working fine. The strange thing is that i am able to see all tables from all schemas.

I tried the following query

SELECT *
   FROM OPENQUERY(ORCL_LINK, 'SELECT  table_name, owner FROM  all_tables ORDER BY  owner, table_name')

As a result i see all schemas and all tables. When i run this query in my oracle database i onyl see tab

I thought that the linked server connection is running in the security context of the remote user but it seems it is not the case. Am i wrong?

Update 1

Oracle SQL Develoer View

Oracle SQL Developer only shows me tables where i am the owner but when i do this query

SELECT * FROM USER_TAB_PRIVS WHERE privilege = 'SELECT';

i see many other tables. Those tables are visible in the linked server connection.

Best Answer

Just ran this on SQL Server. As long as you have the correct USER as the credentials to the linked server, it will give you tables that the User Owns, as well as Tables that the user has SELECT access on.

To Prove this, revoke select on one of the tables to that user (On the Oracle side), and it will no longer show in the query you provided.

REVOKE SELECT ON TABLE FROM LINKUSER;

I just did so and got one less table. As soon as I added the Grant back, the table returned again in the query.

Related Question