Oracle 12c - Logon Trigger According To Machine Information

Question

I want to create a logon trigger. Trigger will do the following:

It will block access to the database based on certain users and certain machine information. Then it will give an information message to the user and insert this session information into a table.

E.g;

Users: mike, john, eric, daisy, albert

Machine: NEWMACH%

Message to the user: You are not allowed to logon from machine '||MACHINE|| ' using '|| USERNAME

The information it will insert into the table: username, osuser, machine, port, terminal, program, module, instance_id, sid, serial#

Table name: DBADMINISTRATOR.LOGON_TBL

I have a trigger idea as follows;

CREATE OR REPLACE TRIGGER DBADMINISTRATOR.LOGON_FILTER
AFTER LOGON
ON DATABASE
DECLARE
USERNAME varchar2 (200);
MACHINE varchar2 (200);
begin
...
...
if
...
then
raise_application_error(-20001,'You are not allowed to logon from machine '||MACHINE|| ' using '|| USERNAME);
end if;
end;
/

Can you please help scripting and will it cause any performance problems?

Best Regards,

pmdba · Accepted Answer · 2021-11-07T11:03:10.990

This will quickly become a maintenance nightmare. What happens when the new laptop that your boss got this morning needs to run a database report for a meeting that starts in 5 minutes, but you haven't had time to update the whitelist yet? Also, all of the system information you can check can be spoofed by the user if they know what they're doing, so it doesn't really do much to enhance security.

If you want to limit access, then use the OS firewall or Oracle Connection Manager (if you have Enterprise Edition) to limit clients to specific network subnets (never individual IP addresses). Otherwise, control what the user can do in the database using roles, profiles, and privileges. You can always control what the user can do once they are connected; it is a lot harder to limit how they connect.

If you need more detail, I posted a couple of blog articles on this sort of thing in the last year, here, inspired by previous posts on Stack Exchange forums:

Oracle 12c - Logon Trigger According To Machine Information

1 Answers1