Re: What driver is connecting to my database?

From: Ben <balvey_at_comcast.net>
Date: 8 Sep 2006 07:49:18 -0700
Message-ID: <1157726957.983695.78200@h48g2000cwc.googlegroups.com>

EdStevens wrote:
> Ben wrote:
> > Sybrand Bakker wrote:
> > > On 7 Sep 2006 13:19:45 -0700, "Ben" <balvey_at_comcast.net> wrote:
> > >
> > > >Oracle 9.2.0.5 Ent Ed AIX5L.
> > > >
> > > >Is it possible to determine the driver / type of connection that is
> > > >being used by different users to connect to the database.
> > > >Our ERP system came with an ODA driver that is read only. If any of our
> > > >users want to use MS Access to connect to our database they are
> > > >supposed to use that driver with linked tables. We have had some data
> > > >corruption lately with some rows being updated and some being deleted.
> > > >No one is supposed to have the Ora92 driver to avoid this situation,
> > > >but we are wondering if someone is using the Oracle supplied driver.
> > > >How can I figure this out?
> > > >
> > > >Thanks
> > >
> > > You are asking the wrong question. You should ask: why didn't I
> > > enforce integrity *in my database* so no one can create havoc, and we
> > > don't have to resort to tricks like using a driver which is supposedly
> > > 'readonly' (which doesn't exist of course).
> > >
> > > --
> > > Sybrand Bakker, Senior Oracle DBA
> >
> >
> > Sorry, I didn't design the database. I just took it over when my
> > company laid the only DBA off. Every environment in this World isn't
> > perfect. There being, yes, sometimes you have to do work arounds to
> > accomplish something. Our database environment is a mess, I can't help
> > that, all I can do is try to improve it, and that is what I'm doing.
> > And the JDE ODA odbc driver is supposedly read only, if it is not then
> > JDE/Peoplesoft/Oracle have some explaining to do.
> > I just asked a question, I have read ( and appreciate) your information
> > that you give Sybrand. Can you help me out here, or is there not a way
> > of doing this?
> > Thanks,
> > Ben

>

> There is no way that I know of to do the specific thing you are asking
> - to enforce an interface driver to take care of your security. Sure,
> some drivers have a 'read-only' property that can be turned on. And it
> can just as easily be turned off. And who has control of the setting
> of that property? Probably not the DBA.

>

> The key is in your statement "If any of our users want to use MS Access
> to connect to our database they are SUPPOSED to use that driver with
> linked tables." (emphasis mine). And if they choose not to use that
> driver? And if they choose to use the Oracle or MS driver, and not
> turn on the 'read-only' attribute?

>

> This really does need to be handled in the DB itself. Previous DBA
> didn't do it? Now that you're the DBA (regardless of your title) you
> can and should fix it correctly. That would be to revoke any
> insert/delete/update capability from the userid making the connection.

>
>

> Are these users using a single Oracle userid, or do each of them have
> their own? Either way, if they are using a userid that elsewhere has a
> legitimate need to be able to modify data? If not, simplly revoke all
> privileges except CREATE SESSION and object privilge of READ on the
> specific tables they need.

I agree fully, and I have started trying to migrate to a better security design. When I started working with it, we had two base users that connected through the ERP. One had access to all tables in our ERP schema and the other was supposed to be restricted and not allowed to view our HR tables. The users were given individual object grants and roles, but it was just a mess, with some of the objects being granted to both users and the roles and some being only to the roles and one user, etc. I've been trying to clean it up.

I do understand that those users that are connecting should have restricted access to tables. I guess I was more curious in wanting to find out how many users are actually connecting via the Ora92 driver/connection and how many were using the ERP ODA.

Thanks for all the input, once again. Received on Fri Sep 08 2006 - 09:49:18 CDT