0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@pmc-ko(K Ohtsuka)in株式会社ピー・エム・シー ITソリューション事業部

プリザンターの内部CRUD操作をSQLだけで実現してみる[第3回：Usersテーブル編]

0
Posted at

はじめに

前回は Groups テーブルと GroupMembers テーブルの CRUD 操作を紹介しました。第 3 回では、Users テーブルを使ったユーザー管理の CRUD 操作を見ていきます。

Users テーブルはプリザンターのマスタテーブルの中で最もカラム数が多く、認証・認可・セキュリティに関わる重要なテーブルです。

回 テーマ
第 1 回 概要と Depts テーブル編 — 共通設計と組織テーブルの CRUD
第 2 回 Groups テーブル編 — グループとメンバー管理の CRUD
第 3 回（本記事） Users テーブル編 — ユーザー管理の CRUD
第 4 回 Results・Issues テーブル編 — レコードデータの CRUD
第 5 回 Wikis テーブル編 — Wiki の CRUD

本連載の SQL は SQL Server を対象にしています。PostgreSQL や MySQL を使用している場合は、関数名やデータ型を適宜読み替えてください。

Users テーブル

Users テーブルはカラム数が 50 以上ありますが、CRUD 操作でよく使うカラムに絞って紹介します。

テーブル構造（主要カラム）

基本情報

カラム 型 NULL 説明
TenantId int NO テナント ID（複合主キー 1）
UserId int NO ユーザー ID（複合主キー 2、IDENTITY）
Ver int NO バージョン番号
LoginId nvarchar(256) YES ログイン ID（ユニーク）
Name nvarchar(128) YES 表示名
Password nvarchar(128) YES パスワード（ハッシュ済み）
DeptId int NO 所属組織 ID（既定値: 0）
FirstName nvarchar(256) YES 名
LastName nvarchar(256) YES 姓
Birthday datetime YES 生年月日
Gender nvarchar(2) YES 性別
Language nvarchar(16) YES 言語設定
TimeZone nvarchar(64) YES タイムゾーン設定
Theme nvarchar(1024) YES テーマ設定
Body nvarchar(max) YES 備考

権限・ロール

カラム 型 NULL 説明
TenantManager bit NO テナント管理者フラグ（既定値: 0）
ServiceManager bit NO サービス管理者フラグ（既定値: 0）
Developer bit NO 開発者フラグ（既定値: 0）
AllowCreationAtTopSite bit NO トップサイト作成許可（既定値: 0）
AllowGroupAdministration bit NO グループ管理許可（既定値: 0）
AllowGroupCreation bit NO グループ作成許可（既定値: 0）
AllowApi bit NO API 使用許可（既定値: 0）
ApiKey nvarchar(256) YES API キー

セキュリティ

カラム 型 NULL 説明
Disabled bit NO 無効フラグ（既定値: 0）
Lockout bit NO ロックアウトフラグ（既定値: 0）
LockoutCounter int NO ロックアウトカウンター（既定値: 0）
PasswordExpirationTime datetime YES パスワード有効期限
PasswordChangeTime datetime YES パスワード変更日時
LastLoginTime datetime YES 最終ログイン日時
EnableSecondaryAuthentication bit NO 二要素認証有効フラグ（既定値: 0）

API 内部実装の解析

Create の内部フロー

UserModel.Create() を解析すると、以下の順序で処理されます。

  1. メールアドレスのバリデーション（API 経由の場合）
  2. TenantId にコンテキストのテナント ID を設定
  3. パスワード履歴の設定（Security.EnforcePasswordHistories が有効な場合）
  4. パスワード有効期限の設定
  5. Rds.InsertUsers() で Users テーブルに INSERT
  6. LoginId の重複チェック（DbException で DuplicateKey エラーをキャッチ）
  7. メールアドレスの登録（API 経由の場合）

Update の内部フロー

  1. メールアドレスのバリデーション
  2. Versions.VerUp() でバージョンアップが必要か判定
  3. バージョンアップする場合、Rds.UsersCopyToStatement() で現在のレコードを Users_history にコピー
  4. Ver をインクリメント
  5. Rds.UpdateUsers() で Users テーブルを UPDATE
  6. 楽観的排他制御による競合チェック
  7. LoginId の重複チェック
  8. メールアドレスの更新

ストアドプロシージャ

ユーザーの作成

パスワードはプリザンター内部でハッシュ化（SHA-512 + ソルト）されて格納されます。SQL で直接ユーザーを作成する場合、パスワードを設定せずに作成し、プリザンターの管理画面からパスワードを設定する運用を推奨します。

CREATE PROCEDURE [dbo].[sp_CreateUser]
    @TenantId INT,
    @UserId INT,
    @LoginId NVARCHAR(256),
    @Name NVARCHAR(128),
    @FirstName NVARCHAR(256) = NULL,
    @LastName NVARCHAR(256) = NULL,
    @DeptId INT = 0,
    @Language NVARCHAR(16) = N'ja',
    @TimeZone NVARCHAR(64) = N'Tokyo Standard Time',
    @NewUserId INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        -- LoginIdの重複チェック
        IF EXISTS (
            SELECT 1 FROM [Users]
            WHERE [TenantId] = @TenantId
                AND [LoginId] = @LoginId
        )
        BEGIN
            RAISERROR(N'指定されたログインIDは既に使用されています。', 16, 1);
        END

        INSERT INTO [Users] (
            [TenantId], [Ver], [LoginId], [Name], [FirstName], [LastName],
            [DeptId], [Language], [TimeZone],
            [TenantManager], [AllowCreationAtTopSite],
            [AllowGroupAdministration], [AllowGroupCreation], [AllowApi],
            [Disabled], [Lockout], [LockoutCounter],
            [EnableSecondaryAuthentication], [Developer], [ServiceManager],
            [Creator], [Updator], [CreatedTime], [UpdatedTime]
        )
        VALUES (
            @TenantId, 1, @LoginId, @Name, @FirstName, @LastName,
            @DeptId, @Language, @TimeZone,
            0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
            @UserId, @UserId, GETDATE(), GETDATE()
        );

        SET @NewUserId = SCOPE_IDENTITY();

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;

ユーザーの更新（履歴保存付き）

CREATE PROCEDURE [dbo].[sp_UpdateUser]
    @TenantId INT,
    @TargetUserId INT,
    @UserId INT,
    @Name NVARCHAR(128) = NULL,
    @FirstName NVARCHAR(256) = NULL,
    @LastName NVARCHAR(256) = NULL,
    @DeptId INT = NULL,
    @Language NVARCHAR(16) = NULL,
    @TimeZone NVARCHAR(64) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        -- 更新前のレコードを履歴テーブルに保存
        INSERT INTO [Users_history]
        SELECT * FROM [Users]
        WHERE [TenantId] = @TenantId
            AND [UserId] = @TargetUserId;

        -- メインテーブルを更新
        UPDATE [Users]
        SET
            [Name] = ISNULL(@Name, [Name]),
            [FirstName] = ISNULL(@FirstName, [FirstName]),
            [LastName] = ISNULL(@LastName, [LastName]),
            [DeptId] = ISNULL(@DeptId, [DeptId]),
            [Language] = ISNULL(@Language, [Language]),
            [TimeZone] = ISNULL(@TimeZone, [TimeZone]),
            [Ver] = [Ver] + 1,
            [Updator] = @UserId,
            [UpdatedTime] = GETDATE()
        WHERE
            [TenantId] = @TenantId
            AND [UserId] = @TargetUserId
            AND [Disabled] = 0;

        IF @@ROWCOUNT = 0
        BEGIN
            RAISERROR(N'対象のユーザーが見つからないか、無効化されています。', 16, 1);
        END

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;

ユーザーの論理削除（履歴保存付き）

CREATE PROCEDURE [dbo].[sp_DeleteUser]
    @TenantId INT,
    @TargetUserId INT,
    @UserId INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        -- 削除前のレコードを履歴テーブルに保存
        INSERT INTO [Users_history]
        SELECT * FROM [Users]
        WHERE [TenantId] = @TenantId
            AND [UserId] = @TargetUserId;

        -- 論理削除
        UPDATE [Users]
        SET
            [Disabled] = 1,
            [Ver] = [Ver] + 1,
            [Updator] = @UserId,
            [UpdatedTime] = GETDATE()
        WHERE
            [TenantId] = @TenantId
            AND [UserId] = @TargetUserId;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;

ロックアウトの解除（履歴保存付き）

CREATE PROCEDURE [dbo].[sp_UnlockUser]
    @TenantId INT,
    @TargetUserId INT,
    @UserId INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        -- 更新前のレコードを履歴テーブルに保存
        INSERT INTO [Users_history]
        SELECT * FROM [Users]
        WHERE [TenantId] = @TenantId
            AND [UserId] = @TargetUserId;

        UPDATE [Users]
        SET
            [Lockout] = 0,
            [LockoutCounter] = 0,
            [Ver] = [Ver] + 1,
            [Updator] = @UserId,
            [UpdatedTime] = GETDATE()
        WHERE
            [TenantId] = @TenantId
            AND [UserId] = @TargetUserId;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;

Read（読取）

ユーザーの一覧取得

有効なユーザーを組織情報とともに取得します。

SELECT
    u.[UserId],
    u.[LoginId],
    u.[Name],
    u.[DeptId],
    d.[DeptName],
    u.[TenantManager],
    u.[LastLoginTime],
    u.[CreatedTime],
    u.[UpdatedTime]
FROM
    [Users] u
    LEFT JOIN [Depts] d
        ON u.[TenantId] = d.[TenantId]
        AND u.[DeptId] = d.[DeptId]
        AND d.[Disabled] = 0
WHERE
    u.[TenantId] = @TenantId
    AND u.[Disabled] = 0
ORDER BY
    u.[UserId];

特定ユーザーの詳細取得

SELECT
    [UserId],
    [LoginId],
    [Name],
    [FirstName],
    [LastName],
    [DeptId],
    [TenantManager],
    [AllowApi],
    [Language],
    [TimeZone],
    [LastLoginTime],
    [Lockout],
    [LockoutCounter],
    [EnableSecondaryAuthentication],
    [CreatedTime],
    [UpdatedTime]
FROM
    [Users]
WHERE
    [TenantId] = @TenantId
    AND [UserId] = @UserId;

ユーザーの変更履歴を取得

SELECT
    [Ver],
    [LoginId],
    [Name],
    [DeptId],
    [TenantManager],
    [Disabled],
    [Updator],
    [UpdatedTime]
FROM
    [Users_history]
WHERE
    [TenantId] = @TenantId
    AND [UserId] = @UserId
ORDER BY
    [Ver] DESC;

便利な管理クエリ

長期間ログインしていないユーザーの検出

SELECT
    [UserId],
    [LoginId],
    [Name],
    [LastLoginTime]
FROM
    [Users]
WHERE
    [TenantId] = @TenantId
    AND [Disabled] = 0
    AND (
        [LastLoginTime] IS NULL
        OR [LastLoginTime] < DATEADD(MONTH, -3, GETDATE())
    )
ORDER BY
    [LastLoginTime];

ロックアウトされたユーザーの一覧

SELECT
    [UserId],
    [LoginId],
    [Name],
    [LockoutCounter],
    [UpdatedTime]
FROM
    [Users]
WHERE
    [TenantId] = @TenantId
    AND [Lockout] = 1;

まとめ

第 3 回では、Users テーブルの CRUD 操作を紹介しました。

  • Users テーブルは 50 以上のカラムを持つが、CRUD でよく使うのは基本情報・権限・セキュリティ関連のカラム
  • API の内部実装では LoginId の重複チェックやパスワード履歴管理が行われる
  • パスワードは内部でハッシュ化されるため、SQL での直接設定は推奨しない
  • 更新時は Users_history テーブルに更新前のレコードを保存する
  • ユーザーの物理削除は Creator・Updator の参照が切れるため、論理削除を推奨

次回は Results テーブルと Issues テーブルの CRUD 操作を紹介します。

0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?