この記事の注意事項
この記事の内容はこちらの記事の内容を修正した内容となります。
この記事で実施していること
- 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を利用しております。