How to prevent ODBC connection

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.---

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

In article <7ij928$2re$1@bagan.srce.hr>, "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. Regards Glyn Balmer BICC Cables Limited Erith Kent UK -- If it always works, why don't parachutists pull the emergency 'chute first? --== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.---

Gabor Heppes wrote: > 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? This is a common plaint within ODBC and is not catered for in the ODBC spec. However, some ODBC driver vendors have covered this with a security module which allows you to restrict access by user, SQL, application name etc etc. We do this. You could try our ODBC driver: SCO SQL-Retriever. Take a look at http://www.sco.com/vision/products/sqlretriever/ for more information and a downloadable eval. Also http://www.sco.com/support/ciservices/sqlr/docs/secman.html for more details on the security aspec. Allan Gould SCO CID Support, Leeds, UK (allang at sco dot com) (Please remove anti-spam measures if replying)

Gabor Heppes wrote: > > 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? OK I'm going to throw my hat into the ring on this one. I see three problems each feeding on the next so we eliminate them one at a time: o I need to prevent smart dummies from loading an ODBC driver on their PC and accessing the database without authorization. Solution: Remove permissions from ALL users and PUBLIC and only grant dangerous (your definition) permissions to a single dummy user (ie a user with a valid login but no password). o BUT users need permissions so they can run apps that access the database and modify data! Solution: Make all legal apps SUID the dummy user. Only these apps will be able to do anything you want to restrict. o Ahh, you know, I could do this by eliminating network connections but I need multiple connections so shared memory does not work! Solution: Use onipcstr, stream pipes, which permit multiple connections but do not allow remote access. The only drawback is the any 4GL 4.xx or 6.xx programs do not support stream pipe connections so you will have to upgrade them to 4GL 7.20 and recompile. Art S. Kagel