Oracle7 Server Distributed Systems Volume I: Distributed Data

Contents Index Home Previous Next

Synonyms

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

Database synonyms are a standard SQL feature that provide alternate names for database objects and, optionally, their locations. A synonym can be created for any table, 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:

[PUBLIC] Specifies that this synonym is available to all users. Omitting this parameter makes a synonym private, and usable only by the creator. Public synonyms can be created only by a user with CREATE PUBLIC SYNONYM system privilege.
synonym_name Specifies the alternate object name to be referenced by users and applications.
schema Specifies the schema of the object specified in object_name. Omitting this parameter uses the creator's schema as the schema of the object.
object_name Specifies either a table, view, sequence, or other name as appropriate.
database_link_name Specifies the database link which identifies the remote username in which the object specified in object_name is located.
A synonym must be a uniquely named object for its schema. If a schema contains a database object and a public synonym exists with the same name, Oracle always finds the database object when the user that owns the schema references that name.

Because a synonym is a reference to the actual object, the security domain of the object is used when the synonym is accessed. For example, a user that has access to a synonym for a specific table must also have privileges on that table to access the data in it. If the user attempts to access the synonym, but does not have privileges on the table it identifies, an error occurs indicating that the table or view does not exist.

A Simple Example

Assume that in every database in a distributed 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 EMP table is hidden by the public synonyms. SQL statements in the application access the table SCOTT.EMP@HQ.ACME.COM 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 More Complex Example

Figure 4 - 2 shows two servers, OHIO and NY_FIN, in which a database link from OHIO to NY_FIN and the synonym FOR_SALE provide an alternate object name for use in OHIO to reference the OPEN table in NY_FIN. The database link and the synonym are created as follows:

CREATE PUBLIC DATABASE LINK NY_FIN
CONNECT TO REAL_ESTATE IDENTIFIED BY NOPASS;
USING 'NY_FIN'
CREATE PUBLIC SYNONYM FOR_SALE
FOR OPEN@NY_FIN;

Figure 4 - 2. Using Synonyms for Alternate Object Names

The table OPEN on NY_FIN could be accessed from OHIO using the SQL statement: 

SELECT * FROM FOR_SALE;

Using this database link, the user is logging on to NY_FIN as user REAL_ESTATE. Notice that this public synonym was created by the DBA on behalf of the REAL_ESTATE username. Without such a prefix, a table that does not exist in the database link user's schema would return an error, because it would be looking for the OPEN table owned by the REAL_ESTATE user.

Relocating a Table

If the OPEN table in the example above were to be moved from one database server to another, only the synonym or the database link would need to be changed to identify the new location. The applications would continue to reference the same object name, although they would be connecting to a new location to access the data in that table. Figure 4 - 3 shows the most common method of redefining the location of a table to retain location transparency. The command would be:

CREATE PUBLIC DATABASE LINK NY_TAX
CONNECT TO REAL_ESTATE IDENTIFIED BY NOPASS;
DROP PUBLIC SYNONYM FOR_SALE;
CREATE PUBLIC SYNONYM FOR_SALE
FOR OPEN@NY_TAX;

Figure 4 - 3. Redefining Table Location to Retain Location Transparency

To relocate the table, a second database link was created called NY_TAX that connected to a new database with the service name NY_TAX, and the synonym was recreated to reference the NY_TAX database link instead of the NY_FIN database link. Other tables that were accessed through the NY_FIN database link to NY_FIN would continue to function properly.

Contents Index Home Previous Next