之前说过了ORACLE可以使用存储过程来发送未加密的smtp协议邮件,但是现在越来越多邮箱服务商要求强制使用ssl加密,按照前一篇文章的方式,是无法发送的,因此这一篇教大家如何使用ORACLE存储过程来发送带ssl加密的email。
文章分以下几个部分
一、获取邮件SSL证书文件
二、把证书添加到oracle 钱夹(wallet)
三、发送邮件的方法
一、获取邮件ssl证书文件
方法一:使用浏览器获取证书(以360极速浏览器为例)
打开对应邮箱的网页客户端,例如QQ邮箱,浏览器会显示一把锁,如图
点击这个锁,弹出
,
然后点击证书信息,显示
,
点击详细信息,复制到文件
,
弹出证书导出向导
,
下一页
下一页
,
任意选择一个路径保存此文件,文件名没有特殊要求。
然后回到证书页面,点击证书路径
,
可以看到有三层,这三层实际上是三个证书,点到第二层,查看证书,按前面的操作保存证书文件,同理第一层也要保存文件。
至此,我们已经获取了3个证书文件
方法二:使用邮箱smtp客户端网络抓包(以foxmail+wireshark+winhex为例)
在foxmail中配置要发送邮件的账号,启动WIRESHARK选择对外网的网络适配器,开始进行抓包,操作FOXMAIL收取一次该账号的邮件,停止wireshark的抓包,接下来在抓包记录中添加过滤器tcp.port == 993 || udp.port == 993(这个是imap收件的SSL端口),并按info排序
找到certificate,如上图,可以看到certificates(3958 bytes)下有三段十六进制数据,这个就是三个证书文件,需要分别保存,
在”certificate:308207833......”上面点击鼠标右键,复制-值,然后打开winhex,点编辑,剪贴板数据,粘贴至新文件,格式选择 ASCILL HEX,保存成cer后缀的文件即可,同理,对另外两个证书文件也要进行保存。
上述两种方法均可获得证书文件,但实测发现,第一层(root)和第二层,保存的证书文件MD5一致,第三层证书MD5不一致,仔细对比发现是证书的有效期不一样,因此无问题。
抓包法适用于邮箱服务商的web页面和实际邮箱收发服务器不一致的情况,所以抓包法获取smtp证书更为通用及准确,嫌抓包法麻烦的可以用方法一浏览器获取证书的方式碰碰运气。
二、把证书添加到ORACLE钱夹(wallet)
关于wallet的介绍,请参考另一篇文章,这里只做简单描述
【ORACLE】简单谈谈ORACLE中WALLET(钱夹)
可以使用命令行工具orapki,或者gui工具Wallet Manager,生成一个钱夹目录,比如d:\smtp_wallet,设置好密码,假设是123,然后添加上面获得的3个证书文件,保存(ORACLE 12c以上的版本,不能添加最末级节点证书,添加2个就好了,加3个会导致使用时报错ORA-29024: Certificate validation failure
参考 https://stackoverflow.com/questions/19380116/using-utl-http-wallets-on-12c-certificate-validation-failure)。
三、发送邮件的方法
其实和不带ssl的邮件发送方式几乎一样,只在UTL_SMTP.OPEN_CONNECTION时有区别,我们看下这个ORACLE自带函数的说明
FUNCTION open_connection(host INVARCHAR2,
port INPLS_INTEGERDEFAULT25,
tx_timeout INPLS_INTEGERDEFAULTNULL,
wallet_path INVARCHAR2DEFAULTNULL,
wallet_password INVARCHAR2DEFAULTNULL,
secure_connection_before_smtp INBOOLEANDEFAULTFALSE,
secure_host INVARCHAR2DEFAULTNULL)
RETURN connection;
其中 “secure_connection_before_smtp”、“wallet_path” 和 “wallet_password”三个参数是能否成功发送ssl邮件的关键
首先,我们要知道带SSL的smtp端口一般默认为“465”,不加密的端口默认为“25”
然后这一段写成:
L_CONN := UTL_SMTP.OPEN_CONNECTION(host =>‘smtp.qq.com’, --发件人的SMTP服务器
port =>‘465’ --端口
wallet_path =>'file:d:\smtp_wallet', --钱夹路径
wallet_password =>'123', --钱夹密码,如果钱夹设置了保存密码,这里可以不传
secure_connection_before_smtp =>true --是否开启安全连接
);
完整代码请从我的github获取。
https://github.com/Dark-Athena/PROCSENDEMAIL_SSL-oracle
提示
这个证书是存在有效期的,而且这种方式无法自动更新,实测某些服务器在证书过期后仍然能使用,如果发邮件时提示证书错误之类的,就再按这个方式获取最新的证书生成钱夹就行了。
之后会考虑更加通用及自动的获取证书方式。
另外UTL_SMTP这个包其实还支持starttls,但目前我测试过国内的十多家邮箱,没有一家是用这个的,所以不细说了。
写在最后
2014年的时候,当时要做自动监控告警和报表推送,我开始研究怎么用ORACLE数据库发送邮件,网上有很多现成的代码,所以没什么难度。最开始是用的公司自建exchange邮箱发送,但是后来该域名被qq邮箱列为黑名单了,甚至国际反垃圾邮件联盟都把这个域名加了进去,导致很多用户都收不到邮件。
2016年的时候,我想着既然用户大多都用QQ邮箱,所以尝试使用QQ邮箱发送,但是正好QQ邮箱开始加强安全认证,必须使用ssl,所以就在网上找ORACLE能不能发带ssl的smtp邮件,结果当时国内所有文章都说不行,但是我翻阅了ORACLE官方文档,
https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/u_smtp.htm
发现是有通过加密端口发送的功能的,了解到需要wallet,但是没说wallet怎么来,我问DBA,DBA直接帮我把数据库用wallet加密了,结果所有用到这个数据库的功能全部崩了,于是赶紧把wallet关了,还好不是跑业务的库。但是我不死心,接着找,终于找到一篇
http://www.idevelopment.info/data/Oracle/DBA_tips/PL_SQL/PLSQL_19.shtml
这个是ORACLE大佬Jeffrey M.Hunter的个人博客,其中的文章被很多人引用,可惜的是目前该博客已经关闭了,不过可以通过网站历史快照网站查看
https://web.archive.org/web/20190103103618/http://www.idevelopment.info/data/Oracle/DBA_tips/PL_SQL/PLSQL_19.shtml (需要梯子),
但是,这篇文章只说了HTTPS的证书怎么弄到手和怎么添加到wallet,没有说smtp的。
最后不得以再研究了一下ssl的机制,知道这个证书是客户端找服务端要的,既然找不到证书放哪了,那我可以直接网络抓包把smtp证书弄出来。
所以在2016年的时候我这边就已经可以用oracle发送ssl的邮件了,当时网上的确没有一份完整的教程。
在这次研究过程中,我稍微认识到了wallet是啥,tcp/ip是啥,还有smtp发邮件时究竟和服务器间是如何通讯的,甚至成功手动在cmd里通过telnet发送了一封邮件。
真是获益良多。
只是email这玩意太容易被邮件服务商拉黑,而且还有一个国际反垃圾邮件联盟的存在,公司外网出口就是一个IP,极度容易被封。所以这个功能需求的用户应该没以前那么多了,对内部用户大多通过企业微信或者钉钉来发送,对外部基于数据安全的要求,建议手工发email。