Edit

ORA-1017

Note
There are some useful tips about auth error

… to log or prevent logon

show parameter audit_trail
audit session whenever not successful;
alter system set events '1017 trace name errorstack level 10';
CREATE OR REPLACE TRIGGER block_tools_trigger
AFTER LOGON ON database
DECLARE
v_prog v$session.program%TYPE;
BEGIN
SELECT program INTO v_prog
FROM v$session
WHERE audsid = USERENV('SESSIONID')
AND audsid != 0 -- Don’t Check SYS Connections
AND ROWNUM = 1; -- Parallel processes will have the same AUDSID’s
IF
-- UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' -- Toad
--or UPPER(v_prog) LIKE '%SQLNAV%' -- SQL Navigator
--or UPPER(v_prog) LIKE '%PLSQLDEV%' -- PLSQL Developer
--or UPPER(v_prog) LIKE '%BUSOBJ%' -- Business Objects
--or UPPER(v_prog) LIKE '%EXCEL%' -- MS-Excel plug-in
--or UPPER(v_prog) LIKE '%SQLPLUS%' -- SQLPLUS
THEN
RAISE_APPLICATION_ERROR(-20983, 'You are not allowed to login.');
END IF;
END;
/
---
CREATE or replace TRIGGER block_tools_trigger AFTER SERVERERROR ON DATABASE
BEGIN
IF (IS_SERVERERROR (1017)) THEN
sys.dbms_lock.sleep(3600);
END IF;
END;
/
---
CREATE or replace TRIGGER block_tools_trigger AFTER SERVERERROR ON DATABASE
BEGIN
IF (IS_SERVERERROR (1017)) THEN
RAISE_APPLICATION_ERROR ( num => -20001, msg => ' YOU ARE NOT AUTHORIZED TO LOGIN ');
--null;
ELSE
RAISE_APPLICATION_ERROR (num => -20002, msg => ora_server_error_msg(1) );
END IF;
END;
/
---
CREATE OR REPLACE TRIGGER logon_denied_write_alertlog AFTER SERVERERROR ON DATABASE
DECLARE
l_message varchar2(2000);
BEGIN
-- ORA-1017: invalid username/password; logon denied
IF (IS_SERVERERROR(1017)) THEN
select 'ORA-1017 Failed login attempt to the "'|| sys_context('USERENV' ,'AUTHENTICATED_IDENTITY') ||'" schema'
|| ' using ' || sys_context ('USERENV', 'AUTHENTICATION_TYPE') ||' authentication'
|| ' at ' || to_char(logon_time,'dd-MON-yy hh24:mi:ss' )
|| ' from ' || osuser ||'@'||machine ||' ['||nvl(sys_context ('USERENV', 'IP_ADDRESS'),'Unknown IP')||']'
|| ' via the "' ||program||'" program.'
into l_message
from sys .v$session
where sid = to_number(substr(dbms_session.unique_session_id,1 ,4), 'xxxx')
and serial# = to_number(substr(dbms_session.unique_session_id,5 ,4), 'xxxx');
-- write to alert log
sys.dbms_system.ksdwrt( 2,l_message );
END IF;
END;
/

… to prevent logon from os (iptables)

#add rule
$ iptables -A INPUT -s ipaddress -j DROP
#remove rule
$ iptables -D INPUT -s ipaddress -j DROP

%23%23%23%20%60ORA-1017%60%0A%3E**Note**%0A*There%20are%20some%20useful%20tips%20about%20auth%20error*%0A%0A%20%20%20...%20to%20log%20or%20prevent%20logon%0A%60%60%60sql%0A%20show%20parameter%20audit_trail%0A%20audit%20session%20whenever%20not%20successful%3B%0A%20alter%20system%20set%20events%20%271017%20trace%20name%20errorstack%20level%2010%27%3B%0A%0ACREATE%20OR%20REPLACE%20TRIGGER%20block_tools_trigger%0AAFTER%20LOGON%20ON%20database%0ADECLARE%0A%20%20v_prog%20v%24session.program%25TYPE%3B%0ABEGIN%0A%20%20SELECT%20program%20INTO%20v_prog%0A%20%20FROM%20v%24session%0A%20%20WHERE%20%20audsid%20%3D%20USERENV%28%27SESSIONID%27%29%0A%20%20%20%20AND%20%20audsid%20%21%3D%200%20%20--%20Don%u2019t%20Check%20SYS%20Connections%0A%20%20%20%20AND%20%20ROWNUM%20%3D%201%3B%20%20--%20Parallel%20processes%20will%20have%20the%20same%20AUDSID%u2019s%0A%0AIF%20%0A%20%20%20--%20%20%20UPPER%28v_prog%29%20LIKE%20%27%25TOAD%25%27%20OR%20UPPER%28v_prog%29%20LIKE%20%27%25T.O.A.D%25%27%20%20--%20Toad%0A%20%20%20--or%20UPPER%28v_prog%29%20LIKE%20%27%25SQLNAV%25%27%20%20%20%20%20--%20SQL%20Navigator%0A%20%20%20--or%20UPPER%28v_prog%29%20LIKE%20%27%25PLSQLDEV%25%27%20%20%20--%20PLSQL%20Developer%0A%20%20%20--or%20UPPER%28v_prog%29%20LIKE%20%27%25BUSOBJ%25%27%20%20%20%20%20--%20Business%20Objects%0A%20%20%20--or%20UPPER%28v_prog%29%20LIKE%20%27%25EXCEL%25%27%20%20%20%20%20%20--%20MS-Excel%20plug-in%0A%20%20%20--or%20UPPER%28v_prog%29%20LIKE%20%27%25SQLPLUS%25%27%20%20%20%20--%20SQLPLUS%0ATHEN%0A%20%20RAISE_APPLICATION_ERROR%28-20983%2C%20%27You%20are%20not%20allowed%20to%20login.%27%29%3B%0AEND%20IF%3B%0AEND%3B%0A/%0A---%0ACREATE%20or%20replace%20TRIGGER%20block_tools_trigger%20AFTER%20SERVERERROR%20ON%20DATABASE%0ABEGIN%0A%20%20%20%20IF%20%28IS_SERVERERROR%20%281017%29%29%20THEN%0A%20%20%20%20%20%20%20sys.dbms_lock.sleep%283600%29%3B%0A%20%20%20%20END%20IF%3B%0A%20END%3B%0A%20/%0A%20---%0ACREATE%20or%20replace%20TRIGGER%20block_tools_trigger%20AFTER%20SERVERERROR%20ON%20DATABASE%0ABEGIN%0A%20%20IF%20%28IS_SERVERERROR%20%281017%29%29%20THEN%0A%20%20%20%20RAISE_APPLICATION_ERROR%20%28%20num%20%3D%3E%20-20001%2C%20msg%20%3D%3E%20%27%20YOU%20ARE%20NOT%20AUTHORIZED%20TO%20LOGIN%20%27%29%3B%0A%20%20%20%20--null%3B%0A%20%20ELSE%0A%20%20%20%20RAISE_APPLICATION_ERROR%20%28num%20%3D%3E%20-20002%2C%20%20msg%20%3D%3E%20ora_server_error_msg%281%29%20%29%3B%0A%20%20END%20IF%3B%0AEND%3B%0A/%0A---%0ACREATE%20OR%20REPLACE%20TRIGGER%20logon_denied_write_alertlog%20AFTER%20SERVERERROR%20ON%20DATABASE%0ADECLARE%0A%20l_message%20varchar2%282000%29%3B%0ABEGIN%0A%20--%20ORA-1017%3A%20invalid%20username/password%3B%20logon%20denied%0A%20IF%20%28IS_SERVERERROR%281017%29%29%20THEN%0A%20select%20%27ORA-1017%20Failed%20login%20attempt%20to%20the%20%22%27%7C%7C%20sys_context%28%27USERENV%27%20%2C%27AUTHENTICATED_IDENTITY%27%29%20%7C%7C%27%22%20schema%27%0A%20%7C%7C%20%27%20using%20%27%20%7C%7C%20sys_context%20%28%27USERENV%27%2C%20%27AUTHENTICATION_TYPE%27%29%20%7C%7C%27%20authentication%27%0A%20%7C%7C%20%27%20at%20%27%20%7C%7C%20to_char%28logon_time%2C%27dd-MON-yy%20hh24%3Ami%3Ass%27%20%29%0A%20%7C%7C%20%27%20from%20%27%20%7C%7C%20osuser%20%7C%7C%27@%27%7C%7Cmachine%20%7C%7C%27%20%5B%27%7C%7Cnvl%28sys_context%20%28%27USERENV%27%2C%20%27IP_ADDRESS%27%29%2C%27Unknown%20IP%27%29%7C%7C%27%5D%27%0A%20%7C%7C%20%27%20via%20the%20%22%27%20%7C%7Cprogram%7C%7C%27%22%20program.%27%0A%20into%20l_message%0A%20from%20sys%20.v%24session%0A%20where%20sid%20%3D%20to_number%28substr%28dbms_session.unique_session_id%2C1%20%2C4%29%2C%20%27xxxx%27%29%0A%20and%20serial%23%20%3D%20to_number%28substr%28dbms_session.unique_session_id%2C5%20%2C4%29%2C%20%27xxxx%27%29%3B%0A%20%0A%20--%20write%20to%20alert%20log%0A%20sys.dbms_system.ksdwrt%28%202%2Cl_message%20%29%3B%0A%20END%20IF%3B%0AEND%3B%0A/%0A%60%60%60%0A%20%20%20...%20to%20prevent%20logon%20from%20os%20%28iptables%29%0A%60%60%60bash%0A%23add%20rule%0A%24%20iptables%20-A%20INPUT%20-s%20ipaddress%20-j%20DROP%0A%23remove%20rule%0A%24%20iptables%20-D%20INPUT%20-s%20ipaddress%20-j%20DROP%0A%60%60%60%0A%0A@%28Postach.io%29%5Bunix%2C%20ora-1017%2C%20trigger%2C%20alert.log%2C%20published%5D