Term: SYNONYM

Definition:
In Oracle PL/SQL, the term SYNONYM refers to a schema object which is created by a user to access an object that is owned by another user. In the process of creating a synonym, the user seeking the object access must first create a synonym for the required object. The Owner User must then grant the access on the synonym to the seeking user.

Synonyms are of two types: Private and Public Synonyms. Private synonyms are the default synonyms created. Public synonyms are created by the Owner User with the PUBLIC keyword. Any user residing within the database can access objects created with the PUBLIC keyword.
Note that public synonyms are non-schema objects. This is because they are able to be accessed by all users within the database.

Example Syntax:

CREATE [ | OR REPLACE] [PUBLIC] SYNONYM [SYNONYM NAME] FOR OWNER.OBJECT_NAME

Example Usage:

In the example below, user Bob creates synonym for EMPLOYEE table which is owned by user Anne:

SQL> CREATE SYNONYM EMPLOYEE FOR A.EMPLOYEE;



Synonym created.

Now user Anne logs in and grants only SELECT privilege to Bob on the EMPLOYEE table. Note that all other operations on the EMPLOYEE table would be restricted for user Bob.

SQL> GRANT SELECT ON EMPLOYEE TO BOB;



Grant succeeded.

User Bob logs in and queries the EMPLOYEE table.

SQL> SELECT EMPNO, ENAME FROM EMPLOYEE;



     EMPNO ENAME

---------- ----------

       100 JOHN

       110 KATE

       120 MILLER

       130 JOHNY

       140 LARRY

       150 EDWIN

       160 KING

       170 SUEZ

       180 HICK



9 rows selected.

Now user Bob attempts to DELETE the EMPLOYEE table records. The operation fails because of insufficient privileges:

SQL> DELETE FROM EMPLOYEE;

DELETE FROM EMPLOYEE

            *

ERROR at line 1:

ORA-01031: insufficient privileges