Can we create Private synonym in other schema for which Object is in different schema

Question

Lets say, I have a schema "SCHEMA_1" and under this I have one object "EMP_TBL_Schema_1". Now I want to access this table through another schema "SCHEMA_2". Only this "SCHEMA_2" should be able to access this table ( not any other schema present in DB except "SCHEMA_1"). So in this case what will be appropriate, Public Synonym or Private Synonym. I am confused here. As I know that private Synonym is created by its owner Schema, Is it possible that "SCHEMA_2" can create private synonym by its own for objects present in "SCHEMA_1" to use.

Explanation through code would be appreciated.

Answer 1

SCHEMA_2 should create a private synonym:

create synonym emp_tbl_schema_1 for schema_1.emp_tbl_schema_1
/

Note that the synonym can be any valid name: it doesn't have to match the table's name in SCHEMA_1. Find out more.

Also note that creating a synonym is just a convenience for making DML easier to write. It doesn't grant any rights on the referenced object. SCHEMA_1 has to grant privileges to SCHEMA_2. This grants select and insert:

SQL>  conn SCHEMA_1/password
SQL>  grant select, insert on emp_tbl_schema_1 to schema_2;

This allows SCHEMA_2 to query the table and insert new records, but not update or delete existing ones. Obviously, the GRANT command is covered fully in the documentation.

Finally, to resolve your confusion, public synonyms are rarely appropriate for application objects. Create them for utility objects which widely used by most or all users. Otherwise, schemas should create private synonyms for objects they use.