Creating Location Transparency

Creating Synonyms

You can create synonyms for the following:

• Tables

• Types

• Views

• Materialized views

• Sequences

• Procedures

• Functions

• Packages

All synonyms are schema objects that 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, hiding 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 is a keyword specifying 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 schema of the creator as the schema of the object.

• object_name specifies either a table, view, sequence, materialized view, type, procedure, function or package as appropriate.

• database_link_name specifies the database link identifying the remote database and schema 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 schema object and a public synonym exists with the same name, then the database always finds the schema object when the user that owns the schema references that name.

Example: Creating a Public Synonym

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.example.com; 

You can design an employee management application without regard to where the application is used because the location of the table scott.emp@hq.example.com is hidden by the public synonyms. SQL statements in the application access the table by referencing the public synonym emp.

Furthermore, if you move the emp table from the hq database to the hr database, then you only need to change the public synonyms on the nodes of the system. The employee management application continues to function properly on all nodes.

Managing Privileges and Synonyms

A synonym is a reference to an actual object. A user who has access to a synonym for a particular schema object must also have privileges on the underlying 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 scott creates local synonym emp as an alias for remote object scott.emp@sales.example.com. scott cannot grant object privileges on the synonym to another local user. scott cannot grant local privileges for the synonym because this operation amounts to granting privileges for the remote emp table on the sales database, which is not allowed. This behavior is different from privilege management for synonyms that are aliases for local tables or views.

Therefore, you cannot manage local privileges when synonyms are used for location transparency. Security for the base object is controlled entirely at the remote node. For example, user admin cannot grant 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 schema of the underlying object in the definition of a synonym.

Using Local Procedures to Reference Remote Data

Procedures or functions (either standalone 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.example.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 when 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.example.com; 

Given this synonym, you can create the fire_emp procedure using the following statement:

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

If you rename or move the table emp@hq, then you only need to modify the local synonym that references the table. None of the procedures and applications that call the procedure require modification.

Using Local Procedures to Call Remote Procedures

You can use a local procedure to call a remote procedure. The remote procedure can then execute the required DML. For example, assume that scott connects to local_db and creates the following procedure:

CONNECT scott@local_db

CREATE PROCEDURE fire_emp (enum NUMBER) 
AS 
BEGIN 
  EXECUTE term_emp@hq.example.com;
END; 

Now, assume that scott connects to the remote database and creates the remote procedure:

CONNECT scott@hq.example.com

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

When a user or application connected to local_db calls the fire_emp procedure, this procedure in turn calls the remote term_emp procedure on hq.example.com.

Using Local Synonyms to Reference Remote Procedures

For example, scott connects to the local sales.example.com database and creates the following procedure:

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

User peggy then connects to the supply.example.com database and creates the following synonym for the procedure that scott created on the remote sales database:

SQL> CONNECT peggy@supply
SQL> CREATE PUBLIC SYNONYM emp FOR scott.fire_emp@sales.example.com; 

A local user on supply can use this synonym to execute the procedure on sales.

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