Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to capture which App Server's old password (connect to oracle) hasn't still been updated?

Quanwen ZhaoJul 1 2020 — edited Jul 3 2020

Hello, my ODC friends ,

A couple of days ago received a notice from the security department, we have been replaced with new PASSWORD from production user with all of our Oracle DB. At the same time, we also asked for our Dev colleagues to update their App Servers (who is connecting to oracle db) to be new password.

Afterwards they gave us a feedback about almost all of App Servers' password has been updated. But now we're observing the situation (97.8% session connect and disconnect and 92.4% Shared Pool Latches) from Oracle EMCC platform.

Session Connect and Disconnect.png

Hence we wrote a trigger detecting login failed/denied from real IP (which App Server) meanwhile writing the corresponding message to ALERT log file.

create or replace trigger logon_denied_to_alert

after servererror on database

declare

message varchar2(120);

IP varchar2(15);

v_os_user varchar2(80);

v_module varchar2(50);

v_action varchar2(50);

v_pid varchar2(10);

v_sid number;

v_username varchar2(50);

v_suser varchar2(50);

begin

IF (ora_is_servererror(1017)) THEN

if sys_context('userenv', 'network_protocol') = 'tcp' then

 IP := sys\_context('userenv', 'ip\_address');

else

 select distinct sid into v\_sid from sys.v\_$mystat;

 SELECT p.SPID

   into v\_pid

   FROM V$PROCESS p, V$SESSION v

  WHERE p.ADDR = v.PADDR

    AND v.sid = v\_sid;

end if;

v_os_user := sys_context('userenv', 'os_user');

v_username := sys_context('userenv', 'CURRENT_USER');

v_suser := SYS_CONTEXT('USERENV','SESSION_USER');

dbms_application_info.READ_MODULE(v_module, v_action);

message := to_char(sysdate, 'Dy Mon dd HH24:MI:SS YYYY') ||

          ' logon denied from '||v\_username||' '||v\_suser||' '|| nvl(IP, v\_pid) || ' ' || v\_os\_user ||

          ' with ' || v\_module || ' ' || v\_action;

sys.dbms_system.ksdwrt(2, message);

end if;

end;

This approach is very nice. Immediately we found an App IP address and then told Dev colleague. Finally he also updated the password.

-- alert_xxxx.log

......

Thu Jul 02 09:51:50 20201

Thu Jul 02 09:51:50 2020 logon denied from SYS 10.1.0.8 with

Thu Jul 02 09:51:50 2020

Thu Jul 02 09:51:50 2020 logon denied from SYS 10.1.0.8 with

Thu Jul 02 09:51:50 2020

Thu Jul 02 09:51:50 2020 logon denied from SYS 10.1.0.8 with

Thu Jul 02 09:51:51 2020