Hello,
We are using Oracle 12c.
We have created a logon trigger to restrict logon access from specific IPs. I saw many discussions about that, but....
When user connects using SQL Plus or JDBC (Thin), returns error on SQL Plus and Exception to the JDBC/Java application.
However, we have only one specific situation when an application that use JPA (Java Persistence..) connects to database.
The JPA tries to connect many times to database. It's like a ATTACK.
I see many INACTIVE sessions on the gv$session.
The trigger:
CREATE OR REPLACE TRIGGER trg_test_logon
AFTER LOGON on DATABASE
BEGIN
IF (SYS_CONTEXT('USERENV', 'IP_ADDRESS') = '10.20.30.40') THEN
RAISE_APPLICATION_ERROR (-20001, 'You are not allowed to logon from this IP');
END IF;
END;
/
The user only has connect and resource privilege. The user doesn't have DBA or ADMINISTER DATABASE TRIGGER profiles.
This is the only situation that connection pass by RAISE_APPLICATION_ERROR and KEEP many INACTIVE sessions.
Is there any tip for this kind of event?
Thank you very much!