Re: DB link Secureness

As long as you are using sensible connection pooling, you won’t be logging in that often. So whatever overhead this is, won’t be hit enough to be important.

That said,
If multiple safe hosts exist per user, you’ll want to change your query to filter on host too. This seems like something an IOT would be useful for (your primary key would be db_link_usr, machine). Then you are just checking existence.
You don’t need to use dual to get values from sys_context .

Im pretty sure host can be spoofed without too much trouble. That might not be a problem if only internal machines have network access.

Thanks,
Andy

On Thu, 10 Dec 2020 at 15:07, 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
>
> OS_USER VARCHAR2 (4000);
>
> Login_host VARCHAR2 (4000);
>
> Login_user VARCHAR2 (4000);
>
> Safe_host VARCHAR2 (4000);
>
> BEGIN
>
> SELECT SYS_CONTEXT('USERENV', 'OS_USER') INTO OS_USER FROM dual;
>
> SELECT SYS_CONTEXT('USERENV', 'HOST') INTO Login_host FROM dual;
>
> SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') INTO Login_user FROM dual;
>
> BEGIN
>
> SELECT machine INTO Safe_host FROM DB_Link_users WHERE DB_LINK_USR
> = Login_user;
>
> EXCEPTION
>
> WHEN NO_DATA_FOUND THEN
>
> Safe_host := NULL;
>
> END;
>
> IF (Safe_host IS NULL OR Safe_host != Login_host) THEN
>
> RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to logon from
> host '|| Login_host|| ' using '|| OS_USER);
>
> END IF;
>
> END;
>
> /
>

--
http://www.freelists.org/webpage/oracle-l