Dbabc.Net

A synonym is an alias for another database object. A public synonym is
available to all users, while a private synonym is available only to the owner
or to the accounts to whom that owner grants privileges. A synonym can point
to a table, view, sequence, procedure, function, or package in the local data-
base or, via a database link, to an object in another database. Synonyms are
frequently used to simplify SQL by giving a universal name to a local or remote
object. Synonyms also can be used to give different or multiple names to indi-
vidual objects. Unlike views or stored SQL, synonyms don’t become invalid
if the objects they point to are dropped. Likewise, you can create a synonym
that points to an object that does not exist or for which the owner does not
have privileges.

Creating and Dropping Synonyms
The syntax for creating a synonym is as follows:
CREATE [PUBLIC] SYNONYM synonym_name
FOR [schema.]object[@db_link];
To create a public synonym called EMPLOYEES for the table
HR.EMPLOYEES, execute the following statement:
CREATE PUBLIC SYNONYM employees FOR hr.employees;
Alternatively, to create a private synonym called EMPLOYEES for the
table HR.EMPLOYEES, you simply remove the keyword PUBLIC, as in the
following statement:
CREATE SYNONYM employees FOR hr.employees;
To remove a synonym, use the DROP SYNONYM statement. For a public syn-
onym, you need to make sure that you include the keyword PUBLIC, as in this
example:
DROP PUBLIC SYNONYM employees;
To drop a private synonym, issue the DROP SYNONYM statement without
the PUBLIC keyword:
DROP SYNONYM employees;

Public Synonyms
Public synonyms are used to identify “well-known” objects (tables, views,
sequences, procedures, functions, and packages). These well-known objects
do not require an owner name prepended to them. In fact, if you try to
prepend the owner PUBLIC to a public synonym, it will raise an exception.
The data dictionary views are good examples of public synonyms. These
synonyms are created when you run catalog.sql at database creation time.
When you write SQL code that references the dictionary view ALL_TABLES,
you do not need to select from SYS.ALL_TABLES, you can simply select from
ALL_TABLES. Your code can use the fully qualified SYS.ALL_TABLES or the

unqualified ALL_TABLES and resolve to the same view, owned by user SYS.
When you reference SYS.ALL_TABLES, you explicitly denote the object
owned by user SYS. When you reference ALL_TABLES, you actually denote the
public synonym ALL_TABLES, which then resolves to SYS.ALL_TABLES.
Sound confusing? Let’s look at some examples to help clarify this concept.
Suppose that the DBA creates a public synonym EMPLOYEES for the HR
table EMPLOYEES:
CREATE PUBLIC SYNONYM employees FOR hr.employees;
Now, user SCOTT, who does not own an EMPLOYEES table but has
SELECT privileges on the HR.EMPLOYEES table, can reference that table
without including the schema owner (HR.):
SELECT COUNT(*) FROM employees;
COUNT(*)
———-
107
As another example, suppose that you want to create a public synonym
NJ_EMPLOYEES for the HR.EMPLOYEES table in the New_Jersey data-
base (using the database link New_Jersey). To create this synonym, execute
the following statement:
CREATE PUBLIC SYNONYM nj_employees for hr.employees@new_jersey;

Private Synonyms
Private synonyms can be created for objects that you own or objects that are
owned by other users. You can even create a private synonym for an object
in another database by incorporating a database link.
Private synonyms can be useful when a table is renamed and both the old
and new names are needed. The synonym can be either the old or new name,
with both the old and new names referencing the same object.
Private synonyms are also useful in a development environment. A developer
can own a modified local copy of a table, create a private synonym that points
to this local table, and test code and table changes without affecting everyone
else. For example, developer Derek runs the following statements to set up a
private version of the HR.EMPLOYEES table so he can test some new func-
tionality, without affecting anyone else using the HR.EMPLOYEES table.
CREATE TABLE my_employees AS SELECT * FROM hr.employees;
ALTER TABLE my_employees ADD pager_nbr VARCHAR2(10);
CREATE SYNONYM employees FOR my_employees;

Now Derek can test changes to his program that will use the new PAGER_
NBR column. The code in the program will reference the table as EMPLOYEES,
but Derek’s private synonym will redirect Derek’s access to the MY_
EMPLOYEES table. When the code is tested and then promoted, the code
won’t need to change, but the reference to employees will resolve via the
public synonym to the HR.EMPLOYEES table.
Use of a private synonym is not restricted to the owner of that synonym.
If another user has privileges on the underlying object, she can reference the
private synonym as if it were an object itself. For example, user HR grants
SELECT privileges on the EMPLOYEES table to both ALICE and CHIPD:
GRANT SELECT ON employees TO alice, chipd;
Then user CHIPD creates a private synonym to alias this HR-owned object:
CREATE SYNONYM emp_tbl FOR hr.employees;
User ALICE can now reference CHIPD’s private synonym:
SELECT COUNT(*) FROM chipd.empl_tbl;
COUNT(*)
———-
107
This redirection can be a useful technique to change the objects that SQL
code references, without changing the code itself. At the same time, this kind
of indirection can add layers of obfuscation to code. Exercise care in the use of
private synonyms.