RE: FW: Security and the ODBC

This is great! I always say that if I'm having a problem then someone must have run into this before. Currently, the ODBC specification seems to ignore this and probably must people never give it a thought. In my case I cannot afford to have the data compromised and assuming the users are to stupid to figure it out is like 'closing the barn door after the horse got away'. But, of course we can purchase other drivers (or write my own) and therefor not load the CLI Client. But why should I? My gosh, I'm paying Informix enough as it is. Also, think of this, even if I do NOT load the CLI-Informix ODBC driver but the Server is ODBC compliant; what's stopping a user from installing the ODBC on a client and gaining access? I've had users purchase Crystal Reports and then configure it to connect to our SQL-Server. Grant it, the Crystal Report package comes with SQL-Server drivers and not Informix. But, a quick phone call to Crystal Reports and there's an Intersolv driver on the way. We're not even talking 'rocket science' hacking here. If the Informix ODBC driver is loaded, you can point and click to the Informix system in minutes. You click and it prompts you for all the information required (oh well). -----Original Message----- From: Dave Otto Sent: Tuesday, September 09, 1997 10:18 AM To: Tim Kelly; 'informix-list@rmy.emory.edu' Subject: Re: FW: Security and the ODBC You have raised a number of very valid points. These issues are what we have to deal with on a daily basis. So far we haven't hit upon "the universal sol'n". At 07:38 AM 9/9/97 -0400, Tim Kelly wrote: }different situations. If you give a user an ODBC connection (using the }Informix CLI) there is no way to ensure they are coming from your }application and your application only. That's the question. Most solutions we have tried in this area are overly complex and rely heavily on "security through obscurity" :-( } Yes, views do restrict certain things. But, you cannot do }certain things like inserts, deletes, and etc on certain types of views. Correct, many-to-one views will not allow data alterations. }multiple databases. The database names and tables change dynamically }and are not always the same. There is no way a view would even work. Hiding a privileged login inside the app' and building views on the fly based on the meta-data is an option, but has its' own risks (obviously). } The only thing I've seen as a possible solution is using a ROLE. }But, again building dynamic database selects seems not to work. For }example, if you switch ROLE in database A and now try to select from }database A, B, and C or just C; you are not known to those foreign }database as your new ROLE. How about using CDR and making copies of data local? } Actually, the application is a Medical Records Imaging database }and there are thousands of business rules. Without these business rules }(a.k.a. security) a user could gain access to confidential patient }information. The rules and security can be enforced by the application. }But, again they can circumvent them if they use another ODBC client }application on that workstation. You cannot use the authorization of the }database engine to restrict information based on a business rule. It sounds like you might want to look at a 3-tier architecture with an application server in the middle. This would allow you to define the business rules and "screen" all access requests. } Another example: If you have HIV Test results available in your }database and a user is allowed to view those only if they are logged in }form terminal #55 using an application called HIV-View which is in a }restricted and secured area (so you can use physical security to prevent }someone from taking that information). How do you code a view to only }allow access to the information in this case? The user can go down the Declare the current DB as the master - use CDR to create a secondary DB w/o the HIV info - use router access lists to control connections to the master DB. ----------- The bottom line is that complex requirements rarely have trivial solutions. I deal with confidential financial information and have to address these types of issues every day. I would be very interested in continuing this discussion here or off-line. dave otto daveotto@acm.org