Oracle 配置TCPS协议

Configuring SSL for Client Authentication and Encryption With Self Signed Certificates On Both Ends Using orapki (Doc ID 401251.1)
One can set up the SSL authentication as described in this note only when testing this authentication method or when the number of the users authenticated via SSL is not large. The rationale is that in this note the client signs it’s own certificate and we import the root certificate of the client into the wallet of the server. As such for a large number of different users we will end up with too many client root certificates being imported into the wallet of the server.

只有在测试此身份验证方法或通过SSL进行身份验证的用户数量不多时，才可以设置本说明中所述的SSL身份验证。
其基本原理是，在本说明中，客户机签署自己的证书，然后我们将客户机的根证书导入到服务器的钱包中。
因此，对于大量不同的用户，我们最终会有太多的客户端根证书被导入到服务器的钱包中。

-- ### 服务端 (注意：服务器不需要生成服务器的客户端版本的CA证书)
-- 创建目录
su - oracle && cd /u01/app/grid/product/db/network/admin
mkdir wallet_location
cd wallet_location
wallet_location=`pwd`

-- (初始化)创建初始化wallet 生成文件： cwallet.sso，ewallet.p12
orapki wallet help
orapki wallet create -wallet ${wallet_location} -auto_login -pwd pwd123456

-- (服务端)创建自签名证书，生成用户证书和CA根证书
orapki wallet add -wallet ${wallet_location} -dn "CN=server" -keysize 512 -self_signed -validity 365 -pwd pwd123456

-- (服务端)导出CA根证书书
orapki wallet export -wallet ${wallet_location} -dn "CN=server" -cert server_ca1.cert


-- ### 客户端
su - oracle 
cd /u01/app/grid/product/db/network/admin
mkdir wallet_location
cd wallet_location
wallet_location=`pwd`

-- (初始化)创建初始化wallet 生成文件： cwallet.sso，ewallet.p12
orapki wallet create -wallet $wallet_location -auto_login -pwd pwd123456

-- (客户端)创建自签名证书，生成用户证书和CA根证书
orapki wallet add -wallet $wallet_location -dn "CN=client" -keysize 512 -self_signed -validity 365 -pwd pwd123456

-- (客户端)导出CA根证书书
orapki wallet export -wallet $wallet_location -dn "CN=client" -cert client_ca1.cert


-- ### 导入证书
-- ## 在客户端导入服务端CA根证书
orapki wallet add -wallet $wallet_location -trusted_cert -cert server_ca1.cert -pwd pwd123456

-- ## 在服务端导入客户端CA根证书
orapki wallet add -wallet $wallet_location -trusted_cert -cert client_ca1.cert -pwd pwd123456



## 服务端配置（需要实例重启，监听器重启）
# The database parameter OS_AUTHENT_PREFIX must be null and REMOTE_OS_AUTHENT must be FALSE.
alter system set remote_os_authent=FALSE scope=spfile;   -- 此参数已失效
alter system set os_authent_prefix='' scope=spfile;      -- 默认值：ops$;

# 创建用户(非必要)
# The user within the database has to be created specifying the distiguished name (DN) on their certificate. For example,
# 用户必须指定认证的ND(distiguished name)
create user client identified externally as 'CN=client';
create user gz5 identified by gz5_12345;
alter user gz5 identified externally as 'CN=client';
grant create session to gz1;




### vi listener.ora (服务器)
# for 单实例
LISTENER = (DESCRIPTION_LIST =
 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ORCL)(PORT = 1521)) )
 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = ORCL)(PORT = 2484)) )
)
WALLET_LOCATION = (SOURCE = (METHOD=File)
   (METHOD_DATA = (DIRECTORY = /u01/wallet_location) )
)
## for RAC集群
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON

# LISTENER for node.public-ip and node.vip-ip  
LISTENER = (DESCRIPTION = (ADDRESS_LIST=
 (ADDRESS=(PROTOCOL=tcp) (HOST=1.1.1.21)(PORT=1521))  
 (ADDRESS=(PROTOCOL=tcps)(HOST=1.1.1.21)(PORT=2484)) 
 (ADDRESS=(PROTOCOL=tcp) (HOST=1.1.1.31)(PORT=1521))  
 (ADDRESS=(PROTOCOL=tcps)(HOST=1.1.1.31)(PORT=2484)) 
 (ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)) ))

# LISTENER_SCAN1 for scan-ip
LISTENER_SCAN1 = (DESCRIPTION = (ADDRESS_LIST=
 (ADDRESS=(PROTOCOL=tcp)(HOST=1.1.1.30)(PORT=1521))  
 (ADDRESS=(PROTOCOL=tcps)(HOST=1.1.1.30)(PORT=2484))  
 (ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)) ))

# for tcps
WALLET_LOCATION = (SOURCE = (METHOD=File)
   (METHOD_DATA = (DIRECTORY = /u01/app/grid/product/db/network/admin/wallet_location) )
)

DIAG_ADR_ENABLED_LISTENER=OFF
DIAG_ADR_ENABLED_LISTENER_SCAN1=OFF
INBOUND_CONNECT_TIMEOUT_LISTENER=0
INBOUND_CONNECT_TIMEOUT_LISTENER_SCAN1=0
ADR_BASE_LISTENER = /u02/log
ADR_BASE_LISTENER_SCAN1 = /u02/log







# vi sqlnet.ora (服务器)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
DIAG_ADR_ENABLED=OFF
SQLNET.INBOUND_CONNECT_TIMEOUT=0

# for tcps
SQLNET.AUTHENTICATION_SERVICES = (BEQ, TCPS)
SSL_VERSION = 1.0
SSL_CLIENT_AUTHENTICATION = TRUE
WALLET_LOCATION = (SOURCE = (METHOD = FILE)
   (METHOD_DATA = (DIRECTORY = /u01/app/grid/product/db/network/admin/wallet_location) )
)




## 客户端配置
# vi sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES = (BEQ, TCPS)
SSL_VERSION = 1.0
SSL_CLIENT_AUTHENTICATION = TRUE

WALLET_LOCATION = (SOURCE = (METHOD = FILE)
   (METHOD_DATA = (DIRECTORY = /u01/wallet_location)   )
)

# vi tnsnames.ora
v10g =  (DESCRIPTION =
 (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = XXXX)(PORT = XXXX))    )
 (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCPS)(HOST = XXXX)(PORT = XXXX))    )
 (CONNECT_DATA = (SERVICE_NAME = v10g)
))





-- (客户端)创建自签名证书，生成用户证书和签名它的CA证书
orapki wallet add -wallet /u01/wallet_location -dn "CN=client" -keysize 512 -self_signed -validity 365 -pwd pwd123456

-- 导出客户端根证书
orapki wallet export -wallet /u01/wallet_location -dn "CN=client" -cert client_ca.cert

-- 导出服务器根证书

-- 导入服务器根证书到客户端
orapki wallet add -wallet wallet_location -trusted_cert -cert server_ca.cert -pwd pwd123456

-- 导入客户端根证书到服务器
orapki wallet add -wallet wallet_location -trusted_cert -cert client_ca.cert -pwd pwd123456


-- 测试项：
-- 防火墙禁用1521端口，
-- 对外只提供2484tcps加密端口
firewall-cmd --permanent --add-port=1521/tcp
firewall-cmd --permanent --remove-port=1521/tcp
firewall-cmd --permanent --add-port=2484/tcp
firewall-cmd --permanent --remove-port=1521/tcp
systemctl restart firewalld.service 
firewall-cmd --list-all 


测试：
sqlplus / as sysdba@ORCL
sqlplus cog/cog@ORCL
sqlplus /@pdb1
select user from dual;
select sys_context('userenv','network_protocol') from dual;

sqlplus cog/cog@1.1.1.91/pdb1
sqlplus cog/cog@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCPS)(HOST=1.1.1.91)(PORT=2484)))(CONNECT_DATA=(SERVICE_NAME=PDB1)))"


sqlplus / as sysdba@ORCL
sqlplus cog/cog@ORCL
sqlplus /@pdb1
select user from dual;
select sys_context('userenv','network_protocol') from dual;

sqlplus cog/cog@1.1.1.91/pdb1
sqlplus cog/cog@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCPS)(HOST=1.1.1.30)(PORT=2484)))(CONNECT_DATA=(SERVICE_NAME=ORCL)))"

tnsping "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=1.1.1.30)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORCL)))"
tnsping "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCPS)(HOST=1.1.1.21)(PORT=2484)))(CONNECT_DATA=(SERVICE_NAME=ORCL)))"

sqlplus cog/cog@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) (HOST=1.1.1.22)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORCL)))"
sqlplus cog/cog@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCPS)(HOST=1.1.1.30)(PORT=2484)))(CONNECT_DATA=(SERVICE_NAME=ORCL)))"    
sqlplus cog/cog@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) (HOST=1.1.1.21)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORCL)))"  
sqlplus cog/cog@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCPS)(HOST=1.1.1.21)(PORT=2484)))(CONNECT_DATA=(SERVICE_NAME=ORCL)))"  
sqlplus cog/cog@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCPS)(HOST=1.1.1.30)(PORT=2484)))(CONNECT_DATA=(SERVICE_NAME=ORCL)))"  
sqlplus cog/cog@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCPS)(HOST=1.1.1.22)(PORT=2484)))(CONNECT_DATA=(SERVICE_NAME=ORCL)))"  

strace sqlplus cog/cog@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCPS)(HOST=1.1.1.21)(PORT=2484)))(CONNECT_DATA=(SERVICE_NAME=ORCL)))"  

复制