How can I identify the user connected to IJC from Oracle

Good afternoon

Someone has accidently deleted more data than expected from the IJC database. I can't remove the 'delete' privilege on the table, as they will occasionnally need to delete data

I would like to implement an 'audit trigger' (see example below) on the Oracle table, when a row is being deleted

http://www.techonthenet.com/oracle/triggers/before_delete.php

I can find out who is currently logged in to IJC using

selectusername 
fromLAB_CHEM.IJC_USER 
wherelogin_time is not null;

But if several users are connected at the same time, is there a way to find out where the delete command has been issued from ?

Thanks

Catherine

Hi Catherine, some how you would need to tie into query like below :

https://forums.oracle.com/forums/thread.jspa?threadID=845602

I cannot see a way to join easily to this from current DESC ijc_user.

The only advice I can say is that you would need to search directly in Oracle tables and perhaps try and use a DATE / TIME column to attempt to match - I would say tricky !

Hi Catherine,

The normal approach is that a single Oracle user is used for all IJC users. This does not allow you to differentiate between the different users at the Oracle level.

It is possible to use an Oracle user as your IJC user, so that ewach user has a differnet Oracle account. This is described here:

http://www.chemaxon.com/instantjchem/ijc_latest/docs/user/help/htmlfiles/security/security_templates.html#db_auth

Though my understanding is that you are using Active Directory authentication, so wouldn't be able to do this?

Tim