Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Restricting user access to a database?
Tim Kearsley <tim.kearsley_at_milton-keynes.gov.uk> schreef in berichtnieuws
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
Best solution would be not to install Oracle Net on the client pc's. Looks like this is impossible for you because you have a "classic" two-tier setup: Forms runs on the clients.
Some proposed to use a logon trigger and deny access based on
v$session.program. From experience I know this will not work. The column is
only 64 characters long. The total path to the program name is in it, big
change you miss the real program name. I have seen this when Word or Access
connect to the db using ODBC.
And when someone renames toad.exe into kwak.exe you will see kwak.exe in
v$session.program.
SQL*Plus is more intelligent and really sets sqlplus in v$session.program -
but I am not totallly sure about this. Test it. If so v$session.program
could be tested in a logon trigger to deny/allow access.
A not so known feature of sqlplus is the product_user_profile table. See the
SQL*Plus User Guide and Reference, Appendix E: Security (for Oracle V8.1.7).
Using this table you can arrange that certain users can no longer use
certain commands, like commit.
Again, this is only a solution for SQL*Plus, not for ODBC access.
In addition to the other ideas I would propose to use dbms_application_info package. It should be called from every form in the Form startup trigger (and Report when used). It can set v$session columns module, action, client_info. So, a logon trigger could only allow logins when client_info has a certain value. Yes, it requires *each* form to be modified. Tip: set module to the name of the form. I worked in an environment once where this was done and it was gold. When there was a performance issue or a locking problem the DBA could easily say which Forms program was running for that session and developers could focus quickly. Very very productive! But this only works when ALL allowed forms are under your control......
We have one database some endusers want to access directly from Word or
MS-Access using ODBC. I've build a on-logon trigger that checks the
combination of machine, OS-user and Oracle user against an allowed
combinations table. If not, an error is raised and the login fails.
There are a few problems with this. When a user has the dba role he always
gains access. Instead the raised error is logged to the alert.log, When it
is monitored for errors this can be very anoying.
Make sure dbms_job sessions have access. Because they are initiated by a
background process there audsid=0. You can't test reliable on
v$session.audsid in the trigger.
When the trigger becomes invalid for some reason only a connect internal on
the database server itself is possible. Then disable the trigger (or make it
valid) to make any (remote) login possible again.
Good luck. Only you can evaluate the proposed ideas and see what fits in your organisation. Received on Tue Jul 15 2003 - 16:31:54 CDT