Skip to Main Content

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!

Logon Tigger is not Working

tad_csOct 25 2018 — edited Oct 26 2018

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!

This post has been answered by EdStevens on Oct 25 2018
Jump to Answer
Comments
Post Details
Added on Oct 25 2018
6 comments
1,450 views