3 Replies Latest reply: Dec 3, 2012 11:59 PM by InquisitiveDBA RSS

    Database Link and DDL

    InquisitiveDBA
      I have a concern with DATABASE LINKS.

      I have a PROD1 database 10g on SunOS with the following users and tables:

      SALES1.EMPLOYEES
      SALES1.COMMISSION

      I also have another database PROD2 running on 9i on Linux with the following users and tables:

      SALES2.DEPARTMENTS
      SALES2.SALARY


      So basically, I am talking about 2 databases and 2 schemas. SALES1 only exist in PROD1 and SALES2 only exists in PROD2

      Function A:
      SALES2 user in PROD2 database must have an INSERT, UPDATE, DELETE grants on SALES1.EMPLOYEES in PROD1.

      Function B:
      SALES1 user in PROD1 must be able to INSERT, UPDATE, DELETE on SALES2.SALARY in PROD2.


      I tried the following but it didn't work as I expected it to be:

      1. Create a public database link for both users: SALES and SALES2
      2. I test the connection using the following:

      SALES1:
      SQL> select * from SALES2.SALARY@PROD2_LNK; --> succeeds

      SALES2:
      SQL> select * from SALES1.EMPLOYEES@PROD1_LNK; --> succeeds

      3. I create a synonym for these remote objects as it is required by the users:

      PROD1:
      SQL> create synonym SALARY for SALES2.SALARY@PROD2_LNK; --> OK
      SQL> select * from SALARY; --> OK

      PROD2:
      SQL> create synonym EMPLOYEES for SALES1.EMPLOYEES@PROD1_LNK; --> OK
      SQL> select * from EMPLOYEES; --> OK

      4. But when I update:

      PROD1:
      SQL> update SALARY set pay=1000 where employee_id=101; --> OK
      SQL> commit; --> OK
      But when I select the pay of employee_id 101, it is not updated.
      (the same scenario happens in PROD2 when i try to update EMPLOYEES)

      I know that I have to grant INSERT, UPDATE, DELETE on the base object to a user. But how can I do this on a database link?

      Any help will be much appreciated.

      Thanx!
        • 1. Re: Database Link and DDL
          976440
          Hi,

          You can directly update the SALES2.SALARY@PROD2_LNK table as below:

          update SALARSALES2.SALARY@PROD2_LNKY set pay=1000 where employee_id=101;

          now if you check it will update salary table

          same thing do for PROD2 scenario


          Regards
          Ambrish
          • 2. Re: Database Link and DDL
            Vedant..
            >
            4. But when I update:

            PROD1:
            SQL> update SALARY set pay=1000 where employee_id=101; --> OK
            SQL> commit; --> OK
            But when I select the pay of employee_id 101, it is not updated.
            (the same scenario happens in PROD2 when i try to update EMPLOYEES)
            Use the following :

            update SALARY@PROD2_LNK set pay=1000 where employee_id=101

            Since you are not using the database link 'PROD2_LNK', the salary table in PROD1 db is getting updated and hence facing the issue.
            Please use the db link it will solve your purpose.

            Edited by: Vedant.. on Dec 4, 2012 10:51 AM
            • 3. Re: Database Link and DDL
              InquisitiveDBA
              Hi,

              The user reported an incorrect information. Turns out the setup is actually working properly. Update on the base object or update on the synonym actually works fine.

              Thanks for all the feedback.