Skip to Main Content
  • Questions
  • advantage of private synonyms over public synonyms

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Walid.

Asked: April 24, 2013 - 5:25 pm UTC

Last updated: April 26, 2013 - 1:41 pm UTC

Version: 11.2

Viewed 10K+ times! This question is

You Asked

Hi Tom

Do you have any articles that list the advantages of using private synonyms over public ones

thanks

and Tom said...

how about this, I list the disadvantages of public synonyms...

public synonyms pollute the namespace - there can only be one public synonym X. If two bits of your application schema want to have X - forget it. Or if you want to put two applications together in your database and they both want X. forget it.


public synonyms can lead to security issues. What if you install your application and create a public synonym X for some bit of it. Another application schema just uses "X" - believing it to be your-app.X. Then some malicious (or not even malicious, just mistaken) developer comes along and implements X in that other schema. All of a sudden, instead of calling the public synonym X - that other app will be invoking this new X.

(this is one reason why people might consider calling SYS.dbms_sql instead of dbms_sql. what if I drop a dbms_sql package into your schema surreptitiously - this dbms_sql want to be just calls sys.dbms_sql - except for the parse routine which will slighly modify your sql first. sort of like a trojan horse).

public synonyms can leads to a maintenance headache. "whoops, we did a create or replace public synonym X for y - didn't know you were using that"

public synonyms are public - no one owns them, anyone that can create them can overwrite them, change them.

and so on

Not a fan of public synonyms. I am a fan of coding references to specific objects or if that is undesirable, create PRIVATE synonyms or views in the application pointing to the objects in question.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Typo?

Shannon Severance, April 25, 2013 - 9:31 pm UTC

I am a fan of coding references to specific objects or if that is undesirable, create <strike>public</strike> private synonyms or views in the application pointing to the objects in question.

Or did I miss something?
Tom Kyte
April 26, 2013 - 1:41 pm UTC

man, I gotta stop doing that.

YES, private synonyms. I've corrected that. thanks

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library