Re: Restricting user access to a database?

"Tim Kearsley" <tim.kearsley_at_milton-keynes.gov.uk> wrote in message news:725736ef.0307150247.72d6f12e_at_posting.google.com...
> Hi all,
>
> We have a situation here where a number of users access an Oracle
> 8.1.7.2 database running on AIX 4.3.3 on RS6000 hardware. The clients
> use PCs running an Oracle Forms application on Windows 2000.
>
> Suddenly, out of the blue, management has got worried about users
> making "unauthorised" access to the database by running SQLPlus and
> issuing their own queries (or updates, inserts etc.). I've therefore
> been asked as to how users' access can be restricted to just using the
> application.
>
> I have responded initially by making the point that if the client PCs
> have suitable ODBC drivers installed (and I believe they do) then
> access could be by a whole range of applications - Microsoft's Word,
> Excel and Access are obvious candidates.
>
> So, the question:
>
> Do you see any way of restricting the users to only accessing the
> database through the "authorised" application? I don't believe
> de-installing ODBC and SQLPLus on every client is an option and I
> don't see how anything can be done at the server end. After all, a
> SQLNet connection is all the database sees and I presume it doesn't
> "know" whether that connection originates from a Forms application or
> SQLPlus or anything else?
>
> Any thoughts very welcome.
>
> Regards,
>
> Tim Kearsley
> Database Manager
> Milton Keynes Council

Tim,

Sorry for the rant, but here's a different viewpoint. Make of it what you will.

IMHO, you're coming at this from *completely* the wrong direction, in a 2 tier app.
Anyone who is trying to 'protect' the database from being accessed from SQL*Plus, TOAD.
M$Access, ODBC or whatever is barking up the wrong tree entirely. What it means is that the database security is inadequate, and that too much trust has been placed in 'the app' to protect the database.

Design the database and its API properly. No direct table access; business transactions are controlled by packages/stored procedures. Then the database neither knows nor cares whether it is being accessed from 'the app', SQL*Plus, M$Access or anything else. If a properly authenticated database connection can call the right procedure with valid parameters, it will succeeed. Otherwise not.

And yes, I do live in the real world with crappy third party apps which we cannot control, and which leave the front door wide open. And in those cases logon triggers can help a little. It's weak, but it's better than nothing, I suppose.

Regards,
Paul Received on Mon Jul 21 2003 - 14:14:16 CDT