この記事の注意事項

この記事の内容はこちらの記事の内容を修正した内容となります。

この記事で実施していること

SQL Serverにあるデータベースのユーザー情報テーブルに、リボーク用のカラムを追加
認証失敗時に、リボークカウントを増やす処理を追加
認証成功時に、リボーク状態を確認する処理を追加
認証成功時に、リボークカウントをリセットする処理を追加
認証成功時に、リボークされている旨をユーザーに知らせる処理を追加
その他、修正が必要なこと

これまで開発した画面の基本動作

exeファイルを実行する。

ログインが成功すると、ログインしたユーザーがアプリケーションを利用できるようになる。

アカウント登録画面から、アカウントを新規登録することができる。

追加機能の仕様

ユーザー情報テーブルに、リボークカウントとリボークフラグのカラムを追加
認証失敗時に、リボークカウントを「1」加算させ、リボークカウントが「5」以上となった場合、リボークフラグをリボーク状態にする
認証成功時に、リボークカウントを「0」にリセットさせる　※リボークカウントが「0~4」の場合のみ
認証成功時に、、リボーク中メッセージを表示し、ログインしないようにする　※リボークされている場合のみ
アカウント登録時に、リボークカウントを「0」、リボークフラグをリボーク状態にセット

1. SQL Serverのデータテーブルにカラムを追加し、データ入力する。

新たに列を追加する。

列を追加後、データを入れる。データを入れるのが手間だったので、クエリを実行する。

サンプルコード

UPDATE UserInfo
SET revoke_count = 0;

UPDATE UserInfo
SET revoke_flag = 0;

データの登録が完了する。

2. 認証失敗時の処理を追加する

カウントが1加算されるようにし、閾値を超えている場合は、リボーク状態にするようにしています。またデータの整合性を考慮し、トランザクションを実装しています。
サンプルコード

BEGIN TRANSACTION;
UPDATE UserInfo SET revoke_count = revoke_count + 1 WHERE user_id = @userID;
UPDATE UserInfo SET revoke_flag = 'True' WHERE user_id = @userID AND revoke_count >= @revokeCount;
COMMIT;

3. 認証成功時の処理を追加する

まずリボークしているかをチェックし、その結果をSQL Server側から取得しています。

SELECT CASE WHEN EXISTS (
   SELECT 1
   FROM USERINFO
   WHERE user_id = @userID
   AND revoke_flag = 'True' )
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS revokeStatus

その後、リボークされていない場合の結果を元に、カウントをリセットさせるようにしています。なおリボークされている状態の場合は、ログインさせないようにしています。

BEGIN TRANSACTION;
UPDATE UserInfo SET revoke_count = 0 WHERE user_id = @userID AND revoke_flag = 'False';
COMMIT;

4. リボークされている場合に、ログインできなくする処理を追加

画面を制御するだけのため、詳細は省略。

5. アカウント登録時の処理を修正

アカウントを追加した場合、現状では新たに追加したカラムに対し、NULLが入ってしまう状態にある。

※↑の補足：本来、ユーザー情報の各カラムにNULLを許容するのはマズイことに気づいたので、このタイミングでNULLが許容されないようにデータテーブルのデザインを変えました。

そのため、SQLを以下のように書き換え、修正を試みる。

INSERT INTO UserInfo (id, user_id, password,revoke_count, revoke_flag) VALUES (@id, @userID, @password, 0, 'False');

注意事項

この記事では、SQL Server、SSMS、visual studioを使用しているので、試してみたい方は事前に準備が必要です。
本記事に掲載されているコードは掲載用にサンプルとして掲載しているものです。したがって本記事を参考にし、コーディングを進める場合は作成しているアプリに応じて、各自で判断して、適応してください。
関連する記事の一覧はこちら。
※参照先のリンクが切れている場合は、トップページから確認してください。

この記事は誰向けの記事か？

VB.NETやSQL Serverに触れたことがない人
リボークの実装について、一通りの流れを確認したい人

環境

OS：Windows10
SQL Server
SQL Server Management Studio（SSMS）:v19.0.2
visual studio 2022
ChatGPT

本記事における注意事項

本記事は、備忘録としてまとめたものになります。
他の方の参考になる可能性も踏まえて、一般公開も行なっております。
また記載内容はすべて、正しい内容が記載されているとは限りません。
誤った内容を見つけた場合は、ご指摘をお願いいたします。
この記事の内容では、一部、ChatGPTを利用しております。

認証にリボーク機能を追加（VB.NET）