Synonym: Why do you need it?

This is a frequently asked question by developers and novice database administrator. Why do we need a synonym when I own the database objects and I am the only user accessing it? I don’t need a synonym; this complicates the design and makes my troubleshooting hard. I hear this all the time and yes, it sometime makes thing complicated but there are several advantages of owning a synonym which we will discuss later.

Scenario: We have an employee table on HR schema which you are the owner of schema. The HR schema consist of few large Tables, Views and some Packages and Procedures. The data from HR schema are moved on a weekly basis to data warehouse for reporting and analysis. HR Tables store information about employee therefore, HR is very sensitive about  other  user accessing to their tables and other database objects therefore, they have created few dozen views. Any objects HR created have synonym on them.  Both public and private synonyms are used in HR schema. The View makes the tables and data secure and prevent unauthorized objects and data.  We will discuss about the fact and myth about owning synonym.

Security: Synonym provides a level of security by masking the name and owner of an object and by providing location transparency for remote objects of a distributed database. Synonyms allow underlying objects to be renamed.

Mocking Tables: Synonym will let you test your code or points to a test table without impacting the production table and data.  It also helps DBA do maintenance on a table while your application is up and working.

Preserve Permission: Will synonym preserver the Grants on objects that it points to when you need to drop or recreate? Using create or replace where possible preserver grants. You can’t do create or replace on tables and other dB objects. How do you preserver the grants on the table when you have to drop and re-create it. Many believe synonym preserves the grants on the objects. This is NOT true. When you drop, all the grants get dropped but not the synonym. You will need to preserver grants somewhere before dropping it.

Easy to use name: When you don’t have option to re-name your database objects for some conflicts or the DDL don’t exist, you can create a synonym to give meaningful name. Renaming needs more than a DDL change, you will also need to find all the places where the object is used inside the code and change it. You may just add a synonym without having to change all your code.

Performance: Will you get a same performance when using synonym vs the real objects, I think there is a negligible difference in performance which you should not be bothered considering the benefits we get with synonym uses.

Object Move: Without synonym you will need to schema preference your object name. You will see [hr.employee] in your code. Sometime, you will need to move objects to different schema for other reasons which may not happen very often but I have seen see this changes on every organization I have worked. This change will break the code calling the objects. Using Synonym resolves the issue where you don’t need to schema prefix your object, just need to alter Synonym to point to right schema.

As a bonus:

Access Order: Knowing the order of execution of Synonym sometime saves you from the headache of Synonym mess. How does the database process synonym? When there are both Private and Public Synonyms with same name. What is the order of execution? As a schema owner, the database will look for a local object, if missing or broken, it will then search for a private synonym; if not available, it will then look for Public Synonym with the name. Public synonym comes last in a search.

Now, how does that work for non-schema/object holder? The process is the same. It first checks the local objects, then to Private Synonym, and then Public Synonym. The order is LOCAL/PRIVATE/PUBLIC.  A good design makes the database secure and synonym easy to maintain and use. Most DBMS will let you create a Synonym to an object which doesn’t exist. Database does not validate the objects existence or typo of object name therefore, we should pay special attention when creating a Synonym.

Public vs Private: The simple definition to Public vs Private is simple. Synonyms that are under your schema are private and the Synonym owned by PUBLIC is public synonym. No one has access to your private Synonym but you. Public Synonyms are owned by PUBLIC schema and anyone who has access to view them. To select/execute from those Synonym you need to have proper grants to underlying objects. Synonym does not by pass the privileges and grants which also adds to the list of synonym myth.

I always try and avoid creating public synonym and you should too.Troubleshooting can be problematic if you are not aware of Public Synonym. It can have collision on object name if multiple application use Public Synonym. Also, it creates a security issue when grant all is issued by inexperience dba which happens all the time.