a. only create session
b. have insert only into a table MYTABLE
c. Can insert on that table(MYTABLE) only from several selected servers
Point a & b are easy to resolve. But for pint c we had to do some this extra.
In order to implement point c we created a trigger on table MYTABLE which used function sys_context to collect & IP from the session and use these information for authentication. My trigger was as below:
create or replace trigger mytrg
before insert on MYTABLE
for each row
declare
var_ip varchar2(50);
var_count_ip number;
var_user varchar2(50);
begin
select sys_context('USERENV', 'IP_ADDRESS') into var_ip from dual;
select count(*) into var_count_ip from servers where ip = var_ip;
select user into var_user from dual;
if var_user = 'ALERTSENDER' and (var_ip is null or var_count_ip <>
RAISE_APPLICATION_ERROR(-20001,
'ERROR:' || var_ip ||
':NOT AN AUTHENTICATED IP');
else
--OTHER NORMAL OPERATIONS
end if;
end;
