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

Latest posts by Stratos Matzouranis (see all)
- How to create a Logon Trigger to control which users are allowed to connect to an Oracle Database - 13 January 2025
- How do we collect the actual execution plan from queries using Extended Event and how do we read its data - 2 December 2024
- How do we find what permissions a user has in an Oracle Database? - 1 November 2024
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:

To disable it, run the following:
alter trigger logon_trigger disable;