3.7 Synonyms A synonym is another name for something. We use synonyms every day. Probably the most common synonym usage is a nickname or familiar name used in conversations. Few people named "William" are called by that name , except perhaps William the Conqueror. Most Williams prefer to be called "Bill." Margaret becomes Peg or Peggy, John becomes Jack (which doesn't make much sense. But then, most of the "synonyms" we describe in the following sections don't make much sense either!). 3.7.1 How Synonyms Are Used Synonyms are used in the Oracle database to provide location transparency by concealing the owner and location of the object. (The next section explains why you might want to do this.) The object can be a table, view, stored procedure, package, function, snapshot, sequence, or even another synonym. Synonyms in the Oracle database are either public or private. If a synonym is public, then all users can reference the synonym even though they may not have privileges to access the underlying object itself. In that case, an error is returned. If a user creates a synonym without the PUBLIC keyword (a special privilege is required to do this), then the synonym is private and can be used only by the user who created the private synonym. 3.7.2 Why Synonyms Are Used The primary reason for using synonyms is to avoid the requirement to know the owner of the object. If mary owns the employee table and ralph wants to select some data from it, then the command would look like this: SQL> SELECT * FROM mary.employee; If ralph does not know that mary owns the table, ralph will have a problem attempting to access that table. On the other hand, mary (a privileged user) creates a public synonym with the following command: SQL> CREATE PUBLIC SYNONYM employee FOR mary.employee; mary then grants access privileges by way of the synonym either to PUBLIC or to specific users. All of those users granted the privilege can refer to the synonym without adding mary as shown in the above example. Although the example shows the synonym with the same name as the table, this is not a requirement. Creating the synonym with the same name as the table is, however, a common implementation practice which makes object tracking easier. By removing the need to specify the owner of a table, the use of a synonym provides location transparency. The syntax of the CREATE SYNONYM command allows reference to a remote database as well. The user not only does not have to know who actually owns the table, but also does not have to know on which database the table is located. Synonyms are treated exactly like the tables they represent when referenced in SQL statements. Privileges given on a synonym translate to the object itself. A user given the SELECT privilege on the public synonym "employee" could still use the mary.employee syntax. The use of synonyms helps to hide the underlying database structure, which helps to protect the database from curious or malicious actions.  | If an underlying object is deleted from the database, the synonym that was created for it will not be deleted automatically. | | | 
