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?
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.