2022. 4. 7. 17:03 오라클
Oracle TCPS 프로토콜 설정 테스트
TCPS 프로토콜 사용 설정 테스트
테스트 환경 19.10
=== 서버쪽 설정====== (RAC 인 경우는 각 노드에서 모두 수행하여야 한다. 1번꺼 복사가 아님..)
1. autologin 설정 및 인증서 export (autologin은 이미 설정되어 있으면 생략가능)
orapki wallet create -wallet /u01/app/oracle/admin/TEST/wallet/ -auto_login -pwd "welcome1"
orapki wallet add -wallet /u01/app/oracle/admin/TEST/wallet -dn 'CN=root_test_CA,C=US' -keysize 2048 -self_signed -validity 3650 -pwd "welcome1"
orapki wallet export -wallet /u01/app/oracle/admin/TEST/wallet/ -dn 'CN=root_test_CA,C=US' -cert /u01/app/oracle/admin/TEST/wallet/root_test_CA.cert
chmod 660 /u01/app/oracle/admin/TEST/wallet/cwallet.sso
chmod 660 /u01/app/oracle/admin/TEST/wallet/ewallet.p12
(oracle)
orapki wallet display -wallet /u01/app/oracle/admin/TEST/wallet/
keytool -printcert -file /u01/app/oracle/admin/TEST/wallet/root_test_CA.cert
2. Listener 설정
GI_HOME listener.ora
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/TEST/wallet)))
SSL_CLIENT_AUTHENTICATION = FALSE
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCPS)(HOST = 192.168.56.92)(PORT = 4521))
)
)
)
- GI_HOME 에 sqlnet.ora
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/TEST/wallet)))
SSL_CLIENT_AUTHENTICATION = FALSE
- ORACLE_HOME 에 sqlnet.ora
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/TEST/wallet)))
SSL_CLIENT_AUTHENTICATION = FALSE
3. listener 재기동
srvctl stop listener
srvctl start listener
[+ASM1]oracle@rac1:/home/oracle# lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-AUG-2021 22:06:41
Copyright (c) 1991, 2020, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 17-AUG-2021 22:06:18
Uptime 0 days 0 hr. 0 min. 22 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/19.3.0.0/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=192.168.56.92)(PORT=4521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.90)(PORT=3521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.92)(PORT=3521)))
Services Summary...
Service "TEST" has 1 instance(s).
Instance "TEST1", status READY, has 1 handler(s) for this service...
Service "TESTXDB" has 1 instance(s).
Instance "TEST1", status READY, has 1 handler(s) for this service...
The command completed successfully
=== 클라이언트쪽 설정======
1. wallet 디렉토리 생성
/home/oracle/wallet_client
2. 인증서를 해당 위치에 복사 후 권한 변경
[root@ora19c wallet_client]# chmod 600 root_test_CA.cert
[root@ora19c wallet_client]# chown oracle:dba root_test_CA.cert
3. wallet 생성 및 Import
orapki wallet create -wallet /home/oracle/wallet_client -auto_login -pwd "welcome1"
orapki wallet add -wallet /home/oracle/wallet_client -trusted_cert -cert /home/oracle/wallet_client/root_test_CA.cert -pwd "welcome1"
[TEST]oracle@ora19c:/home/oracle/wallet_client# ls -al
total 12
drwxr-xr-x 2 oracle dba 115 Aug 17 22:20 .
drwx------. 9 oracle dba 279 Aug 17 22:14 ..
-rw------- 1 oracle dba 1021 Aug 17 22:21 cwallet.sso
-rw------- 1 oracle dba 0 Aug 17 22:20 cwallet.sso.lck
-rw------- 1 oracle dba 976 Aug 17 22:21 ewallet.p12
-rw------- 1 oracle dba 0 Aug 17 22:20 ewallet.p12.lck
-rw------- 1 oracle dba 1029 Aug 17 22:17 root_test_CA.cert
4. sqlnet.ora 화일 구성
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/oracle/wallet_client)))
5. tnsnamess.ora
TCPS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = 192.168.56.92)(PORT = 4521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST)
)
)
=== 클라이언트에서 연결 테스트!! ========
vi /oracle/app/oracle/product/19.0.0/network/admin/tnsnames.ora
TCPS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = 192.168.56.92)(PORT = 4521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST)
)
)
tnsping TCPS
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = 192.168.56.92)(PORT = 4521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TEST)))
OK (50 msec)
sqlplus system/welcome1@TCPS
[TEST]oracle@ora19c:/oracle/app/oracle/product/19.0.0/network/admin# sqlplus system/welcome1@TCPS
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 17 23:57:02 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Last Successful login time: Tue Aug 17 2021 23:50:27 +09:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
SQL> SELECT SYS_CONTEXT('USERENV', 'network_protocol') FROM DUAL;
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')
--------------------------------------------------------------------------------
tcps
ORA-28864: SSL connection closed gracefully
=======================================================
srvctl modify listener -endpoints "TCP:3521/TCPS:4521"
alter system set local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=192.168.56.92)(PORT = 3521)','(ADDRESS = (PROTOCOL=TCPS)(HOST=192.168.56.92)(PORT = 4521))' scope=both;
alter system set local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=192.168.56.93)(PORT = 3521)','(ADDRESS = (PROTOCOL=TCPS)(HOST=192.168.56.93)(PORT = 4521))' scope=both;
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.92)(PORT = 3521))
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
(ADDRESS = (PROTOCOL = TCPS)(HOST =192.168.56.92)(PORT = 4521))
)
)
SECURE_REGISTER_LISTENER = (IPC,TCPS)
SQL> SELECT SYS_CONTEXT('USERENV', 'network_protocol') FROM DUAL;