Re: DB link Secureness

Yes we do use connection pooling for OLTP applications, but we do have some batch applications pointing to this ~4 node Rac database which are using dedicated connections. I tried to capture the pattern of statistics 'Logons Per Sec' from dba_hist_sysmetric_summary ,using the below query for one of the peak days, and I see the pattern below. Will this be of any concern? And in that case would it be a good idea to just check the access/connection which are made via DB link(using SYS_CONTEXT('USERENV','DBLINK_INFO'). So if it returns NOT NULL value then only we will proceed else we will skip all other validation in the trigger. So it will help validating only DB link connections rather checking it for all types of incoming connections. Correct me if wrong.

select max(end_time),

sum(case metric_name when 'Logons Per Sec' then maxval end) logon_per_sec

from dba_hist_sysmetric_summary

group by snap_id

order by snap_id;

[image: User: "image.png"]

When you suggested usage of IOT, rather a normal heap table, was your intention was just to make the SELECT run as fast as possible, as that will skip the "table access by index rowid" step? or any other thought in your mind wrt this specific scenario? We are also planning to store "IP address" and other details in that table.

Can you please help me understand How can the "hostname" be spoofed here which will then open another loophole in security here? I hope it can't be done using simple DBMS_SESSION.SET_CONTEXT.

On Thu, Dec 10, 2020 at 8:48 PM Andy Sayer <andysayer_at_gmail.com> wrote:

> 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