2
Distributed Database Administration

This chapter discusses issues of concern to the database administrator (DBA) implementing or maintaining distributed databases.

Topics covered include:

• Global Database Names and Global Object Names

• Types of Database Links

• Techniques for Location Transparency

• Statement Transparency

Global Database Names and Global Object Names

In a distributed database system, each database should have a unique global name. Global database names identify each database in the system. A global database name consists of two components: a database name of eight characters or less (for example, SALES) and a domain name that contains the database (see below).

The domain name component of a global database name must follow standard Internet conventions. Levels in domain names must be separated by dots and the order of domain names is from leaf to root, left to right. The database name and the domain name are determined by the initialization parameters DB_NAME and DB_DOMAIN. See the Oracle8i Reference for more information about specifying these initialization parameters.

A database link should be given the same name as the global database name of the remote database it references. When you set the initialization parameter GLOBAL_NAMES to TRUE, Oracle ensures that the name of the database link is the same as the global database name of the remote database. See the Oracle8i Reference for more information about specifying the initialization parameter GLOBAL_NAMES.

Attention: If you set the initialization parameter GLOBAL_NAMES to FALSE, you are not required to use global naming. However, Oracle Corporation highly recommends that you use global naming because many useful features, including Oracle Advanced Replication, require global naming be enforced.

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

CREATE PUBLIC DATABASE LINK sales.division3.acme.com ... ;

Oracle uses the global database name to globally name the schema objects using the following naming scheme:

<schema>.<schema_object>@<global_database_name>

where

<schema>

<schema_object>

<global_database_name>

For example, using the previously defined database link, a user or application can now reference remote data using the global object name:

SELECT * FROM scott.emp@sales.division3.acme.com; 

Types of Database Links

To support application access to the data and schema objects throughout a distributed database system, administrators must create all necessary database links. The following sections compares the various types of database links that Oracle provides.

Private, Public, and Global Database Links

Oracle allows you to create private, public, and global database links.

Determining the type of database links to employ in a distributed database depends on the specific requirements of the applications using the system.

Consider some of the advantages and disadvantages for using each type of database link.

• A private database link is more secure than a public or global link, because only the owner of the private link, or subprograms within the same schema, can use the private link to access the specified remote database.

• When many users require an access path to a remote Oracle database, an administrator can create a single public database link for all users in a database.

• When an Oracle network uses Oracle Names, an administrator can conveniently manage global database links for all databases in the system. Database link management is centralized and simple.

Creating a Private Database Link

To create a private database link, you specify:

CREATE DATABASE LINK ...;

See the Oracle8i SQL Reference and the following sections for more information.

Creating a Public Database Link

To create a public database link, you use the keyword PUBLIC:

CREATE PUBLIC DATABASE LINK ...;

See the Oracle8i SQL Reference and the following sections for more information.

Creating a Global Database Link

You must define global database links in the Oracle Name Server. See your Net8 Administrator's Guide for more information.

Security Options for Database Links

A database link defines a communication path from one database to another. When an application uses a database link to access a remote database, Oracle establishes a database session in the remote database on behalf of the local application request.

When you create a private or public database link, you can determine which schema on the remote database the link will establish connections to by creating fixed user, current user, and connected user database links.

Fixed User Database Links

To create a fixed user database link, you embed the credentials (in this case, a username and password) required to access the remote database in the definition of the link:

CREATE DATABASE LINK ... CONNECT TO username IDENTIFIED BY password ...;

When an application uses a fixed user database link, the local server always establishes a connection to a fixed remote schema in the remote database. The local server also sends the user's credentials across the network when an application uses the link to access the remote database. If an unsecure network supports a distributed database that uses fixed user database links, consider encrypting login packets for server-to-server connections.

Connected User and Current User Database Links

Connected user and current user database links do not include any credentials in the definition of the link. The credentials used to connect to the remote database can change depending on the user that references the database link and the operation being performed by the application. To understand the difference between the two types of database links, you must first understand the concepts of connected and current users.

• A connected user is a user that connects to a database using a database application. For example, when you start SQL*Plus and connect to an Oracle database as SCOTT, the connected user is SCOTT.

• A current user is determined by the security context in which a database operation executes. For example, when you connect to an Oracle database as the user SCOTT and execute the procedure SALES.DEL_EMP, the current user while executing the DEL_EMP procedure defaults to SALES because a stored procedure executes within the security context of its owner.

To understand the difference between connected user and current user database links, simply extend your understanding of the different types of users. With a connected user database link, an operation being performed in a remote database always occurs within the security context of the connected user at the local database.

With a current user database link, an operation performed in a remote database always occurs within the security context of the current user at the local database.

For example, consider what happens when the user SCOTT calls a procedure SALES.DEL_EMP, and the procedure deletes an employee record from a remote database. If the procedure references a connected user database link to access the remote database, the deletion of the remote employee record happens as SCOTT, the connected user in the local database. However, if the procedure references a current user database link to access the remote database, the deletion of the remote employee record happens as SALES, the current user in the local database.

To create a connected user database link, you merely omit the CONNECT TO clause. The following example creates a connected user database link:

CREATE DATABASE LINK sales.division3.acme.com USING 'sales';

To create a current user database link, use the following syntax:

CREATE DATABASE LINK ... CONNECT TO CURRENT_USER ...;

To use a current user database link, the current user must be a global user that is authenticated by the Oracle Security Server.

See the Oracle8i SQL Reference for more syntax information about creating database links.

Shared Database Links

Every application that references a remote server using a standard database link establishes a connection between the local database and the remote database. Many users running applications simultaneously can cause a high number of connections between the local and remote databases.

Shared database links enable you to limit the number of network connections required between the local server and the remote server. To use shared database links, the local server must run in multi-threaded server (MTS) mode. The remote server can either run in multi-threaded server mode, or can run in dedicated server mode.

Attention: Although shared database links can reduce the number of required connections between the local and remote server, you could cause more physical connections and processes to be required than simply using standard (not shared) database links, if you use this functionality incorrectly.

Please be sure you understand the information presented in this section before attempting to implement a system that uses shared database links with shared servers.

Properties of Shared Database Links

Shared database links differ from standard database links in two ways:

• Network connections made for shared database links can be shared among those that use the same database link schema object. When a user needs to establish a connection to a remote server from a particular shared server process, the shared process can reuse connections already established to the remote server (if that connection was established on the same shared server with the same database link).

• When you use a shared database link, a network connection is established directly out of the shared server in the local server. For a normal (non-shared) database link, if the local server was a multithreaded server, this connection would have been established through the local dispatcher, requiring context switches for the local dispatcher, and requiring data to go through the dispatcher.

When to Use Shared Database Links

You should look carefully at your application and your multi-threaded server configuration to determine whether to use shared links or not.

For example, if you have designed your application to use a standard public database link, and 100 users simultaneously require a connection, 100 network direct network connections will also be required.

However, if your application uses shared database links, and there are ten shared servers in the local MTS-mode database, the 100 users that use the same (shared public) database link will require only 10 network connections (or fewer) to the remote server. Each local shared server may only need one connection to the remote server.

When Not to Use Shared Database Links

Shared database links are not useful in all situations. Suppose there is only one user that accesses the remote server. If that user defines a shared database link, and there are ten shared servers in the local database, that one user can require up to 10 network connections to the remote server. Every shared server may have established a connection to the remote server, since each shared server might have been used by that user.

Clearly, a standard database link would be preferable in this situation because it would require (and allow) only one network connection. The lesson: shared database links can lead to more network connections in single-user scenarios, therefore, they should be used only when you expect that many users will need to use the same database link. Typically, this is the case for public database links, but may also be true for private database links if you expect many clients to use the same local schema (and therefore the same private database link).

A rule of thumb is to use shared database links when the number of users accessing a database link is expected to be much larger than the number of shared servers in the local database.

Setting Up Shared Database Links

To create a shared database link you use the keyword SHARED in the SQL CREATE DATABASE LINK command:

CREATE SHARED DATABASE LINK dblink_name
[CONNECT TO username IDENTIFIED BY password]|[CONNECT TO CURRENT_USER]
AUTHENTICATED BY schema_name IDENTIFIED BY password
[USING 'service_name'];

See the Oracle8i SQL Reference for more syntax information.

Whenever the keyword SHARED is used, the clause AUTHENTICATED BY is also required. There must be an account on the remote database with the specified USERID/PASSWORD and with the CREATE SESSION privilege. No other privileges are required.

The schema specified in the AUTHENTICATED BY clause is only used for security reasons and could be considered a "dummy" schema. It is not affected when using shared database links, nor does it affect the users of the shared database link. The AUTHENTICATED BY clause is required to prevent unauthorized clients from masquerading as a database link user and gaining access to unauthorized information.

Shared Database Link Configurations

Shared database links can be used in two configurations.

Shared Database Links to Dedicated Servers

In the first configuration a shared server in the local server owns a dedicated remote server, and a direct network transport connection exists between the shared server and the remote dedicated server. The advantage is that a direct network transport exists between the local shared server and the remote dedicated server. A disadvantage of this configuration is that extra back-end servers are needed. See Figure 2-1.

Figure 2-1 A Shared Database Link to Dedicated Server Processes

.

Shared Database Links to Multi-Threaded Servers

The second configuration uses shared servers on the remote server. This configuration eliminates the need for more dedicated servers, but requires to go through the dispatcher on the remote server. See Figure 2-2. Note that both the local and the remote server must be configured as multi-threaded servers.

Figure 2-2 Shared Database Link to Multi-Threaded Server

Examples

Example 1: A Public Fixed User Database Link

The following statement creates a public fixed user database link:

CREATE PUBLIC DATABASE LINK sales.division3.acme.com
CONNECT TO SCOTT IDENTIFIED BY TIGER
USING 'sales';

Any user connected to the local database can use the SALES.DIVISION3.ACME.COM database link to connect to the remote database. Each user will connect to the same remote schema, SCOTT in the remote database. To access the table EMP table in SCOTT's remote schema, a user could issue the SQL query:

SELECT * FROM emp@sales.division3.acme.com;

Note that each application or user session creates a separate connection to the common account on the server. The connection to the remote database remains open for the duration of the application or user session.

Example 2: A Public Fixed User Shared Database Link

Consider the following example of creating a public fixed user shared database link:

CREATE SHARED PUBLIC DATABASE LINK sales.division3.acme.com
CONNECT TO scott IDENTIFIED BY tiger 
AUTHENTICATED BY scott IDENTIFIED BY tiger
USING `sales';

Any user connected to the local MTS-mode server can use this database link to connect (through a shared server process) to the remote SALES database, and query tables in the SCOTT schema.

In the above example, each local shared server might establish one connection to the remote server. Whenever a local shared servers process needs to access the remote server through the SALES.DIVISION3.ACME.COM database link, the local shared server process will reuse established network connections.

Example 3: A Public Connected User Database Link

The following statement would create a public connected user database link:

CREATE PUBLIC DATABASE LINK sales.division3.acme.com
USING 'sales';

Any user connected to the local database can use the SALES.DIVISION3.ACME.COM database link. The connected user in the local database who uses the database link determines the remote schema. If SCOTT is the connected user and uses the database link, the database link connects to the remote schema SCOTT. If FORD is the connected user and uses the database link, the database link connects to FORD's remote schema.

The following statement will fail for the user FORD in the local database if the remote schema FORD cannot resolve the EMP schema object. That is, if the FORD schema in the SALES.DIVISION3.ACME.COM does not have EMP as a table, view, or (public) synonym, an error will be returned.

SELECT * FROM emp@sales.division3.acme.com; 

Example 4: A Public Connected User Shared Database Link

The following statement creates a public connected user shared database link:

CREATE SHARED PUBLIC DATABASE LINK sales.division3.acme.com AUTHENTICATED
BY ward IDENTIFIED BY orange
USING `sales';

Each user connected to the local server can use this shared database link to connect to the remote database, and query the tables in the corresponding remote schema.

In the above example, each local shared server will establish one connection to the remote server. Whenever a local shared server process needs to access the remote server through the SALES.DIVISION3.ACME.COM database link, the local shared server process will reuse established network connections, even if the connected user is a different user.

If this database link is used frequently, eventually every shared server in the local database will have a remote connection. At that point no more physical connections will be needed to the remote server, even if new users use this shared database link.

Example 5: A Public Current User Database Link

The following statement creates a public current user database link:

CREATE PUBLIC DATABASE LINK sales.division3.acme.com
CONNECT TO CURRENT_USER
USING `sales';

SCOTT creates a local procedure FIRE_EMP, that deletes a row from the remote EMP table, and grants execute privilege to FORD.

CREATE PROCEDURE fire_emp (enum NUMBER) AS
BEGIN
   DELETE FROM emp@sales.division3.acme.com
   WHERE empno=enum;
END;

GRANT EXECUTE ON FIRE_EMP TO FORD;

When FORD executes the procedure SCOTT.FIRE_EMP, the procedure runs under SCOTT's privileges. Since a current user database link is used, the connection is established to SCOTT's remote schema. (Note that, were a connected user database link used instead, the connection would be established to FORD's remote schema.) Note that SCOTT must be a global user and FORD may or may not be a global user.

Note that the same could have been accomplished by using a fixed user database link that connects to SCOTT's remote schema. However, with fixed user database links, security can be compromised, because SCOTT's username and password are available in readable format in the database to DBAs.

Connection Qualifiers

In some situations, you may want to have several database links of the same type (e.g., public) that point to the same remote database, yet establish connections to the remote database using different communication pathways. For example, if a remote database is using the Oracle Parallel Server, you might want to define several public database links at your local node so that connections can be established to specific instances of the remote database.

To facilitate such functionality, Oracle allows you to create a database link with an optional connection qualifier in the database link name. When creating a database link, a connection qualifier is specified as the trailing portion of the database link name, separated by an at sign ("@"). For example, assume that a remote database HQ.ACME.COM is managed by the Oracle Parallel Server. The HQ database has two instances, named HQ_1 and HQ_2. The local database can contain the following public database links to define pathways to the remote instances of the HQ database:

CREATE PUBLIC DATABASE LINK hq.acme.com@hq_1 
        USING 'string_to_hq_1'; 

CREATE PUBLIC DATABASE LINK hq.acme.com@hq_2 
        USING 'string_to_hq_2'; 

CREATE PUBLIC DATABASE LINK hq.acme.com 
       USING 'string_to_hq'; 

Notice in the above examples that a connection qualifier is simply an extension to a database link name. The text of the connection qualifier does not necessarily indicate how a connection is to be established; this information is specified in the service name of the USING clause. Also notice that in the third example, a connection qualifier is not specified. In this case, just as when a connection qualifier is specified, the instance is determined by the USING string.

To use a connection qualifier to specify a particular instance, include the qualifier at the end of the global object name:

SELECT * FROM scott.emp@hq.acme.com@hq_1

Database Link Resolution

Whenever a SQL statement includes a reference to a global object name, Oracle searches for a database link with a name that matches the database name specified in the global object name. Oracle does this to determine the path to the specified remote database.

Oracle always searches for matching database links in the following order:

1. Private database links in the schema of the user who issued the SQL statement.

2. Public database links in the local database.

3. Global database links (only if an Oracle Name Server is available).

If a SQL statement specifies a complete global database name; that is, both the database and domain components are specified, Oracle searches for private, public, and global database links that match only the explicitly specified global database name. If any portion of the domain is specified, Oracle assumes that a complete global database name is specified. However, if a SQL statement specifies a partial global database name; that is, only the database component is specified, Oracle appends the local database's network domain component to the database name to form a complete global database name. Then Oracle searches for private, public, and network database links that match only the constructed global database name. If a matching database link is not found, Oracle returns an error and the SQL statement cannot execute.

Optimization: If a global object name references an object in the local database and a connection qualifier is not specified, Oracle automatically detects that the object is local and does not search for, or use, database links to resolve the object reference.

Oracle expands a global object reference, whether or not a connection qualifier is specified. Furthermore, if a connection qualifier is specified, only database links that match, including the connection qualifier, are used to resolve the object reference.

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

The first match determines the remote schema; that is, if no CONNECT clause is specified a connected user database link will be used, if the "CONNECT TO username IDENTIFIED BY password" clause is specified a fixed user database link will be used, and if a "CONNECT TO CURRENT_USER" clause is specified a current user database link.

If the first match does not specify a USING clause, the search continues until a link that specifies a database string is found. If matching database links are found and a database string is never identified, Oracle returns an error.

Once a complete path is determined, Oracle creates a remote session, assuming an identical connection is not already open on behalf of the same local session.

Schema Object Name Resolution

Once the local Oracle database connects to the specified remote database on behalf of the local user that issued the SQL statement, object resolution continues as if the remote user had issued the associated SQL statement. That is, if a fixed user database link is used, object resolution proceeds in the specified schema; if a connected user database link is used, object resolution proceeds in the connected user's remote schema (including synonyms) and if a current user database link is used object resolution proceeds in the current user's remote schema. If the object is not found, public objects of the remote database are then checked.

If an object is not resolved, the established remote session remains but the SQL statement cannot execute.

Examples of Name Resolution

The following are examples of global object name resolution in a distributed database system.

For all the following examples, assume that the remote database is named SALES.DIVISION3.ACME.COM, the local database is named HQ.DIVISION3.ACME.COM, and an Oracle Name Server (and therefore, global database links) is not available.

Example 1

This example illustrates how a complete global object name is resolved and the appropriate path to the remote database is determined using both a private and public database link.

For this example, assume that a remote table EMP is contained in the schema TSMITH.

Consider the following statements issued at the local database:

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

CONNECT jward/bronco; 

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

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

Oracle notices that a complete global object name is referenced in JWARD'S UPDATE statement. Therefore, it begins searching in the local database for a database link with a matching name. Oracle finds matching private database link in the schema JWARD. However, the private database link JWARD.SALES.DIVISION3.ACME.COM does not indicate a complete path to the remote SALES database, only a remote account. Therefore, Oracle now searches for and finds a matching public database link. From this public database link, Oracle takes the service name. Combined with the remote account taken from the matching private fixed user database link, a complete path is determined and Oracle proceeds to establish a connection to the remote SALES database as the user TSMITH/RADIO.

The remote database can now resolve the object reference to the EMP table. Oracle searches in the specified schema, TSMITH, and finds the referenced EMP table. No further resolution is necessary; the remote database completes the execution of the statement, and returns the results to the local database.

Example 2

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

For this example, assume that a remote table EMP is contained in the schema TSMITH and a remote public synonym named EMP points to the previously mentioned EMP table. Also assume the creation of the public database link in Example 1.

Consider the following statements issued at the local database:

CONNECT scott/tiger; 

CREATE DATABASE LINK sales.division3.acme.com; 

DELETE FROM emp@sales 
        WHERE empno = 4299; 

Oracle notices that a partial global object name is referenced in SCOTT's DELETE statement. First, the global object name is expanded to a complete global object name using the domain name of the local database, resulting in the following statement:

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

Now, Oracle begins searching in the local database for a database link with a matching name. Oracle finds a matching private connected user database link in the schema SCOTT. However, the private database link indicates no path at all. Oracle uses the connected username/password as the remote account portion of the path and then searches for and finds a matching public database link. Oracle takes the database string from the public database link. At this point, a complete path is determined and Oracle can connect to the remote database as SCOTT/TIGER.

Once connected to the remote database as SCOTT, the remote Oracle resolves the reference to EMP. First, it searches for and does not find an object named EMP in the schema SCOTT.

Next, the remote database searches for a public synonym named EMP and finds one. The remote database then completes statement execution and returns the results to the local database.

Views, Synonyms, Procedures and Global Name Resolution

A remote schema object can be referenced by its global object name in the definition of a view, synonym, or PL/SQL program unit (e.g., procedure, trigger). If a complete global object name is referenced in the definition of a view, synonym, or program unit, Oracle stores the definition of the object as specified, without having to perform any expansion of the global object name being referenced. However, if a partial global object name (that is, only the database name and not the domain name) is referenced in the definition of a view, synonym, or program unit, Oracle must expand the partial name using the domain component of the local database's global database name.

The following list explains when Oracle completes the expansion of a partial global object name for views, synonyms, and program units:

• When a view is created, partial global object names in the defining query are not expanded; the data dictionary stores the exact text of the defining query. Instead, Oracle expands a partial global object name each time a statement that uses the view is parsed.

• When a synonym is created, partial global object names are expanded; the definition of the synonym stored in the data dictionary includes the expanded global object name.

• Each time a program unit is compiled, partial global object names are expanded.

The above behavior should be considered when creating views, synonyms, and procedures that reference remote data using partial global object names. If the global database name of the containing database is changed (which should rarely happen), views and procedures may try to reference a different database than they did before the global database name change; alternatively, synonyms do not expand database link names at runtime, so they do not change. Depending on the situation, this behavior may or may not be desired. For example, consider two databases named SALES.UK.ACME.COM and HQ.UK.ACME.COM. Also, assume that the SALES database contains the following view and synonym:

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

CREATE SYNONYM employee FOR scott.emp@hq; 

Oracle expands the EMPLOYEE synonym definition and stores it as:

"scott.emp@hq.uk.acme.com" 

The company undergoes a reorganization. First, consider the situation where both the Sales and Human Resources departments are moved to the United States. Consequently, the corresponding global database names are both changed to SALES.US.ACME.COM and HQ.US.ACME.COM. In this case, the defining query of the EMPLOYEE_NAMES view still expands to the correct database when the view is used:

"SELECT ename FROM scott.emp@hq.us.acme.com" 

However, the definition of the EMPLOYEE synonym continues to reference the previous database name, HQ.UK.ACME.COM.

Now consider that only the Sales department is moved to the United States. Consequently, the corresponding new global database name is SALES.US.ACME.COM, while the Human Resources database is HQ.UK.ACME.COM. In this case, the defining query of the EMPLOYEE_NAMES view expands to a non-existent global database name when the view is used:

"SELECT ename FROM scott.emp@hq.us.acme.com" 

Alternatively, the EMPLOYEE synonym continues to reference the correct database, HQ.UK.ACME.COM.

In summary, you should decide when you want to use partial and complete global object names in the definition of views, synonyms, and procedures. Keep in mind that database names should be stable and databases should not be unnecessarily moved within a network.

Dropping a Database Link

You can drop a database link just as you can drop a table or view. The command syntax is:

DROP DATABASE LINK dblink; 

For example, to drop the database link NY_FIN, the command would be:

DROP DATABASE LINK ny_fin; 

Listing Available Database Links

The data dictionary of each database stores the definitions of all the database links in that database. The USER/ALL/DBA_DB_LINKS data dictionary views show the database links that have been defined at the local database.

Any user can query the data dictionary to determine what database links are available to that user. For information on viewing the data dictionary, see Oracle8i Concepts or the Oracle8i SQL Reference.

Limiting the Number of Active Database Links

You can limit the number of connections from a user process to remote databases with the initialization parameter OPEN_LINKS. This parameter controls the number of remote connections that a single user session can use concurrently within a single SQL statement per session. See the Oracle8i SQL Reference for more information.

Techniques for Location Transparency

Users of a distributed database system need not (and often should not) be aware of the location and functioning of the parts of the database with which they work. The DBA and network administrators can ensure that the distributed nature of the database remains transparent to users, as shown in the following sections.

Views and Location Transparency

Local views can provide location transparency for local and remote tables in a distributed database system.

For example, assume that table EMP is stored in a local database. Another table, DEPT, is stored in a remote database.

To make the location of, and relationship between, these tables transparent to users of the system, a view named COMPANY can be created in the local database that joins the data of the local and remote servers:

CREATE VIEW company AS
SELECT empno, ename, dname 
FROM emp a, dept@hq.acme.com b 
HERE a.deptno = b.deptno; 

Figure 2-3 Views and Location Transparency

When users access this view, they do not know, or need to know, where the data is physically stored, or if data from more than one table is being accessed. Thus, it is easier for them to get required information. For example:

SELECT * FROM company; 

provides data from both the local and remote database table.

Figure 2-3 illustrates this example of location transparency.

Views and Privileges

Assume a local view references a remote table or view. The owner of the local view can grant only those object privileges on his view that have been granted by the remote user. (The remote user is implied by the type of database link). This is similar to privilege management for views that reference local data.

Synonyms and Location Transparency

Synonyms are very useful in both distributed and non-distributed environments because they hide the identity of the underlying object, including its location in a distributed database system. If the underlying object must be renamed or be moved, only the synonym needs to be redefined; applications based on the synonym continue to function without modification. Synonyms can also simplify SQL statements for users in a distributed database system.

A synonym can be created for any table, type, view, snapshot, sequence, procedure, function, or package. All synonyms are stored in the data dictionary of the database in which they are created. To simplify remote table access through database links, a synonym can allow single-word access to remote data, isolating the specific object name and the location from users of the synonym. The syntax to create a synonym is:

CREATE [PUBLIC] synonym_name 
FOR [schema.]object_name[@database_link_name] 

where:

Assume that in every database in a distributed database system, a public synonym is defined for the SCOTT.EMP table stored in the HQ database:

CREATE PUBLIC SYNONYM emp FOR scott.emp@hq.acme.com; 

An employee management application can be designed without regard to where the application is used, because the location of the table SCOTT.EMP@HQ.ACME.COM is hidden by the public synonyms.

SQL statements in the application access the table by referencing the public synonym EMP.

Furthermore, if the EMP table is moved from the HQ database to the HR database, only the public synonyms need to be changed on the nodes of the system. The employee management application continues to function properly on all nodes.

A synonym must be a uniquely named object for its schema. If a schema contains a schema object and a public synonym exists with the same name, Oracle always finds the schema object when the user that owns the schema references that name.

Synonyms and Privileges

A synonym is a reference to the actual object. A user who has access to a synonym for a particular schema object, must also have privileges on schema object itself. For example, if the user attempts to access a synonym but does not have privileges on the table it identifies, an error occurs indicating that the table or view does not exist.

Assume a local synonym is an alias for a remote object. The owner of the local synonym cannot grant any object privileges on the synonym to any other local user. This behavior is different from privilege management for synonyms that are aliases for local tables or views. In the case where a synonym is an alias for a remote object, local privileges for the synonym cannot be granted, because this would amount to granting privileges for the remote object, which is not allowed. Therefore, no local privilege management can be performed when synonyms are used for location transparency; security for the base object is controlled entirely at the remote node. For example, the user ADMIN cannot grant any object privileges for the EMP_SYN synonym.

Unlike a database link referenced in a view or procedure definition, a database link referenced in a synonym is resolved by first looking for a private link owned by the schema in effect at the time the reference to the synonym is parsed.

Therefore, to ensure the desired object resolution, it is especially important to specify the underlying object's schema in the definition of a synonym.

Procedures and Location Transparency

PL/SQL program units called procedures can also provide location transparency. There are two options:

• A local procedure references remote data

• A local synonym references a remote procedure

The second option provides location transparency through synonyms. This is discussed in "Synonyms and Location Transparency". The first option is discussed in the next section.

Local procedure referencing remote data

Procedures or functions (either stand-alone or in packages) can contain SQL statements that reference remote data. For example, consider the procedure created by the following statement:

CREATE PROCEDURE fire_emp (enum NUMBER) AS 
BEGIN 
DELETE FROM emp@hq.acme.com 
WHERE empno = enum; 
END; 

When a user or application calls the FIRE_EMP procedure, it is not apparent that a remote table is being modified.

A second layer of location transparency is possible if the statements in a procedure indirectly reference remote data using local procedures, views, or synonyms. For example, the following statement defines a local synonym:

CREATE SYNONYM emp FOR emp@hq.acme.com; 

Given this synonym, the FIRE_EMP procedure can be defined with the following statement:

CREATE PROCEDURE fire_emp (enum NUMBER) AS 
BEGIN 
DELETE FROM emp WHERE empno = enum; 
END; 

If the table EMP@HQ is renamed or moved, only the local synonym that references the table needs to be modified. None of the procedures and applications that call the procedure require modification.

Procedures and Privileges

Assume a local procedure includes a statement that references a remote table or view. The owner of the local procedure can grant the EXECUTE privilege to any user, thereby giving that user the ability to execute the procedure and, indirectly, access remote data.

In general, procedures aid in security. Privileges for objects referenced within a procedure do not need to be explicitly granted to the calling users.

Statement Transparency

Oracle allows the following standard DML statements to reference remote tables:

• SELECT (queries)

• INSERT

• UPDATE

• DELETE

• SELECT... FOR UPDATE

• LOCK TABLE

Queries including joins, aggregates, subqueries, and SELECT ... FOR UPDATE can reference any number of local and remote tables and views. For example, the following query joins information from two remote tables:

SELECT empno, ename, dname FROM scott.emp@sales.division3.acme.com e,
jward.dept@hq.acme.com d 
  WHERE d.deptno = d.deptno; 

UPDATE, INSERT, DELETE, and LOCK TABLE statements can reference both local and remote tables. No programming is necessary to update remote data. For example, the following statement inserts new rows into the remote table EMP in the SCOTT.SALES schema by selecting rows from the EMP table in the JWARD schema in the local database:

INSERT INTO scott.emp@sales.division3.acme.com 
  SELECT * FROM jward.emp; 

Restrictions

Several restrictions apply to statement transparency:

• Within a single SQL statement, all referenced LONG and LONG RAW columns, sequences, updated tables, and locked tables must be located at the same node.

• Oracle does not allow remote data definition language (DDL) statements (for example, CREATE, ALTER, and DROP).

The LIST CHAINED ROWS clause of an ANALYZE statement cannot reference remote tables.

Values for Environmentally-Dependent SQL Functions

In a distributed database system, Oracle always evaluates environmentally-dependent SQL functions, such as SYSDATE, USER, UID, and USERENV with respect to the local server, no matter where the statement (or portion of a statement) executes.

Shared SQL for Remote and Distributed Statements

The mechanics of a remote or distributed statement using shared SQL are essentially the same as those of a local statement. The SQL text must match, the referenced objects must match, and the bind types of any bind variables must be the same. If available, shared SQL areas can be used for the local and remote handling of any statement (or decomposed query).