0

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,

Improve this question
1

1 Answer 1

Reset to default
4

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:

See also:

Improve this answer
5
  • Hello Pete. First of all, thank you for the information. But what we are trying to do here is this; we will not actually block all access from these machines. We want to prevent future connections with only certain users from these machines. People can connect from the same machines (NEWMACH\%) using other users, that's fine. E.g; we don't want you to connect as Albert from machine NEWMACH\PETE1, but we allow you to connect with user APP_READ1 from the same machine. I don't know what you want to say here, do you think it is wrong to log these connections to a table?
    – jrdba123
    Commented Nov 7, 2021 at 9:06
  • Not wrong to log the connections (there's an example of a whiltelist trigger on my blog site), but also not practical to limit them in the way you're describing. Are you using Enterprise Edition, by any chance? If you're differentiating between users and applications that might make it a bit easier, too, but there are still better ways to handle this. The biggest problem is that all of that machine information can be faked by the client, so any trigger based on that data can be fooled.
    – pmdba
    Commented Nov 7, 2021 at 10:41
  • I'd start by using certificate authentication (client must have their own cert loaded on the client machine) instead of password authentication; use separate service_names for users vs apps and use Connection Manager (if you have EE) to limit connections to the app service_name to known app servers and users to non-app servers. Use auditing to track logins (not a custom solution); and proxy user privileges if access to shared accounts by users or developers is required. Depending on the tools and code in play you might consider smart application roles to limit privs based on session params.
    – pmdba
    Commented Nov 7, 2021 at 10:54
  • Hello Pete. I know your experiences and I read your blog whenever I have time. Thank you for sharing your experiences. Yes, we are using the Enterprise Edition. Actually, we have 2 exadatas and many databases. I looked into the issue you mentioned, thank you.
    – jrdba123
    Commented Nov 7, 2021 at 20:08
  • We have already done our work in the Network and OS layers. Actually, I wrote trigger for other databases and it works. At the same time, I wrote a trigger for the topic I opened here. Since we want to achieve a standard right now, everything will be done with trigger, we do not plan to go beyond this. I was just wondering if there was a better trigger idea than mine.
    – jrdba123
    Commented Nov 7, 2021 at 20:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.