Re: How to prevent ODBC connection

Try the OpenLink multi-tier ODBC drivers (openlinksw.com). The client-side drivers interact with a server-side request broker. The request broker can be configured with a set of rules that accept or reject connection requests based on a combination of user name, application name, database name, client OS, client machine name (hostname), and a couple of other attributes. It can also force a read-only connection based on these attributes. For instance, once you find the application name that is passed by your third-party application (shown in the OpenLink log file), you could accept a connection request in read-write mode if and only if the user was using that application. If they tried to connect using Access (for example) you could either reject the connection or force it to read-only. Once the request broker accepts a connection request, it spawns a child process that connects to the database. You can specify environment variables that will be set in the child process, and you can even change the user-id of the child process, if you want. The one thing I'm not sure of is whether OpenLink will connect to the database using a network connection. I've not tried that. I believe that it should work just fine. For this to help you, though, you would have to hide your Informix listener ports so that none of your PC users knew which ports were used for the database server. Thus, /etc/services and sqlhosts files would have to be protected from prying eyes. If they know the ports, and if they get Intersolv (Merant) or Informix ODBC drivers, there is no way to prevent the database from accepting their ODBC requests. A side benefit of the OpenLink drivers is that they require very little set up on the client. > "El Nino" <ElNino@ucsd.com> wrote: > > Gabor Heppes wrote in message <7iisbh$6uh$1@nnrp1.deja.com>... > > >Hi All, > > > > > >My problem with ODBC is not how to make it work, rather how to prevent > > >smart users accessing the database via ODBC. Users in the database have > > >to be granted update/insert/delete access on most of the tables the app > > >works with, but I wouldn't want users to directly have access to these > > >tables. I guess this is a general client/server question, really. You > > >have no control as to what tools a user can install on their machine. As > > >our application uses polyserver and other daemon processes to access the > > >database, I though I could shut off the network connection and use only > > >shared memory. Unfortunately some of the processes make multiple > > >connections to the database, and you can't do that with shm connections. > > >Any suggestions, solutions? > > > > > >TIA > > > > > >-- > > >Gabor Heppes > > >IBM Global Services > > >gaborh@au1.ibm.com > > > > > > > > >--== Sent via Deja.com http://www.deja.com/ ==-- > > >---Share what you know. Learn what you don't.--- > > > > Hi Gabor, > > try using SET SESSION AUTHORIZATION and SET ROLE with ROLEs. > > You can "mask" these sql commands in yours 4gl (or client) code and > > if all database permissions are set well, no one can even see what > > tables are in the database. > > best regards, > > HZ > > > > > > We also suffer from this problem. We have many clued-up users who need to > have all database permissions in order for our third-party product to work > correctly. We are unable to use ROLES as this third-party product is written > in C, to which we do not have access (or indeed the skills). At the present > moment in time this is an outstanding issue for us - one with which I am less > than happy as the DBA. Mark Collins mcollins@us.dhl.com The computer's intermediation and commitment to its own, nanosecond rap beat are hardly the most promising aids for those who would regain real time.

In article <7ik7rv$a78$1@news.xmission.com>, Mark Collins <mcollins@us.dhl.com> wrote: Thanks for the replies! The OpenLink products look good, but unfortunately I don't think it would solve my problem. I don't need ODBC connection, and I must have IDS running network connection (because the third-party program uses multiple connections to the database). I don't think just not telling users what port the server is using is a protection good enough. I haven't used "set session authorization" a lot so far, but I think you need to have DBA privilege in order to use it, which I think just makes things worse. Using roles is generally a good idea, but again, once you've been granted a role, you can switch to that role using a front-end tool like MS-Access (using pass-thru SQL). What would be nice is a facility where I could grant a role to a user at the session level, after verifying that the user is indeed connected via the application he/she is authorized to be using. Because this role would be valid only for the session, it would prevent the same user connecting through another (non-authorized) application and setting the same role. Thanks > > Try the OpenLink multi-tier ODBC drivers (openlinksw.com). The [snip] > fine. For this to help you, though, you would have to hide your Informix listener > ports so that none of your PC users knew which ports were used for the database > server. Thus, /etc/services and sqlhosts files would have to be protected from > prying eyes. If they know the ports, and if they get Intersolv (Merant) or Informix > ODBC drivers, there is no way to prevent the database from accepting their ODBC > requests. > [snip] > > > Hi Gabor, > > > try using SET SESSION AUTHORIZATION and SET ROLE with ROLEs. > > > You can "mask" these sql commands in yours 4gl (or client) code and > > > if all database permissions are set well, no one can even see what > > > tables are in the database. > > > best regards, > > > HZ > > > > > > > > > > We also suffer from this problem. We have many clued-up users who need to > > have all database permissions in order for our third-party product to work > > correctly. We are unable to use ROLES as this third-party product is written > > in C, to which we do not have access (or indeed the skills). At the present > > moment in time this is an outstanding issue for us - one with which I am less > > than happy as the DBA. > > Mark Collins > mcollins@us.dhl.com > > The computer's intermediation and commitment to its own, nanosecond > rap beat are hardly the most promising aids for those who would > regain real time. > > -- Gabor Heppes IBM Global Services gaborh@au1.ibm.com Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

I'll take back my comments about the "set session authorization". It does look promising, provided you can get your third-party app to call a stored procedure at the beginning of a session. I just tried it! I don't have to grant DBA to the user, I just need a DBA procedure which will switch the user to another userid that has the necessary access set up. Of course somehow this proc should first verify that the user is connecting from the right app. create dba procedure give_me_authority() -- perform some checks -- eg. because we're using processes running on the -- server host, all connections to the database are from -- the host the database is on. -- Where a connection is originating from can be checked -- through sysmaster:syssessions (host field) if user_checks_out_OK then set session authorization to 'userid_with_access'; set role whatever_role; else raise exception -746,0,"Sorry buddy, not this time"; end if end procedure; The application then should exec this proc first thing after connecting to the database. Cheers In article <7ikqgl$juv$1@nnrp1.deja.com>, Gabor Heppes <gaborh@au1.ibm.com> wrote: > In article <7ik7rv$a78$1@news.xmission.com>, > Mark Collins <mcollins@us.dhl.com> wrote: > > Thanks for the replies! > > The OpenLink products look good, but unfortunately I don't think it > would solve my problem. I don't need ODBC connection, and I must have > IDS running network connection (because the third-party program uses > multiple connections to the database). I don't think just not telling > users what port the server is using is a protection good enough. > > I haven't used "set session authorization" a lot so far, but I think you > need to have DBA privilege in order to use it, which I think just makes > things worse. > > Using roles is generally a good idea, but again, once you've been > granted a role, you can switch to that role using a front-end tool like > MS-Access (using pass-thru SQL). What would be nice is a facility > where I could grant a role to a user at the session level, after > verifying that the user is indeed connected via the application he/she > is authorized to be using. Because this role would be valid only for the > session, it would prevent the same user connecting through another > (non-authorized) application and setting the same role. > > Thanks > [snip] -- Gabor Heppes IBM Global Services gaborh@au1.ibm.com Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.