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:
What Are Database Links?
A database link is a pointer that defines a one-way communication path from anOracle 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.
We shows an example of user scott accessing the emp table on the remote database with the global name hq.acme.com:
Database Link
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 table
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.
What Are Shared Database Links?
A shared database link is a link between a local server process and the remote database. The link is shared because multiple client processes can use the same link simultaneously.
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 table
A shared database link can exist in any of these four configurations. Shared links differ from standard database links in the following ways:
Why Use Database Links?
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 needs to 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.
Database links allow you to grant limited access on remote databases to local users. By using current user links, you can create centrally managed global users whose password information is hidden from both administrators and non administrative users. For example, A/P users can access the hq database as scott, but unlike fixed user links, scott's credentials are not stored where database users can see them.
By using fixed user links, you can create nonglobal users whose password information is stored in unencrypted form in the LINK$ data dictionary table. Fixed user links are easy to create and require low overhead because there are no SSL or directory requirements, but a security risk results from the storage of password information in the data dictionary.
Global Database Names in Database Links
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.
Hierarchial arrangement of Network Databases
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 acme_ 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 us.americas.acme _auto.com and uk.europe.acme _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:
Names for Database Links
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.oracle.com, then the database link is also called sales.us.oracle.com.
When you set the initialization parameter GLOBAL _NAMES to 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 hq is hq.acme.com, and GLOBAL _NAMES is TRUE, then the link name must be called hq.acme.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 GLOBAL _NAMES to 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 hq.acme.com as foo.
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 sales:
CREATE PUBLIC DATABASE LINK sales.division3.acme.com USING 'sales1';Types of Database Links
Oracle Database lets you create private, public, and global database links. These basic link types differ according to which users are allowed access to the remote database:
Determining the type of database lins to employ in a distributed database depends on the specific requirements of the applications using the system. Consider these features when making your choice:
Users of Database Links
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:
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. Furthermore, because no connect string is associated with the link, no password is stored in clear text 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.
The REMOTE _OS _AUTHENT parameter operates as follows:
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 in unencrypted form in the data dictionary in the LINK$ table.
For an example of this security problem, assume that jane does not have privileges to use a private link that connects to the hq database as scott/tiger, but has SELECT ANY TABLE privilege on a database in which the O7 _DICTIONARY _ ACCESSIBILITY initialization parameter is set to TRUE. She can select from LINK$ and read that the connect string to hq is scott/tiger. If jane has an account on the host on which hq resides, then she can connect to the host and then connect to hq as scott using the password tiger. She will have all scott's privileges if she connects locally and any audit records will be recorded as if she were scott.
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.
Note that current user database links have these consequences:
Creation of Database Links: Examples
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 sales.us.americas.acme _auto.com database:
Schema Objects and Database Links
After you have created a database link, you can execute SQL statements that access objects on the remote database. For example, to access remote object emp using database link foo, you can issue:
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.
Naming of Schema Objects Using Database Links
Oracle Database uses the global database name to name the schema objects globally using the following scheme:
schema.schema _object@global _database_namewhere:
For example, using a database link to database sales.division3.acme.com, a user or application can reference remote data as follows:
If GLOBAL _NAMES is set to FALSE, then you can use any name for the link to sales.division3.acme.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 nameAuthorization for Accessing Remote Schema Objects
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 UPDATE, INSERT, or 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.
Synonyms for Schema Objects
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 emp@hq.acme.com;You can create the synonym emp for emp@hq.acme.com so that you can issue the following query instead to access the same data:
SELECT * FROM emp;Schema Object Name Resolution
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 SYSTEM:
CONNECT SYSTEM/password@sales1You then issue the following statements using database link hq.acme.com to access objects in the scott and jane schemas on remote database hq:
Database Link Restrictions
You cannot perform the following operations using database links:
– Tables
– Views
– Procedures
– Functions
|
|
Oracle 10g Related Tutorials |
|
---|---|
Oracle 9i Tutorial | Oracle 8i Tutorial |
Informatica Tutorial | Oracle 11g Tutorial |
Oracle 10g Related Interview Questions |
|
---|---|
Oracle 10g Interview Questions | Oracle 9i Interview Questions |
Oracle 8i Interview Questions | Informatica Interview Questions |
PL/SQL Interview Questions | Oracle 11g Interview Questions |
SQL Interview Questions | Oracle apps Interview Questions |
Sybase Interview Questions | Oracle Apps ERP Interview Questions |
Oracle 7.3 Interview Questions | Oracle Access Manager Interview Questions |
Oracle Application Framework Interview Questions | Oracle Apps DBA Interview Questions |
Oracle 10g Related Practice Tests |
|
---|---|
Oracle 10g Practice Tests | Oracle 9i Practice Tests |
Oracle 8i Practice Tests | Informatica Practice Tests |
PL/SQL Practice Tests | Oracle 11g Practice Tests |
SQL Practice Tests | Oracle apps Practice Tests |
Sybase Practice Tests | Oracle Apps ERP Practice Tests |
Oracle 7.3 Practice Tests |
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.