数据库设计 - 授予对记录的访问权限

Question

我正在设计一个限制对某些对象的访问的数据库。我和我的同事讨论了解决这个问题的不同方法，主要候选者有:1) 隐式访问，以及 2) 显式访问

为了便于说明，我们假设有以下表格:

User (Id)
ObjectA (Id, ParentId) -- Where ParentId is an ObjectA
ObjectB (Id, ObjectAId)
UserObjectA (UserId, ObjectAId) -- Grants access to an ObjectA
UserObjectB (UserId, ObjectBId) -- Grants access to an ObjectB

隐式方法:

1. 因为 ObjectA 作为 ObjectB 的包含实体，如果用户有权访问作为 ObjectB 容器的 ObjectA，那么他也可以访问包含的 ObjectB，即使没有这样的显式记录在 UserObjectB 中。
2. 类似地，如果用户有权访问父 ObjectA，那么即使在 UserObjectA 中没有这样的记录，他也有权访问所有 ObjectA 后代。
3. 此外，如果用户在任一访问权限表中都没有记录，则隐含地他可以访问 ObjectA 和 ObjectB 中的所有记录。

显式方法:

1. 要访问 ObjectA 或 ObjectB 记录，用户必须分别在 UserObjectA 或 UserObjectB 中有记录。
2. 没有记录等于没有访问权限，句号。

隐式方法有两个好处:1)当用户隐式访问许多对象时节省空间，以及 2)隐式访问所有对象的用户将可以访问将来添加的所有对象，而无需触发在创建对象时插入或处理存储过程中的插入。

显式方法的好处是查询更简单、可维护且性能更高。

最初，我们使用隐式方法运行。然而，在实现各种存储过程之后，处理访问的逻辑变得很复杂，我们遇到了各种微妙之处，使得这种方法比显式方法更容易出错。 (请注意，真实场景比简化示例要复杂一些。)我发现自己不断地实现递归 CTE 来确定访问权限，这不允许我(在考虑性能时)抽象出 View 中的某些逻辑部分或内联 TVF。所以我必须在许多不同的存储过程中重复和调整容易出错的逻辑。如果有任何变化，我们将面临一项艰巨的维护任务。

那么，我们是否在使用这种隐式访问方法时犯了错误？我肯定会重新考虑，希望有任何具有类似设计决策经验的人提供建议。

Answer 1

如果你能等一个月，Postgres 9.5 就会出来并且有 Row Security。如果你有 1000 万美元，甲骨文现在就有了。

现在，或在其他数据库中，您可以模仿行安全性:

1. 每个 protected 表都有一个“所有者”列。默认情况下，只有所有者可以选择、更新或删除该行。

2. 每个“子”表也有一个所有者列，带有一个到父表的级联外键。因此，如果更改 parent.owner，那么这也会更改所有 children.owner

3. 使用可更新的 CHECK OPTION View 来加强安全性。

4. 您需要在您的应用程序中设置 current_user。 Here's how对于 pg + spring

在 Postgres 中:

create schema protected;

create table protected.foo (
  foo_id int primary key,
  bar text,
  owner name not null default_current user
);

create table protected.foo_children (
  foo_child_id int primary key,
  foo_id int not null references foo(food_id),
  owner name not null default current_user references foo(owner) on update cascade
);

现在一些 CHECK OPTION View - 如果 postgres 使用 security_barrier:

create view public.foo with (security_barrier) as 
  select
    *
  from protected.foo
  where
    owner = current_user
WITH CHECK OPTION;

create view public.foo_children with (security_barrier) as 
  select
    *
  from protected.foo_children
  where
    owner = current_user
WITH CHECK OPTION;

grant delete, insert, select, update on public.foo to some_users;
grant delete, insert, select, update on public.foo_children to some_users;

为了共享，您需要添加更多的表格。重要的是您可以为正确的列建立索引，这样您就不会降低性能:

create schema acl;

create table acl.foo (
  foo_id int primary key references protected.foo(foo_id),
  grantee name not null,
  privilege char(1) not null
);

更新你的观点:

create or update view public.foo with (security_barrier) as 
  select
    *
  from protected.foo
  where
    owner = current_user
    or exists ( select 1 from acl.foo where privilege in ('s','u','d') and grantee = current_user) );

 --add update trigger that checks for update privilege

 --add delete trigger that checks for delete privilege