1

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.

New contributor
Logitek Dev is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 1
    Post the SQL Server statement for creating the link. Post the result SELECT USER FROM DUAL when ran across the DB Link. Start debugging by ensuring the Oracle user only has CREATE SESSION system privilege. – Michael Kutz Feb 11 at 16:00
  • I have created the link via the Wizard – Logitek Dev Feb 11 at 16:47
  • The result of the query is exactly the user i have provided to connect with – Logitek Dev Feb 11 at 16:48
  • The strange thing is conecting to my oracle via ORACLE SQL Developer using the same credentials only shows me my objects i have access to. – Logitek Dev Feb 11 at 16:52
0

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.

  • 1
    I see this user has SELECT rights on many tables. Still strange ORACLE SQL DEVELOPER does not show them to me. But this worked. Thx – Logitek Dev Feb 11 at 17:18
  • 1
    @LogitekDev - SQLDeveloper fetches rows in batches of 50 - 100. You need to scroll to the end of the Result Table to see all of the values. ( short cut: Place OS cursor in Result Table and press CTRL+END ). Also, if you exported the Result Table to CSV/Excel, you should see all of the values. – Michael Kutz Feb 11 at 17:46
  • @LogitekDev you'll need to make sure you go to "Other Users" section at the very end if you're browsing objects via the "connections" tab. Once expanded, it will show all other schemas and their objects (if you have access). – MguerraTorres Feb 11 at 19:45
  • I did SELECT * FROM USER_TAB_PRIVS; and saw this user has many SELECT grants on other tables. Still SQL Developer does not show me this tables in the table browser. No filtering applied – Logitek Dev Feb 12 at 12:51
  • SQL Developer only shows you the tables you own in the object browser. You NEED to go to "Other Users" section at the bottom of the Object Browser in order to see tables owned by other schemas. – MguerraTorres Feb 12 at 21:48

Your Answer

Logitek Dev is a new contributor. Be nice, and check out our Code of Conduct.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.