31 Distributed Database Concepts

Distributed Databases Versus Distributed Processing

The terms distributed database and distributed processing are closely related, yet have distinct meanings. There definitions are as follows:

• Distributed database

  A set of databases in a distributed system that can appear to applications as a single data source.

• Distributed processing

  The operations that occurs when an application distributes its tasks among different computers in a network. For example, a database application typically distributes front-end presentation tasks to client computers and allows a back-end database server to manage shared access to a database. Consequently, a distributed database application processing system is more commonly referred to as a client/server database application system.

Distributed database systems employ a distributed processing architecture. For example, an Oracle Database server acts as a client when it requests data that another Oracle Database server manages.

Distributed Databases Versus Replicated Databases

The terms distributed database system and database replication are related, yet distinct. In a pure (that is, not replicated) distributed database, the system manages a single copy of all data and supporting database objects. Typically, distributed database applications use distributed transactions to access both local and remote data and modify the global database in real-time.

The term replication refers to the operation of copying and maintaining database objects in multiple databases belonging to a distributed system. While replication relies on distributed database technology, database replication offers applications benefits that are not possible within a pure distributed database environment.

Most commonly, replication is used to improve local database performance and protect the availability of applications because alternate data access options exist. For example, an application may normally access a local database rather than a remote server to minimize network traffic and achieve maximum performance. Furthermore, the application can continue to function if the local server experiences a failure, but other servers with replicated data remain accessible.

Heterogeneous Services

Heterogeneous Services (HS) is an integrated component within the Oracle Database server and the enabling technology for the current suite of Oracle Transparent Gateway products. HS provides the common architecture and administration mechanisms for Oracle Database gateway products and other heterogeneous access facilities. Also, it provides upwardly compatible functionality for users of most of the earlier Oracle Transparent Gateway releases.

Transparent Gateway Agents

For each non-Oracle Database system that you access, Heterogeneous Services can use a transparent gateway agent to interface with the specified non-Oracle Database system. The agent is specific to the non-Oracle Database system, so each type of system requires a different agent.

The transparent gateway agent facilitates communication between Oracle Database and non-Oracle Database systems and uses the Heterogeneous Services component in the Oracle Database server. The agent executes SQL and transactional requests at the non-Oracle Database system on behalf of the Oracle Database server.

Generic Connectivity

Generic connectivity enables you to connect to non-Oracle Database data stores by using either a Heterogeneous Services ODBC agent or a Heterogeneous Services OLE DB agent. Both are included with your Oracle product as a standard feature. Any data source compatible with the ODBC or OLE DB standards can be accessed using a generic connectivity agent.

The advantage to generic connectivity is that it may not be required for you to purchase and configure a separate system-specific agent. You use an ODBC or OLE DB driver that can interface with the agent. However, some data access features are only available with transparent gateway agents.

Global Name as a Loopback Database Link

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 hr.employees@db1.example.com;

In this case, the @db1.example.com portion of the SQL statement is effectively ignored.

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

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 password. 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/password.

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.

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:

• 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 scott.

• 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 scott.p, then 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.

  See Also:

  • "Distributed Database Security" for more information about security issues relating to database links

  • Oracle Database Advanced Security Administrator's Guide

  • Oracle Database PL/SQL Language Reference for more information about invoker's rights functions, procedures, or packages.

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_name

where:

• 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_NAME and 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 scott.emp@sales.division3.example.com;  # emp table in scott's schema
SELECT loc FROM scott.dept@sales.division3.example.com;

If 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

Authorization 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.example.com;

You can create the synonym emp for emp@hq.example.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@sales1

You then issue the following statements using database link hq.example.com to access objects in the scott and jane schemas on remote database hq:

SELECT * FROM scott.emp@hq.example.com;
INSERT INTO jane.accounts@hq.example.com (acc_no, acc_name, balance)
  VALUES (5001, 'BOWER', 2000);
UPDATE jane.accounts@hq.example.com
  SET balance = balance + 500;
DELETE FROM jane.accounts@hq.example.com
  WHERE acc_name = 'BOWER';

Authentication Through Database Links

Database links are either private or public, authenticated or non-authenticated. You create public links by specifying the PUBLIC keyword in the link creation statement. For example, you can issue:

CREATE PUBLIC DATABASE LINK foo USING 'sales';

You create authenticated links by specifying the CONNECT TO clause, AUTHENTICATED BY clause, or both clauses together in the database link creation statement. For example, you can issue:

CREATE DATABASE LINK sales CONNECT TO scott IDENTIFIED BY password USING 'sales';
CREATE SHARED PUBLIC DATABASE LINK sales CONNECT TO nick IDENTIFIED BY password1 
     AUTHENTICATED BY david IDENTIFIED BY password2 USING 'sales';

This table describes how users access the remote database through the link:

Authentication Without Passwords

When using a connected user or current user database link, you can use an external authentication source such as Kerberos to obtain end-to-end security. In end-to-end authentication, credentials are passed from server to server and can be authenticated by a database server belonging to the same domain. For example, if jane is authenticated externally on a local database, and wants to use a connected user link to connect as herself to a remote database, the local server passes the security ticket to the remote database.

Supporting User Accounts and Roles

In a distributed database system, you must carefully plan the user accounts and roles that are necessary to support applications using the system. Note that:

• The user accounts necessary to establish server-to-server connections must be available in all databases of the distributed database system.

• The roles necessary to make available application privileges to distributed database application users must be present in all databases of the distributed database system.

As you create the database links for the nodes in a distributed database system, determine which user accounts and roles each site must support server-to-server connections that use the links.

In a distributed environment, users typically require access to many network services. When you must configure separate authentications for each user to access each network service, security administration can become unwieldy, especially for large systems.

Centralized User and Privilege Management

The database provides different ways for you to manage the users and privileges involved in a distributed system. For example, you have these options:

• Enterprise user management. You can create global users who are authenticated through SSL or by using passwords, then manage these users and their privileges in a directory through an independent enterprise directory service.

• Network authentication service. This common technique simplifies security management for distributed environments. You can use the Oracle Advanced Security option to enhance Oracle Net and the security of an Oracle Database distributed database system. Windows NT native authentication is an example of a non-Oracle authentication solution.

  See Also:

  • Oracle Database Advanced Security Administrator's Guide

  • Oracle Database Enterprise User Security Administrator's Guide

CREATE USER fred IDENTIFIED GLOBALLY AS 'CN=fred adams,O=Oracle,C=England';

Schema-Independent Global Users

The database supports functionality that allows a global user to be centrally managed by an enterprise directory service. Users who are managed in the directory are called enterprise users. This directory contains information about:

• Which databases in a distributed system an enterprise user can access

• Which role on each database an enterprise user can use

• Which schema on each database an enterprise user can connect to

The administrator of each database is not required to create a global user account for each enterprise user on each database to which the enterprise user must connect. Instead, multiple enterprise users can connect to the same database schema, called a shared schema.

Note:

You cannot access a current user database link in a shared schema.

For example, suppose jane, bill, and scott all use a human resources application. The hq application objects are all contained in the guest schema on the hq database. In this case, you can create a local global user account to be used as a shared schema. This global username, that is, shared schema name, is guest. jane, bill, and scott are all created as enterprise users in the directory service. They are also mapped to the guest schema in the directory, and can be assigned different authorizations in the hq application.

Figure 31-5 illustrates an example of global user security using the enterprise directory service:

Figure 31-5 Global User Security

Description of "Figure 31-5 Global User Security"

Assume that the enterprise directory service contains the following information on enterprise users for hq and sales:

Database	Role	Schema	Enterprise Users
hq	clerk1	guest	bill scott
sales	clerk2	guest	jane scott

Also, assume that the local administrators for hq and sales have issued statements as follows:

Database	CREATE Statements
hq	CREATE USER guest IDENTIFIED GLOBALLY AS ''; CREATE ROLE clerk1 GRANT select ON emp; CREATE PUBLIC DATABASE LINK sales_link CONNECT AS CURRENT_USER USING 'sales';
sales	CREATE USER guest IDENTIFIED GLOBALLY AS ''; CREATE ROLE clerk2 GRANT select ON dept;

Assume that enterprise user scott requests a connection to local database hq in order to execute a distributed transaction involving sales. The following steps occur (not necessarily in this exact order):

1. Enterprise user scott is authenticated using SSL or a password.

2. User scott issues the following statement:

   SELECT e.ename, d.loc 
   FROM emp e, dept@sales_link d
   WHERE e.deptno=d.deptno;
   

3. Databases hq and sales mutually authenticate one another using SSL.

4. Database hq queries the enterprise directory service to determine whether enterprise user scott has access to hq, and discovers scott can access local schema guest using role clerk1.

5. Database sales queries the enterprise directory service to determine whether enterprise user scott has access to sales, and discovers scott can access local schema guest using role clerk2.

6. Enterprise user scott logs into sales to schema guest with role clerk2 and issues a SELECT to obtain the required information and transfer it to hq.

7. Database hq receives the requested data from sales and returns it to the client scott.

   See Also:

   Oracle Database Enterprise User Security Administrator's Guide for more information about enterprise user security

Data Encryption

The Oracle Advanced Security option also enables Oracle Net and related products to use network data encryption and checksumming so that data cannot be read or altered. It protects data from unauthorized viewing by using the RSA Data Security RC4 or the Data Encryption Standard (DES) encryption algorithm.

To ensure that data has not been modified, deleted, or replayed during transmission, the security services of the Oracle Advanced Security option can generate a cryptographically secure message digest and include it with each packet sent across the network.

Enterprise Manager

Enterprise Manager is the Oracle Database administration tool that provides a graphical user interface (GUI). Enterprise Manager provides administrative functionality for distributed databases through an easy-to-use interface. You can use Enterprise Manager to:

• Administer multiple databases. You can use Enterprise Manager to administer a single database or to simultaneously administer multiple databases.

• Centralize database administration tasks. You can administer both local and remote databases running on any Oracle Database platform in any location worldwide. In addition, these Oracle Database platforms can be connected by any network protocols supported by Oracle Net.

• Dynamically execute SQL, PL/SQL, and Enterprise Manager commands. You can use Enterprise Manager to enter, edit, and execute statements. Enterprise Manager also maintains a history of statements executed.

  Thus, you can reexecute statements without retyping them, a particularly useful feature if you must execute lengthy statements repeatedly in a distributed database system.

• Manage security features such as global users, global roles, and the enterprise directory service.

Third-Party Administration Tools

Currently more than 60 companies produce more than 150 products that help manage Oracle Databases and networks, providing a truly open environment.

SNMP Support

Besides its network administration capabilities, Oracle Simple Network Management Protocol (SNMP) support allows an Oracle Database server to be located and queried by any SNMP-based network management system. SNMP is the accepted standard underlying many popular network management systems such as:

• HP OpenView

• Digital POLYCENTER Manager on NetView

• IBM NetView/6000

• Novell NetWare Management System

• SunSoft SunNet Manager

Name Resolution When the Global Database Name Is Complete

Assume that you issue the following SQL statement, which specifies a complete global database name:

SELECT * FROM emp@prod1.us.example.com;

In this case, both the database name (prod1) and domain components (us.example.com) are specified, so the database searches for private, public, and global database links. The database searches only for links that match the specified global database name.

Name Resolution When the Global Database Name Is Partial

If any part of the domain is specified, the database assumes that a complete global database name is specified. If a SQL statement specifies a partial global database name (that is, only the database component is specified), the database appends the value in the DB_DOMAIN initialization parameter to the value in the DB_NAME initialization parameter to construct a complete name. For example, assume you issue the following statements:

CONNECT scott@locdb
SELECT * FROM scott.emp@orders;

If the network domain for locdb is us.example.com, then the database appends this domain to orders to construct the complete global database name of orders.us.example.com. The database searches for database links that match only the constructed global name. If a matching link is not found, the database returns an error and the SQL statement cannot execute.

Name Resolution When No Global Database Name Is Specified

If a global object name references an object in the local database and a database link name is not specified using the @ symbol, then the database automatically detects that the object is local and does not search for or use database links to resolve the object reference. For example, assume that you issue the following statements:

CONNECT scott@locdb
SELECT * from scott.emp;

Because the second statement does not specify a global database name using a database link connect string, the database does not search for database links.

Terminating the Search for Name Resolution

The database does not necessarily stop searching for matching database links when it finds the first match. The database must search for matching private, public, and network database links until it determines a complete path to the remote database (both a remote account and service name).

The first match determines the remote schema as illustrated in the following table:

After the database determines a complete path, it creates a remote session, assuming that an identical connection is not already open on behalf of the same local session. If a session already exists, the database reuses it.

Example of Global Object Name Resolution: Complete Object Name

This example illustrates how the database resolves a complete global object name and determines the appropriate path to the remote database using both a private and public database link. For this example, assume the following:

• The remote database is named sales.division3.example.com.

• The local database is named hq.division3.example.com.

• A directory server (and therefore, global database links) is not available.

• A remote table emp is contained in the schema tsmith.

Consider the following statements issued by scott at the local database:

CONNECT scott@hq

CREATE PUBLIC DATABASE LINK sales.division3.example.com 
CONNECT TO guest IDENTIFIED BY network 
  USING 'dbstring'; 

Later, JWARD connects and issues the following statements:

CONNECT jward@hq

CREATE DATABASE LINK sales.division3.example.com 
  CONNECT TO tsmith IDENTIFIED BY radio; 

UPDATE tsmith.emp@sales.division3.example.com 
  SET deptno = 40 
  WHERE deptno = 10; 

The database processes the final statement as follows:

1. The database determines that a complete global object name is referenced in jward's UPDATE statement. Therefore, the system begins searching in the local database for a database link with a matching name.

2. The database finds a matching private database link in the schema jward. Nevertheless, the private database link jward.sales.division3.example.com does not indicate a complete path to the remote sales database, only a remote account. Therefore, the database now searches for a matching public database link.

3. The database finds the public database link in scott's schema. From this public database link, the database takes the service name dbstring.

4. Combined with the remote account taken from the matching private fixed user database link, the database determines a complete path and proceeds to establish a connection to the remote sales database as user tsmith/radio.

5. The remote database can now resolve the object reference to the emp table. The database searches in the tsmith schema and finds the referenced emp table.

6. The remote database completes the execution of the statement and returns the results to the local database.

Example of Global Object Name Resolution: Partial Object Name

This example illustrates how the database resolves a partial global object name and determines the appropriate path to the remote database using both a private and public database link.

For this example, assume that:

• The remote database is named sales.division3.example.com.

• The local database is named hq.division3.example.com.

• A directory server (and therefore, global database links) is not available.

• A table emp on the remote database sales is contained in the schema tsmith, but not in schema scott.

• A public synonym named emp resides at remote database sales and points to tsmith.emp in the remote database sales.

• The public database link in "Example of Global Object Name Resolution: Complete Object Name" is already created on local database hq:

  CREATE PUBLIC DATABASE LINK sales.division3.example.com 
    CONNECT TO guest IDENTIFIED BY network 
    USING 'dbstring'; 
  

Consider the following statements issued at local database hq:

CONNECT scott@hq

CREATE DATABASE LINK sales.division3.example.com;

DELETE FROM emp@sales 
  WHERE empno = 4299; 

The database processes the final DELETE statement as follows:

1. The database notices that a partial global object name is referenced in scott's DELETE statement. It expands it to a complete global object name using the domain of the local database as follows:

   DELETE FROM emp@sales.division3.example.com 
     WHERE empno = 4299; 
   

2. The database searches the local database for a database link with a matching name.

3. The database finds a matching private connected user link in the schema scott, but the private database link indicates no path at all. The database uses the connected username/password as the remote account portion of the path and then searches for and finds a matching public database link:

   CREATE PUBLIC DATABASE LINK sales.division3.example.com 
     CONNECT TO guest IDENTIFIED BY network 
     USING 'dbstring'; 
   

4. The database takes the database net service name dbstring from the public database link. At this point, the database has determined a complete path.

5. The database connects to the remote database as scott/password and searches for and does not find an object named emp in the schema scott.

6. The remote database searches for a public synonym named emp and finds it.

7. The remote database executes the statement and returns the results to the local database.

What Happens When Global Names Change

Global name changes can affect views, synonyms, and procedures that reference remote data using partial global object names. If the global name of the referenced database changes, views and procedures may try to reference a nonexistent or incorrect database. However, synonyms do not expand database link names at run time, so they do not change.

Scenarios for Global Name Changes

For example, consider two databases named sales.uk.example.com and hq.uk.example.com. Also, assume that the sales database contains the following view and synonym:

CREATE VIEW employee_names AS 
        SELECT ename FROM scott.emp@hr; 

CREATE SYNONYM employee FOR scott.emp@hr; 

The database expands the employee synonym definition and stores it as:

scott.emp@hr.uk.example.com

Location Transparency

An Oracle Database 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:

• Access to remote data is simple, because database users do not need to know the physical location of database objects.

• Administrators can move database objects with no impact on end-users or existing database applications.

Typically, administrators and developers use synonyms to establish location transparency for the tables and supporting objects in an application schema. For example, the following statements create synonyms in a database for tables in another, remote database.

CREATE PUBLIC SYNONYM emp
  FOR scott.emp@sales.us.americas.example_auto.com;
CREATE PUBLIC SYNONYM dept
  FOR scott.dept@sales.us.americas.example_auto.com;

Now, rather than access the remote tables with a query such as:

SELECT ename, dname
  FROM scott.emp@sales.us.americas.example_auto.com e,
       scott.dept@sales.us.americas.example_auto.com d
  WHERE e.deptno = d.deptno;

An application can issue a much simpler query that does not have to account for the location of the remote tables.

SELECT ename, dname
  FROM emp e, dept d
  WHERE e.deptno = d.deptno;

In addition to synonyms, developers can also use views and stored procedures to establish location transparency for applications that work in a distributed database system.

SQL and COMMIT Transparency

The Oracle Database distributed database architecture also provides query, update, and transaction transparency. For example, standard SQL statements 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 statements 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.

• The database 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. Specifically, when the network or system is restored, the nodes either all commit or all roll back the transaction.

Internal to the database, each committed transaction has an associated system change number (SCN) to uniquely identify the changes made by the statements within that transaction. In a distributed database, the SCNs of communicating nodes are coordinated when:

• A connection is established using the path described by one or more database links.

• A distributed SQL statement is executed.

• A distributed transaction is committed.

Among other benefits, the coordination of SCNs among the nodes of a distributed database system allows global distributed read-consistency at both the statement and transaction level. If necessary, global distributed time-based recovery can also be completed.

Replication Transparency

The database also provide many features to transparently replicate data among the nodes of the system. For more information about Oracle Database replication features, see Oracle Database Advanced Replication.