Re: Can updates via ODBC be disabled while retrievals are allowed?

Mitchell Nussbaum <nussbm_at_dnr.state.wi.us> wrote in article <01bce31a$bf0434b0$1705eca8_at_im8020>...
> Some of my users have Oracle tables that are updated via PowerBuilder
> applications. The managers of these applications are thinking of
> installing ODBC and MS Access on their users' workstations, to allow them
> to generate ad hoc queries and reports against these tables.
>
> They are concerned, however, that some of these users will try to use
> Access to update the Oracle tables, circumventing the data-integrity
 checks
> that are built into our PowerBuilder applications. Can anyone suggest
 ways
> of cutting off this end-run of our system? In particular, is there are
 way
> to make an Oracle ODBC data source read-only, or to configure the Oracle
> Server (we are running 7.1.5 under Open VMS for AXP) to reject update
> transactions that come via ODBC?

There's no way to make ODBC read-only AFAIK. However there are some read-only ODBC drivers available on the market but I have not yet came across an Oracle one. The only other method I can think of is a bit dirty but should work.

Create an Oracle userid for MS-Access users that only have connect and select priviliges. Run a little background SQL job on the server every 10 or 20 seconds that checks the PROGRAM column in V$SESSION. If that matches the MS-Access program name and the SCEMENAME is not the valid Oracle user for MS-Access users, log the session details and then alter system and kill that session.

Then come down like a ton of bricks on users who do not use the right Oracle userid with MS-Access and scare the livin' daylights out of them. :-)

You can also use the PROGRAM column method in triggers in the database to protect tables against changes from unauthorised client programs, but this will likely cause additional programming and maintenance overheads.

regards,
Billy Received on Tue Oct 28 1997 - 00:00:00 CST