Unfortunately, if you're looking for a quick and easy method for controlling ODBC, there isn't one. You have a couple of options. Let's examine those in detail... First, use object level security. This is the most definitive way to control who can do what with an object. It doesn't matter if the request is ODBC, JDBC, FTP, DDM or a program executing a Web application or an operator running a command from a command line or submitting a batch job, if you set *PUBLIC authority on a file to *USE, no one will be able to update that file unless they or their group have been given a private authority of *CHANGE or they have *ALLOBJ special authority. What is the downside to this approach? Many exit program vendors will claim that it's managing all those authorities to the objects. That's not really a big deal because commands like Grant Object Authority (GRTOBJAUT) and Change Authority (CHGAUT) allow you to change the *PUBLIC authority for all objects in a library or directory or change all objects of a particular type, such as a *FILE. No, the issue is really determining what might break if you make this change. If your applications run with adopted authority and the same profile that owns the application programs also owns the files, then this shouldn't be an issue. If the application relies on *PUBLIC authority being set to *CHANGE or worse, *ALL, then this method will most likely cause the application to fail and it will have to be changed to use adopted authority.
Unfortunately, you can't control the update of a file by changing the authority at the library level. *USE at the library level allows users to use, modify or delete any object in the library -- assuming, of course, that they have the appropriate authority to the object itself. *CHANGE to a library allows a user to add objects into the library itself, but doesn't control whether you can change the actual object. Again, that is controlled by the object's authorities.Second, use exit programs. There are several things to consider with this option. First is whether to write your own exit program or purchase a solution. It is my opinion that your money is better spent purchasing a solution than writing it yourself. If all of the exit point formats were the same or were as easy to understand as the FTP formats, then I'd say to write it yourself. But they aren't. They vary from exit point to exit point and tend to be very complex, especially for the servers that are hit when you use ODBC. If you get granular at all, you usually end up having to parse the incoming string. The last time I counted there were 12 vendors that provide this type of solution. In other words, it's become a commodity and therefore, you should be able to get a very good price for the software.
Next, understand that, even when you purchase an exit point solution, resolution of this issue will not be a snap. When you first install the software, you usually have to end your TCP/IP and host servers. Some companies find it difficult to find a time to get this done. Next, I have yet to see a vendor make controlling ODBC easy. Comparing exit point solutions is difficult as well. You have to uninstall Company A's software before you can install Company B's software. I've seen cases where Company A's solution leaves pieces of itself scattered around the system and causes Company B's solution to fail. In addition, every time you install or uninstall a solution you must end your TCP/IP and host servers. Finally, terminology varies from one vendor to the next so you need to "clear" your mind before looking at the next vendor's solution or you will be totally confused.
Finally, understand that exit point solutions only control the interfaces
that have exit programs. They do not control objects that are accessed via
batch jobs, commands submitted from a command line or Web applications.
This was first published in January 2004