the point is to create the code in one database and grant execute privileges to

GLOBAL_NAME
--------------------------------------------------------------------------------
PDB1

SQL> show user
USER is "MOHAMMAD"


SQL> create or replace procedure my_pro1
  2  AUTHID current_user
  3  is
  4  BEGIN
  5  DBMS_OUTPUT.PUT_LINE('THIS IS A TEST');
  6  end;
  7  /

Procedure created.


SQL> create database link DBP2N connect to scott identified by scott using 'NCDB' ;

Database link created.


grant all on mohammad.my_pro1 to scott with grant option
  2  /

Grant succeeded.


GLOBAL_NAME
--------------------------------------------------------------------------------
NCDB


SQL> show user
USER is "SCOTT"


SQL> create database link DBN2P connect to mohammad identified by mohammad using 'PDB1' ;

Database link created.



SQL> exec my_pro1@dbn2p ;

PL/SQL procedure successfully completed.


SQL> grant execute on my_pro1@dbn2p to hr ;
grant execute on my_pro1@dbn2p to hr
                         *
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database

this is what i'm trying to do actually:
- basically is it possible to this with only one database link "dbn2p" ?
- if not. what do u suggest to implement similar scenario?

Best Regards
and thanks a lot for you precious time

Mohammad,

As Connor said, a connection via a database link is just a client connection to the target database.

Privileges cannot be granted across database links, only to local user accounts. When you use a database link, you are connecting to a remote database as the user in the link definition.

So if you are User1 in DB1 with a link to User2 in DB2, then everything User1 does over the link is performed as User2 in DB2. DB2 doesn't know anything about User1 or DB1. Privileges in DB2 can only be granted to User2 in DB2. Code executed in DB2 will only operate within the scope of User2's privileges in DB2, so unless there is a second link back from DB2 to DB1 User2 can't directly access any objects in DB1.

If you want multiple users in DB1 to access code in DB2, then each user will need its own private database link to DB2, or you will need a public database link in DB1 that is open to all users (this is generally not recommended​for security reasons).

For this reason (in my opinion) it is generally simpler and safer to have a copy of your stored procedure in each database, and not use links unless it is absolutely necessary. Too many links with auto-executing code can quickly create impossible dependencies that make startup and shutdown impossible without application errors. In your scenario, what would happen to the monitoring procedure call in DB2 if DB1 was down?