Difference between Public and Private DBlink

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

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.

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

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.
"