Main | Data Security »

The Case Against Public Synonyms

I have been walking the 2.5 miles to and from work lately. The subzero temperatures have not affected my mind, because I find this to be a great time to gather my thoughts about work and life. So it was yesterday as I was walking home after a long day. I thought about all the things I have learned about public synonyms and database links that seem to outweigh most of the benefits. I'm going to start with public synonyms, and then as I find the time I'll post my tirade against database links.

This is not to say that public synonyms should not be used, but we tend to employ them with some expectation that they will make our lives easier. That is often not the case when you consider the big picture. They can create unintended problems down the road. I've made a list of the disadvantages of using them here. These are the things that should be weighed before creating public synonyms.

  • Degraded performance—This may be the most famous one. Consultant Steve Adams demonstrated it back in the 8i days. Tom Kyte has several posts on asktom.oracle.com about it, including this one where he repeasts Adams' test. Public synonyms and to a lesser extent private ones create a performance problem in the database instance.
  • Difficulty in maintaining—While we intend for synonyms to make our lives easier, sometimes it can be more work to keep them up to date than to live without them. If an object is dropped, the synonym should be dropped too. If we create a new table, we probably should create the public synonym too. If we rename an object, we should rename the synonym too. And then we have source control. If we become dependent on synonyms, we need to make sure they get migrated through code deployments. Is it really worth the worry?
  • Clutter—This is something we can avoid if we think things through before creating synonyms. But usually we don't, and we end up with public synonyms hanging aroud for objects that no longer exist. Or we have synonyms that refer to renamed objects using the original name.
  • Ambiguity—This can happen with synonyms that refer to dropped objects and with multiple users creating private synonyms in addition to public ones that already exist. I gets messy. It becomes a chore trying to figure out which synonym or object name is taking precedence. Troubleshooting problems is a more cumbersome task.
  • Security—Sometimes it is a good idea not to expose the names of all of your database objects to every user on the system. If you create a public synonym, you're creating something that everyone can see regardless of whether they can execute it or query its contents. If you had a table named "PROJECTED_LAYOFFS_2009," would you want everyone to know? And before you discount the significance of this, think about some of the descriptive names you may have seen chosen for tables or views. And look at the quarterly Oracle Critical Patch Update notices. Sometimes there are exploits where simply knowing the name of an object and having a grant on some DBMS package is enough to get your foot in the door.

There are alternatives to public synonyms that we can use to the same benefit without some of the pitfalls:


  • Alter session set current_schema='target_user'—No need to prefix every object name with the owner name. This is like telling Oracle to assume every object you refer to is owned by 'target_user' unless you specify otherwise.

  • User proxy authentication—In effect, users are authenticating with their own credentials but have authorization to operate within the database as though they are the proxy user. It is also a way to grant developers the ability to develop in a shared schema without having to share the password to that schema. I've had problems getting this to work in Toad, however. I'm fairly certain it is a bug in Toad, but the Quest support rep gave up and closed my support ticket.

  • Prefix everything with the owner!—Yes, this is what everyone is trying to avoid. I must admit, this is my preferred way because it is deliberate and unambiguous. I always know what object I am referring to and there is always explicit feedback that I am working on objects for a particular schema. Why hide schema objects beneath synonyms? If we're working within a database then we're presumably proficient with computers, so we should be fast enough at typing to get by this way without too much trouble.

Maybe the best way to summarize all of this is that we should consider public synonyms only as a last resort. There are alternatives that are better in most cases. At minimum, we should think problems through before creating that next public synonym with the expectation that it is making things easier.