News, views, tips and tricks on Oracle and other fun stuff

Not a Fan of Public Synonyms, Here is Why

Tom is not not a fan of public synonyms, here is why:

  • public synonyms pollute the namespace.
  • public synonyms can lead to security issues.
  • public synonyms can lead to a maintenance headache.
  • public synonyms are public – no one owns them.

So, instead of public synonyms…

create PRIVATE synonyms or views in the application pointing to the objects in question.

Excellent advice!

Update: Over on Twitter, Allan reminded me of another way to avoid using synonyms, public and private. Use “alter session set current_schema=x”. Nuno has been using this technique for many years and Tom wrote about it here.

Filed in Oracle on 04 May 13 | Tags:

Reader's Comments

  1. |

    I just wish there was a way to grant someone the privilege to create private synonyms on behalf of another schema, but just THAT schema. CREATE SYNONYM only works for MY synonyms. CREATE PUBLIC SYNONYM – well you’ve already explained the problems with that. And CREATE ANY SYNONYM lets you create synonyms in ANY schema – too much privilege.

    • |

      Like for example “CREATE SYNONYM TO scott”. I can see it coming handy in certain scenarios. To be implemented in 13c maybe? It depends on how many customers request such a feature I guess.