ORA-00604 ORA-20101 클라이언트로 접속시 나는 에러

클라이언트 프로그램으로 DB 접속시에 이런 에러가 나는 경우가 있습니다.


ORA-00604: error occurred at recursive SQL level 1
ORA-20101: You are not allowed to login to the <오라클계정> user!!
ORA-06512: at line 12 (<-strings)


이것 때문에 완전 애먹었는데요.


sqlnet.ora 에서 ip단위가 아닌, DB에서 유저 단위로 접속 제한을 걸어놓으면 이런 현상이 생깁니다.

유저 제한 트리거만 만들어놓고 allow_ip 테이블을 생성해두지 않아 허용 ip의 정보는 없는 상태라면 이런 현상이 발생합니다.



<DB에서 유저 단위로 접속 제한>


sys계정에서 작업 해줘야 합니다.


1. ip-address 를 저장할 Table생성


SQL> create table allow_ip ( ip_address varchar2(15), real_user varchar2(30) )


2. Scott user에 login 할 때 client 의 ip-address를 check하는 trigger 생성

SQL> create or replace trigger check_log_오라클계정 after logon on 오라클계정.schema declare flag number; begin if ora_client_ip_address is not null then select 1 into flag from allow_ip where ip_address = ora_client_ip_address;

if flag = 1 then null;

end if;

end if;

exception when no_data_found then raise_application_error (-20101, 'You are not allowed to login to the 오라클계정 user!!');

end;
/


3. 실행한 후에 table에 저장되지 않은 ip-address 를 가진 client 에서 접속하면 다음과 같이 에러가 발생하면서 접속이 실패합니다.

SQL> connect 오라클계정/비번@oracledb


ERROR: ORA-00604: error occurred at recursive SQL level 1

ORA-20101: You are not allowed to login to the Scott user!!

ORA-06512: at line 12


server 에서 터미널로 접속하는 경우에는 ora_client_ip_address 의 값이 null 이 되므로 이 경우에는 무조건 접속됩니다.



접속 제한 트리거의 조회


SELECT * FROM all_source WHERE text LIKE '%ora_client%';


로 검색을 하면 접속 제한이 걸려있는 계정을 알수 있습니다.



트리거 내용 조회


SELECT line, text FROM all_source WHERE name = '트리거이름' order by line;


1 trigger check_log_오라클계정
2 after logon on seromedu.schema
3 declare
4 flag number;
5 begin
6 if ora_client_ip_address is not null then
7 select 1 into flag from allow_ip where ip_address = ora_client_ip_address;
8 if flag = 1 then
9 null;
10 end if;
11 end if;
12 exception
13 when no_data_found then
14 raise_application_error (-20101, 'You are not allowed to login to the 오라클계정 user!!');
15 end;



트리거에 대한 자세한 정보 보기


SQL> SELECT * FROM ALL_TRIGGERS where TRIGGER_NAME='CHECK_LOG_오라클계정';


조회를 하면 권한, 소유자, 소유된 테이블, 트리거의 이름이나 트리거의 대한 모든 정보를 알수 있습니다.

특정 컬럼 값만 선택해서 셀렉트를 날려주면, 원하는 값만 조회가 가능하죠.

status에 ENABLED 되어 있으면 비활성화 해줍니다.


SQL> select distinct a.name, b.status from all_source a, ALL_TRIGGERS b where a.text like '%ora_client_ip_address%' and a.NAME=b.TRIGGER_NAME;



유저 접속 제한 풀기


SQL> alter trigger system.CHECK_LOG_오라클계disable;


스키마 자체는 system이 소유하고 있습니다.

이렇게 해주면 allow_ip가 없어 접속 제한이 걸린 계정으로 접속할수가 있습니다.

티스토리 툴바