CODEWITHSUNDEEP
sqloracle-databasetoad
amri
amri

Reputation: 87

give access to table through DB link

I work at a place where we have multiple SQL servers. On each server, I have a user that allows me to create and delete my own tables and access other tables. Usually, i work by creating links back and forth to and from the servers, but so far i haven't had the need to link to tables that i created for myself.

My question is therefore, giving the information above (i hope it's enough), how can i create a table on server2 and access through one of the established DB links from server1? I've been looking at creating DB links, but i've convinced myself that this is not the way to go, as DB links are already established between the servers. I assume it has something to do with granting access.

As of now, i'm not able to access the table from server1 using a query like select * from table1@server2, which is the way i normally access remote tables. When i try to do this, i receive the usual error message saying that Table or view does not exist and preceding line from SERVER2.

Thank you

edit:

When i'm connect to server1 and view all remote tables @ server2, using the query select * from all_tables@server2, the table that i created on server2 is visible. However, i'm still not able to select from it using the query above.

Upvotes: 0

Views: 2383

Answers (2)

amri
amri

Reputation: 87

I found a solution. The answer was to select from the table using the following query

SELECT * FROM user2.table1@server2

where user2 is the user i used to create table1 at server2.

Upvotes: 0

Michal Tenenberg
Michal Tenenberg

Reputation: 518

It sounds like the user on the remote database does not have sufficient privileges (that is if you are sure the table exists), and the solution depends on how the DB link was created.

If the DB link was created using a CONNECT TO someuser IDENTIFIED BY ... clause, then you can only connect to "someuser", who in your case does not have permission to access the table. In that case it might be advisable to create a new DB link to the correct user.

Otherwise, the DB link has a CONNECT TO CURRENT_USER or no CONNECT BY clause, which means that the username and credentials you use on server1 are also used for server2. In that case you just need to make sure that user actually has access to the table.

You can read more about remote users in DB links here (from the Oracle documentation).

Upvotes: 2

Related Questions