Autonomous DBでOCIメール配信サービスを使用してメールを送信

本ブログは、[オラクル・クラウドの個人ブログ](https://qiita.com/letian/items/10152a4fec813c1b7d98)の1つです。

# 初めに
前回の記事で、[OCI電子メール配信サービスの概要](https://qiita.com/liu-wei/items/9878ea57ddde7933813a)を紹介しました。今回は「Autonomous Database」でこのサービスを使ってメールを送信する方法を紹介します。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121622/334648dd-c64f-72bb-f921-2ec15e9ad257.png", width="550">

__検証環境__
リージョン：東京
ワークロード・タイプ：Data Warehouse (19c)
アクセス・タイプ： (下記両方は動作確認済)
✅ すべての場所からのセキュア・アクセスを許可
✅ VCN (プライベート・エンドポイント)

__ステップ__
- [事前準備](#事前準備)
  - [SMTP接続エンドポイントを取得 (Console)](#smtp接続エンドポイントを取得-console)
  - [新しいDBユーザーを作成 (ADB - 管理者)](#新しいdbユーザーを作成-adb---管理者)
  - [プライベート・サブネット内のADBを利用する場合](#プライベートサブネット内のadbを利用する場合)
- [1. SMTP資格証明を生成 (Console)](#1-smtp資格証明を生成-console)
- [2. ポリシーを作成 (Console)](#2-ポリシーを作成-console)
- [3. 承認済送信者を作成 (Console)](#3-承認済送信者を作成-console)
- [4. メールユーザーにSMTPアクセスを許可 (ADB - 管理者)](#4-メールユーザーにsmtpアクセスを許可-adb---管理者)
- [5. 電子メールを送信するPL/SQLプロシージャを作成 (ADB - メールユーザー)](#5-電子メールを送信するplsqlプロシージャを作成-adb---メールユーザー)
- [6. テスト電子メールを送信 (ADB - メールユーザー)](#6-テスト電子メールを送信-adb---メールユーザー)

# 事前準備
## SMTP接続エンドポイントを取得 (Console)
東京リージョン：`smtp.email.ap-tokyo-1.oci.oraclecloud.com`
コンソールで情報を取得できます（"電子メール配信 -> 構成"）。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121622/5dfab99d-50fc-64ad-016f-16d8006d3ef1.png)
他リージョンは、[SMTP接続の構成](https://docs.oracle.com/ja-jp/iaas/Content/Email/Tasks/configuresmtpconnection.htm)を参照してください。

## 新しいDBユーザーを作成 (ADB - 管理者)
ADB管理者ユーザー（ADMIN）で手軽い検証だけをする場合は、このステップをスキップしてもよいです。

「ADMIN」ではなく、別の専用のメール・ユーザーを使用することをお勧めします。この場合は、管理者ユーザーで「Autonomous DB」に接続し、次のコマンドを実行してユーザーを新規作成し、必要な権限を付与します。
```sql
-- USER SQL
CREATE USER "EMAILUSER" IDENTIFIED BY "<password>"  
DEFAULT TABLESPACE "DATA"
TEMPORARY TABLESPACE "TEMP";

-- ROLES
GRANT "CONNECT" TO "EMAILUSER" ;

-- SYSTEM PRIVILEGES
GRANT CREATE ANY PROCEDURE TO "EMAILUSER" ;
GRANT EXECUTE ANY PROCEDURE TO "EMAILUSER" ;
```

## プライベート・サブネット内のADBを利用する場合
SMTP接続エンドポイントに通信するには、OCIネットワークの下記箇所が正しく設定ていることをご確認ください。

__ルート・テーブル__ 
プライベート サブネットに紐づいているルート・テーブルに「NATゲートウェイ」または「サービス・ゲートウェイ」を追加します。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121622/8a6026e8-a970-fe28-bb6a-334dbbd425ee.png", width="600">
__エグレス・ルール (Egress Rule)__
セキュリティ・リスト (または NSG) でTCP ポート<font color=blue>587</font>が許可されていることを確認します。

# 1. SMTP資格証明を生成 (Console)
電子メール配信を介した電子メール送信のためには、Simple Mail Transfer Protocol (SMTP)の資格証明が必要です。セキュリティのベスト・プラクティスは、すでに権限が割り当てられているコンソール・ユーザーではなく、新規ユーザーに対してSMTP資格証明を生成することです。

新しいOCIユーザーを作成してログインします。それで下記を参照してSMTP資格証明を作成してください。
[OCI Email Deliveryでメールを送信するクイック・スタート - STEP 1](https://qiita.com/liu-wei/items/9878ea57ddde7933813a#1-smtp%E8%B3%87%E6%A0%BC%E8%A8%BC%E6%98%8E%E3%81%AE%E4%BD%9C%E6%88%90)

# 2. ポリシーを作成 (Console)
新しいOCIユーザーは、`email-family`リソースを管理する権限を持つ必要があります。テナンシ管理者としてログインし、下記を参照してポリシーを作成してください。
 [OCI Email Deliveryでメールを送信するクイック・スタート - STEP 2](https://qiita.com/liu-wei/items/9878ea57ddde7933813a#2-%E3%83%9D%E3%83%AA%E3%82%B7%E3%83%BC%E3%81%AE%E4%BD%9C%E6%88%90)

ステートメント: `Allow group <group name> to manage email-family in compartment <compartment name>`

# 3. 承認済送信者を作成 (Console)
新しいOCIユーザー（ポリシー付与された）としてログインし、下記を参照して承認済送信者を作成してください。
[OCI Email Deliveryでメールを送信するクイック・スタート - STEP 4](https://qiita.com/liu-wei/items/9878ea57ddde7933813a#4-%E6%89%BF%E8%AA%8D%E6%B8%88%E9%80%81%E4%BF%A1%E8%80%85%E3%81%AE%E4%BD%9C%E6%88%90)

作成後、以下のように送信者の情報を取得できます。
- a) 電子メール・ドメインをお持ちでない場合：
電子メール配信 -> 承認済送信者
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121622/2d46b10c-0df7-f399-b5c8-61a1c0b465ab.png)
- b) 電子メール・ドメインをお持ちの場合：
電子メール配信 -> 電子メール・ドメイン -> 承認済送信者
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121622/3c22451c-6248-4cc0-7814-610ad9d99ec4.png)
<font color=blue>DKIM署名付きの電子メール・ドメイン</font>は必須ではありませんが、強くお勧めします。このテストではこれを使っています。

# 4. メールユーザーにSMTPアクセスを許可 (ADB - 管理者)
管理者（ADMIN）ユーザーで Autonomous DB に接続し、次のコマンドを実行します。
```sql
BEGIN
  -- Allow SMTP access for Email User
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => 'smtp.email.ap-tokyo-1.oci.oraclecloud.com',
    lower_port => 587,
    upper_port => 587,
    ace => xs$ace_type(privilege_list => xs$name_list('SMTP'),
                       principal_name => 'EMAILUSER',
                       principal_type => xs_acl.ptype_db));
END;
/
```
出力結果：`PL/SQL procedure successfully completed.`

__ドキュメントの注意点__
1. [ドキュメント](https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/smtp-send-mail.html)のサンプルコードにご注意ください。 変数`host`の文字列は、電子メール・ドメインではなく、<font color=blue>SMTP接続エンドポイント</font>（`smtp.email.<リージョン識別子>.oci.oraclecloud.com`）に置き換えてください。 これは<strong>重要</strong>です。そうしないと、メールを送信する際にエラーが発生します。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121622/1ae743c9-d7e6-ca72-9c31-008749a6f618.png", width="550">
2. サンプルコードの`principal_name`の行は、"ADMIN"ユーザーにSMTPアクセスを許可していますが、 別のユーザーに変更することをお勧めします。このテストでは、新規作成のユーザー`EMAILUSER`を使っています。

# 5. 電子メールを送信するPL/SQLプロシージャを作成 (ADB - メールユーザー)
__クレデンシャルの作成__
事前にクレデンシャルを作成し、送信用ストアドプロシージャ`SEND_MAIL`にユーザー名とパスワードを<font color=blue>ハードコーディングしない</font>ことを推奨します。
```sql
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'SMTP_CRED',
    username => '<SMTP credential username>',
    password => 'SMTP credential password'
  );
END;
```
変数 <strong>username</strong> : `<SMTP credential username>`を実際の文字列で置き換えます ([step-1](#1-smtp資格証明を生成-console)で生成)。
変数 <strong>passwd</strong> : `<SMTP credential password>`を実際の文字列で置き換えます( [step-1](#1-smtp資格証明を生成-console)で生成)。

__PL/SQLプロシージャの作成__

```sql
CREATE OR REPLACE PROCEDURE SEND_MAIL (
  msg_to varchar2,
  msg_subject varchar2,
  msg_text varchar2 ) 
IS

  mail_conn utl_smtp.connection;
  msg_from varchar2(50) := '<Approved Sender>';
  mailhost VARCHAR2(50) := 'smtp.email.ap-tokyo-1.oci.oraclecloud.com';
  connection_open BOOLEAN := FALSE; 
  CRLF CONSTANT VARCHAR2(2) := UTL_TCP.crlf;
BEGIN
  mail_conn := UTL_smtp.open_connection(mailhost, 587);
  connection_open := TRUE; -- Connection is now open
  UTL_SMTP.starttls(mail_conn);
  UTL_SMTP.SET_CREDENTIAL(mail_conn, 'SMTP_CRED', schemes => 'PLAIN');
  UTL_SMTP.mail(mail_conn, msg_from);
  UTL_SMTP.rcpt(mail_conn, msg_to);
  
  UTL_SMTP.open_data(mail_conn);
  UTL_SMTP.write_data(mail_conn, 'Date: ' || TO_CHAR(CURRENT_DATE, 'DD-MM-YYYY HH24:MI:SS') || CRLF);
  UTL_SMTP.write_data(mail_conn, 'To: ' || msg_to || CRLF);
  UTL_SMTP.write_data(mail_conn, 'From: ' || msg_from || CRLF);
  UTL_SMTP.write_data(mail_conn, 'Subject: ' || msg_subject || CRLF);
  UTL_SMTP.write_data(mail_conn, 'Reply-To: ' || msg_to || CRLF || CRLF);
  UTL_SMTP.write_data(mail_conn, msg_text || CRLF || CRLF);
  
  UTL_SMTP.close_data(mail_conn);
  UTL_SMTP.quit(mail_conn);

EXCEPTION
  WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error THEN
    dbms_output.put_line('SMTP Error: ' || SQLERRM);
  WHEN OTHERS THEN
    dbms_output.put_line('Unexpected Error: ' || SQLERRM);
  -- Common cleanup logic for all exceptions
  IF connection_open THEN
    BEGIN
      UTL_SMTP.close_data(mail_conn);
      UTL_SMTP.quit(mail_conn);
    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line('Error closing SMTP connection: ' || SQLERRM);  -- Handle errors during cleanup
    END;
  END IF;
END;
/
```
出力結果：`Procedure SEND_MAIL compiled`

変数 <strong>msg_from</strong> : `<Approved Sender>`を実際の文字列で置き換えます([step-3](#3-承認済送信者を作成-console)で作成)。
変数 <strong>mailhost</strong> : 他のリージョンを使用する場合は変更してください。

# 6. テスト電子メールを送信 (ADB - メールユーザー)
メールユーザーとして Autonomous DB に接続し、次のコマンドでテスト用メールを送信します。 (`<Recipient_Email_Address>` を実際のメールアドレスに置き換えてください。)

コマンドの書き方：
`exec send_mail('<メールの宛先>','<メールの表題>','<メールの本文>')`

```sql
exec send_mail('<Recipient_Email_Address>', 'Email from Oracle Autonomous Database', 'Sent using UTL_SMTP');
```
出力結果：`PL/SQL procedure successfully completed.`

受信者には次のようなメールが届きます。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121622/7fba547d-150e-9d3c-4d02-5e715e888246.png", width="500">

:::note
電子メール配信では、<font color=blue>2MB</font>までのメッセージ(メッセージ・ヘッダー、本文およびアタッチメントを含む)がサポートされています。送信ドメインに<strong>SPFおよびDKIM</strong>を設定してある場合は、要件に基づいて、この制限の引上げをリクエストできます。リクエストできる最大サイズは<font color=blue>60MB</font>です。
:::

以上

----
__関連記事__
[オラクル・クラウドの個人ブログ一覧](https://qiita.com/liu-wei/items/10152a4fec813c1b7d98)
[OCI電子メール配信サービスの概要](https://qiita.com/liu-wei/items/9878ea57ddde7933813a)
[OCI Computeにログインする時にメール通知を送信する方法](https://qiita.com/liu-wei/items/5036f3fbdd4e098fa997)

__公式ドキュメント__
[Autonomous Databaseでの電子メールの送信](https://docs.oracle.com/ja-jp/iaas/autonomous-database-serverless/doc/smtp-send-mail.html)