service 이용하여 특정 인스턴스만 접속 못하게 하는 방법

- Trigger 를 이용해서 특정 인스턴스만 접속 못하게 하는 방법

---------------------------------------------------------------------

2개의 서비스가 있다고 가정하자..

 

TEST1, TEST2

---------------------------------------------------------------------

sqlplus / as sysdba

 create or replace trigger ban_test1 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,'TEST1 Access users restricted',true);
  end if;
  end;
  /
 

---------------------------------------------------------------------

sqlplus scott/tiger@TEST1
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: TEST1 Access users restricted
ORA-06512: at line 12

--------------------------------------------------------------------- 

sqlplus scott/tiger@test2

works fine.