4 Replies Latest reply on Sep 23, 2016 9:38 AM by Gbenga Ajakaye

    DB Link

    3305994

      Hello!

      I want two DB's link (WODIS -> WODIS_BGL).

      CREATE DATABASE LINK WODIS_BGL_Link
      CONNECT TO BGL
      IDENTIFIED BY "password"
      USING 'WODIS_BGL'

      The following error message appears:

      Error starting at line 1 in command -
      CREATE DATABASE LINK WODIS_BGL_Link
      CONNECT TO BGL
      IDENTIFIED BY "password"
      USING 'WODIS_BGL'
      Error Report -
      SQL Error: ORA-01031: Insufficient Permissions
      01,031th 00000 - "insufficient privileges"
      * Cause: An attempt what made to perform a database without surgery
                
      The Necessary privileges.
      * Action: Ask your database administrator or designated security
                
      administrator to grant you The Necessary privileges

      According to SQL Developer, the user has the CREATE DATABASE link - right

      Rechte_WODIS.JPG

      However, when I do a search, it is not listed with:

      -- Current user

      select username from USER_USERS;

      - Rights of the user
      SELECT * FROM DBA_SYS_PRIVS WHERE grantee = 'WODIS' ORDER BY 2;

       

      ->
      USERNAME                    
      ------------------------------
      WODIS                        

       

      GRANTEE                        PRIVILEGE                            ADM
      ------------------------------ ---------------------------------------- ---
      WODIS                          ALTER ANY TABLE                 YES
      WODIS                          ALTER ANY TRIGGER            YES
      WODIS                          ALTER USER                           YES
      WODIS                          ANALYZE ANY                         YES
      WODIS                          CREATE INDEXTYPE              YES
      WODIS                          CREATE PROCEDURE           YES
      WODIS                          CREATE PUBLIC SYNONYM  YES
      WODIS                          CREATE SEQUENCE              YES
      WODIS                          CREATE SYNONYM                 YES
      WODIS                          CREATE TABLE                        YES
      WODIS                          CREATE TRIGGER                   YES
      WODIS                          CREATE USER                          YES
      WODIS                          CREATE VIEW                           YES
      WODIS                          DELETE ANY TABLE                 YES
      WODIS                          DROP PUBLIC SYNONYM        YES
      WODIS                          DROP USER                               YES
      WODIS                          EXECUTE ANY PROCEDURE  YES
      WODIS                          INSERT ANY TABLE                   YES
      WODIS                          SELECT ANY DICTIONARY       YES
      WODIS                          SELECT ANY SEQUENCE        YES
      WODIS                          SELECT ANY TABLE                 YES
      WODIS                          UNLIMITED TABLESPACE        YES
      WODIS                          UPDATE ANY TABLE                 YES

       

      Why the right is not "transfer" ?

      And how do I link the DB's ?

       

       

        • 1. Re: DB Link
          B.Delmée

          Sorry I cannot help but

           

          > 01,031th 00000 - "insufficient privileges"
          > * Cause: An attempt what made to perform a database without surgery

           

          really ? did you make that message up ?

          • 2. Re: DB Link
            Gbenga Ajakaye

            3305994 wrote:

             

            Hello!

            I want two DB's link (WODIS -> WODIS_BGL).

            CREATE DATABASE LINK WODIS_BGL_Link
            CONNECT TO BGL
            IDENTIFIED BY "password"
            USING 'WODIS_BGL'

            The following error message appears:

            Error starting at line 1 in command -
            CREATE DATABASE LINK WODIS_BGL_Link
            CONNECT TO BGL
            IDENTIFIED BY "password"
            USING 'WODIS_BGL'
            Error Report -
            SQL Error: ORA-01031: Insufficient Permissions
            01,031th 00000 - "insufficient privileges"
            * Cause: An attempt what made to perform a database without surgery
            The Necessary privileges.
            * Action: Ask your database administrator or designated security
            administrator to grant you The Necessary privileges

            According to SQL Developer, the user has the CREATE DATABASE link - right

            Rechte_WODIS.JPG

            However, when I do a search, it is not listed with:

            -- Current user

            select username from USER_USERS;

            - Rights of the user
            SELECT * FROM DBA_SYS_PRIVS WHERE grantee = 'WODIS' ORDER BY 2;

             

            ->
            USERNAME
            ------------------------------
            WODIS

             

            GRANTEE PRIVILEGE ADM
            ------------------------------ ---------------------------------------- ---
            WODIS ALTER ANY TABLE YES
            WODIS ALTER ANY TRIGGER YES
            WODIS ALTER USER YES
            WODIS ANALYZE ANY YES
            WODIS CREATE INDEXTYPE YES
            WODIS CREATE PROCEDURE YES
            WODIS CREATE PUBLIC SYNONYM YES
            WODIS CREATE SEQUENCE YES
            WODIS CREATE SYNONYM YES
            WODIS CREATE TABLE YES
            WODIS CREATE TRIGGER YES
            WODIS CREATE USER YES
            WODIS CREATE VIEW YES
            WODIS DELETE ANY TABLE YES
            WODIS DROP PUBLIC SYNONYM YES
            WODIS DROP USER YES
            WODIS EXECUTE ANY PROCEDURE YES
            WODIS INSERT ANY TABLE YES
            WODIS SELECT ANY DICTIONARY YES
            WODIS SELECT ANY SEQUENCE YES
            WODIS SELECT ANY TABLE YES
            WODIS UNLIMITED TABLESPACE YES
            WODIS UPDATE ANY TABLE YES

             

            Why the right is not "transfer" ?

            And how do I link the DB's ?

             

             

            Re-grant

            • GRANT create database link to user;
            • GRANT create public database link to user

             

            Then re-run your create db link command. Check this link for more details. https://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_admin002.htm#i1007840

            1 person found this helpful
            • 3. Re: DB Link
              3305994

              Hello BCG14!

               

              Thanks for your answer!

               

               

              I need a little time to familiarize myself with fundamentals.

               

               

              With another user I could create the DB-Link, am still cause research.

               

               

               

              Thank you!

              • 4. Re: DB Link
                Gbenga Ajakaye

                Fantastic. Be sure to make the answer correct.