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
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@xxxxxxxxx> 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@xxxxxxxxx> 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;
/
