Distributed Database Administration

The following sections explain some of the topics relating to database management in an Oracle Database distributed database system:

Site Autonomy

Site autonomy means that each server participating in a distributed database is administered independently from all other databases. Although several databases can work together, each database is a separate repository of data that is managed individually. Some of the benefits of site autonomy in an Oracle Database distributed database include:

  • Nodes of the system can mirror the logical organization of companies or groups that need to maintain independence.

  • Local administrators control corresponding local data. Therefore, each database administrator's domain of responsibility is smaller and more manageable.

  • Independent failures are less likely to disrupt other nodes of the distributed database. No single database failure need halt all distributed operations or be a performance bottleneck.

  • Administrators can recover from isolated system failures independently from other nodes in the system.

  • A data dictionary exists for each local database. A global catalog is not necessary to access local data.

  • Nodes can upgrade software independently.

Although Oracle Database permits you to manage each database in a distributed database system independently, you should not ignore the global requirements of the system. For example, you may need to:

  • Create additional user accounts in each database to support the links that you create to facilitate server-to-server connections.

  • Set additional initialization parameters such as COMMIT_POINT_STRENGTH, and OPEN_LINKS.

Distributed Database Security

The database supports all of the security features that are available with a non-distributed database environment for distributed database systems, including:

  • Password authentication for users and roles

  • Some types of external authentication for users and roles including:

    • Kerberos version 5 for connected user links

    • DCE for connected user links

  • Login packet encryption for client-to-server and server-to-server connections

The following sections explain some additional topics to consider when configuring an Oracle Database distributed database system:

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:


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:


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

Link Type Authenticated Security Access
Private No When connecting to the remote database, the database uses security information (userid/password) taken from the local session. Hence, the link is a connected user database link. Passwords must be synchronized between the two databases.
Private Yes The userid/password is taken from the link definition rather than from the local session context. Hence, the link is a fixed user database link.

This configuration allows passwords to be different on the two databases, but the local database link password must match the remote database password.

Public No Works the same as a private nonauthenticated link, except that all users can reference this pointer to the remote database.
Public Yes All users on the local database can access the remote database and all use the same userid/password to make the connection.

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

See Also:

"Creating Database Links" for more information about the user accounts that must be available to support different types of database links in the system

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.

Schema-Dependent Global Users

One option for centralizing user and privilege management is to create the following:

  • A global user in a centralized directory

  • A user in every database that the global user must connect to

For example, you can create a global user called fred with the following SQL statement:

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

This solution allows a single global user to be authenticated by a centralized directory.

The schema-dependent global user solution has the consequence that you must create a user called fred on every database that this user must access. Because most users need permission to access an application schema but do not need their own schemas, the creation of a separate account in each database for every global user creates significant overhead. Because of this problem, the database also supports schema-independent users, which are global users that an access a single, generic schema in every database.

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 needs to connect. Instead, multiple enterprise users can connect to the same database schema, called a shared schema.


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 29-5 illustrates an example of global user security using the enterprise directory service:

Figure 29-5 Global User Security

Description of Figure 29-5 follows
Description of "Figure 29-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
tb clerk1 guest bill


sales clerk2 guest jane


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

Database CREATE Statements
CREATE ROLE clerk1 GRANT select ON emp;
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.

See Also:

Oracle Database Advanced Security Administrator's Guide for more information about these and other features of the Oracle Advanced Security option

Auditing Database Links

You must always perform auditing operations locally. That is, if a user acts in a local database and accesses a remote database through a database link, the local actions are audited in the local database, and the remote actions are audited in the remote database, provided appropriate audit options are set in the respective databases.

The remote database cannot determine whether a successful connect request and subsequent SQL statements come from another server or from a locally connected client. For example, assume the following:

  • Fixed user link hq.acme.com connects local user jane to the remote hq database as remote user scott.

  • User scott is audited on the remote database.

Actions performed during the remote database session are audited as if scott were connected locally to hq and performing the same actions there. You must set audit options in the remote database to capture the actions of the username--in this case, scott on the hq database--embedded in the link if the desired effect is to audit what jane is doing in the remote database.


You can audit the global username for global users.

You cannot set local auditing options on remote objects. Therefore, you cannot audit use of a database link, although access to remote objects can be audited on the remote database.

Administration Tools

The database administrator has several choices for tools to use when managing an Oracle Database distributed database system:

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 need to 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