Oracle Trigger to Log Logon Denied ORA-1017 Error into Alert Log

 

create trigger logon_denied_to_alert

after servererror on database

declare

message varchar2(4000);

begin

select 'ip='||sys_context('userenv','ip_address')||',host='||sys_context('userenv','host')||',osuser='||SYS_CONTEXT ('USERENV', 'OS_USER')

into message

from dual;

IF (ora_is_servererror(1017)) THEN

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

sys.dbms_system.ksdwrt(2,message);

end if;

end;

/


===


And to view where the trace file is:


G:\oracle\diag\rdbms\syp\syp\trace>sqlplus / as sysdba


SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 14 12:09:14 2020


Copyright (c) 1982, 2017, Oracle.  All rights reserved.



Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> show parameter background


NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

background_core_dump                 string      partial

background_dump_dest                 string      G:\oracle\diag\rdbms\syp\syp\t

                                                 race

SQL>


===


As seen on https://www.toolbox.com/tech/oracle/question/who-locked-the-user-account-051110/


//alak

Comments

Popular posts from this blog

SAP Backdoors {placeholder Post]

Fiori Apps Keep Calling Internal Hostname / Internal FQDN