DB link Secureness
Date: Thu, 10 Dec 2020 20:36:15 +0530
Message-ID: <CAKna9VY850RYuENCUzHguQ+i8ToDUBcqGOpRX8y+L9OoiRNcvQ_at_mail.gmail.com>
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:06:15 CET