production security

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..

Auditing
Logon trigger
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;
v_user VARCHAR2(240);
CURSOR c1 IS
SELECT osuser , sid, serial#, program as tool
FROM sys.v_$session
WHERE audsid=userenv('SESSIONID');
c1_rec c1%rowtype;
v_cnt number;
BEGIN
OPEN c1;
FETCH c1 INTO c1_rec;
CLOSE c1;
--
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');
COMMIT;
RAISE_APPLICATION_ERROR(-20000,'Illegal Logon recorded in audit trail!');
END IF;
END;