2

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.

Improve this question
4
  • 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, 2019 at 16:00
  • I have created the link via the Wizard
    – Logitek Dev
    Feb 11, 2019 at 16:47
  • The result of the query is exactly the user i have provided to connect with
    – Logitek Dev
    Feb 11, 2019 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, 2019 at 16:52

1 Answer 1

Reset to default
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.

Improve this answer
5
  • 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, 2019 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, 2019 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).
    – SQLDevDBA
    Feb 11, 2019 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, 2019 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.
    – SQLDevDBA
    Feb 12, 2019 at 21:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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