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


create trigger logon_denied_to_alert

after servererror on database


message varchar2(4000);


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;


end if;




And to view where the trace file is:

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

SQL*Plus: Release 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 - 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




As seen on



Popular posts from this blog

SAP Backdoors {placeholder Post]

Fiori Apps Keep Calling Internal Hostname / Internal FQDN