How to restrict the user connection at service name in Oracle
In Example, we are consider two service one is PROD and one is TEST. We are stopping access to PROD by service.
To stop access, we need to create a logon trigger on database to stop access in Oracle
create trigger stop_session_prod after logon on database
declare
v_sid number;
v_isdba varchar2(10);
v_SERVICE_NAME varchar2(48);
begin
execute immediate
'select distinct sid from sys.v_$mystat' into v_sid;
execute immediate
'select SERVICE_NAME from sys.v_$session where sid = :b1' into v_SERVICE_NAME using v_sid;
select sys_context('userenv','ISDBA') into v_isdba from dual;
if upper(V_SERVICE_NAME) = 'TEST1' and v_isdba = 'FALSE' then
raise_application_error
(-20001,'Access is restricted to users',true);
end if;
end;
/