I suspect ordinary users know the production password from a user with DBA privilege, bad application design I know but it is historical before I arrived on site.
Therefore what is the best way I can identify who is logging in as this user! machine name e.g obviously v$session but I want to know if anyone has implemented anything similar. I can of course change the password but I want to catch the sneaky buggers red handed!
Thanks in advance..
Send email on logon
Do you have TOAD on your site?
If you do you can use the kill/trace session facility to see who is logged on, which terminal they are logged on at, their o/s userid, and even what sql statements they are executing.
This is what I use to protect my application schema from invalid logons - however if the culprit is sneaky enough to change his O/S logon on his PC to hack, I suppose he/she could - but he/she does not know enough to do so and may have problems with our local network if they change their o/s name on their pc.
Also, even if someone hacks, it is audited in the table I create called system.xaud table.
Hope this helps.
CREATE OR REPLACE TRIGGER system.wmsowner_logon_trigger
AFTER LOGON ON wms_owner.schema
DECLARE PRAGMA AUTONOMOUS_TRANSACTION;
CURSOR c1 IS
SELECT osuser , sid, serial#, program as tool
FETCH c1 INTO c1_rec;
SELECT user INTO v_user FROM dual;
IF lower(c1_rec.osuser) NOT IN ('oracle','oracle_app','javaapp') THEN
INSERT INTO system.xaud(a_date, osuser, db_user, tool, SID, notes)
VALUES (sysdate, c1_rec.osuser, v_user, c1_rec.tool, c1_rec.sid, 'LOGON Denied - Session Killed');
RAISE_APPLICATION_ERROR(-20000,'Illegal Logon recorded in audit trail!');
Click Here to Expand Forum to Full Width