Difference between public and private dblinks

In Oracle, database links (dblinks) allow you to access data in a remote database from your local database. There are two types of database links: public and private. Here’s a detailed explanation of the differences between public and private dblinks:

Private Database Links

• Scope: Private database links are owned by a specific user and are only accessible to that user.
• Security: They provide a higher level of security because they restrict access to the database link to a single user.
• Creation: Created by the user who will own the link and does not require any special privileges other than CREATE DATABASE LINK.

Syntax

CREATE DATABASE LINK dblink_name
CONNECT TO remote_user IDENTIFIED BY remote_password
USING 'tns_service_name';

Example

Assume you are logged in as user1 and want to create a private database link to access a remote database.

CREATE DATABASE LINK my_private_dblink
CONNECT TO remote_user IDENTIFIED BY remote_password
USING 'remote_db';

Only user1 can use my_private_dblink to access the remote database.

Public Database Links

• Scope: Public database links are created in the public schema and are accessible to all users in the database.
• Convenience: They are convenient for providing shared access to a remote database without having to create individual database links for each user.
• Security: Less secure compared to private database links because they are available to all users in the database. Care should be taken to ensure that sensitive data is not exposed.
• Creation: Requires special privileges (CREATE PUBLIC DATABASE LINK).

Syntax

CREATE PUBLIC DATABASE LINK dblink_name
CONNECT TO remote_user IDENTIFIED BY remote_password
USING 'tns_service_name';

Example

Assume you want to create a public database link that all users can use to access a remote database.

CREATE PUBLIC DATABASE LINK my_public_dblink
CONNECT TO remote_user IDENTIFIED BY remote_password
USING 'remote_db';

Now, any user in the local database can use my_public_dblink to access the remote database.

Key Differences

1. Accessibility:

   • Private Database Links: Accessible only by the user who created the link.
   • Public Database Links: Accessible by all users in the database.

2. Security:

   • Private Database Links: More secure as they restrict access to a single user.
   • Public Database Links: Less secure as they are accessible to all users.

3. Privileges Required:

   • Private Database Links: Requires the CREATE DATABASE LINK privilege.
   • Public Database Links: Requires the CREATE PUBLIC DATABASE LINK privilege.

4. Use Case:

   • Private Database Links: Suitable for scenarios where database access needs to be restricted to specific users.
   • Public Database Links: Suitable for scenarios where database access needs to be shared among multiple users.

Example Use Cases

Private Database Link Use Case

A user named user1 needs to access a remote database to fetch data for personal reports. A private database link ensures that only user1 can access the remote data, providing an additional layer of security.

-- As user1
CREATE DATABASE LINK personal_dblink
CONNECT TO remote_user IDENTIFIED BY remote_password
USING 'remote_db';

-- Using the private database link
SELECT * FROM employees@personal_dblink;

Public Database Link Use Case

An organization needs to allow all users in the local database to access a shared remote database for reading data. A public database link is created to facilitate this shared access.

-- As a privileged user (e.g., DBA)
CREATE PUBLIC DATABASE LINK shared_dblink
CONNECT TO remote_user IDENTIFIED BY remote_password
USING 'remote_db';

-- Any user can now use the public database link
SELECT * FROM employees@shared_dblink;

Conclusion

The choice between public and private database links depends on the specific requirements for security and accessibility. Private database links are ideal for individual user access, providing a secure connection to a remote database. Public database links, on the other hand, offer a convenient way to share access across multiple users, but at the cost of reduced security. Proper management and consideration of these factors are crucial when implementing database links in Oracle.