Restrict user session on Service Name in Oracle

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;
  /
This entry was posted in Oracle on by .
Unknown's avatar

About SandeepSingh

Hi, I am working in IT industry with having more than 15 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Leave a Reply