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