|Oracle® Database Administrator's Guide
11g Release 2 (11.2)
Part Number E17120-05
Chapter 32, "Managing a Distributed Database" to learn how to administer homogenous systems
Oracle Database Heterogeneous Connectivity User's Guide to learn about heterogeneous services concepts
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
Password authentication for users and roles
Some types of external authentication for users and roles including Kerberos version 5 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:
See Also:Oracle Database Advanced Security Administrator's Guide for more information about external authentication
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:
|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.|
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.
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
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.
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.
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.
For example, suppose
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
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
Figure 31-5 illustrates an example of global user security using the enterprise directory service:
Figure 31-5 Global User Security
Assume that the enterprise directory service contains the following information on enterprise users for
Also, assume that the local administrators for
sales have issued statements as follows:
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';
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):
scott is authenticated using SSL or a password.
scott issues the following statement:
SELECT e.ename, d.loc FROM emp e, dept@sales_link d WHERE e.deptno=d.deptno;
sales mutually authenticate one another using SSL.
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
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
scott logs into
sales to schema
guest with role
clerk2 and issues a
SELECT to obtain the required information and transfer it to
hq receives the requested data from
sales and returns it to the client
See Also:Oracle Database Enterprise User Security Administrator's Guide for more information about enterprise user security
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
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 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.
Note: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.
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.
Currently more than 60 companies produce more than 150 products that help manage Oracle Databases and networks, providing a truly open environment.
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: