A Synonym is an alias or alternate name for a table

Table (database)

In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...

, view

View (database)

In database theory, a view consists of a stored query accessible as a virtual table in a relational database or a set of documents in a document-oriented database composed of the result set of a query or map and reduce functions...

, Sequence

Sequence

In mathematics, a sequence is an ordered list of objects . Like a set, it contains members , and the number of terms is called the length of the sequence. Unlike a set, order matters, and exactly the same elements can appear multiple times at different positions in the sequence...

, or other Schema object

Schema object

In database lore, a schema object is a logical data storage structure. This possibly originates from the use of the term in the context of Oracle databases. The term "schema" can have other meanings when talking about non-Oracle databases. This article discusses the Oracle use of the term...

. They are used mainly to make it easy for users to access database objects owned by other users. They hide the underlying object's identity and make it harder for a malicious program or user to target the underlying object. Because a synonym is just an alternate name for an object, it requires no storage other than its definition. When an application uses a synonym, the DBMS forwards the request to the synonyms underlying base object. By coding your programs to use synonyms instead of table names, you insulate yourself from any changes in the name, ownership, or table locations. If you frequently refer to a table that has a long name, you might appreciate being able to refer to it with a shorter name without having to rename the table and alter the code referring to that table.

Synonyms are very powerful from the point of view of allowing users access to objects that do not lie within their schema

Database schema

A database schema of a database system is its structure described in a formal language supported by the database management system and refers to the organization of data to create a blueprint of how a database will be constructed...

. All synonyms have to be created explicitly with the CREATE SYNONYM command and the underlying objects can be located in the same database

Database

A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...

or in other databases that are connected by database links.

There are two major uses of synonyms:

• Object transparency: Synonyms can be created to keep the original object transparent to the user.
• Location transparency: Synonyms can be created as aliases for the tables and other objects that belong other than the local database.

When you create a table or a procedure, it is created in your schema, and other users can access it only by using your schema name as a prefix to the object's name. The way around for this is for the schema owner creates a synonym with the same name as the table name.

Public Synonyms

Public synonyms are owned by special schema in the oracle database called PUBLIC. As mentioned earlier, public synonyms can be referenced by all users in the database. Public synonyms are usually created by the application owner for the tables and other objects such as procedures and packages so the users of the application can see the objects.

The following code shows how to create a public synonym for the employee table:

CREATE PUBLIC SYNONYM employees for hr.employees;

Now any user can see the table by just typing the original table name. If you wish, you could provide a different table name for that table in the CREATE SYNONYM statement. Remember that the DBA must explicitly grant the CREATE PUBLIC SYNONYM privilege to the user HR before HR can create any public synonyms. Just because you can see a table through public (or private) synonym doesn’t mean that you can also perform SELECT, INSERT, UPDATE or DELETE operations on the table. To be able to perform those operations, a user needs specific privileges for the underlying object, either directly or through roles from the application owner.

Private Synonyms

A private synonym is a synonym within a database schema that a developer typically uses to mark the true name of a table, view stored procedure, or other database object in an application schema.

Private synonyms, unlike public synonyms, can be referenced only by the schema that owns the table or object. You may want to create private synonyms when you want to refer to the same table by different contexts. Private synonym overrides public synonym definitions. You create private synonyms the same way you create public synonyms, but you omit the PUBLIC keyword in the CREATE statement.

The following example shows how to create a private synonym called addresses for the locations table. Note that once you create the private synonym, you can refer to the synonym exactly as you would the original table name.

CREATE SYNONYM addresses FOR hr.locations;

Drop a Synonym

Synonyms, both private and public, are dropped in the same manner by using the DROP SYNONYM command, but there is one important difference. If you are dropping a public synonym; you need to add the keyword PUBLIC after the keyword DROP.

DROP SYNONYM addresses;

The ALL_SYNONYMS (or DBA_SYNONYMS) view provides information on all synonyms in your database.