Aw: Re: DB link Secureness

Do you mean to say , if we create this database level trigger and this table gets truncated then the first condition(Safe_host IS NULL ) in the trigger code will always be satisfied and thus will not let any user to login into the database? Will it also block the DBA to login too from SYS and thus can halt all DB operations and thus we should never create such database logon triggers?  

On Thu, Dec 10, 2020 at 9:10 PM Rich J <rich242j_at_gmail.com> wrote:

You might want to consider creating multiple triggers on just the schemas needing to be audited/secured.  If someone were to accidentally delete/truncate that security table, well, it would be bad.  I would always have at least one way into the database that didn't rely on that trigger firing.

My $.02,

Rich

On Thu, Dec 10, 2020 at 9:07 AM Lok P <loknath.73_at_gmail.com> wrote:

Hi, we are on the 11.2.0.4 version of Oracle. I have been a bit confused about working on the public VS private DB links. But recently, we have a security audit requirement in which it's required to block the login of users from other hosts except the defined ones through the DB link user login account. Team is coming up with the below trigger to handle this, for which we will insert all possible legitimate "HOST Name" and "DB link username" entries manually in a table "DB_LINK_USERS", and then below trigger will ensure the login from valid hosts.

We are trying to understand if this solution is okay considering it will be fired in each and every login and if it will have any significant performance overhead. Or any other way we should cater this need?

CREATE OR REPLACE TRIGGER SYSTEM.LOGON_DENY

AFTER LOGON ON DATABASE

DECLARE