"Tim Kearsley" <tim.kearsley_at_milton-keynes.gov.uk> wrote
> 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.
>
Oracle already provides for this using non-default roles. You assign the
role to the user but don't make it a default role, so by default when he
logs in the database he doesn't have the privs associated with the role. You
can even password protect it. Then, in your Forms app, enable the role *for
the forms session only*(this is in the code, so is invisible to the user).
Grant all the access you need to this role, as, no matter what the users are
accessing the database with, they won't be able to use the privs associated
with this role except via your app.
The one trick is when calling reports, since this opens a separate session.
Either use a separate role that is a default role for 'SELECT' privileges
(assuming it is ok to give everyone selects via separate tools) or add a
parameter to the report call to enable the role.
It works flawlessly.
Received on Tue Jul 15 2003 - 15:07:52 CDT
