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.
CREATE [ | OR REPLACE] [PUBLIC] SYNONYM [SYNONYM NAME] FOR OWNER.OBJECT_NAME
In the example below, user Bob creates synonym for EMPLOYEE table which is owned by user Anne:
SQL> CREATE SYNONYM EMPLOYEE FOR A.EMPLOYEE;
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;
User Bob logs in and queries the EMPLOYEE table.
SQL> SELECT EMPNO, ENAME FROM EMPLOYEE;
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