Re: ODBC and Security

Mike Sullivan was concerned about security issues when using ODBC connections to his database (his article is included at the bottom). In short: Most complex databases have rules and restrictions about data entry and maintenance that are programmed into a central application. But, if users have SQL access to modify data, they can do so using any client program, including ODBC client software, opening up the possibility of data corruption. There are two solutions that I have considered. First, you can program all the rules and restrictions within the database using constraints, triggers and stored procedures. This is great in theory because now any client program will conform to the rules when modifying data. However, this method can be very complicated and difficult to implement. Although I haven't followed this route and I can't say what animals you might run into, I have found that the stored procedure language is not very robust. The other solution is much easier. The basic idea is to apply security on the program level, allowing a secure maintenance application to modify data while all other client software is allowed only read access to the database. This can be done by giving all the users only read access (grant select) to data, and then creating a single user, e.g. called "program", with full access to the database. When a user runs the maintenance application, it connects to the database as user "program", gaining full access to the database. Note that the application supplies the password, perhaps encrypted in the program code. All rules and restrictions concerning the modification of data are coded in the maintenance application. I have followed this route using Informix-4gl (charged with Power-4gl, see sig.) and found it to be simple and practical. I'm actually surprised that more people aren't concerned about this security issue. Maybe they don't realize the danger or they trust that the users won't be "clever" enough to find the loopholes. I think they're just blindly following the trends, but who is leading? One question is whether you really need a graphical user interface for the maintenance of your data. I have found that the true need for graphics is in reporting tasks to effectively present information, which requires only read access. On the other hand, programs that maintain critical data (the kind that you deem transaction loggin necessary for) are best served by a centrally controlled, stable and secure system. In my opinion, todays two tiered client-server tools don't fit that bill. Maybe New Era's upcoming three tier architecture will do it, but on the other hand maybe the future of client-server will be with www browser tools like Java from Sun. Meanwhile Informix-4gl does the job. Anyway, how is everyone else handling the security issue with ODBC. I would be very interested in any comments. Sorry about the long article. +---------------------------------------------------------+ | John H. Frantz | | Power-4gl: Extending the Informix-4gl user interface | | http://www.strengur.is/~frantz/pow4gl.html | +---------------------------------------------------------+ Mike Sullivan wrote: > > I recently set up a service on my database server to allow pc clients > to access our finicial database. It lives in an on-line instance and the > clients use Intersolve ODBC drivers to connect to it. It works well > except for security. We use relaxed permissions on our data as the > applications provide security tables which we maintain. > > What I would like to do is limit the access granted to users who access > the data through the service port. The user is prompted for a user id > and password when the connection is made, but once connected the user can > do any sort of updates, inserts and/or deletes. > > My frist thought on this was to force the users to use a special UID > for inqueries only. I could then grant read access to the tables for > that UID, anyone running an application would use their reular UID. That > limited UID could be set in the clients ODBC.INI file. Well any clever > user would quickly learn to edit it and connect as themselves. Are there > any options I could use on /usr/informix/lib/sqlexecd that may help? > That is the process that listens to the port. Is the source for sqlexecd > available? It would seem fairly simple to modify it to filter out UIDs > and thus protect the database. > > I would like to give this capability to the users but am leary of the > potential for data corruption. Any one out there addressed this issue > yet? I am open to ideas or suggestions, please e-mail or post here. > > Thanks > Mike Sullivan