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:の後に/を入れるとエラーになる)
まとめ
という感じで。
この知識が生かされないことを祈ります。。。。
正規表現で空白をマッチさせるときは\sじゃなくて\hのほうが良いのかもしれない。
正規表現で空白文字とマッチさせるときは\sを使用することが多いと思いますが、本当に求めているものは\hなのかもしれません。
- \s 空白文字: [\t\n\x0B\f\r]
- \h 水平方向の空白文字: [ \t\xA0\u1680\u180e\u2000-\u200a\u202f\u205f\u3000]
\sはasciiで定義されている空白っぽい文字(改行含む)にマッチしますが、\hはUnicodeで定義されている空白っぽい文字(改行を含まない)にマッチします。 歴史的経緯で\sを使う人も多いですが、改行含んでマッチさせたい場合なんてほとんどないのだから\hを使いましょう。
Javaの正規表現の一覧は以下から(※ほぼPerl互換)
なお：
Java に限らず、\h の扱いが言語ごとに違うみたいです 😰
— YujiSoftware (@YujiSoftware) 2023年12月23日
Java … Unicode の空白
Perl … Unicode の空白（\s と等価？）
Ruby … 16進数字 [0-9a-fA-F]
Go … 対応しない
C# … 対応しない（ただし、\s が Unicode の空白にもマッチ）
SQLExceptionをハンドリングしなくてもよくなるJDBCドライバ
DB障害時にもJDBCから例外返すなと言われるとねぇ
— いとうちひろ(Chihiro Ito) (@chiroito) February 7, 2019
そういえば、log4jdbcみたいにラップしてあげれば実装できるよねーということに気が付いたので、実装してみました。
なんと！SQLExceptionが発生したとしても全部握りつぶしてくれる！！！！！
DBが落ちたとしても動く！！！！(ただし結果セットは帰ってこない)
例外が発生するのが気に食わないという人が居たらライブラリを追加してみんなで幸せになろう！(幸せになれるとは言ってない)
Tomcatのログの保持日数がTomcat 9から90日になっていた。
題名のとおり。
ついでに、Tomcat-9.0.13からはログの出力文字がUTF-8に変更されました。 よって、現状は以下のような感じになっています。
※conf/logging.propertiesから抜粋
※下2行が増えた。
1catalina.org.apache.juli.AsyncFileHandler.level = FINE
1catalina.org.apache.juli.AsyncFileHandler.directory = ${catalina.base}/logs
1catalina.org.apache.juli.AsyncFileHandler.prefix = catalina.
1catalina.org.apache.juli.AsyncFileHandler.maxDays = 90
1catalina.org.apache.juli.AsyncFileHandler.encoding = UTF-8
追記：
バックポートもされて古いのも更新されているみたい。
8.5.37も90日保管＋UTF-8みたいですよー。https://t.co/kTkpQWvooo
— つりーべる (@tada_suzu) 2019年1月22日
Oracle DBのjdbcドライバをmavenから利用する。
日本語情報だと、どこからか取ってきたjdbcドライバを自前でローカルリポジトリにインストールしろという記事しか見つからなかったので。
Oracle社はOracle DB用のjdbcドライバを自前のmavenリポジトリでリリースしており、そちらから取得する設定を行うことで、ローカルリポジトリに個別にインストールしなくても使用することが出来ます。
基本的には以下の記事のとおりです。
記事自体は2016年に公開されたものですが、細かいバージョン等はアップデートされているようで、現時点では以下のバージョンが利用できるとの事。
- 18.3.0.0
- 12.2.0.1
- 12.1.0.2
- 12.1.0.1
- 11.2.0.4
まずは、Oracleのアカウントを作成して以下にアクセスし、利用規約に同意する必要があります。
https://www.oracle.com/webapps/maven/register/license.html
%USER_HOME%/.m2/setting.xmlに以下の記述を追加してください。 serverのusernameとpasswordはOracleにアカウントを登録した自分のものを使用する必要があります。
<settings>
<!-- Oracleのサーバーのプロファイルでの有効化 -->
<profiles>
<profile>
<id>oracle</id>
<activation>
<activeByDefault>true</activeByDefault>
</activation>
<repositories>
<repository>
<id>maven.oracle.com</id>
<name>oracle-maven-repo</name>
<url>https://maven.oracle.com</url>
<layout>default</layout>
<releases>
<enabled>true</enabled>
<updatePolicy>always</updatePolicy>
</releases>
</repository>
</repositories>
</profile>
</profiles>
<!-- Oracleのサーバーの追加 -->
<servers>
<server>
<id>maven.oracle.com</id>
<username>OracleアカウントのID</username>
<password>Oracleアカウントのパスワード</password><!-- 暗号化したい場合は後述 -->
<configuration>
<basicAuthScope>
<host>ANY</host>
<port>ANY</port>
<realm>OAM 11g</realm>
</basicAuthScope>
<httpConfiguration>
<all>
<params>
<property>
<name>http.protocol.allow-circular-redirects</name>
<value>%b,true</value>
</property>
</params>
</all>
</httpConfiguration>
</configuration>
</server>
</servers>
</settings>
この状態で、pom.xmlのdependencyに以下のように記載すればOKです。atrifactIdやversionについてはサポート情報を参照しつつ適切なものを指定してください。
<dependency>
<groupId>com.oracle.jdbc</groupId>
<artifactId>ojdbc8</artifactId>
<version>18.3.0.0</version>
</dependency>
パスワードの暗号化
パスワードを暗号化したい場合は、setting.xmlの隣にsettings-security.xmlを置く必要があります。 以下のコマンドでmaven用のマスターパスワードを暗号化します。
mvn -encrypt-master-password "マスターパスワード"
(例)
$ mvn -encrypt-master-password password
{9w4lYAShAwsHjAeOPalqAanbuKtz+vReNStoIHpd0wc=}
setting-security.xmlの内容としては以下のように記述します。
<settingsSecurity> <master>上記のコマンドの出力結果</master> </settingsSecurity>
以下のコマンドを打つことでOracleアカウントのパスワードを暗号化することが出来ます。
mvn -encrypt-password Oracleアカウントのパスワード
(例)
$ mvn -encrypt-password password
{cmpjcpQb1KsH1O6ljNnoidSMmsYshOwW4kF90QFXzd0=}
この出力内容をpasswordのタグの中に記載することでパスワードが暗号化された状態で使用できます。 マスターパスワードが隣に保存されているかぎりあまり意味はないですが、pom.xmlに記載したい場合は意味がある・・・・・はず。
JavaのAPIを使用して証明書にアクセスする(keytoolは使用せずに)
証明書の期限切れが世の中をにぎわせている今、プログラムから証明書の期限を取得してAPIとして期限切れかどうかをチェックできるものがあるといいよなぁと思い、Javaから証明書にアクセスする方法を調べてみました。
証明書の自動更新とかの実装例を見てると3か月に1回の定期起動みたいな感じになっているので、証明書の中身を見て、切れそうになったら更新みたいな感じで実装できると幸せになる？ならない？もうありそう？
とりあえず、簡単にできます。
keystoreから取得する場合
import java.io.FileInputStream; import java.io.IOException; import java.security.KeyStore; import java.security.KeyStoreException; import java.security.NoSuchAlgorithmException; import java.security.cert.CertificateException; import java.security.cert.CertificateFactory; import java.security.cert.X509Certificate; import java.text.SimpleDateFormat; import java.util.Enumeration; public class KeyStoreAccess { public static void main(String[] args) throws KeyStoreException, IOException, NoSuchAlgorithmException, CertificateException { SimpleDateFormat f = new SimpleDateFormat("yyyy/MM/dd"); // Javaのキーストアから取得 KeyStore ks = KeyStore.getInstance("JKS"); try (FileInputStream in = new FileInputStream("C:\\Program Files\\Java\\jdk-11\\lib\\security\\cacerts")) { ks.load(in, "changeit".toCharArray()); //changeitはJavaのキーストアのデフォルトパスワード } Enumeration<String> aliases = ks.aliases(); while (aliases.hasMoreElements()) { String alias = aliases.nextElement(); X509Certificate certificate = (X509Certificate) ks.getCertificate(alias); System.out.println("名前:" + alias + ".発行者:" + certificate.getIssuerX500Principal().getName() + "," + f.format(certificate.getNotAfter()) + "まで"); } } }
単体の証明書から取得する場合
import java.io.FileInputStream; import java.io.IOException; import java.security.KeyStore; import java.security.KeyStoreException; import java.security.NoSuchAlgorithmException; import java.security.cert.CertificateException; import java.security.cert.CertificateFactory; import java.security.cert.X509Certificate; import java.text.SimpleDateFormat; import java.util.Enumeration; public class KeyStoreAccess { public static void main(String[] args) throws KeyStoreException, IOException, NoSuchAlgorithmException, CertificateException { SimpleDateFormat f = new SimpleDateFormat("yyyy/MM/dd"); // 直接証明書を取得する。 try (FileInputStream fis = new FileInputStream("C:\\work\\aaa.cer")) { CertificateFactory cf = CertificateFactory.getInstance("X.509"); X509Certificate cert = (X509Certificate) cf.generateCertificate(fis); System.out.println("発行者:" + cert.getIssuerX500Principal().getName() + "," + f.format(cert.getNotAfter()) + "まで"); } } }
参考：
