How to create a Logon Trigger to control which users are allowed to connect to an Oracle Database
The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.
Certifications:
Certifications:
Latest posts by Stratos Matzouranis (see all)
- How to roll back an Oracle Database using a restore point in a Data Guard environment - 28 April 2025
- How can we increase performance on Oracle GoldenGate Replicat target with parallelism? - 19 March 2025
- How to create users in databases that belong to a SQL Server Always On Availability Group - 10 February 2025
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;
