数据库的多个位置和不同的用户权限

Question

我不知道这条路是什么最好的路。我有个客户，他的生意有三个不同的地点。每个地点员工只能访问他们的地点数据。业主可以访问所有..。然后，不同的角色应该只能访问他们的东西(金融可以看到金融，而不能看到销售等等)。

做这件事最好的方法是什么？我能想到的解决办法是：

1. 创建一个用户表，给出一个位置ID和角色ID，并将其作为数据的基础。不过，这需要添加大量的位置ID。
2. 创建3个单独的数据库，并根据角色ID显示信息。
3. 在数据库端使用功能，存储过程等等.

Answer 1

将一个 security model改造成一个现有的数据库并不是一个简单的任务--从一开始就应该将它设计成模型。

一个非常简单的模型(每个用户一个角色，每个用户一个位置)如下所示：

-- You need to add simple lookup tables for Role, Location
CREATE TABLE User
(
   UserId INT, -- PK
   RoleId INT, -- FK
   LocationId INT NULL -- FK
);

所有敏感表要么直接需要LocationId分类，要么需要与具有LocationId分类的表连接，即：

CREATE TABLE SomeTable -- with location-sensitive data
(
   Col1 ... Col N,
   LocationId INT
);

然而，最困难的部分是调整敏感数据表上的所有系统查询，以便它们现在强制执行特定于位置的限制。这通常是作为附加谓词筛选器完成的，附加在对这些表执行的查询的where子句之后，然后连接回用户位置表：

SELECT Col1 ... ColN
FROM SomeTable
   INNER JOIN User on SomeTable.LocationId = User.LocationId
WHERE -- Usual Filter Criteria
   AND ((User.UserId = @UserIdExecutingThisQuery 
         AND User.RoleId = `Finance`) -- Well, the Id for Finance
        OR User.RoleId = `Administrator`) -- Well, the Id for Admin

作为重新设计工作的结果，作为短期解决方案，您可能会考虑维护3个不同的区域数据库(或同一个数据库中的3个区域模式)，然后使用复制或类似的方法将所有数据集中到主数据库以供owner角色使用。

这将使您有时间重新设计您的数据库(和应用程序)，以使用多租户设计。我建议一个更全面的模型，允许每个用户多个角色，每个用户多个位置(即多个连接表)，而不是这里显示的简单模型。