Re: ODBC and Security

> Billy Wheeler wrote: > > > > My understanding is that client/server architecture is *extremely* > > vulnerable to basically any app (Lotus/Access/MS-Word :) with an ODBC driver > > coming in and either acquiring or altering your data. I'd be very interested > > to hear what experienced C/S developers do to minimise this risk. > > -- Me too. This is *important*. > Alex Fiore <alex@informix.com> writes: > Using an ODBC driver to an Informix database does not give > you any greater access than with ESQL/C. The same user-level security > is in place. The ODBC driver will still have to go through the > connectivity software (ESQL/C and INET). They will still have to > provide a username and password to login to the database. Whatever > security you have implemented in the design of your database (user > access to tables, permissions, views, etc.) are still in effect. > > If your database is designed with no security precautions (everyone > logs in with the same user ID, all permissions are available to > public, etc.) than that is what you get. Anyone accessing the > database using any tool (whether via ODBC or not) will have > full access to your tables. I am sorry, Alex, but you have missed the point here. If this is the general attitude inside Informix we are all in for real trouble as we start using ODBC more and more (our users demand that). The main problem is limiting the users ability to update a database (insert/update/ delete++). Select access is a smaller problem. The problem is simply as follows: 1. We have some application running on the server (written with Informix 4GL, ESQL/C or whatever) that updates the database (insert/update/delete). To run this the user has a login name and password on the server. The application works like most does - it updates the tables directly. To enable this the user has to have appropriate rights on the tables involved. This wasn't a problem because the user was limited to run the application. He had no access to ISQL/Dbaccess or any other tool that could directly update any table. The application itself made sure the user could do nothing very wrong. It was easy to make all tools and programs except the once the user should be allowed to run inaccessible to that user. 2. Now the user gets an application running on his PC using I-Net and possibly ODBC (written in NewEra or any other language or tool). This application in itself also is no problem as it takes care of the user as before (with the application running on the server). 3. However: Now there is no way of stopping the user from running MS Access or any other application that uses ODBC and/or I-Net to access the database. There is no way of controlling which programs the user has or installs on his PC. (No diskless workstations is *not* a solution. The user is supposed to be able to do his own installations and reading disketts, CD-ROMS or whatever.) The user logs into the server via I-Net/ODBC using the same loginname/password as with the application in 1 or 2 above and can freely update any table that he could previously only via the applications. We now have a full breakdown of all security we could previously rely fully upon. This breakdown is present even without 2 above if the user is allowed/able to install ODBC on his PC. Point 2 simply makes it worse as the access tools are allready installed. The user can simply start whatever other tool he may have and start creating caos in the database. It may be intentional or not, it doesn't realy make much difference. One solution that has been proposed is to use different login-names for different purposes. These are all hackers solutions that aren't realy workable, at least not in the long run. Maintenance of loginnames/passwords is but one of the minor problems. Another is that the application in 2 above must be allowed to update tables directly. What do we do then with the problems in 3? A third problem, related to the suggestions that server based applications should switch to one common "user" whoever loged in, is that we want to know exactly who did what in the database. That becomes more difficult, if not impossible, under this senario. Also it looks too much like a hackers solution in the face of a problem that should have been solved better. Do I know a solution? No, but there may be several to look at. First, it is very important to relize the situation created by the application running on the PC. This must still be able to update the tables directly, while using MS Access in native mode from the same PC should not. The application may however be written in MS Access, so the problem is not easy to solve. Please think through this in detail before you propose solutions. Using stored procedures for all database updates is *not* a workable solution. The leagacy applications can't be changed to do that, and even if they could it's to hard. Using roles in the database may help, but what will hinder a user from setting any role from say MS Access? May be that DCE/Kerberos or similar tools allready have solutions to this. I haven't had time to look into this yet. May bee login servers can be a solutions. As I understand these the user gets one system wide loginname/password while the server maintains different names and/or passowrds for each application. Such tools are available from ICL and EDS (in Germany) and possibly others. If the login server itself maintains the different passwords and these are unknown to the user it may be workable. Two problems though are the price of such solutions and the requirement for fairly complicated script programs for each user to do this maintenance - at least as I understand these tools right now. Another small problem is that I-Net doesn't even give the user a way of changing his passowrd. When it no longer works because it is aged on the Unix-server the user must be given some way of loging into that server directly to change his password. Prety simplistic from Informix if you ask mee. So far we have "solved" the problem by using ODBC only for users who don't know how to start up MS Access or similar tools or in small tightly controlled workgroups where it is possible to tell the users that they shouldn't use any such tools. This won't be workable for long! This is a sufficiently important and difficult issue that Informix and everybody else involved in database tools should have some very clear idea on either how we should solve it now, or if that is not possible, how you intend to enable us to solve it within a relatively short timeframe! If this requires fundamental changes to the way ODBC works so be it. Than it *must* be changed. There wouln't be any uproar in the industry if this can be shown to be necessary. On the contrary, there will be an uproar if it is not. Any ideas anyone? Any that can realy solve the problem in a professional way? Nils.Myklebust@ccmail.telemax.no NM-data AS, Toyenbekken 21, Oslo, Norway My opinions are those of my company