odbc connections and database security

[bgill]

Hi,
here is my dilemma. I have a database with hundreds of users. The database is oracle 8.1.7.2 on solaris 8. The database runs a financial application (banking). All the users connect to oracle through the application. All users have insert update and delete privileges on the main schema tables as they need these to do their jobs (posting + reversing transactions, amending names addresses etc.)
Any changes or updates made are audited within the application, and users are restricted to what they can do by the application's own menu security system, so I have no problems with security at this level.
The problem I have is with ms access databases. Every user has ms office on their pc. It appears that anyone who can set up an odbc connection to the production database, and connect using their own username and password can basically do any damage they want, delete whole tables, change data etc without any trace. We have a couple of access databases in the IT dept that we need to be able to connect to the production db, and we wont allow anyone else connect like this. ( cd's and floppy drives are locked down on all user pc's and odbc functionality has been removed also, except on certain IT dept pc's. But i'm still uneasy about this.
Is there any way in oracle (or unix) that odbc connections can be restricted (say only to specific ip addresses, oracle users or groups)? If not can they be logged or monitored in any way?
Any help or suggestions greatly appreciated.

 

[JtheRipper]

Hi there,

If the user has a database username and password it can basically connect from anywhere using sql gold, excel, ms access, etc.

I would have a look at creating an "after logon" trigger to capture the username, times, program(from v$session) etc of users that logon to the database. Just beware this might put a load on your database if you have an app or process making zillions of connections to your database every minute.

Also have a look at using profiles to limit the users to only 1 database session.

You can also configure the listener to do logging by setting the log_status to on and configuring the log_directory_listener and log_file_listener names in the listener.ora file. It does not pick up all the connections but it picks up connection from excel, ms sql server, etc.

Hope this helps.
J.

 

[DBAwhosaysNIE]

are the ODBC drivers required by the application? if not just strip them off everyone's workstation and use policy editor to prevent users from installing them.

if so can you modify the application to abstract the Oracle DB password(s) from the user's application password? as long as users have actual Oracle DB passwords your're going to be fighting an uphill battle. you might be able to su something with Advanced Security but I believe that's licensed separately.

 

[bgill]

Thanks for the replies guys.
Just to update. Yes I can monitor connections from the listener log and am working on a way to get notified automatically if a connection is made to the database other than via the application. We have already stripped odbc from all workstations and I am having the tech support team look at the policy editor to prevent anyone installing odbc.
Unfortunately at the moment the application username must be the oracle username for each user. I will probably have to talk with the vendors and raise this issue with them, but it is an interesting idea.
As far as an after logon trigger is concerned I will definitely be exploring this further. Would it be possible to create a trigger that will terminate a connection immediately if it is not made through the application?
Thanks again for your input.

 

[DBAwhosaysNIE]

OOH! OOH! Mista Carter!

this would be a REALLY ugly hack BUT...:

you could create an after logon trigger that enqueues a message (AQ) with sid & serial# then write a job which dequeues it , kills the session if not from the app or grants object privileges if it is. write a before logoff trigger with a queue & job to revoke object privileges and you're in business.

pretty draconian but should also be pretty effective.

happy nuking!