Timeline for Oracle 12c - Logon Trigger According To Machine Information
Current License: CC BY-SA 4.0
8 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Nov 7, 2021 at 20:38 | vote | accept | jrdba123 | ||
| Nov 7, 2021 at 20:09 | comment | added | jrdba123 | 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. | |
| Nov 7, 2021 at 20:08 | comment | added | jrdba123 | 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. | |
| Nov 7, 2021 at 11:03 | history | edited | pmdba | CC BY-SA 4.0 |
added more links for reference
|
| Nov 7, 2021 at 10:54 | comment | added | pmdba | 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. | |
| Nov 7, 2021 at 10:41 | comment | added | pmdba | 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. | |
| Nov 7, 2021 at 9:06 | comment | added | jrdba123 | 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? | |
| Nov 7, 2021 at 1:10 | history | answered | pmdba | CC BY-SA 4.0 |