|Oracle® Database Administrator's Guide
11g Release 2 (11.2)
Part Number E17120-11
|PDF · Mobi · ePub|
The central concept in distributed database systems is a database link. A database link is a connection between two physical database servers that allows a client to access them as one logical database.
This section contains the following topics:
A database link is a pointer that defines a one-way communication path from an Oracle Database server to another database server. The link pointer is actually defined as an entry in a data dictionary table. To access the link, you must be connected to the local database that contains the data dictionary entry.
A database link connection is one-way in the sense that a client connected to local database A can use a link stored in database A to access information in remote database B, but users connected to database B cannot use the same link to access data in database A. If local users on database B want to access data on database A, then they must define a link that is stored in the data dictionary of database B.
A database link connection allows local users to access data on a remote database. For this connection to occur, each database in the distributed system must have a unique global database name in the network domain. The global database name uniquely identifies a database server in a distributed system.
Figure 31-3 shows an example of user
scott accessing the
emp table on the remote database with the global name
Database links are either private or public. If they are private, then only the user who created the link has access; if they are public, then all database users have access.
One principal difference among database links is the way that connections to a remote database occur. Users access a remote database through the following types of links:
|Type of Link||Description|
|Connected user link||Users connect as themselves, which means that they must have an account on the remote database with the same user name and password as their account on the local database.|
|Fixed user link||Users connect using the user name and password referenced in the link. For example, if Jane uses a fixed user link that connects to the
|Current user link||A user connects as a global user. A local user can connect as a global user in the context of a stored procedure, without storing the global user's password in a link definition. For example, Jane can access a procedure that Scott wrote, accessing Scott's account and Scott's schema on the
Create database links using the
CREATE DATABASE LINK statement. After a link is created, you can use it to specify schema objects in SQL statements.
Oracle Database SQL Language Reference for syntax of the
CREATE DATABASE statement
Oracle Database Advanced Security Administrator's Guide for information about Oracle Advanced Security
When a local database is connected to a remote database through a database link, either database can run in dedicated or shared server mode. The following table illustrates the possibilities:
|Local Database Mode||Remote Database Mode|
|Shared server||Shared server|
A shared database link can exist in any of these four configurations. Shared links differ from standard database links in the following ways:
Different users accessing the same schema object through a database link can share a network connection.
When a user must establish a connection to a remote server from a particular server process, the process can reuse connections already established to the remote server. The reuse of the connection can occur if the connection was established on the same server process with the same database link, possibly in a different session. In a non-shared database link, a connection is not shared across multiple sessions.
When you use a shared database link in a shared server configuration, a network connection is established directly out of the shared server process in the local server. For a non-shared database link on a local shared server, this connection would have been established through the local dispatcher, requiring context switches for the local dispatcher, and requiring data to go through the dispatcher.
See Also:Oracle Database Net Services Administrator's Guide for information about shared server
The great advantage of database links is that they allow users to access another user's objects in a remote database so that they are bounded by the privilege set of the object owner. In other words, a local user can access a link to a remote database without having to be a user on the remote database.
For example, assume that employees submit expense reports to Accounts Payable (A/P), and further suppose that a user using an A/P application must retrieve information about employees from the
hq database. The A/P users should be able to connect to the
hq database and execute a stored procedure in the remote
hq database that retrieves the desired information. The A/P users should not need to be
hq database users to do their jobs; they should only be able to access
hq information in a controlled way as limited by the procedure.
To understand how a database link works, you must first understand what a global database name is. Each database in a distributed database is uniquely identified by its global database name. The database forms a global database name by prefixing the database network domain, specified by the
DB_DOMAIN initialization parameter at database creation, with the individual database name, specified by the
DB_NAME initialization parameter.
For example, Figure 31-4 illustrates a representative hierarchical arrangement of databases throughout a network.
The name of a database is formed by starting at the leaf of the tree and following a path to the root. For example, the
mfg database is in
division3 of the
example_tools branch of the
com domain. The global database name for
mfg is created by concatenating the nodes in the tree as follows:
While several databases can share an individual name, each database must have a unique global database name. For example, the network domains
uk.europe.example_auto.com each contain a
sales database. The global database naming system distinguishes the
sales database in the
americas division from the
sales database in the
europe division as follows:
See Also:"Managing Global Names in a Distributed System" to learn how to specify and change global database names
You can use the global name of a database as a loopback database link without explicitly creating a database link. When the database link in a SQL statement matches the global name of the current database, the database link is effectively ignored.
For example, assume the global name of a database is
db1.example.com. You can run the following SQL statement on this database:
SELECT * FROM email@example.com;
In this case, the
@db1.example.com portion of the SQL statement is effectively ignored.
Typically, a database link has the same name as the global database name of the remote database that it references. For example, if the global database name of a database is
sales.us.example.com, then the database link is also called
When you set the initialization parameter
TRUE, the database ensures that the name of the database link is the same as the global database name of the remote database. For example, if the global database name for
TRUE, then the link name must be called
hq.example.com. Note that the database checks the domain part of the global database name as stored in the data dictionary, not the
DB_DOMAIN setting in the initialization parameter file (see "Changing the Domain in a Global Database Name").
If you set the initialization parameter
FALSE, then you are not required to use global naming. You can then name the database link whatever you want. For example, you can name a database link to
Note:Oracle recommends that you use global naming because many useful features, including Replication, require global naming.
After you have enabled global naming, database links are essentially transparent to users of a distributed database because the name of a database link is the same as the global name of the database to which the link points. For example, the following statement creates a database link in the local database to remote database
CREATE PUBLIC DATABASE LINK sales.division3.example.com USING 'sales1';
See Also:Oracle Database Reference for more information about specifying the initialization parameter
|Private||User who created the link. View ownership data through:
||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||User called PUBLIC. View ownership data through views shown for private database links.||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||User called PUBLIC. View ownership data through views shown for private database links.||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.
Determining the type of database links to employ in a distributed database depends on the specific requirements of the applications using the system. Consider these features when making your choice:
|Type of Link||Features|
|Private database link||This 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.|
|Public database link||When many users require an access path to a remote Oracle Database, you can create a single public database link for all users in a database.|
|Global database link||When an Oracle network uses a directory server, an administrator can conveniently manage global database links for all databases in the system. Database link management is centralized and simple.|
When creating the link, you determine which user should connect to the remote database to access the data. The following table explains the differences among the categories of users involved in database links:
|User Type||Description||Sample Link Creation Syntax|
|Connected user||A local user accessing a database link in which no fixed username and password have been specified. If
Note: A connected user does not have to be the user who created the link, but is any user who is accessing the link.
|Current user||A global user in a
See Oracle Database Advanced Security Administrator's Guide for information about global security
|Fixed user||A user whose username/password is part of the link definition. If a link includes a fixed user, the fixed user's username and password are used to connect to the remote database.||
Note:The following users cannot be target users of database links:
See Also:"Specifying Link Users" to learn how to specify users when creating 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 do not 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.
||An externally-authenticated user can connect to the remote database using a connected user database link.|
||An externally-authenticated user cannot connect to the remote database using a connected user database link unless a secure protocol or a network authentication service supported by the Oracle Advanced Security option is used.|
REMOTE_OS_AUTHENTinitialization parameter is deprecated. It is retained for backward compatibility only.
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
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
Fixed user links have a user name and password associated with the connect string. The user name and password are stored with other link information in data dictionary tables.
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 is a global user and authenticated through a certificate over SSL, but
jane is not.
Note that current user database links have these consequences:
If the current user database link is not accessed from within a stored object, then the current user is the same as the connected user accessing the link. For example, if
scott issues a
SELECT statement through a current user link, then the current user is
When executing a stored object such as a procedure, view, or trigger that accesses a database link, the current user is the user that owns the stored object, and not the user that calls the object. For example, if
jane calls procedure
scott.p (created by
scott), and a current user link appears within the called procedure, then
scott is the current user of the link.
If the stored object is an invoker's rights function, procedure, or package, then the invoker's authorization ID is used to connect as a remote user. For example, if user
jane calls procedure
scott.p (an invoker's rights procedure created by
scott), and the link appears inside procedure
jane is the current user.
You cannot connect to a database as an enterprise user and then use a current user link in a stored procedure that exists in a shared, global schema. For example, if user
jane accesses a stored procedure in the shared schema
guest on database
hq, she cannot use a current user link in this schema to log on to a remote database.
"Distributed Database Security" for more information about security issues relating to database links
Oracle Database PL/SQL Language Reference for more information about invoker's rights functions, procedures, or packages.
Create database links using the
CREATE DATABASE LINK statement. The table gives examples of SQL statements that create database links in a local database to the remote
|SQL Statement||Connects To Database||Connects As||Link Type|
||Connected user||Private connected user|
||Current global user||Private current user|
||Private fixed user|
||Public fixed user|
||Shared public fixed user|
SELECT * FROM emp@foo;
You must also be authorized in the remote database to access specific remote objects.
Constructing properly formed object names using database links is an essential aspect of data manipulation in distributed systems.
Oracle Database uses the global database name to name the schema objects globally using the following scheme:
schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema.
schema_object is a logical data structure like a table, index, view, synonym, procedure, package, or a database link.
global_database_name is the name that uniquely identifies a remote database. This name must be the same as the concatenation of the remote database initialization parameters
DB_DOMAIN, unless the parameter
GLOBAL_NAMES is set to
FALSE, in which case any name is acceptable.
For example, using a database link to database
sales.division3.example.com, a user or application can reference remote data as follows:
SELECT * FROM firstname.lastname@example.org; # emp table in scott's schema SELECT loc FROM email@example.com;
GLOBAL_NAMES is set to
FALSE, then you can use any name for the link to
sales.division3.example.com. For example, you can call the link
foo. Then, you can access the remote database as follows:
SELECT name FROM scott.emp@foo; # link name different from global name
To access a remote schema object, you must be granted access to the remote object in the remote database. Further, to perform any updates, inserts, or deletes on the remote object, you must be granted the
SELECT privilege on the object, along with the
DELETE privilege. Unlike when accessing a local object, the
SELECT privilege is necessary for accessing a remote object because the database has no remote describe capability. The database must do a
SELECT * on the remote object in order to determine its structure.
Oracle Database lets you create synonyms so that you can hide the database link name from the user. A synonym allows access to a table on a remote database using the same syntax that you would use to access a table on a local database. For example, assume you issue the following query against a table in a remote database:
SELECT * FROM firstname.lastname@example.org;
You can create the synonym
email@example.com so that you can issue the following query instead to access the same data:
SELECT * FROM emp;
See Also:"Using Synonyms to Create Location Transparency" to learn how to create synonyms for objects specified using database links
To resolve application references to schema objects (a process called name resolution), the database forms object names hierarchically. For example, the database guarantees that each schema within a database has a unique name, and that within a schema each object has a unique name. As a result, a schema object name is always unique within the database. Furthermore, the database resolves application references to the local name of the object.
In a distributed database, a schema object such as a table is accessible to all applications in the system. The database extends the hierarchical naming model with global database names to effectively create global object names and resolve references to the schema objects in a distributed database system. For example, a query can reference a remote table by specifying its fully qualified name, including the database in which it resides.
For example, assume that you connect to the local database as user
You then issue the following statements using database link
hq.example.com to access objects in the
jane schemas on remote database
SELECT * FROM firstname.lastname@example.org; INSERT INTO email@example.com (acc_no, acc_name, balance) VALUES (5001, 'BOWER', 2000); UPDATE firstname.lastname@example.org SET balance = balance + 500; DELETE FROM email@example.com WHERE acc_name = 'BOWER';
Grant privileges on remote objects
DESCRIBE operations on some remote objects. The following remote objects, however, do support
Analyze remote objects
Define or enforce referential integrity
Obtain nondefault roles on a remote database. For example, if
jane connects to the local database and executes a stored procedure that uses a fixed user link connecting as
scott's default roles on the remote database. Jane cannot issue
SET ROLE to obtain a nondefault role.
Execute hash query joins that use shared server connections
Use a current user link without authentication through SSL, password, or NT native authentication