Synonyms provide a level of security by hiding the name and owner of a schema object such as a table or a view. On top of that, they provide location transparency for remote objects of a distributed database.
Synonyms create a level of abstraction of the underlying schema objects so that you can rename and move of the underlying objects without affecting the applications based on the synonyms. Note that synonyms themselves are not secured. When you grant object privileges on a synonym, you are granting privileges on the underlying object, and the synonym only acts as an alias in the
Synonyms can be public or private. A public synonym is accessible to every user in a database and owned by a specified group named
PUBLIC while a private synonym is stored a specific schema owned by a specific user and available only to that user.
- Create synonym – show you how to create a new synonym for a table.
- Drop a synonym – describe how to drop a synonym from the database.
In a distributed database system, synonyms help simplify SQL statements.
Suppose you have a table called
sales in the schema owned by the user
lion, and you granted the
SELECT privilege for the
sales table to
To query data from the
sales table, you use the following statement:
Code language: SQL (Structured Query Language) (sql)
SELECT * FROM lion.sales;
Notice that you must include the name of the schema and the table name to in the query.
To simplify this query, you can create a public synonym using the following
CREATE PUBLIC SYNONYM statement:
Code language: CSS (css)
CREATE PUBLIC SYNONYM sales FOR lion.sales;
Now, you can query the table
sales with a simpler SQL statement:
SELECT * FROM sales;
Notice that the
sales public synonym hides the name of the
sales table and its schema