7 Replies Latest reply: Jul 18, 2008 1:54 PM by user594143 RSS

    Difference between Public and Private DBlink

    user594143
      I need to create DBlink to access objects on remote database. I came across 2 different ways of doing by creating Public DBlink and Private. Not sure what would be the difference? Or what is advantage of having private vs public

      Thanks.

      Oracle Version: 9.2
        • 1. Re: Difference between Public and Private DBlink
          Eduardo Legatti
          Hi,

          PRIVATE
          Creates link in a specific schema of the local database. Only the owner of a private database link or PL/SQL subprograms in the schema can use this link to access database objects in the corresponding remote database.

          PUBLIC
          Creates a database-wide link. All users and PL/SQL subprograms in the database can use the link to access database objects in the corresponding remote database.

          GLOBAL
          Creates a network-wide link. When an Oracle network uses a directory server, the directory server automatically create and manages global database links (as net service names) for every Oracle Database in the network. Users and PL/SQL subprograms in any database can use a global link to access objects in the corresponding remote database.

          Note: In earlier releases of Oracle Database, a global database link referred to a database link that was registered with an Oracle Names server. The use of an Oracle Names server has been deprecated. In this document, global database links refer to the use of net service names from the directory server.

          For more information, take a look at [url http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/ds_concepts002.htm]Database Links documentation

          Cheers

          Legatti
          • 2. Re: Difference between Public and Private DBlink
            user594143
            Ok. Thanks I understand this concept now. Here is new question that came up.

            What would be the best practice to create Private DBlink which would not hold user name/password for other user in remote database? We have audit requremenst to avoid this situation.
            • 3. Re: Difference between Public and Private DBlink
              247514
              Please check posted document link earlier,
              to avoid put direct username/password into DB link DDL, you can use
              CONNECT TO CURRENT_USER
              which works on both private and public dblink.
              • 4. Re: Difference between Public and Private DBlink
                user594143
                What about Creating a Connected User Database Link option.

                For example on primary database I create private link under user ABC and my remote database has same user ABC with same password. Would this work for my goal to have DBlink in primary database that does not expose credentials.

                Could you please confrim this? I am new to this stuff and just want to make sure I understand this.

                Thanks again.
                • 5. Re: Difference between Public and Private DBlink
                  Eduardo Legatti
                  Hi,

                  According to documentation, a Private Database Link is more secure than a public or global link, because only the owner of the private link, or subprograms within the same schema, can use the link to access the remote database. About your question, I advise you to take a look at "Users of Database Links" section on the documentation that I provided in my previous post:

                  Connected User Database Links

                  Connected user links have no connect string associated with them. The advantage of a connected user link is that a user referencing the link connects to the remote database as the same user, and credentials don't have to be stored in the link definition in the data dictionary. Connected user links have some disadvantages. Because these links require users to have accounts and privileges on the remote databases to which they are attempting to connect, they require more privilege administration for administrators. Also, giving users more privileges than they need violates the fundamental security concept of least privilege: users should only be given the privileges they need to perform their jobs. The ability to use a connected user database link depends on several factors, chief among them whether the user is authenticated by the database using a password, or externally authenticated by the operating system or a network authentication service. If the user is externally authenticated, then the ability to use a connected user link also depends on whether the remote database accepts remote authentication of users, which is set by the REMOTE_OS_AUTHENT initialization parameter.

                  Fixed User Database Links

                  A benefit of a fixed user link is that it connects a user in a primary database to a remote database with the security context of the user specified in the connect string. For example, local user joe can create a public database link in joe's schema that specifies the fixed user scott with password tiger. If jane uses the fixed user link in a query, then jane is the user on the local database, but she connects to the remote database as scott/tiger. Fixed user links have a username and password associated with the connect string. The username and password are stored with other link information in data dictionary tables.

                  Current User Database Links

                  Current user database links make use of a global user. A global user must be authenticated by an X.509 certificate or a password, and be a user on both databases involved in the link. The user invoking the CURRENT_USER link does not have to be a global user. For example, if jane is authenticated (not as a global user) by password to the Accounts Payable database, she can access a stored procedure to retrieve data from the hq database. The procedure uses a current user database link, which connects her to hq as global user scott. User scott is a global user and authenticated through a certificate over SSL, but jane is not.

                  Cheers

                  Legatti
                  • 6. Re: Difference between Public and Private DBlink
                    247514
                    Using connected user will works too but that require you have same user accounts on both databases. Depends on how many users you have, that could be hard to manage.

                    "
                    Creating Connected User and Current User Database Links
                    Connected user and current user database links do not include credentials in the definition of the link. The credentials used to connect to the remote database can change depending on the user that references the database link and the operation performed by the application.

                    Note:

                    For many distributed applications, you do not want a user to have privileges in a remote database. One simple way to achieve this result is to create a procedure that contains a fixed user or current user database link within it. In this way, the user accessing the procedure temporarily assumes someone else's privileges.
                    "
                    • 7. Re: Difference between Public and Private DBlink
                      user594143
                      Guys Thanks you very much! I am clear on this now.