Oracle DBのPL/SQLからOffice365(starttls)を利用してメールを送信する
君がこれを読んでいるということは非常につらい状態に置かれているということだろう。 公式ドキュメント通りにやっても動かないという人向けに記す。
以下についてはきちんと読みましょう。
https://support.microsoft.com/ja-jp/help/4458479/improvements-in-smtp-authenticated-submission-client-protocolsupport.microsoft.com
サマリー
- ほかの手段があるならやらないほうが良い。回れ右をすること。
- STARTTLS(TLS接続)を利用するためには証明書のキーストアとしてOracle Walletを使用する必要がある。デフォルトでは何の証明書も入っていないため、必要な証明書については自分で持ってきて、定期的な更新作業も自分で行う必要がある。
- ACLは適切に設定しよう。
- OracleのUTL_SMTPはメール送信をするためのパッケージではなく、SMTPプロトコルを会話するためのユーティリティなので、SMTPの会話内容を抑えているなら容易い。
- SMTPのプロトコルが理解できていないなら、JavaMail等、メール送信を行うためのライブラリを利用して、そのデバッグログ等でSMTPのプロトコル内容を把握してから使うべき。
Oracle Walletを設定する。
Oracle Walletはいわゆる証明書のキーストアです。Web標準では公開鍵式暗号を使用しており、事前に公開鍵をインポートしておく必要があります。 通常、ブラウザ等では信頼性が高いとされている公開鍵をデフォルトで保持しており、また、セキュリティパッチ等で自動的に更新されるのですが、Oracle DBではデフォルトでは公開鍵をひとつも持っていないため、インストールする必要があります。
Office365の場合は以下からダウンロードすることができます。
証明書のOracle Wallet(証明書のキーストア)への取り込みはOracle Wallet Managerを使用することで行えます。マニュアル通りなので、そちらを参照してください。
作成したOracle Walletファイル(ディレクトリごと)はローカルの適切な場所に配置します。
Oracle Walletに保存した証明書は適時手動で更新する必要があります。注意してください。
Oracle Walletに保存した証明書は適時手動で更新する必要があります。注意してください。
Oracle Walletに保存した証明書は適時手動で更新する必要があります。注意してください。
大事なことなので3回書いておきます。
ACLを設定する
ACLはアクセスコントロールリストです。Oracle DBから接続できる外部サイトを指定します。 DBMS_NETWORK_ACL_ADMINのマニュアル通りに設定してください。
以下のような感じになります。
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('ACL.xml', 'ACL for SMTP Server', SCHEMANAME, TRUE, 'connect'); DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('ACL.xml', SCHEMANAME, TRUE, 'resolve'); DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('ACL.xml', 'smtp.office365.com');
UTL_SMTPを利用してメールを送信する
インフラ対応が終わったので、ここからUTL_SMTPを利用してメールを送信するプログラムを書いていきます。 UTL_SMTPはメールを送信するためのパッケージではなく、SMTPプロトコルで会話するためのパッケージなので、使用するためにはSMTPプロトコルを理解する必要があります。
気力がある人は以下あたりを読みましょう。
https://www.ietf.org/rfc/rfc3207.txt
https://www.ietf.org/rfc/rfc5321.txt
読むのが面倒くさいので、ここではぐぐれば出てくるJavaMailの適切な設定を行い、デバッグログ出力を利用することで、何をすればいいのかというのを調べます。 Office365ではSTARTTLSが利用されており、以下のような感じでメール送信プロセスが行われるようです。 本当はデバッグログがもうちょっと出ますが、今回は必要な通信部分のみ抜粋しました。
>から始まる行はクライアントからサーバーへの送信。それ以外の行はサーバー側からクライアントへの受信です。
(接続) 220 OSAPR01CA0250.outlook.office365.com Microsoft ESMTP MAIL Service ready at Sun, 14 Apr 2019 06:52:53 +0000 > EHLO XXXXXXXXXXXXXXXXXXX 250-OSAPR01CA0250.outlook.office365.com Hello [180.52.91.76] 250-SIZE 157286400 250-PIPELINING 250-DSN 250-ENHANCEDSTATUSCODES 250-STARTTLS 250-8BITMIME 250-BINARYMIME 250-CHUNKING 250 SMTPUTF8 > STARTTLS 220 2.0.0 SMTP server ready > EHLO XXXXXXXXXXXXXXXXXXX 250-OSAPR01CA0250.outlook.office365.com Hello [180.52.91.76] 250-SIZE 157286400 250-PIPELINING 250-DSN 250-ENHANCEDSTATUSCODES 250-AUTH LOGIN XOAUTH2 250-8BITMIME 250-BINARYMIME 250-CHUNKING 250 SMTPUTF8 (ここでログインメカニズムを実行) > MAIL FROM:<sender's office365 mail address> 250 2.1.0 Sender OK > RCPT TO:<receiver's office365 mail address> 250 2.1.5 Recipient OK > DATA 354 Start mail input; end with <CRLF>.<CRLF> > Date: Sun, 14 Apr 2019 15:52:53 +0900 (JST) > From: sender's office365 mail address > To: receiver's office365 mail address > Message-ID: <###message id###> > Subject: subject > MIME-Version: 1.0 > Content-Type: text/plain; charset=us-ascii > Content-Transfer-Encoding: 7bit > > content > .
この通信を模したPL/SQLは以下のようになります。 変数の型については適時UTL_SMTPのドキュメントを参照して補ってください。
conn := UTL_SMTP.open_connection( host => 'smtp.office365.com', port => '587', tx_timeout => 60, wallet_path => 'file:path_to_wallet', wallet_password => 'password', secure_connection_before_smtp => false); UTL_SMTP.EHLO(conn, 'my host name'); UTL_SMTP.STARTTLS(conn); UTL_SMTP.EHLO(conn, 'my host name'); UTL_SMTP.AUTH( c => conn, username => 'sender's office365 mail address', password => 'office365 password', schemes => 'LOGIN XOAUTH2'); UTL_SMTP.MAIL(conn, 'sender's office365 mail address'); ~~~ 以下、UTL_SMTPパッケージのマニュアル通り ~~~
ポイントは以下の通りです。
- SMTPプロトコルでの会話内容を愚直に守る。
- UTL_SMTP.AUTHのschemesの値は、サーバーにEHLOコマンドで問い合わせた結果、AUTHに入ってくる値を使用する。
- wallet_pathはfileプロトコルの形式で書いてくれとマニュアルに記載があるが、Windowsの場合はfile:c:/hogehogeの形式でないと受け付けてくれない。(file:///c:/hogehogeのようにfile:の後に/を入れるとエラーになる)
まとめ
という感じで。
この知識が生かされないことを祈ります。。。。