Synonym Of Oracle - Get Access To DB From Another Schema Easily

A great example of a use case of synonym in Oracle PLSQL.

Introduction

Suppose you have dedicated DB at one schema for example stores and on the other hand, you have a different schema of employees and some procedures are made which insert into a table from the employees schema but with some reference of store schema. Then we can use synonyms for accessing tables, packages, procedures, functions and more from another schema with some grants to perform specific actions like execute, insert, delete, and more.

Note: You cannot directly access a procedure or function inside a package. Also, you have different grants for packages/procedures/functions and tables.

As I said, we have one schema Store , we created a procedure add_job_history to insert some records in table job_history like below:

create or replace NONEDITIONABLE PROCEDURE add_job_history  
  (  p_emp_id          job_history.employee_id%type  
   , p_start_date      job_history.start_date%type  
   , p_end_date        job_history.end_date%type  
   , p_job_id          job_history.job_id%type  
   , p_department_id   job_history.department_id%type   
   )  
IS  
BEGIN  
  INSERT INTO job_history (employee_id, start_date, end_date,   
                           job_id, department_id)  
    VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);  
END add_job_history;

Now we have to create another schema employees (if you don’t have one)

create user employees identified by employees;

Grant to create synonym to schema employees :

grant create synonym to employees;

Note: We can grant different grants to the schema as we wish according to the protocol. Please see Oracle documentation.

Verify if we created a different schema using the below command:

select username, account_status from dba_users;

Giving another grant to execute the procedure to schema employees :

grant execute on add_job_history to employees;

If you still face any issues with privileges, then login to SQLPLUS with any user then:

Connect sys/password as sysdba
grant execute on add_job_history to employees;

In the Employees schema, run the below commands:

How to create a synonym to access the store procedure add_job_history

create synonym ajh_syn
for store.add_job_history;

Now you can create a PLSQL program to execute the procedure with ease.

What I learned from multiple websites, I am putting everything here to make it easier for others to work. Please let me know if you have any doubts, I will try my best to give some resolution steps.