Distributed Database Administration - Oracle 10g

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

• Site Autonomy
• Distributed Database Security
• Auditing Database Links
• Administration Tools

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 nondistributed 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
• Authentication Without Passwords
• Supporting User Accounts and Roles
• Centralized User and Privilege Management
• Data Encryption

Authentication Through Database Links

Database links are either private or public, authenticated or nonauthenticated. 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:

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.

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:

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.

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

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 table

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

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. sales clerk2 guest jane scott

Data Encryption

The Oracle Advanced Security option also enables Oracle Net and related products to use network data encryption and check summing 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.

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 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
• Third-Party Administration Tools
• SNMP Support

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