Oracle 접속, IP 로 접속 차단 하기
출처 : https://logic.edchen.org/how-to-make-a-blacklist-for-db-connection/
보안정책에 따라 정기적으로 패스워드 변경을 하고 있다
VDI + 웹로직 + 오렌지 + SQL Developer 로 개발환경인데
장기 휴가자, 출산 휴가, 공용 PC 등의 VDI 의 웹로직에서 Connection pool 비번 변경을 하지 않아서
비번 변경 후, 개발자(~DEV)계정이 LOCK 이 걸려서, 5분마다 체크 하여 UNLOCK 하고 있는데
근본적으로 비번 틀리는 IP에 대한 차단, 즉, DB 접속 전 Lister Level 에서 차단을 위한 Blacklist Test
## 정상접속 환경
[oracle@oel7 admin]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 24-NOV-2022 21:28:08
Copyright (c) 1991, 2021, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel7)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 24-NOV-2022 21:27:26
Uptime 0 days 0 hr. 0 min. 41 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /ORA19/app/oracle/product/19.0.0/db_1/network/admin/listener.ora
Listener Log File /ORA19/app/oracle/diag/tnslsnr/oel7/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel7)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "oracle19" has 1 instance(s).
Instance "oracle19", status READY, has 1 handler(s) for this service...
Service "oracle19XDB" has 1 instance(s).
Instance "oracle19", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oel7 admin]$
정상 접속 시, listenerlog
[oracle@oel7 ~]$ tail -f /ORA19/app/oracle/diag/tnslsnr/oel7/listener/alert/log.xml
<msg time='2022-11-24T21:28:41.732+09:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='oel7'
host_addr='192.168.137.50' pid='12707'>
<txt>24-NOV-2022 21:28:41 * (CONNECT_DATA=(SERVICE_NAME=oracle19)(CID=(PROGRAM=C:\Program?Files??x86?\WareValley\Orange?for?ORACLE?4.0?DBA?Unicode\OrangeMainU.exe)(HOST=LAPTOP-0OKL4MTC)(USER=TGHOME))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.137.1)(PORT=58852)) * establish * oracle19 * 0
</txt>
</msg>
<msg time='2022-11-24T21:28:42.477+09:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='oel7'
host_addr='192.168.137.50' pid='12707'>
<txt>24-NOV-2022 21:28:42 * service_update * oracle19 * 0
</txt>
</msg>
## 접속차단 환경
## 1) sqlnet.ora 수정
[oracle@oel7 admin]$ cat sqlnet.ora
#sec_case_sensitive_logon = false
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
TCP.VALIDNODE_CHECKING=YES
TCP.EXCLUDED_NODES=(192.168.137.1,192.168.137.2)
[oracle@oel7 admin]$
## 2) sqlnet.ora 수정 후, listener 재기동
[oracle@oel7 admin]$ lsnrctl stop
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 24-NOV-2022 21:30:25
Copyright (c) 1991, 2021, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel7)(PORT=1521)))
The command completed successfully
[oracle@oel7 admin]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 24-NOV-2022 21:30:28
Copyright (c) 1991, 2021, Oracle. All rights reserved.
Starting /ORA19/app/oracle/product/19.0.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /ORA19/app/oracle/product/19.0.0/db_1/network/admin/listener.ora
Log messages written to /ORA19/app/oracle/diag/tnslsnr/oel7/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel7)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel7)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 24-NOV-2022 21:30:28
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /ORA19/app/oracle/product/19.0.0/db_1/network/admin/listener.ora
Listener Log File /ORA19/app/oracle/diag/tnslsnr/oel7/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel7)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@oel7 admin]$
## 3) dynamic listener 등록
SQL> alter system register ;
System altered.
SQL>
[oracle@oel7 admin]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 24-NOV-2022 21:31:11
Copyright (c) 1991, 2021, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel7)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 24-NOV-2022 21:30:28
Uptime 0 days 0 hr. 0 min. 42 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /ORA19/app/oracle/product/19.0.0/db_1/network/admin/listener.ora
Listener Log File /ORA19/app/oracle/diag/tnslsnr/oel7/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel7)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "oracle19" has 1 instance(s).
Instance "oracle19", status READY, has 1 handler(s) for this service... <<<< service 등록 완료
Service "oracle19XDB" has 1 instance(s).
Instance "oracle19", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oel7 admin]$
## 4) 접속 차단 ip 에서 접속 시도 시
## 5) Clinet 측, 메시지
Orange 로 접속 시도 시 >> ORA-12537 : tns 연결이 종료되었습니다.
## 6) client 접속 시도 시, listener.log 정보
C:\Users\TGHOME>
## listener 로그 ( Incoming connection from 192.168.137.1 rejected )
<msg time='2022-11-24T21:34:20.932+09:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='oel7'
host_addr='192.168.137.50' pid='13017'>
<txt>Incoming connection from 192.168.137.1 rejected
</txt>
</msg>
<msg time='2022-11-24T21:34:20.932+09:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='oel7'
host_addr='192.168.137.50' pid='13017'>
<txt>24-NOV-2022 21:34:20 * 12546
</txt>
</msg>
<msg time='2022-11-24T21:34:20.932+09:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='oel7'
host_addr='192.168.137.50' pid='13017'>
<txt>TNS-12546: TNS:permission denied
TNS-12560: TNS:protocol adapter error
TNS-00516: Permission denied
</txt>
</msg>
## Client측, tnsping 시도 >> 실패 ( TNS-12537: TNS:연결이 종료되었습니다 )
C:\app\client\TGHOME\product\12.2.0\client_1\network\admin>tnsping oracle19
TNS Ping Utility for 64-bit Windows: Version 21.0.0.0.0 - Production on 24-11월-2022 21:40:41
Copyright (c) 1997, 2021, Oracle. All rights reserved.
사용된 매개변수 파일:
D:\21c\app\TGHOME\homes\OraDB21Home1\network\admin\sqlnet.ora
별칭 분석을 위해 TNSNAMES 어댑터 사용
(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oel7)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = oracle19)))에 접속하려고 시도하는 중
TNS-12537: TNS:연결이 종료되었습니다
C:\app\client\TGHOME\product\12.2.0\client_1\network\admin>
## tsnping 시, listener.log 로그 정보 ( Incoming connection from 192.168.137.1 rejected )
<msg time='2022-11-24T21:41:16.003+09:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='oel7'
host_addr='192.168.137.50' pid='13661'>
<txt>Incoming connection from 192.168.137.1 rejected
</txt>
</msg>
<msg time='2022-11-24T21:41:16.004+09:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='oel7'
host_addr='192.168.137.50' pid='13661'>
<txt>24-NOV-2022 21:41:16 * 12546
</txt>
</msg>
<msg time='2022-11-24T21:41:16.004+09:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='oel7'
host_addr='192.168.137.50' pid='13661'>
<txt>TNS-12546: TNS:permission denied
TNS-12560: TNS:protocol adapter error
TNS-00516: Permission denied
</txt>
</msg>