« Data Security | Main | Data Security Redux »

The Case Against (Public) Database Links

As promised, here's my rant on db links. Database links aren't as bad as synonyms. It is usually the implementation that is at fault. There are of course many reasons where database links are useful and make things possible that would not otherwise be. The problem is when they are created in a nonsecure way or to accomplish things in code that could be done in a better way.

I don't like them because they complicate security administration. They tend to be out of sight/out of mind after the initial creation. They can become a back door to things that were not intended. Whenever possible, I would prefer to use other methods.

There are two conceptual points that I think make it more clear. First we have the idea of a user or web application connecting to one database and accessing data over a public db link. The public db link subverts any ability we may have had in securing the remote database against the user or web app. Usually a public link is created with some purpose in mind, but its availability is exploited for other purposes and we soon lose track of what or who is using it.

The second is that the purpose of a db link is not to relieve us of the chore of having to log in separately to the remote database. Sometimes db links are created just so a user or app does not need to go to any trouble to authenticate a second session. If the db link is a public one, the temptation to use it that way is spread to any of the applications running on an instance.

And why can't a web-based app just connect directly to the remote database and do its work? The only reason is that the app may need to perform an operation that joins a local and a remote table. For that, a private db link with minimum permissions granted is sufficient. For everything else, it is actually more desireable to have separate sessions to each accessed database because it gives the developer more flexibility in trapping and handling errors. If one database is down, the parts of the app that use another may still function, for example. When things are done over a database link, any Oracle exceptions are all wrapped up in the exception stack for the local database. We get the sometimes ambiguous errors such as "end of file on communication channel."

For database jobs and PL/SQL procedures, db links are the only option for working with data in a remote database. That's fine. Just don't use a public link.

The really scary scenario is where permissions like "select any table" or "unlimited quota" are granted to a user that is connected to via a public db link in another database. I bring this one up because I discovered it as a lowly user at a previous job. I found a db link that gave me access to all of the tables in a remote database, and when I asked one of the administrators whether it would be okay to use that link to get some data for a project I was working on it set off a scramble behind the scenes to close it up. They didn't know the door was left open and they had no auditing in place to raise any flags.

The main point here is that a public database link can add to your administration workload. There are a plethora of security considerations to deal with that could simply be avoided by never allowing public database links. It is just not worth it. And limit your use of private ones too, making sure that proper planning has been undertaken. If developers complain about it, ask them if they'd like to be the ones held responsible for the security and integrity of an invaluable company asset--the data in the database.