Re: Q on restricting connections from some applications

> I contributed to an earlier discussion regarding the above. We have a > third party application to which the user connects via a shared memory > connection with a unique UNIX userid. In order for the application to > succeed, all users need all permissions to all tables. Given that the > user knows his UNIX userid and password, there is nothing we can do to > prevent him obtaining an ODBC driver then use his MS Office > applications (corporate standard) to execute an SQL statement such > as "delete from <tablename>". Therein is our problem. We believe what > we need is the ability to detect that a database call has come from > ODBC - this would at least be a start. Forgive me if I have > misunderstood, but I don't believe your solution above (elegant though > it is!) would help us. A slightly different option exists for restricting ODBC access. As Jonathan says, though, a knowledgeable user could still subvert this solution. Basically, you would substitute the OpenLink (http://www.openlinksw.com) ODBC driver for the default Informix/Intersolv/Merant ODBC. You would then hide your native Informix listener port, by not telling anyone what port number it is connected to. This is the weak spot, though. If you do not require any connectivity between multiple instances of Informix, you could completely disable TCP access, allowing only shared memory connections. The multi-tier OpenLink product runs on both the client and the server, and when a user requests a connection, it compares several parameters against a rule book to see whether a user is allowed to connect. For instance, I have PCs connect using a VB application and need to update the database. For those users, I have a rule that says if they want to connect to database "X" and are using application "Y", they can connect with read/write access. I have another rule that says if they are connecting to database "X" but not using application "Y" they connect with a read-only connection. This prevents them from using MS-Office to do any updates. The rules can work on a combination of userid, database name, application ID, host name (of the PC the application is running on), service type, client O/S, and requested access mode (R/W vs. RO). There are still flaws with this solution, of course. One is that OpenLink, while reasonably priced, is not free. Additionally, a sufficiently intelligent programmer could probably write another VB program that would pass the correct application ID to OpenLink and then send through destructive SQL statements. Also, if you have to keep TCP listeners within Informix (for distributed transactions or whatever other reason), a user can find what the correct port is, then instatll the Informix ODBC driver and connect to the database and run whatever SQL they desire. Another problem is that there is no way to allow read/write access to only certain tables and read only access to others based on the rule book. Basically, the SQL security model has many flaws, and no one has yet addressed them satisfactorily. A non-SQl related drawback is that now you have another vendor to contend with, and Informix does not offer any help diagnosing problems with connectiviy unless you are using the Informix/Intersolv/whatever drivers. One positive thing, though, is that it is very easy to set up the OpenLink client software. I've said some choice words when trying to set up I-Connect/CLI. Given what I read in your posting, the application must be running on the same machine as the database, since you are using shared memory connections. You could then disable the Informix listener by not coding any 'onsoctcp' entries in $INFORMIXSQLHOSTS. One thing I liked about DB2/MVS was the concept of a bound plan. At compile time, the SQL precompiler produced two sets of output. One went on to the COBOL compiler and link editor to create a load module. The other went through a post-process step that would create a DBRM (database resource module) that listed each SQL statement within the program and the access plan as determined by the optimizer (no run-time optimization) for that statement. The DBA would then grant permission on the DBRM to individual users. When using that DBRM, the users could perform actions that they normally could not, somewhat similar to Informix's stored procedures. Mark Collins mcollins@us.dhl.com