Our database administrators manage a number of databases and schemas for our project. There are a number of production instances, different schemas for development, and separate databases for testing. These counts get multiplied by the different environments required for multiple versions of our system. All of these machines and instances are managed by one common "tnsnames.ora" file. The DBAs put this file out on the network in a globally accessible location. All non-production users point to this file using the TNSADMIN environment variables. Normally this arrangement works well. However once in a while, somebody messes up the tnsnames file. The result is a lot of pain.
Just this past week a couple developers encountered this sort of problem. We have been modifying the design used to print reports from our application suite. Previously we fed reports into an Oracle 6i Reports Server installed on the workstations. After moving to an Oracle 10g database on the back end, we detected some problems running the reports from the Reports Server. So we had a developer code up our own Reports Server using Visual Basic. This work around seemed to run pretty well. We just needed the application to call the Visual Basic app instead of the Reports Server. But then this fix just stopped working. In fact none of the reports seemed to be working correctly.
A couple of us developers spent a few days looking into the problem. I had been uninstalling and reinstalling Oracle software like the Reports Server and Reports Builder. None of this worked. After a while somebody realized that the tnsnames entry for reports was removed from the tnsnames file. It was a relief for the development team. We still were angry that our time was wasted. Luckily we install a local file on all our Production user workstations. The senior DBA decided it was finally time to lock down access to the tnsnames file. The trouble is that development needs access to the file in order to connect to all the databases we use.
So the senior DBA decided to "hide" the tnsnames file on the server. I tested a couple applications that use the file for connectivity. So did the senior DBA. We did not notice any problems. However one of the reports developers started seeing some weird problems in reports. She recalled out tnsnames debacle and started first by trying to research the tnsnames file. She panicked when she could not locate the tnsnames files on the network server (remember it had been hidden). I told her what the senior DBA had done. As of today I have not heard back whether she has resolved her reports problems. Maybe it is related to the hiding of the tnsnames file.
There must be a better way of providing access to the tnsnames file for connectivity while protecting it from accidental modification. Any ideas?
