A year ago or so I was asked to do a health scan on a database. When I worked for Transfer Solutions we used to have a script for that, that generates quite a large HTML file that we could review in comfort at the office after a visit to a customer.
Since I’m quite security minded, soon enough I scrolled to the security section in the report. Luckily database security at first glance wasn’t bad at all. The DBA role wasn’t granted to everyone and their grandmother, neither dictionary privileges or SELECT ANY TABLE privileges and such stuff. No open accounts with default passwords. No spaghetti structure of grants to everyone. The application did not log on with the schema owner, but with a separate user with just insert, update, delete, select and execute privileges on that schema. The privileges seemed pretty well thought out.
Granted, the schema owners had the DBA role. Now that isn’t good. It might be salvaged somewhat if only the DBA has access to these accounts. Still it’s a risk if PL/SQL packages of that owner have SQL injection leaks. But this was actually better than I often see. Or so I thought. All seemed reasonably well, until I arrived at the database link section of the report. There were several public database links. Each of which connected to the schema owners on the same database. The schema owners with the DBA roles. So.. everyone had the DBA role after all.
Public database links can be used by all users. You just need CREATE SESSION privileges. Congratulations, you can now use public database links. And yet I often see DBA’s implement public database links without a care in the world. Often these public database links weren’t required by the application in the first place. But the DBA needed to implement a solution that required materialized views, or data pump exports and imports. Stuff that often easily can be done with a private database link. But sometimes the DBA doesn’t know the password of the owner and the application administrator was on holiday. So that thought creeps up: “Oh I know how to get this temporarely fixed. I make a public database link for the time being”. Of course it will be there for years to come. Because changing it afterwards is dangerous.
Now you might argue that it doesn’t matter that there are public database links to users with DBA roles. Because the application can’t use them. Unless, that is, the application isn’t actually leak proof itself. It might have SQL injection leaks. That’s the thing with security: you can never assume you can depend on other (metaphorical) links in the chain. Also, remember those some power users that connect to the production database with SQL Developer or TOAD? Those tools make database links easily visible.
And there is another problem with database links in general. If you have two or more databases linked together with database links and you have to restore one of them, do you have to restore all of them? Think about it: they are linked.
So think twice before you use database links and avoid public database links if you can. Usually you can. And if not, do you really want all those users take advantage of that? So check your database links in dba_db_links. Make sure you know and trust what’s there.