ユーザー権限 DB設計

Question

こんにちは。
現在システムを開発していて、ユーザー権限毎に使用できる機能に制限をかけるような機能を実装したいと思っています。

権限の一覧（実際に使用する権限名ではなく、例で書かせていただきます。）
・オーナー
・ブロック長
・店長
・店員

こういった4つの権限があります。
図で表すと、

こういった形で紐付いております。
※ ユーザーは必ず1つの権限しか持てません。
※ 店長Aの中にいる店員Aは、店長Bの下にも存在する、ということはありません。

オーナーとブロック長は、1対n
ブロック長と店長は、1対n
店長と店員は1対n
のような関係性です。

各権限に機能の制限をかけるというのは、
オーナーがログインした場合、自分に紐づくブロック長〜店員まで全てのユーザーを見ることができる。
ブロック長がログインした場合、自分に紐づく店長〜店員まで全てのユーザーを見ることができる。
店長がログインした場合、自分に紐づく店員まで全てのユーザーを見ることができる。
店員がログインした場合、自分の情報だけを見ることができる。

というような形です。

このような機能を作ろうと思っているのですが、DBの組み方はどのように設計するかヒントを頂ければと思っております。

ユーザーテーブル
id
name
email
password
..

オーナーテーブル
id
user_id
owner_name

ブロック長テーブル
id
user_id
block_name
owner_id

このような形で各権限毎にテーブルを作っていく形がいいのでしょうか？
どこか参考サイトなどご教授頂ければ幸いです。

よろしくお願いいたします

Answer 1

権限の管理を行う場合、ロール という概念を用いて管理するのが多いのではないでしょうか。

例えば、以下のようにします。

CREATE TABLE `User` (
  `userId` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'プライマリーキー',
  `roleId` tinyint(1) DEFAULT NULL COMMENT 'ロールID',
  `displayName` varchar(64) NOT NULL DEFAULT '' COMMENT '表示名',
  `email` varchar(128) DEFAULT NULL COMMENT 'メールアドレス',
  `passwordHash` varchar(255) DEFAULT NULL COMMENT 'パスワード',
  PRIMARY KEY (`userId`),
  KEY `roleId` (`roleId`),
  KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ユーザー;

CREATE TABLE `Role` (
  `roleId` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'プライマリーキー',
  `roleName` varchar(64) NOT NULL DEFAULT '' COMMENT 'ロール名'
  PRIMARY KEY (`roleId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ロール';

CREATE TABLE `Auth` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'プライマリーキー',
  `roleId` int(11) NOT NULL DEFAULT '0' COMMENT 'ロールID',
  `menuId` int(11) NOT NULL DEFAULT '0' COMMENT 'メニューID',
  `enabled` tinyint(1) NOT NULL DEFAULT '0' COMMENT '利用可能フラグ',
  `deleteUserId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `roleId` (`roleId`,`menuId`,`deleteFlag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='権限';

メニューマスタは自明なので省略します。

こちらのメリットとしては、

• ロール（例えは、店長とか店員など）のロールが可変である。
• ロールごとに権限を適切に管理できる。
• 役職が変更された時、User テーブルの roleId を変更するだけで、自動的に権限も変更される。

Comments

[退会済みユーザー]

ご回答ありがとうございます。

ロールという概念を用いるんですね！
ご回答のSQLから、質問で載せた図のように、ブロック帳Aの下には、店長Aと店長Bがいて、その店長Aの下には、班員が3名いる、と分かるように紐付けすることはできるのでしょうか？

[退会済みユーザー]

Role テーブルに parentId を追加して上下関係を表すことができますよ。

[退会済みユーザー]

ご返信ありがとうございます。
parentIdには、ユーザーIDが入って来るイメージで合ってますでしょうか？

[退会済みユーザー]

違います。
例えば、Role テーブルのデータは以下のようになります。
roleId: 1, roleName: オーナー, parentId: 0
roleId: 2, roleName: ブロック長, parentId: 1 <- 上位にあるロールID

Answer 2

あるユーザーに対する親は必ず 1 人、というのであれば以下のような構造でも良いかも…

ユーザーテーブル
user_id
user_name
email
password
..
parent_user_id   --親ユーザーのID。

-- 「parent_user_id に自分の user_id が入っているレコード」を拾えば「自分の直下の子」が取れる。

…と思いましたが、やっぱり「ユーザー情報」と「親子関係」は別で持ちたいですかね… どうだろう…

---

親子関係だけ持つテーブル
※ユーザー情報はあくまでユーザーテーブルにある。以下は紐付け情報だけのテーブル

user_id        -- 親となるユーザーのuser_id
child_user_id  -- 子となるユーザーのuser_id

でもこれだと再帰とかで取りにいかないと孫とか曾孫を取るのが大変でしょうか。
MySQL って再帰ってあるんでしたっけ

SQLServerなら再帰で取れるからこれかなあ…
ただ「店員の下にブロック長がぶら下がらないように」みたいな整合性チェックをちゃんとしないとですね

あと「オーナーの下にいきなり店員がぶら下がる」というのも駄目なのでしょうか。

そういうのを気にするなら、各権限毎の紐づけテーブルを作った方がパッと見わかりやすい気もしますね。

---

オーナーとブロック長を紐付けるテーブル

owner_user_id
block_user_id

ブロック長と店長を紐付けるテーブル

block_user_id
tencho_userid

...

とか。まあ、一案です。

Comments

[退会済みユーザー]

MySQLは再帰できないですね。
プログラム上でやることになります。

[sk_3122]

あ、できないんですか。ありがとうございます。

となるともし紐付けテーブルを１つだけ作る場合は
一回ごそっと取ってきて LOOP で回しながらぶら下げていく感じでしょうか。

一番最後の「各権限毎の紐づけテーブル」を作るパターンなら
SQLで普通に紐付けられますかね…？

----
私事ですが、今ちょうどあるシステムのヘルプ画面を作っていて、
ヘルプページ間で親子関係を持たせられるような作りにしています。

この場合は、子はいないかもしれないし、あるいは曾孫までいるかもしれない
階層が確定していないので「個別に紐付けテーブルを作る」というのはちょっとできないのですが、
質問者さんののケースでは階層が固定されているっぽいので
個別に紐付けテーブルを作っちゃっても悪くはないような気がします。どうですかねー…

[退会済みユーザー]

そうですね、要件に拠っても多少変化するとは思いますが、対応関係が 1:n であって、 n:m にならないのであれば、わざわざリンクテーブルを設ける必要はないんじゃないかと思います。

[sk_3122]

今は 1:n だけど今後 n:m になったとしてもこういう造りにしておけば対応しやすいかな…
と思ったのですが、
よく考えたら 1 つの店舗を複数の店長で見ることってないですよね。
であれば 1:n という関係性は基本崩れないと思って良いんですかね。

あとは、今うちで扱ってるシステムのユーザーテーブルが肥大化していて
何でもかんでもユーザーテーブルに持っているので細かく分けたい、という気持ちもありました。
「ユーザー基本情報」「権限関連」「役職関連」「親子関係」「銀行口座情報」「契約関連」etc...
全部ユーザーテーブルに追加追加で増やしているので細かく分けたい。
が、これはちょっと別件ですね。

[退会済みユーザー]

判断が難しいところですね。
「予算と納期しだい」といったところでしょうかw
実際の場面でもよくあるんですよね…それで悩むところが…

1:n を前提とした作りであれば、ソースコードも簡潔になりますが、実際に n:m に変更しなければいけない場面が有ったら…
User table, Role table, UserRoleLink table ... に分割するしかないですよね。
追加発注に誘導します…

Answer 3

ディレクトリのTree構造を参考にするといいのではないでしょうか。

例えば
id,
name,
email,
password,
role,
parent,
のようなテーブルを１つ作成する。

roleには、オーナー、ブロック長、店長、店員から選ぶ
parentには、その人が所属する上位の人のIDを保存する
（オーナーだけ親IDが無いので、暫定的に「0」とかを入れる）

そうすれば１つのテーブルでユーザを管理でき、roleの値で、
権限を分ける（できることとできないことを設定する）でどうでしょうか？

また、１つのテーブルにしておけば、例えば店員の下にバイトとかが必要になった時、
新たにテーブルを作らなくていいと思います。

ツリー構造は再帰処理が複雑なので、
http://tryerror.net/tryerror/wordpress/post-151
あたりが参考になればと思います。

見当違いでしたらゴメンナサイ。
よろしくお願いします。