用于声明身份验证的数据库架构

Question

在数据库中，我有表格：USERS，USERS_PROFILES，USERS_CLAIMS。

create table dbo.USERS
(
  Id int identity not null, 
  Username nvarchar (120) not null,
  Email nvarchar (120) not null
);
create table dbo.USERS_PROFILES
(
  Id int not null,
  [Name] nvarchar (80) not null
);
create table dbo.USERS_CLAIMS
(
  Id int not null,
  [Type] nvarchar (200) not null,
  Value nvarchar (200) not null,
);

我正在使用声明授权。当用户注册并创建标识时。标识包含声明，每个声明都有一个类型和一个值：

用户名来自用户的用户名类型> 电子邮件类型>来自用户的电子邮件 名称键入>USERS_PROFILES中的名称 角色类型>直接从USERS_CLAIMS

所以我正在从 3 个表中的许多列创建标识。

我最终得到了这个，因为我迁移到了声明身份验证。

问题

我应该将用户名、电子邮件和姓名移动到USERS_CLAIMS吗？USERS_PROFILES桌会消失...USERS表将仅包含诸如"UserId，LastLoginDate，CreatedDate，..."之类的信息。

如果我想通过用户名获取用户，我只会获得用户名类型的声明......

如果我想登录用户，我只需获取所有声明并创建标识。

因此，身份模型与 SQL 表非常相似。

这有意义吗？您将如何设计表格？

谢谢米格尔

Answer 1

您正在创建键值存储。 它们是在SQL中查询的噩梦。 考虑通过USER_CLAIMS表上的值查询用户属性的难度。 例：

-- Users with name and email by username
SELECT p.ID, p.Username, p.Name, p.Email, u.LastLoggedIN
FROM USER_PROFILES p
INNER JOIN Users u on p.ID = u.ID
WHERE p.ID = @UserID
-- Users with name and email by username with a claims table
-- Does not specify whether there is only one email, so this could return multiple
-- rows for a single user.
SELECT p.ID, cUName.Value as Username, cName.Value as Name, cEMail.Value as Email, u.LastLoggedIN
FROM Users u
LEFT OUTER JOIN USER_CLAIMS cName ON u.ID = cName.ID and cName.[Type] = 'http://schemas.xmlsoap.org/ws/2005/05/identity/claims/name'
LEFT OUTER JOIN USER_CLAIMS cUName ON u.ID = cUName.ID and cUName.[Type] = 'http://schemas.xmlsoap.org/ws/2005/05/identity/claims/privatepersonalidentifier'
LEFT OUTER JOIN USER_CLAIMS cEmail ON u.ID = cEmail.ID and cEmail.[Type] = 'http://schemas.xmlsoap.org/ws/2005/05/identity/claims/email'
WHERE p.ID = @UserID

Answer 2

一个用户可以有多个配置文件吗？ 如果没有，则不需要"USERS_PROFILES"表。 保留"用户"表上的"用户名"和"电子邮件"列。 如果将它们放在"USERS_CLAIMS"表上，则每当用户提出索赔时，您都会存储冗余信息。

我不确定您希望对用户进行哪种跟踪，但我建议使用一个单独的表来跟踪用户登录的时间。 像这样：

CREATE TABLE USERS_LOG (user_id INT, log_in DATETIME);

然后，您可以删除"USERS"表上的"LastLoginDate"，并执行联接以获取用户上次登录的时间。 它将为您提供更多跟踪用户的方法，并且您不会通过不断更新"USERS"表来创建块。