Re: DB link Secureness

From: Lok P <loknath.73_at_gmail.com>
Date: Fri, 11 Dec 2020 13:09:09 +0530
Message-ID: <CAKna9VatO1uc8DhLYYh0f2U4=JWOA=SuNkKVWpG4DERPjgirbg_at_mail.gmail.com>



If I am correct on the usage of IOT(Index organized table), as in this case we are going to access this table always by db_link_usr and only fetch/validate the machine name for that, so hopefully that is the correct use case of an IOT. But we are planning to capture other columns like IP address, Date created, created by , date modified and modified by etc. So will that defeat the purpose of usage of IOT here?

Another thing I am still struggling to understand , how somebody can spoof the hostname such that the Oracle sys_context function will be fooled by that?

We are thinking to do something as below,

Login_user VARCHAR2(4000):=SYS_CONTEXT('USERENV', 'SESSION_USER');

Login_host VARCHAR2(4000):=SYS_CONTEXT('USERENV', 'HOST');

v_dbl_info VARCHAR2(4000) := sys_context('USERENV','DBLINK_INFO');

 SELECT count(*) dblink_user_cnt,

     sum(case when machine=Login_host then 1 else 0 end) host_count

     INTO …

     FROM DB_Link_users

     WHERE DB_LINK_USR = Login_user;

     If (dblink_user_cnt =0 then login allowed

     If (dblink_user_cnt >0 and v_dbl_info is null then login not allowed

     If (dblink_user_cnt >0 and v_dbl_info is not null and host_count
>=1 then login allowed

      If (dblink_user_cnt >0 and v_dbl_info is not null and host_count 0 then login not allowed

On Thu, Dec 10, 2020 at 11:00 PM Lok P <loknath.73_at_gmail.com> wrote:

> 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
Received on Fri Dec 11 2020 - 08:39:09 CET

Original text of this message