ODC SQLチェック仕様による高リスク操作の自動検出

背景

データベースの開発と変更は、すべての企業にとって避けて通れない課題です。適切なデータストレージ設計により、データベースのパフォーマンスを最大限に引き出し、運用コストを削減できます。規範的で制御可能なデータベース変更は、ビジネスの安定した運用の基盤となりますが、これらすべてはSQL開発規範のサポートなしには成り立ちません。

現在、SQL開発規範は依然として紙面上に留まっていることが多いです。「口伝え」による方法は拘束力に欠け、同じ規範を異なる人が異なる解釈する可能性があります。また、膨大な数の開発規範は、DBAの新人たちが短期間で習得することを困難にしています。

本番環境では、時に一つのSQLが甚大な影響を及ぼすことがあります。例えば：

• データベース削除によるサービス停止

  2020年、ある企業の従業員が生活の苦しさやローン返済の困難さから、自宅でパソコンを使って会社の仮想専用網に接続し、サーバーにログインして会社のデータベース内のデータをすべて削除しました。これにより、会社のサービスが停止し、数百万のユーザーが正常に利用できなくなりました。数日間の緊急修理を経て最終的にサービスは復旧しましたが、それでも数千万元の経済的損失が発生し、会社の市場価値は数億元蒸発しました。

• クエリ効率の低下

  ある企業の業務テーブルは次のように定義されています：

  create table bz_tbl (
      id bigint(11) primary key,
      name varchar(64),
      ...
      gmt_create timestamp,
      index bz_tbl_idx(name)
      );
  

  このテーブルには大量のレコードが含まれており、開発者が select * from bz_tbl where name like '%xxx' というステートメントを実行したところ、クエリが非常に遅くなることがわかりました。クエリ実行計画を確認すると、このステートメントがインデックスを使用していないことが判明しました。最終的に問題の原因を特定しました：

  1. name列にはインデックスが定義されていましたが、クエリ条件がname列で左側のあいまい一致を行ったため、インデックスが無効になりました。

  2. select * の書き方により、大量の再テーブルクエリが発生し、結果としてクエリのパフォーマンスが低下しました。

• クエリ結果が期待に反する

  開発者はbz_tblテーブルのcol列の値が1、2、NULLに属さないレコードを検索したいと考え、次のSQLを記述しました：

  select col from bz_tbl where col not in (1,2,NULL);
  

  実行後、このSQLが何のレコードも返さないことが判明しました。開発者はすぐに再度クエリを実行したところ、テーブルにはデータが存在することがわかりました：

  $ > select col from bz_tbl;
  +------+
  | COL  |
  +------+
  |    1 |
  |    2 |
  | NULL |
  |    3 |
  +------+
  4 rows in set (0.00 sec)
  

  比較した結果、not in式にNULL値が含まれていたためであることが判明しました。

シナリオの概要

上記の事例からわかるように、これらの障害の原因は、開発者のSQL開発経験不足または悪意のある攻撃によるものです。開発者の経験不足に対処するため、従来は開発者ドキュメントや口伝えの規範によって開発者の行動を指導してきましたが、強い拘束力を持たせることはできず、開発者自身の保証に頼るしかありませんでした。悪意のある攻撃に対しては、開発者のデータベースアカウントの権限を制限することで回避するしかありませんでしたが、これは通常、開発効率の低下を招きます。これらの課題を解決するため、ODCはV4.2.0バージョンでSQLチェック規範（SQL-Check）機能を導入しました。組み込みの数十のSQL開発ルールが共同でOceanBase上のベストSQLプラクティスを構成し、OB-MySQLモードおよびOB-Oracleモードに完璧に対応しています。SQLチェック機能により、ODCは以下の問題を解決しました：

• SQLチェック規範が紙面上のものに留まる問題：ODCはSQLチェック規範を製品に組み込み、プログラミング言語でルールを固定化することで、口伝えの過程で生じる偏りや、異なる開発者とDBA間でのルール適用に関する理解の相違を回避しました。 SQLチェック規範に強い拘束力がない問題：ODCはSQLチェック規範を日常の開発・変更プロセスに統合しました。ODCを使用して開発・変更を行う限り、SQLチェックを回避することはできません。これにより、データベースの保護と開発・変更担当者への拘束が強化されました。
• SQLチェック規範の意味が不明確な問題：開発者にとって、以前は特定のSQLチェックルールが何を意味するのか理解できないことがありました。「データベースがこのような機能を提供しているのに、なぜ使用を制限するのか」という疑問が生じていました。ODCは、ユーザーが特定のルールに違反した場合、詳細かつ完全な説明を提供します。
• SQLチェック機能が異なる開発環境で異なるルールチェックの強度を提供する問題：データベース開発とデータベース変更の2つのシナリオでユーザーにサービスを提供します。

SQLチェック規範

SQLチェック機能はカスタマイズ可能で、異なる開発環境に応じてルールのチェック強度を設定できます。データベース開発とデータベース変更の2つのシナリオでサービスを提供します。

厳密な設定

カスタムルールの内容

SQLチェック規範は数十のルールで構成されており、ODC開発センターのSQLチェック規範で対応するルールを確認できます。一部のルールには設定可能な範囲があり、異なるパラメータ値を指定することでルールの動作を変更できます。以下では「主キーデータ型の制限」を例に説明します。SQLチェックルールの詳細な操作と設定方法については、SQLチェック規範をご参照ください。

1. Web版ODCにログインします。

2. 左側のナビゲーションペインで、セキュリティ > 環境 > 開発 > SQLチェック規範 をクリックします。

3. SQLチェック規範タブで、現在のすべてのルール情報を確認できます。

   

4. ページをめくって主キーデータ型の制限ルールを見つけ、そのルールの操作列にある**編集**をクリックします。

5. ルールの詳細ページで、現在のルールの詳細な設定を確認します。主キー参照列のデータ型がルールで許可されているデータ型の範囲外にある場合、このルールがトリガーされます。デフォルトでは、主キー参照列の許容範囲にはint、varchar2、number、float、bigintが含まれます。他の型の範囲に緩和または厳格化したい場合は、このルールの主キーとして許可する型項目を編集することで実現できます。

   

異種開発環境への適応

企業のデータベースリソースは通常、開発環境、テスト環境、本番環境など、異なる環境に分かれています。異なる環境のデータベースは異なる役割を担い、それに応じて異なる強度の管理が施されます。ODC V4.2.0では、ユーザーのデータベースをデフォルト、開発、本番、テストの4つの開発環境に分けています。

自身の管理ニーズに応じて、異なる環境に対して異なる強度のSQLチェック規範を適用できます。例えば、「SELECT文で*を使用することは推奨されません」というルールを本番環境で有効にし、テスト環境で無効にしたい場合は、「本番環境」と「テスト環境」でこのルールの「改善レベル」を異なるものに設定することで実現できます。

開発プロセスの全面的な守護

以下では、実際のデータベース開発シナリオを通じて、SQLチェックルールがユーザーのデータベース開発をプロセス全体でどのように守るかを示します。 ユーザーの開発プロセスを以下のように仮定します：

1. 「テスト環境」でデータベーステーブルを作成します。構造は以下のとおりです：

   CREATE TABLE IF NOT EXISTS `user`(
       `id` bigint NOT NULL,
       `name` varchar(128) NOT NULL,
       `account_name` varchar(128) NOT NULL,
       `organization_id` bigint NOT NULL,
       `email_address` varchar(320) DEFAULT NULL,
       `password` varchar(256) NOT NULL,
       `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record insertion time',
       `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Record modification time',
       `description` varchar(512) DEFAULT NULL,
       CONSTRAINT pk_user_id PRIMARY KEY(`id`),
       CONSTRAINT uk_user_organization_id_account_name UNIQUE KEY(`organization_id`, `account_name`)
       );
   

2. 「テスト環境」のデータベーステーブルに、いくつかのテストデータを挿入します。

   insert into `user` values (1, 'test', 'test_account', 1, 'xxx@test.com', '****', CURRENT_TIMESTAMP, null, 'desp info');
   insert into `user` values (2, 'test2', 'test_account2', 1, 'xxx@test2.com', '****', CURRENT_TIMESTAMP, null, 'desp info2');
   insert into `user` values (3, 'test3', 'test_account3', 1, 'xxx@test3.com', '****', CURRENT_TIMESTAMP, null, 'desp info3');
   

3. 「テスト環境」でテーブル内のデータをクエリし、挿入が完了していることを確認します。

   select * from `user`;
   

4. 「本番環境」でも、最初のステップで作成したテーブル構造を同様に構築します。

   CREATE TABLE IF NOT EXISTS `user`(
       `id` bigint NOT NULL,
       `name` varchar(128) NOT NULL,
       `account_name` varchar(128) NOT NULL,
       `organization_id` bigint NOT NULL,
       `email_address` varchar(320) DEFAULT NULL,
       `password` varchar(256) NOT NULL,
       `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record insertion time',
       `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Record modification time',
       `description` varchar(512) DEFAULT NULL,
       CONSTRAINT pk_user_id PRIMARY KEY(`id`),
       CONSTRAINT uk_user_organization_id_account_name UNIQUE KEY(`organization_id`, `account_name`)
       );
   

データベース開発に関する制約

この記事では、データベース開発の操作視点から、各ルールを簡潔に紹介します。

管理者は「開発環境」で以下のSQLチェック規範を設定しました：

1. テーブル作成ステートメントにはテーブルレベルのコメントが必要です。これにより、開発者が各テーブルの業務的意味をより深く理解し、コミュニケーションコストを削減できます。
2. テーブル作成ステートメントには主キーが含まれている必要があり、主キー列はbigint型のみを使用できます。また、自動インクリメント属性の使用が必須です。自動インクリメントの初期値は不要です。
3. テーブル作成ステートメントの各列にはコメントを付けることを推奨します。列コメントを含まないテーブルを作成する場合は承認が必要です。
4. テーブル作成ステートメントでNOT NULLと宣言された列にはデフォルト値が必要です。これにより、上位の業務システムから値が渡されない場合におけるデータベースへの書き込み異常を回避できます。
5. SELECT * を使用したデータのクエリは許可されます。
6. INSERTステートメントで具体的な列を指定しないことも許可されます。

ユーザーが開発データベースにアクセスし、準備済みのテーブル作成ステートメントを編集ウィンドウに貼り付けます。開発者は、ツールバーの SQLチェック ボタンをクリックして、入力したSQLがルールに違反していないか確認できます：

すべての違反ルールは異なる色でレベルが区別されており、特定 ボタンをクリックすると、SQLがチェックルールに違反した具体的な位置を特定できます。ユーザーは検出結果に基づいてSQLを修正するか、直接実行するかを選択できます。直接実行を選択した場合、ODCは以下のプロセスで処理します：

まず、SQLのタイプが直接実行可能かどうかを判断します。直接実行可能な場合、次にSQLが「改善が必要」レベルのルールに違反していないかどうかを判断します。違反している場合は実行をブロックします。「改善が必要」レベルのルールに違反していない場合、次に「承認が必要」レベルのルールに違反していないかどうかを判断します。違反している場合は、SQLをデータベース変更チケットに転送します。上記のいずれのルールにも違反していない場合は、直接実行します。

ユーザーがテーブル作成ステートメントを一部修正して実行をクリックすると、結果は次のとおりです：

SQLが違反するルールに「改善が必要」レベルのルールが含まれているため、ODCはSQLの実行を遮断します。ユーザーがさらに修正し、「改善が必要」レベルのルールをすべて解消すると、「承認が必要」レベルのルールがいくつか残ります：

SQLが違反するルールに「承認が必要」レベルのルールが含まれているため、ODCは引き続きそのSQLの実行をブロックします。ユーザーは「承認を開始」をクリックしてデータベース変更チケットを作成し、具体的な承認者にそのSQLを実行できるかどうかを決定してもらうことができます。

最後に、ユーザーがすべての問題を改善し、いずれのルールにも違反せず、または「改善不要」のルールのみに違反している場合、SQLはウィンドウで直接実行できます：

テーブルにいくつかのテストデータを挿入してクエリを実行したい場合は、ウィンドウに直接SQLを入力できます：

INSERTステートメントとSELECTステートメントはどちらもルールにヒットしましたが、これらのルールはすべて「改善不要」レベルであるため、実行には影響しません。「本番環境」では、管理者はこれら2つのルールのリスクレベルを「必ず改善」と変更できます。ユーザーが実行するSQLがこれらのルールにヒットした場合、ODCはその実行をブロックします：

データベース変更の保護

開発環境では、ユーザーは主にSQLウィンドウを通じてデータベース操作を行いますが、本番環境ではより厳格な管理ポリシーに基づき、データベースとのやり取りは主にデータベース変更を通じて行われます。SQLチェックの結果は、以下の2つのシナリオで機能します：

• 最初のシナリオ：データベース変更を開始できるかどうか。変更対象のSQLが「必ず改善」レベルのルールに違反している場合、データベース変更チケットは作成できません。

• 2番目のシナリオ：データベース変更がどの承認フローにマッチするか。ODCはSQLチェックの結果に基づいて変更のリスクを計算し、それに基づいて承認パスをマッチングします。

以下の変更プロセスの例を用いて、SQLチェックルールによる保護と制限を説明します。

管理者は引き続き、「制約されたデータベース開発」のセクションで説明したルールを用いて、すべての変更対象SQLを制約します：

1. テーブル作成ステートメントにはテーブルレベルのコメントが必要です。これにより、開発者が各テーブルの業務的意味をより深く理解し、コミュニケーションコストを削減できます。
2. テーブル作成ステートメントには主キーが含まれていなければならず、主キー列はbigint型のみを使用でき、自動インクリメント属性を使用する必要があります。インクリメントの初期値は必須ではありません。
3. テーブル作成ステートメントの各列にはコメントを付けることを推奨します。列コメントを含まないテーブルを作成する場合は承認が必要です。
4. テーブル作成ステートメントでNOT NULLと宣言された列にはデフォルト値が必要です。これにより、上位の業務システムから値が渡されない場合に生じるデータベース書き込み異常を回避できます。

ユーザーは以下のSQLを使用してデータベース変更チケットを作成します：

このSQLは、SQLチェックルールのうち複数の「必ず改善」レベルのルールに違反しているため、チケットの作成は失敗します。このチケットの詳細をクリックすると、具体的なルール違反の状況を確認できます：

ユーザーがすべての「必ず改善」レベルのルールを修正すれば、正常にチケットを作成できます。

変更対象のSQLが「承認が必要」レベルのルールにヒットしたため、そのリスクレベルは「高リスク」と判定され、プロジェクト管理者およびプロジェクトDBAの共同承認を受けることになります：

関連ドキュメント

• SQLチェック規範
• リスクレベル、リスク識別ルール、承認プロセス
• データベース変更管理