How to create a Logon Trigger to control which users are allowed to connect to an Oracle Database

How to create a Logon Trigger to control which users are allowed to connect to an Oracle Database
How to create a Logon Trigger to control which users are allowed to connect to an Oracle Database

Many times, for security reasons, we may want specific users to connect only from specific machines - servers, and in the event that a connection comes from a different machine, not allow access but display a rejection message.

We can do this very easily in an Oracle database by creating a Logon Trigger by running the following code. The only thing that needs to be changed is the username and the hosts we want to allow to connect:

CREATE OR REPLACE TRIGGER logon_trigger
AFTER LOGON ON DATABASE
BEGIN
  IF USER = 'STRATOS' AND SYS_CONTEXT('USERENV', 'HOST') not in ('oracledev1','oracledev2') THEN
    RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to login from this host.');
  END IF;
END;
/

When someone tries to connect from a host other than the two we excluded, they will see the following message:

How to create a Logon Trigger to control which users are allowed to connect to an Oracle Database
01

To disable it, run the following:

alter trigger logon_trigger disable;
Share it

Leave a reply