Re: Security and the ODBC

Tim Kelly <Tkelly@svhs.org> wrote in article <5v6d4o$jil@cssun.mathcs.emory.edu>... > set role appl; > select count(*) from wippage@hospital4:t600_wip_page;> > Doesn't work. I'm connected to database mris_prd and this database has > a role called appl. The wippage@hospital4 has no rights to public and > only understands the appl user and appl role. You cannot select data > from wippage@hospital4:t600_wip_page unless that database has public > access or your original access. You get > > 387: No connect permission. > 111: ISAM error: no record found> > Now the mris_prd database is fooled by the role, but all of the others > are wide open:-( > I missed the very beginning of this thread and responded to a response, so I'm not sure if I'm off the mark here, or if you've seen this suggestion before. Anyway ... I have frequently encountered the problem where a client wants to grant users access to an application database through MS-Access (or other Windows based query/report tool). Of course the way to do this is with ODBC. However, the users normal Unix log in which they use to access the application allows complete read/write access to the database. Normally this is OK because the application handles security and proper maintenance of referential integrity, etc. However, this level of access within MS-Access is quite dangerous since the user could alter and even delete data at will without regard to the controls normally enforced by the application. The best solution I have found to this dilemna to date is to use the OpenLink Multi-Tier ODBC driver instead of Informix-CLI or other "single tier" driver. The difference is with the OpenLink multi-tier driver, the ODBC driver on the PC communicates with a request broker and database agent on the server. The request broker can limit connections to read-only based upon various combinations of user id, host (client) IP address/name, client application name, database name, etc. In the simplest case, I set things up so ALL connections are restricted to read only. In some cases, certain users and/or PC applications are given R/W access. To carry the security even further, we set up the request broker to connect to Informix via a shared memory connection and do not even define a TCP/IP connection to Informix. This prevents anyone who somehow obtains Informix-CLI (or a similar driver) from connecting. If you still need a TCP/IP connection (for communication between multiple Informix servers on different Unix boxes, for example) I believe you can configure Informix & Unix in such a way as to allow these connections only from specific machines. (Although I have never done that.) If this sounds like a solution, you can find OpenLink at http://www.openlinksw.com where you can download a completely functional trial version. HTH -- Irwin Goldstein Objective Software Systems, Inc. http://www.objectsoft.com