Re: Restricting user access to a database?

tim.kearsley_at_milton-keynes.gov.uk (Tim Kearsley) 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

1. use a firewall and block the port that sqlplus uses to access the database. I forget what it is. only let people from certain IP addresses get in this way. Namely your developers and DBAs
2. look at the program column in v$session. When a user connects via sqlplus it gets listed here. You will need to substr(instr)) to trim off the rest. Have a context in the database, that does not allow users to see any data when logged in via SQLPLUS. Its pretty easy. Or you can reverse it and only allow users to access data via the application. Look up Virtual Private Database on OTN. Its very easy to use. You could do the same thing by just granting and revoking priviledges. Have a logon trigger that checks to see what application is being used to login. If its not your forms, revoke all priviledges and if it is a form grant all priviledges.