production security

[netbar]

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

[stecal]

Auditing
Logon trigger
Send email on logon

[bgill]

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.

[gopi]

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;