4.8. Synonyms

A synonym is an identifier that can be used to reference another database object in a SQL statement. The types of database objects for which a synonym may be created are a table, view, sequence, or another synonym.

There are two types of synonyms - public synonyms and private synonyms. A public synonym is a synonym that is globally available in a database and can be referenced by any user in the database cluster. A public synonym does not belong to any schema. A private synonym is one that does belong to a specific schema. EnterpriseDB currently supports only public synonyms.

4.8.1. Creating a Public Synonym

To create a public synonym, use the command CREATE PUBLIC SYNONYM. Give the synonym a name of your choice as long as it follows the rules of creating identifiers and the name is not already used for an existing public synonym. For example:

  CREATE PUBLIC SYNONYM personnel FOR enterprisedb.emp;

Now, the emp table in the enterprisedb schema can be referenced in any SQL statement, both DDL and DML, by using the synonym, personnel:

INSERT INTO personnel VALUES (8142,'ANDERSON','CLERK',7902,'17-DEC-06',1300,NULL,20);

SELECT * FROM personnel;

Note: Public synonyms can be created for non-existent objects

4.8.2. Deleting a Public Synonym

To delete a public synonym, use the command DROP PUBLIC SYNONYM. In the following example, the synonym, personnel, created in the previous example is dropped.

  DROP PUBLIC SYNONYM personnel;

4.8.3. Public Synonym Namespace

The name given to a public synonym can be any valid identifier as long as there is no other public synonym in the same database with the same name. This means, that a public synonym can have the same name as an existing schema, table, view, or any other database object.

Thus, it is important to choose public synonym names carefully as unexpected results may occur if the same name is used by other objects in the search path as explained in the next section.

4.8.4. Public Synonym Name Resolution and the Search Path

Name resolution is the process of determining exactly which particular object is to be acted upon in a SQL command. If an object is fully-qualified by its schema name, there is no ambiguity. The desired object is the one belonging to the named schema. However, if an object is not qualified by its schema name, then there is a series of steps to determine where the desired object resides.

As discussed in Section 4.7, the search path is used to determine where an unqualified object resides. If an unqualified name appears in a SQL command, and only if that name does not appear in any schema to which the user has access in the current search path, the public synonyms in the database are examined to see if this name is a public synonym. If so, then the name resolves to the object underlying the public synonym.

As a consequence, if there is a public synonym defined which is intended for use in a SQL command, but the current search path happens to contain another identically named object in a schema accessible by the user, the name will resolve to the object in the search path and not to the public synonym.

4.8.5. Public Synonyms and Privileges

Any user can create a public synonym. There are no special privileges for public synonym creation. Any user can reference a public synonym in a SQL command. However, when the SQL command is executed, the privileges of the current user are checked against the synonym's underlying database object and if the user does not have the proper permissions for that object, the SQL command will fail.