RE: No ODBC access to Informix...
The poster wanted to stop users bypassing his ERP's business logic by querying the Solaris Informix database directly via MS Query/ODBC, asking whether a 'listener' could be configured to refuse ODBC connections, ideally running a separate reporting instance. Replies argued the real fix is database-level privileges (revoke update/insert/delete, let the app connect as a privileged alter-ego user, use read-only views or a refreshed report instance). Art Kagel noted the listener is just an engine thread and can't be made read-only, and Jonathan Leffler explained sqlhosts is client-side configurable (INFORMIXSQLHOSTS, DSN-less connections), so emptying it is only security through obscurity. No way to block ODBC specifically was found; the thread ends without the poster accepting a solution.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I have the same situation here. I have set up views to my production database with read-only access to the data. The ODBC configuration for the users PCs are then configured to look at those read-only views. Of course any user that knows how could just change their ODBC config to point to whichever database they want. Our NT admins tell me there is no way to secure the ODBC configuration. I also run a script on my DB server to check that the only users connected to my production database are those from our application server. I also have a report instance which is refreshed daily at 0700. The purpose of this instance is not for security, although it is read-only. Management wanted a static instance of the database to run reports from so that everyone who ran a report would have the same data even if not generated at the same time. As far as I know there is no way to roll the logs forward daily to refresh this report instance. I do a physical restore from my daily level 0 each morning and then roll the logs forward to my point-in-time of 0700. Regards, Bill > -----Original Message----- > From: wolf.duttlinger-manger@gmx.de [SMTP:wolf.duttlinger-manger@gmx.de] > Sent: Monday, November 08, 2004 9:36 AM > To: informix-list@iiug.org > Subject: No ODBC access to Informix... > > Hello all, > > after googling I saw that the same discussion came up in 1997/98 - but > did not lead a satisfactory result for me.... > > Situation: > We have some kind of a pre-fab ERP solution in house. From any PC a > telnet session is used to connect to the Solaris and to start the > application. > > The application uses the Solaris user ID to identify itself to the > database - and hence there are certain rights, that those users have. > E.g. a user may well have the right to change a record using the > application interface's business logic - BUT may not have the right to > change a row arbitrarily. > > So far reporting has been done using MS Query/ODBC. Of course any user > could simply go in MS Query and generate any SQL code - basically > allowing him/her to circumvent the application's business logic.... > > I do not know Informix - but from my understanding there must be a way > to totally disable ODBC access towards this database. I know a little > about DB2 and Oracle and from there I have the following picture: > > - every box may have one or more instances of a database "engine" > - every engine may manage one or more databases > > Is it like this with Informix (5.01)? Is there a "listener" per > engine, that can be tweaked the way I want it? > > We want to create a "report" DB - either on a seperate machine or - > preferably - on the same machine, where MS Query access would be > allowed.... > > So my ideal picture looks like this: > - one Solaris box > - two instances of Informix loaded > --- one w/o ODBC access > --- one w/ ODBC access > - every instance managing one DB > - applying the "redo"-logs of the w/o ODBC engine towards the other DB > gives a time-shifted "copy" of the production DB. > > Does this make sense at all????????????? > > Any postings appreciated! > > Regards > Wolf Duttlinger-Manger sending to informix-list
On Mon, 08 Nov 2004 14:00:45 -0500, Bill Dare wrote: WAIT just one minute there cowboy! Why not just disable the user's update/insert/delete permissions on the database itself! The 'business' app can log into the database on the users' behalf using a different ID which is priveleged to modify the data. If doing that with a single login for all, then create another small read-only database that maps real user-id and password to a unique alter-ego user-id which will be used to connect to the DB server. Art S. Kagel >> I do not know Informix - but from my understanding there must be a way to >> totally disable ODBC access towards this database. I know a little about >> DB2 and Oracle and from there I have the following picture:
Yeeeeha!!!! Peng, peng!!! <G> I totally agree to this Art - only - the ERP is there and it works the way it works... i.e. the users log on to the DB using their! uid - and therefore have to have the rights they have..... So - also to the others that posted - I do not see a solution - "hiding" a database is no solution - we have to mitigate the risk of a user changing the data outside the ERP..... Again - please does anybody have an idea how to block ODBC access to a Solaris based Informix *** at all *** - i.e. I do not want to allow _ANY_ ODBC calls to be coming in to this box - somehow configuring a "listener" - if there is such thing in Informix...... Regards Wolf "Art S. Kagel" <kagel@bloomberg.net> wrote in message news:<pan.2004.11.08.15.48.49.861051.1355@bloomberg.net>... > On Mon, 08 Nov 2004 14:00:45 -0500, Bill Dare wrote: > > WAIT just one minute there cowboy! Why not just disable the user's > update/insert/delete permissions on the database itself! The 'business' app > can log into the database on the users' behalf using a different ID which is > priveleged to modify the data. If doing that with a single login for all, > then create another small read-only database that maps real user-id and > password to a unique alter-ego user-id which will be used to connect to the DB > server. > > Art S. Kagel > > >> I do not know Informix - but from my understanding there must be a way to > >> totally disable ODBC access towards this database. I know a little about > >> DB2 and Oracle and from there I have the following picture:
On Tue, 09 Nov 2004 05:03:31 -0500, Wolf wrote: A listener in IDS is just a thread in the engine so there's no way to replace it with one that only allows read-access. Art S. Kagel > Yeeeeha!!!! > > Peng, peng!!! > > <G> > > I totally agree to this Art - only - the ERP is there and it works the way > it works... i.e. the users log on to the DB using their! uid - and therefore > have to have the rights they have..... > > So - also to the others that posted - I do not see a solution - "hiding" a > database is no solution - we have to mitigate the risk of a user changing > the data outside the ERP..... > > Again - please does anybody have an idea how to block ODBC access to a > Solaris based Informix *** at all *** - i.e. I do not want to allow _ANY_ > ODBC calls to be coming in to this box - somehow configuring a "listener" - > if there is such thing in Informix...... > > Regards > Wolf > > "Art S. Kagel" <kagel@bloomberg.net> wrote in message > news:<pan.2004.11.08.15.48.49.861051.1355@bloomberg.net>... >> On Mon, 08 Nov 2004 14:00:45 -0500, Bill Dare wrote: >> >> WAIT just one minute there cowboy! Why not just disable the user's >> update/insert/delete permissions on the database itself! The 'business' >> app can log into the database on the users' behalf using a different ID >> which is priveleged to modify the data. If doing that with a single login >> for all, then create another small read-only database that maps real >> user-id and password to a unique alter-ego user-id which will be used to >> connect to the DB server. >> >> Art S. Kagel >> >> >> I do not know Informix - but from my understanding there must be a way >> >> to totally disable ODBC access towards this database. I know a little >> >> about DB2 and Oracle and from there I have the following picture:
Hi there, I just was informed that providing an empty 'sqlhosts' file would prevent anybody from accessing the box. Now - in sqlhosts a "nettype" and some "options" are entered. Are there any chances to narrow down the scope of "neglection" using these parameters? Like - nettype not including "ODBC-net access", or options stating only read-oinly access.... Next - the 'sqlhosts' file - is it one per box, or one per "instance" - i.e. would it be possible to install two dbm-instances on one machine with two different sqlhosts files? (I know RTFM - but - I don't have them, and I basically do not have time to become a Informix admin.....) Regards Wolf wolf.duttlinger-manger@gmx.de (Wolf) wrote in message news:<6e942e26.0411090203.1f43e57d@posting.google.com>... > Yeeeeha!!!! > > Peng, peng!!! > > <G> > > I totally agree to this Art - only - the ERP is there and it works the > way it works... i.e. the users log on to the DB using their! uid - and > therefore have to have the rights they have..... > > So - also to the others that posted - I do not see a solution - > "hiding" a database is no solution - we have to mitigate the risk of a > user changing the data outside the ERP..... > > Again - please does anybody have an idea how to block ODBC access to a > Solaris based Informix *** at all *** - i.e. I do not want to allow > _ANY_ ODBC calls to be coming in to this box - somehow configuring a > "listener" - if there is such thing in Informix...... > > Regards > Wolf > > "Art S. Kagel" <kagel@bloomberg.net> wrote in message news:<pan.2004.11.08.15.48.49.861051.1355@bloomberg.net>... > > On Mon, 08 Nov 2004 14:00:45 -0500, Bill Dare wrote: > > > > WAIT just one minute there cowboy! Why not just disable the user's > > update/insert/delete permissions on the database itself! The 'business' app > > can log into the database on the users' behalf using a different ID which is > > priveleged to modify the data. If doing that with a single login for all, > > then create another small read-only database that maps real user-id and > > password to a unique alter-ego user-id which will be used to connect to the DB > > server. > > > > Art S. Kagel > > > > >> I do not know Informix - but from my understanding there must be a way to > > >> totally disable ODBC access towards this database. I know a little about > > >> DB2 and Oracle and from there I have the following picture:
Wolf wrote: > I just was informed that providing an empty 'sqlhosts' file would > prevent anybody from accessing the box. > > Now - > in sqlhosts a "nettype" and some "options" are entered. Are there any > chances to narrow down the scope of "neglection" using these > parameters? Like - nettype not including "ODBC-net access", or options > stating only read-oinly access.... Nope. There are a few options, but ... There are logically two sqlhosts files - the one used by the client and the one used by the server - though they're often the same file. You can control the one used by the server more carefully and fully than you can the one used by the client. > Next - > the 'sqlhosts' file - is it one per box, or one per "instance" - i.e. > would it be possible to install two dbm-instances on one machine with > two different sqlhosts files? Normally, one per INFORMIXDIR, or one per box. But anybody can provide their own for the client side - set INFORMIXSQLHOSTS in the environment on Unix. ODBC provides for DSN-less connections, and the DSN corresponds to an sqlhosts entry. No - empty sqlhosts files are not a reliable control (security through obscurity at best). > (I know RTFM - but - I don't have them, and I basically do not have > time to become a Informix admin.....) > > Regards > Wolf > > wolf.duttlinger-manger@gmx.de (Wolf) wrote in message news:<6e942e26.0411090203.1f43e57d@posting.google.com>... > >>Yeeeeha!!!! >> >>Peng, peng!!! >> >><G> >> >>I totally agree to this Art - only - the ERP is there and it works the >>way it works... i.e. the users log on to the DB using their! uid - and >>therefore have to have the rights they have..... >> >>So - also to the others that posted - I do not see a solution - >>"hiding" a database is no solution - we have to mitigate the risk of a >>user changing the data outside the ERP..... >> >>Again - please does anybody have an idea how to block ODBC access to a >>Solaris based Informix *** at all *** - i.e. I do not want to allow >>_ANY_ ODBC calls to be coming in to this box - somehow configuring a >>"listener" - if there is such thing in Informix...... >> >>Regards >>Wolf >> >>"Art S. Kagel" <kagel@bloomberg.net> wrote in message news:<pan.2004.11.08.15.48.49.861051.1355@bloomberg.net>... >> >>>On Mon, 08 Nov 2004 14:00:45 -0500, Bill Dare wrote: >>> >>>WAIT just one minute there cowboy! Why not just disable the user's >>>update/insert/delete permissions on the database itself! The 'business' app >>>can log into the database on the users' behalf using a different ID which is >>>priveleged to modify the data. If doing that with a single login for all, >>>then create another small read-only database that maps real user-id and >>>password to a unique alter-ego user-id which will be used to connect to the DB >>>server. >>> >>>Art S. Kagel >>> >>> >>>>>I do not know Informix - but from my understanding there must be a way to >>>>>totally disable ODBC access towards this database. I know a little about >>>>>DB2 and Oracle and from there I have the following picture: -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
wolf.duttlinger-manger@gmx.de (Wolf) schrieb: >Again - please does anybody have an idea how to block ODBC access to a >Solaris based Informix *** at all *** - i.e. I do not want to allow >_ANY_ ODBC calls to be coming in to this box - somehow configuring a >"listener" - if there is such thing in Informix...... Wolf, you seem to have a misconception about the way ODBC works.