Re: Restricting ODBC access

howdy This does not answer all your questions, nor all the questions that the = whole issue of ad-hoc database access leads to, but it is a practical = solution in place here and it seems to work. This solution requires the use of an ODBC driver that supports security = features. We use Openlink which you can download from www.openlinksw.com. = (Non expiring 2 user eval). This is how I do it. 1. I have created an end-user-computing (euc) instance of Online on the = same host as the production instance. 2. In this instance are databases named euc_[LiveDatabaseName]. 3. Nightly, cron runs a short script that drops all views in the above and = rebuilds them on the basis of the structures in the live production = databases. These are read-only views on the live system. 4. Specific application access is allowed to the production system for = specific users using the openlink security features. Access to the euc = instance is open. No-one has I-Net (or it's equivalent these days). Only openlink, which is = an open driver. If a user tries to access the production instance via ODBC and if they are = not an authorised user and application, they are rejected by Openlink. Access to the euc instance is open for anyone with connect permission, but = the views are read-only. =20 There are a few gotchas, people have had to learn to be wise in how they = structure their selects as remote joins etc can come into play if they are = creating temp tables in euc. But on the whole it has been very successful. We are in our third year of = doing this., and it is practical and works. It would not stop a determined attack by a knowledgeable end-user but it = does stop the kind of problem you are referring to. I can post the script which creates the views if you like. rgds Peter Tashkoff <tashkop@iname.com> Zespri International Limited. Standard Disclaimers apply All rights reserved, no party may use this document to vilify another Zespri New Zealand Kiwifruit, The Worlds Finest >>> Jacob Salomon <jake@garpac.com> 12/02/98 11:22:41 >>> Hi Family. I have a client with a serious concern. Say I (jake) am a regular end user - when I log in to my Unix box, I fall into a captive session, the application's main menu (no shell). In this way I can hit at the database only through the application. BTW, database access happens to be through a shared memory connection, although I suppose socket access should also be set up for this system. Now I get clever - the greatest nightmare of an operations manager. I go back to my PC and access the database via an Excel spreadsheet with ODBC. I run a query and get the results in my sheet. I then update some rows in the sheet (for what-if analysis - innocent) and inadvertently save the data back to the database. Whoops! I have just messed with some data without going into the application. Can anyone come up with a way to prevent database access via ODBC? I can't simply deny ODBC drivers to all PC users - there are other databases to be accessed. The restriction I need should be settable on a database by database basis. (Say that fast 10 times. ;-) Ideally, it should be able to restrict data modifications via ODBC. The one idea I came up with I really don't like: Give everyone different user-id's on the Unix box and on their PC's. Since the PC-based user ID has not been granted priveleges, the database is protected. Now I have noted that whe I access the database from Relational Object Manager (where does Informix come up with these silly product names?) and run onstat -u, my PC session shows up as user JAKE while a regular unix based session shows up as user jake. Might this be suffucient to prevent updates via ODBC connections? I can't test this myself because I have no ODBC drivers on my PC. Any better ideas? Thanks. --=20 -- Jake (Pondering the color of an asphyxiated smurf) +------------------------------------------------------------+ | The expedient performance of a task with excessive concern | | regarding its duration-to-completion engenders a virtual | | certainty of diminished benefit therefrom. | | -- Benjamin Franklin (but he said it in 3 words) | +------------------------------------------------------------+