Database Links

(Excerpts from Oracle8 Distributed Database Systems Part I)

Distributed Databases

A distributed database is a set of databases stored on multiple computers that typically appears to applications as a single database. Consequently, an application can simultaneously access and modify the data in several databases in a network. Each Oracle database in the system is controlled by its local Oracle server but cooperates to maintain the consistency of the global distributed database.

What is a database link?

To facilitate application requests in a distributed database system, Oracle uses database links. A database link defines a one-way communication path from an Oracle database to another database. After creating a database link, applications connected to the local database can access data in the remote database.

Location Transparency

An Oracle distributed database system has features that allow application developers and administrators to hide the physical location of database objects from applications and users. Location transparency exists when a user can universally refer to a database object such as a table, regardless of the node to which an application connects. Location transparency has several benefits, including: 

Most typically, administrators and developers use synonyms to establish location transparency for the tables and supporting objects in an application schema. Views and Procedures can also be used for location transparency.

Statement and Transaction Transparency

Oracle's distributed database architecture also provides query, update, and transaction transparency. For example, standard SQL commands such as SELECT, INSERT, UPDATE, and DELETE work just as they do in a non-distributed database environment. Additionally, applications control transactions using the standard SQL commands COMMIT, SAVEPOINT, and ROLLBACK--there is no requirement for complex programming or other special operations to provide distributed transaction control. 

The statements in a single transaction can reference any number of local or remote tables. 
Oracle guarantees that all nodes involved in a distributed transaction take the same action: they either all commit or all roll back the transaction.  

If a network or system failure occurs during the commit of a distributed transaction, the transaction is automatically and transparently resolved globally; that is, when the network or system is restored, the nodes either all commit or all roll back the transaction.

Types of database links

Private database link

You can create a private database link in a specific schema of a database. Only the owner of a private database link or PL/SQL subprograms in the schema can use a private database link to access data and database objects in the corresponding remote database.

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 private link to access the specified remote database.

Public database link

You can create a public database link for a database. All users and PL/SQL subprograms in the database can use a public database link to access data and database objects in the corresponding remote database.

When many users require an access path to a remote Oracle database, an administrator can create a single public database link for all users in a database.

Global database link

When an Oracle network uses Oracle Names, the names servers in the system automatically create and manage global database links for every Oracle database in the network. All users and PL/SQL subprograms in any database can use a global database link to access data and database objects in the corresponding remote database.

Security Options for Database Links

A database link defines a communication path from one database to another. When an application uses a database link to access a remote database, Oracle establishes a database session in the remote database on behalf of the local application request. 

When you create a private or public database link, you can determine which schema on the remote database the link will establish connections to by creating fixed user, current user, and connected user database links. 

Fixed User Database Links

To create a fixed user database link, you embed the credentials (in this case, a username and password) required to access the remote database in the definition of the link: 

CREATE DATABASE LINK ... CONNECT TO username IDENTIFIED BY password ...;

When an application uses a fixed user database link, the local server always establishes a connection to a fixed remote schema in the remote database. The local server also sends the user's credentials across the network when an application uses the link to access the remote database. If an unsecure network supports a distributed database that uses fixed user database links, consider encrypting login packets for server-to-server connections. 

Connected User and Current User Database Links

Connected user and current user database links do not include any 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 being performed by the application. 

A connected user is a user that connects to a database using a database application. For example, when you start SQL*Plus and connect to an Oracle database as SCOTT, the connected user is SCOTT. 

A current user is determined by the security context in which a database operation executes. For example, when you connect to an Oracle database as the user SCOTT and execute the procedure SALES.DEL_EMP, the current user while executing the DEL_EMP procedure defaults to SALES because a stored procedure executes within the security context of its owner. 

To create a connected user database link, you merely omit the CONNECT TO clause. The following example creates a connected user database link: 

CREATE DATABASE LINK sales.division3.acme.com USING 'sales';

To use a current user database link, the current user must be a global user that is authenticated by the Oracle Security Server.

Database Link Resolution

Whenever a SQL statement includes a reference to a global object name, Oracle searches for a database link with a name that matches the database name specified in the global object name. Oracle does this to determine the path to the specified remote database. 

Oracle always searches for matching database links in the following order: 
1. Private database links in the schema of the user who issued the SQL statement. 
2. Public database links in the local database. 
3. Global database links (only if an Oracle Name Server is available).