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

# この記事の注意事項
この記事の内容は[こちらの記事](https://qiita.com/portfoliokns3/items/143921a3c70c924516dd)の内容を修正した内容となります。

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

# これまで開発した画面の基本動作
exeファイルを実行する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3079289/71983851-8473-77b7-c145-0db521a7b02f.png)
ログインが成功すると、ログインしたユーザーがアプリケーションを利用できるようになる。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3079289/8c1e75c7-5502-a53f-7733-7a43fb07d8f8.png)
アカウント登録画面から、アカウントを新規登録することができる。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3079289/0f402c59-6093-c4a6-8ccd-3d4bacf3dc0f.png)

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

## 1. SQL Serverのデータテーブルにカラムを追加し、データ入力する。
新たに列を追加する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3079289/c90c0d9f-2b5e-9976-491f-7d94a989d0d7.png)
列を追加後、データを入れる。データを入れるのが手間だったので、クエリを実行する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3079289/11268842-9294-accc-f923-8a1f8aa90235.png)
**サンプルコード**
```SQL
UPDATE UserInfo
SET revoke_count = 0;

UPDATE UserInfo
SET revoke_flag = 0;
```
データの登録が完了する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3079289/488b8ca5-d44f-63a7-e919-3e260360df4e.png)

## 2. 認証失敗時の処理を追加する
カウントが1加算されるようにし、閾値を超えている場合は、リボーク状態にするようにしています。またデータの整合性を考慮し、トランザクションを実装しています。
**サンプルコード**
```SQL
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側から取得しています。
```SQL
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
```

その後、リボークされていない場合の結果を元に、カウントをリセットさせるようにしています。なおリボークされている状態の場合は、ログインさせないようにしています。
```SQL
BEGIN TRANSACTION;
UPDATE UserInfo SET revoke_count = 0 WHERE user_id = @userID AND revoke_flag = 'False';
COMMIT;
```

## 4. リボークされている場合に、ログインできなくする処理を追加
画面を制御するだけのため、詳細は省略。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3079289/e7153c3a-59b9-eed4-137f-bd10e0e0910f.png)

## 5. アカウント登録時の処理を修正
アカウントを追加した場合、現状では新たに追加したカラムに対し、NULLが入ってしまう状態にある。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3079289/37a84d8f-ec2b-847f-899b-169e5279156f.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3079289/6a5799ae-93b4-ded8-485f-3c1735e2bbff.png)
**※↑の補足：本来、ユーザー情報の各カラムにNULLを許容するのはマズイことに気づいたので、このタイミングでNULLが許容されないようにデータテーブルのデザインを変えました。**
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3079289/bf87bc31-d443-e38f-2ca3-67e4ac653443.png)

そのため、SQLを以下のように書き換え、修正を試みる。
```SQL
INSERT INTO UserInfo (id, user_id, password,revoke_count, revoke_flag) VALUES (@id, @userID, @password, 0, 'False'); 
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3079289/4da1da7f-0c5f-44d3-d371-6fc1a36a8b02.png)

# 注意事項
- この記事では、SQL Server、SSMS、visual studioを使用しているので、試してみたい方は事前に準備が必要です。
- 本記事に掲載されているコードは掲載用にサンプルとして掲載しているものです。したがって本記事を参考にし、コーディングを進める場合は作成しているアプリに応じて、各自で判断して、適応してください。
- 関連する記事の一覧は[こちら](https://qiita.com/portfoliokns3/items/7c789b7e1ba40500abbb#%E3%81%93%E3%82%8C%E3%81%8B%E3%82%89%E6%89%B1%E3%81%A3%E3%81%A6%E3%81%84%E3%81%8D%E3%81%9F%E3%81%84%E4%BA%BA%E5%90%91%E3%81%91%E3%81%AE%E8%A8%98%E4%BA%8B)。
※参照先のリンクが切れている場合は、[トップページ](https://qiita.com/portfoliokns3)から確認してください。

# この記事は誰向けの記事か？
- VB.NETやSQL Serverに触れたことがない人
- リボークの実装について、一通りの流れを確認したい人

# 環境
- OS：Windows10
- [SQL Server](https://www.microsoft.com/ja-jp/sql-server/sql-server-downloads)
- [SQL Server Management Studio（SSMS）](https://learn.microsoft.com/ja-jp/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16):v19.0.2
- [visual studio 2022](https://visualstudio.microsoft.com/ja/vs/)
- [ChatGPT](https://chat.openai.com/chat)

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