Accessing synonym of DB link object

Question

Background: We have database FIN having schema as FIN_DEV.

We have another database APP having schema APP_DEV.

A DB link is created in APP_DEV pointing to FIN_DEV

CREATE DATABASE LINK FINDEV_FIN
CONNECT TO FIN_DEV
IDENTIFIED BY <PWD>
USING 'FIN'

We are able to access the objects in FIN_DEV from APP_DEV using

CREATE OR REPLACE SYNONYM TBL_FINA FOR TBL_FINA@FINDEV_FIN

All good until this point.

Here comes the question:

Another schema INT_DEV in APP database needs to access SYNONYM TBL_FINA from INT_DEV.

Could you please let me know the best way to accomplish this?

Answer 1

Here's how; I don't have your users (and don't feel like creating them), so:

• my remote database = orcl (it is your fin database)
• user in my remote database = my_remote_user (it is fin_dev in your database)
• user in my local database = scott (app_dev in your database)
  • it'll create a database link and a synonym
• another user in my local database = mike (int_dev in your database)

---

In a remote database, I'm creating a table to mimic your situation:

SQL> create table tbl_fina (id number);

Table created.

SQL> insert into tbl_fina values (1);

1 row created.

SQL> commit;

Commit complete.

SQL>

Connecting to local database, creating a database link & a synonym:

SQL> show user
USER is "SCOTT"
SQL> create database link findev_fin
  2  connect to my_remote_user
  3  identified by its_password
  4  using 'orcl';

Database link created.

SQL> -- Testing, whether the DB link works
SQL> select * From dual@findev_fin;

D
-
X

SQL> -- Creating a snynonym
SQL> create synonym tbl_fina for tbl_fina@findev_fin;

Synonym created.

SQL> select * from tbl_fina;

        ID
----------
         1

SQL>

So far, so good - this is what you currently have.

---

Now, let's allow another user - in my local database - to access that synonym. Straightforward solution is to grant select on it, right?

SQL> grant select on tbl_fina to mike;
grant select on tbl_fina to mike
                *
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database


SQL>

Whooops! That won't work. A workaround is to create a view (on the synonym) and grant select on that view to mike:

SQL> create view v_tbl_fina as select * from tbl_fina;

View created.

SQL> grant select on v_tbl_fina to mike;

Grant succeeded.

SQL>

That works. Finally, connect as another user and select from the view (i.e. a synonym):

SQL> connect mike/pwd
Connected.
SQL> select * from scott.v_tbl_fina;

        ID
----------
         1

SQL>

For easier access - to avoid naming view owner (scott) - mike can now create its own synonym:

SQL> create synonym tbl_fina for scott.v_tbl_fina;

Synonym created.

SQL> select * from tbl_fina;

        ID
----------
         1

SQL>

---

Certainly, another option is to create a database link in my mike user, but that's kind of dangerous as database link allows its owner to do virtually anything in the remote database, as it is now identified by the remote username and its password.