[OCI] PL/SQL SDKを使ってAutonomous Databaseからパスワード期限切れの警告メールをNotificationサービス経由で送ってみた

10759 ワード
OracleDatabase oraclecloud Autonomous plsql oci plsql テキストリンク

はじめに

ある日突然アプリケーションがDBに接続できなくなるトラブルが発生し、その原因がアプリケーションで使用しているDBユーザのパスワード期限切れだった、という話を時々耳にします。

DB自体がパスワード期限切れを警告してくれたらそのようなトラブルも減らせるのではないかと考え、ユーザのパスワードの有効期間を確認して、一定日数以下の場合はNotificationサービス経由で警告メールを送信するPL/SQLプロシージャを作成してみました。

Oracle Databaseでメール、というとUTL_SMTP、UTL_MAILなどがありますが、Notificationサービスを使う場合はSMTPサーバとの連携設定などが不要になるといったメリットがありますね。

1.Notificationサービスのトピックの作成

「コンソールメニュー」　→ 「アプリケーション統合」　→ 「通知」

1.「トピックの作成」をクリックします。

2.トピックの名前（ここでは名前をTestTopicとします）を入力し、「作成」をクリックします。

3.作成したトピックのOCIDをメモしておきます。

2.Notificationサービスのサブスクリプションの作成

「コンソールメニュー」　→ 「アプリケーション統合」　→ 「通知」

1.画面左の「サブスクリプション」をクリックします。

2.「サブスクリプションの作成」をクリックします。

3.下記の内容を入力し、「作成」をクリックします。
　トピック：先ほど作成したトピック（ここではTestTopic）
　プロトコル：「電子メール」を選択
　電子メール：通知の宛先メール

以上で、Notificationサービスの準備が整いました。

3.DBユーザの作成

パスワード有効期限切れの監視対象となるDBユーザ（test）を作成します。

ユーザを作成する前に、パスワードの有効期間が2日となるプロファイル（pw_life_2）を作成します。

CREATE PROFILE pw_life_2 LIMIT PASSWORD_LIFE_TIME 2;

こちらのプロファイルを使用して、DBユーザ（test）を作成します。

CREATE USER test
IDENTIFIED BY Demo#1Demo#1
PROFILE pw_life_2;

testユーザのパスワード失効日を確認します。
※日本時間を表示するため、+9時間しています。

SELECT username, TO_CHAR(expiry_date+9/24,'YYYY/MM/DD HH24:MI:SS') expiry_date FROM dba_users
WHERE username = 'TEST';

USERNAME   EXPIRY_DATE
---------- -------------------
TEST	   2020/11/03 17:41:21

現在日時を確認します。
※日本時間を表示するため、+9時間しています。

SELECT TO_CHAR(sysdate + 9/24,'YYYY/MM/DD HH24:MI:SS') now FROM dual;

NOW
-------------------
2020/11/01 17:41:48

testユーザのパスワードが2日後に失効することが確認できました。

以下のSQLで、パスワード失効までの日数が取得できます。

SELECT EXPIRY_DATE - sysdate days_until_expiration FROM DBA_USERS
WHERE username = 'TEST';

DAYS_UNTIL_EXPIRATION
---------------------
	   1.99828704

4.PL/SQLプロシージャの作成

こちらのSQLの実行結果をもとに、
・パスワード失効までの日数が7日以下の場合は、パスワードの有効期間が短いことを警告する内容のメール
・それ以外の場合は当たり障りのない内容のメール
をNotificationサービスを使用して送信するPL/SQLプロシージャ（password_expiration_check）を作成します。

CREATE OR REPLACE PROCEDURE password_expiration_check(user IN VARCHAR2)
IS

  current_date VARCHAR2(30);
  password_expiry_date VARCHAR2(30);
  days_until_password_expiration_raw NUMBER;
  days_until_password_expiration NUMBER;

  message_details   dbms_cloud_oci_ons_notification_data_plane_message_details_t;
  response_body     dbms_cloud_oci_ons_notification_data_plane_publish_result_t;
  response          dbms_cloud_oci_ons_notification_data_plane_publish_message_response_t;

  json_obj          json_object_t;
  l_keys            json_key_list;
  
BEGIN

  -- 現在日時を取得（Autonomous DatabaseはタイムゾーンがUTCであることを考慮し、9時間プラス）
  SELECT TO_CHAR(sysdate+9/24,'YYYY/MM/DD HH24:MI:SS')||' JST' INTO current_date FROM dual;
  
  -- ユーザのパスワード失効日時を取得（Autonomous DatabaseはタイムゾーンがUTCであることを考慮し、9時間プラス）
  SELECT TO_CHAR(expiry_date+9/24,'YYYY/MM/DD HH24:MI:SS')||' JST' INTO password_expiry_date FROM dba_users
  WHERE username = user;
  
  -- ユーザのパスワード失効までの日数（小終点以下あり）を取得し、days_to_expire_password_rawにセット
  SELECT expiry_date - CURRENT_DATE INTO days_until_password_expiration_raw FROM dba_users
  WHERE username = user;

  -- 小数点以下を切り上げたユーザのパスワード失効までの日数をdays_to_expire_passwordにセット
  days_until_password_expiration := CEIL(days_until_password_expiration_raw);

  -- Notificationサービスに渡すメッセージの内容の設定  
  message_details := dbms_cloud_oci_ons_notification_data_plane_message_details_t();

  IF (days_until_password_expiration < 8) THEN
    -- パスワード失効までの日数が7日以下の場合は、Notificationサービスに渡すメッセージにパスワード失効を警告する内容をセット
    message_details.title := 'Password Expiration Alert';
    message_details.body := '
    ヤバイよ、ヤバイよ。あと'||days_until_password_expiration||'日以内にユーザ'||user||'のパスワードが失効しちゃうよ。
    パスワード失効日時は'||password_expiry_date||'だよ。
    ';
  ELSE
    -- パスワード失効までの日数が8日以上の場合は、Notificationサービスに渡すメッセージに当たり障りのない内容をセット
    message_details.title := 'No problem!';
    message_details.body := '今日も元気に過ごしましょう。';
  END IF;

  -- トピックのOCIDを指定して、Notificationサービスにメッセージをパブリッシュ
  response := DBMS_CLOUD_OCI_ONS_NOTIFICATION_DATA_PLANE.publish_message (
    topic_id => 'ocid1.onstopic.oc1.ap-tokyo-1.XXXXXXXXXXXXXXXXXXXX',
    message_details => message_details,
    region => 'ap-tokyo-1',
    credential_name => 'MY_SDK_CRED'
  );
    
  -- レスポンスヘッダの出力
  dbms_output.put_line('Headers: ' || CHR(10) ||'------------');
  json_obj := response.headers;
  l_keys := json_obj.get_keys;
  for i IN 1..l_keys.count loop
      dbms_output.put_line(l_keys(i)||':'||json_obj.get(l_keys(i)).to_string);
  end loop;
 
  -- ステータスコードの出力
  dbms_output.put_line('Status Code: ' || CHR(10) || '------------' || CHR(10) || response.status_code);
  dbms_output.put_line(CHR(10));
 
  -- レスポンスボディ（実行結果）とメッセージの内容の出力
  response_body := response.response_body;

  dbms_output.put_line('Message sent.');
  dbms_output.put_line('Message ID :'||response_body.message_id);
  dbms_output.put_line('Timestamp :'||current_date);
  dbms_output.put_line('Password will expire on :'||password_expiry_date);
  dbms_output.put_line('Days until password expiration :'||days_until_password_expiration_raw);

  dbms_output.put_line('Message Title :'||message_details.title);
  dbms_output.put_line('Message Body :'||message_details.body);

END;
/

Procedure PASSWORD_EXPIRATION_CHECK compiled

Elapsed: 00:00:00.144

5.PL/SQLプロシージャの実行

プロシージャが完成したので、ユーザ名にTESTを指定して実行してみます。

EXEC password_expiration_check('TEST');

実行結果は以下のようになりました。

Headers: 
------------
Connection:"close"
Date:"Sun, 01 Nov 2020 08:44:45 GMT"
opc-request-id:"FOI5B439KC/733D60D36D6D520FB1753FC631365D5B/962567CDBB3AEC2C1DC6
EAB1845ECD8A"
Content-Type:"application/json"
X-Content-Type-Options:"nosniff"
Content-Length:"58"
Status Code: 
------------
202


Message sent.
Message ID :5bb35ce7-68f6-3254-3d89-4b7a412b70c2
Timestamp :2020/11/01 17:44:45 JST
Password will expire on :2020/11/03 17:41:21 JST
Days until password expiration :1.99763888888888888888888888888888888889
Message Title :Password Expiration Alert
Message Body :ヤバイよ、ヤバイよ。あと2日以内にユーザTESTのパスワードが失効しちゃうよ。パスワード失効日時は2020/11/03
17:41:21 JSTだよ。


PL/SQL procedure successfully completed.

Elapsed: 00:00:00.570

メールボックスを確認してみます。

おお！パスワード期限切れの警告メールが届きました！

次に、testユーザの有効期間を30日にしてみます。

パスワードの有効期間が30日となるプロファイル（pw_life_30）を作成します。

CREATE PROFILE pw_life_30 LIMIT PASSWORD_LIFE_TIME 30;

作成したプロファイルをtestユーザに適用します。

ALTER USER test PROFILE pw_life_30;

User TEST altered.

testユーザのパスワード失効日を確認します。

SELECT username, TO_CHAR(expiry_date+9/24,'YYYY/MM/DD HH24:MI:SS') expiry_date FROM dba_users
WHERE username = 'TEST';

USERNAME   EXPIRY_DATE
---------- -------------------
TEST	   2020/12/01 17:41:21

testユーザのパスワード失効までの日数を確認します。

SELECT EXPIRY_DATE - sysdate days_until_expiration FROM DBA_USERS
WHERE username = 'TEST';

DAYS_UNTIL_EXPIRATION
---------------------
	    29.995787

この状態で、再度プロシージャを実行してみます。

EXEC password_expiration_check('TEST');

実行結果は以下のようになりました。

Headers: 
------------
Connection:"close"
Date:"Sun, 01 Nov 2020 08:48:09 GMT"
opc-request-id:"PPWYXDATO9/793509D54E8A2B467D7B71C630B0B3CF/118D314868ABFEA265C8
48865DC7A444"
Content-Type:"application/json"
X-Content-Type-Options:"nosniff"
Content-Length:"58"
Status Code: 
------------
202


Message sent.
Message ID :6e3f90ce-4dd5-f84f-dcd2-d01a61ddac43
Timestamp :2020/11/01 17:48:08 JST
Password will expire on :2020/12/01 17:41:21 JST
Days until password expiration :29.99528935185185185185185185185185185185
Message Title :No problem!
Message Body :今日も元気に過ごしましょう。


PL/SQL procedure successfully completed.

Elapsed: 00:00:00.323

再度、メールボックスを確認します。

当たり障りのない内容のメールが届いていました。

予定通りの動作をしてくれました。
めでたし、めでたし。

まとめ

今回はPL/SQL SDKを使用してNotificationサービスと連携してパスワード有効期限切れの警告メールを送信してみました。

こちらのPL/SQLプロシージャをDBMS_SCHEDULERのジョブに登録して定期的に実行することで、アプリケーションで使用しているDBユーザのパスワード有効期限切れによるトラブルを回避できるかもしれませんね。
パスワード有効期限切れの警告以外にも、日次の簡単なレポート配信などにも使えそうで、夢は広がります。

なお、Notificationサービスはメール送信だけでなく、Slackをはじめ、様々な通知方法を指定できますので、とても便利です。

PL/SQL SDKとNotificationサービスの組み合わせ、皆さんも活用してみてはいかがでしょうか。