• In Oracle, use the CREATE SYNONYM statement to create a Synonym which serves an alias name for a table, view, sequence, procedure, stored function, package, materialized view.
• Appropriate privileges must be granted to a user before the user can use the synonym.
• Synonyms provide location transparency by allowing access to objects owned by other schemas without schema prefix.
• Synonyms helps applications to function without modification regardless of which user owns the table or view.

Syntax:

CREATE OR REPLACE PUBLIC SYNONYM <your_schema>.<your_synonym>

Let’s take user1 as “OE” and user2 as “HR” and try to understand the usage of Synonyms in Oracle through below example.

SQL> SHOW USER;
USER is "OE"

SQL> SET SQLP "OE> "

OE> CREATE TABLE Test_synonym(
  tid     integer,
  tname   varchar2(100)
);

Table created.

• By default the user who created the object becomes the owner of the object.
• By default the the owner gets all rights to the object.

--Accessing the table as OE user
OE> SELECT count(*) FROM oe.Test_synonym;

  COUNT(*)
----------
	 0

--Accessing the object without table prefix
OE> SELECT count(*) FROM Test_synonym;

  COUNT(*)
----------
	 0

Now login as “HR” user and access the table.

SQL> SHOW USER;
USER is "HR"

SQL> SET SQLP "HR> "

--Accessing table logged in as "HR"
HR> SELECT count(*) FROM oe.Test_synonym;
SELECT count(*) FROM oe.Test_synonym
                        *
ERROR at line 1:
ORA-00942: table or view does not exist


--Accessing the object without table prefix
HR> SELECT count(*) FROM Test_synonym;
SELECT count(*) FROM Test_synonym
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

• In Oracle, either the owner or its super user can delegate rights to other users.

Login as “OE” user and grant read access to “HR” user using below command.

OE> GRANT SELECT ON oe.Test_synonym TO HR;

Grant succeeded.

Again login as “HR” user and try access the table.

--Login to HR user and access other schema object
HR> SELECT count(*) FROM oe.Test_synonym;

  COUNT(*)
----------
	 0

HR> SELECT count(*) FROM Test_synonym;
SELECT count(*) FROM Test_synonym
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

Even though the “OE” user provided read privileges to “HR” user, but “HR” user still can not access the object without schema prefix. What’s the solution?

Create PUBLIC SYNONYM which allows all users to access objects without schema prefix. However each user must have appropriate privileges on the underlying object in order to use the synonym. When resolving references to an object, Oracle Database uses a public synonym only if the object is not prefaced by a schema and is not followed by a database link.

OE> CREATE PUBLIC SYNONYM Syn_Test FOR oe.Test_synonym;

Synonym created.

Since “OE” user has already granted read access on underlying table that is “oe.Test_synonym”, “HR” user must now be able to access the public synonym.

HR> SELECT count(*) FROM Syn_Test;

  COUNT(*)
----------
	 0

Synonym Equivalent in PostgreSQL

The CREATE SYNONYM is not available in PostgreSQL. You can use set search_path to include the schema so that objects in other schemas can be accessed without schema prefix.

PostgreSQL allows settings search_path in following ways:

• Using SET search_path = "$user", public, <your_schema>; to set current session’s search path.
• Using ALTER ROLE SET search_path = "$user", public, <your_schema>; to permanently set current user’s search_path.

postgres=# CREATE SCHEMA OE;
CREATE SCHEMA

postgres=# CREATE TABLE oe.Test_synonym(
  tid     integer,
  tname   character varying(100)
);
CREATE TABLE

postgres=# SELECT count(*) FROM oe.Test_synonym;
 count 
-------
     0
(1 row)

--Accessing the table without schema prefix with throw error
postgres=# SELECT count(*) FROM Test_synonym;
ERROR:  relation "test_synonym" does not exist
LINE 1: SELECT count(*) FROM Test_synonym;


postgres=# SHOW search_path;
   search_path   
-----------------
 "$user", public
(1 row)

--Setting search_path at session level
postgres=# SET search_path = "$user", public, oe;
SET

postgres=# SHOW search_path;
        search_path        
---------------------------
 "$user", public, oe
(1 row)

postgres=# SELECT count(*) FROM Test_synonym;
 count 
-------
     0
(1 row)

You can also set the search_path at database level using following command.

postgres=# ALTER ROLE myuser SET search_path TO "$user", public, oe;

• Setting the search_path instructs PostgreSQL to look for objects in the listed schemas. It does not grant permission to see what’s there.
• Setting schema in search_path will enable all objects to be accessed without schema prefix if the user has necessary privileges on the object and schema.

Hope this chapter helped you understand how synonyms work in Oracle and the best feasible way to handle the functionality in PostgreSQL.