Re: DB link Secureness
Date: Thu, 10 Dec 2020 15:18:36 +0000
Message-ID: <CACj1VR5+3O7dO_pHMoZ4_SAuVZHM0nOY3w6F-ajy4NHFd4vxUw_at_mail.gmail.com>
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-lReceived on Thu Dec 10 2020 - 16:18:36 CET