Db2 プロシージャ実行権限の確認方法と、移行方法について考える

Db2データベース移行の際には、テーブル定義やデータの移行を行います。(当たり前ですね)
それに加えてプロシージャなどデータベース内に格納されたプログラムのが存在する場合、こういったオブジェクトも移行することになります。

また、ただプロシージャを新環境に作成するだけではなくて、実行権限も付与する必要があります。

通常プログラムは業務チームの作業範疇となりますが、
中に何が入っているか全くわかっていないDBを、よしなに移行しといて！と
基盤チームにお鉢がぶん投げられる辛いケースが稀にあります。

そうなると、そもそも何があるのか/誰に実行権限を付与しなくてはいけないのか、皆目見当がつきません。
そんな時のための確認方法を備忘録として残しておきます。

＊こんな手探りの移行は手間も時間もかかる上、業務要件を把握していない基盤チーム側で業務要件を担保することは不可能な話で、本来は業務チームの作業領域であることが大前提です。

＊Db2の移行作業の全体像や作業手順については、こちらに公開される資料が参考になります。
[Db2] V9.7/V10.1/10.5/V11.1 から V11.5 への移行に関連する情報
https://www.ibm.com/support/pages/node/6458073?myns=swgimgmt&mynp=OCSSEPGG&mync=E&cm_sp=swgimgmt-_-OCSSEPGG-_-E

###◆作成済プロシージャについての情報を抽出するSQL
Db2製品導入完了時点で内部的に作成済のプロシージャを外して、ユーザーによって定義されたプロシージャだけを出力するには以下のような条件指定を行います。
(出力列は必要に応じて、適宜絞り込んでください)

```
db2 "select * from syscat.routines where routineschema not like 'SYS%' and routineschema <> 'NULLID' and routineschema <> 'SQLJ'"
```

###◆プロシージャ実行権限確認のためのSQL
どのユーザにプロシージャの実行権限が付与されているかを確認するSQLです。
以下の情報を取得します。

* 所有者(作成者）
* GRANT実行ユーザの名前
* 権限を与えられたユーザ名
* スキーマ名
* ルーチン名(プロシージャ名)
* ルーチンタイプ
* 実行権限

```
db2 "select substr(b.owner,1,16) as owner, substr(a.grantor,1,16) as grantor,a.grantortype, substr(a.grantee,1,16) as grantee, a.granteetype, substr(a.schema,1,16) as schema, substr(b.routinename,1,16) as routinename ,a.routinetype, a.executeauth  from syscat.routineauth as a , syscat.routines as b where a.schema not like 'SYS%' and a.schema <> 'NULLID' and a.schema <> 'SQLJ' and a.SCHEMA=b.routineschema and a.specificname=b.specificname"
```


実行結果の例)

```
[db2inst1@4c4fd18c3faf sql]$ db2 "select substr(b.owner,1,16) as owner, substr(a.grantor,1,16) as grantor,a.grantortype, substr(a.grantee,1,16) as grantee, a.granteetype, substr(a.schema,1,16) as schema, substr(b.routinename,1,16) as routinename ,a.routinetype, a.executeauth  from syscat.routineauth as a , syscat.routines as b where a.schema not like 'SYS%' and a.schema <> 'NULLID' and a.schema <> 'SQLJ' and a.SCHEMA=b.routineschema and a.specificname=b.specificname"

OWNER            GRANTOR          GRANTORTYPE GRANTEE          GRANTEETYPE SCHEMA           ROUTINENAME      ROUTINETYPE EXECUTEAUTH
---------------- ---------------- ----------- ---------------- ----------- ---------------- ---------------- ----------- -----------
DB2INST1         SYSIBM           S           DB2INST1         U           USER01           PROC1            P           G
DB2INST1         DB2INST1         U           USER01           U           USER01           PROC1            P           Y
USER01           SYSIBM           S           USER01           U           USER01           PROC2            P           G

  3 record(s) selected.

[db2inst1@4c4fd18c3faf sql]$

```

 * 1行目と3行目はそれぞれ新規プロシージャを作成した時に、プロシージャ作成者に対して付与された権限です。(GRANTORがSYSIBM)
 * 2行目は、DB2INST1ユーザによりUSER01に対して明示的にEXECUTE権限を付与したものです。

### 補足：プロシージャ移行のために使えるツール

[db2look](https://www.ibm.com/docs/ja/db2/11.5?topic=commands-db2look-db2-statistics-ddl-extraction-tool) コマンドにて、移行元DBから CREATE PROCEDURE 文を抽出することができます。


実行コマンド例)

```
 db2look -d <db_name> -z db2apusr -x -e -o <output_file_name>
```

コマンドオプション)

 * -e ：データベース・オブジェクト用の DDL ステートメントを抽出<br>(※プロシージャ部分のCREATE文だけを選択して抽出することは出来ず、スキーマ/テーブル/ビューなどのユーザー定義オブジェクトすべてのCREATE文が出力されます)
 
 * -z ：指定されたスキーマ名を持つオブジェクトに対する DDL ステートメントを生成。作動不能オブジェクトは含まれない
 * -x ：GRANT ステートメントなどの許可 DDL ステートメントを生成

　※他にも非常に多くの便利なオプションあり。詳細はマニュアル参照 -> [Db2 Doc [db2look]](https://www.ibm.com/docs/ja/db2/11.5?topic=commands-db2look-db2-statistics-ddl-extraction-tool)

どのユーザが作成したプロシージャであるかを前述のSQLで確認し(OWNER列)、そのユーザでDB接続を行った上で、db2lookにて抽出した CREATE PROCEDURE文を実行します。


### 補足：実行権限の与え方

追加で権限付与が行われているプロシージャの場合には、CREATE PROCEDURE 実行後、実行権限の付与も行います。
上のSQLで確認した権限付与者(GRANTOR列) のユーザIDでDB接続を行い、対象ユーザ(GRANTEE列)に対して実行権限を付与します。

以下の例では、DB2INST1ユーザがUSER01に対して、プロシージャPROC1の実行権限を付与します。

```
db2 connect to <db_name> user db2inst1 using <passwd>
db2 grant execute on procedure user01.proc1 to user user01
```

:::note warn
所有者が異なる場合、スキーマと同じ名前のユーザであってもPROC1プロシージャを実行することは出来ません。上記の例のように、実行権限を与える必要があります。
:::

#### 補足の補足：GRANTOR(権限付与者)について

db2look -x オプションではそのDBに実行されたすべてのGRANT文が出力されます。-u オプションで実行ユーザを指定しても、「このユーザで実行したGRANT文だけを抽出」することはできません。

GRANT文はどのユーザが実行したとしても、「誰に、何の権限が付与されるか」という点は変わらないため、機能的にはそこまで気にする必要はないケースがほとんどであっても、移行元・先とでSYSCAT.xxAUTH ビューと突き合わせを行うと、GRANTOR が違うが問題ないの？と不安になることも。
(例：実環境は、テーブル所有者がGRANTORだが、移行時はインスタンスオーナーで db2look出力DDLを一括実行したためGRANTORがインスタンスオーナーになっている 等)

この観点では、db2lookコマンド(-xオプション)で出力されたファイルを丸ごと実行するだけでは権限付与者まで含め権限設定まで完璧に移行元環境同様に複製することはできないため、完全な模造を目指す場合には上のSQLの出力結果と照らし合わせて、db2look出力ファイルに含まれる各GRANT文は誰が行ったかを確認しながらGRANTしていけば、カタログビュー上も差分のない状態まで持ち込むことはできます。

‥ここまでする必要性があるかというと、コストパフォーマンスの視点が気になります。
(アプリケーションのテスト項目すべてクリアできているなら権限の要件は満たしていて、そこまでそろえる努力をする必要は無いともいえるのでは)

‥こんな苦労をしなくてよいように。
最新バージョンへのバックアップリストアがサポートされるうちに（＝SWバージョンが古くなりすぎないうちに）バージョンアップをしていくこと、選べる場合にはバックアップ・リストアのようなDBの中身をまるごと移行できる移行方法を選択すること、がものすごく大事、というごく基本的な教訓を噛みしめています。