Question

我有表格的图表,其内容基本如下:

A set of users
A set of object groups
An access control list (acl) indicating what users have access to what groups
A set of objects, each of which belongs to exactly one group.

我想创建一个支持访问控制的简单应用程序。我认为这里使用视图会是一个不错的方法。

假设我有以下的数据库初始化：

/* Database definition */

BEGIN;

CREATE SCHEMA foo;

CREATE TABLE foo.users (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE foo.groups (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE foo.acl (
    user_ INT REFERENCES foo.users,
    group_ INT REFERENCES foo.groups
);

CREATE TABLE foo.objects (
    id SERIAL PRIMARY KEY,
    group_ INT REFERENCES foo.groups,
    name TEXT,
    data TEXT
);

/* Sample data */

-- Create groups A and B
INSERT INTO foo.groups VALUES (1,  A );
INSERT INTO foo.groups VALUES (2,  B );

-- Create objects belonging to group A
INSERT INTO foo.objects VALUES (1, 1,  object in A ,  apples );
INSERT INTO foo.objects VALUES (2, 1,  another object in A ,  asparagus );

-- Create objects belonging to group B
INSERT INTO foo.objects VALUES (3, 2,  object in B ,  bananas );
INSERT INTO foo.objects VALUES (4, 2,  object in B ,  blueberries );

-- Create users
INSERT INTO foo.users VALUES (1,  alice );
INSERT INTO foo.users VALUES (2,  amy );
INSERT INTO foo.users VALUES (3,  billy );
INSERT INTO foo.users VALUES (4,  bob );
INSERT INTO foo.users VALUES (5,  caitlin );
INSERT INTO foo.users VALUES (6,  charlie );

-- alice and amy can access group A
INSERT INTO foo.acl VALUES (1, 1);
INSERT INTO foo.acl VALUES (2, 1);

-- billy and bob can access group B
INSERT INTO foo.acl VALUES (3, 2);
INSERT INTO foo.acl VALUES (4, 2);

-- caitlin and charlie can access groups A and B
INSERT INTO foo.acl VALUES (5, 1);
INSERT INTO foo.acl VALUES (5, 2);
INSERT INTO foo.acl VALUES (6, 1);
INSERT INTO foo.acl VALUES (6, 2);

COMMIT;

我的想法是使用反映数据库的意见,但只将内容限制在目前用户(受我购买力平价的描述)可以查阅的内容上(我只使用用户浏览器)。附录一在每届邮政总局会议一开始即行。

BEGIN;

CREATE TEMPORARY VIEW users AS
SELECT * FROM foo.users
WHERE name= bob ;

CREATE TEMPORARY VIEW acl AS
SELECT acl.* FROM foo.acl, users
WHERE acl.user_=users.id;

CREATE TEMPORARY VIEW groups AS
SELECT groups.* FROM foo.groups, acl
WHERE groups.id=acl.group_;

CREATE TEMPORARY VIEW objects AS
SELECT objects.* FROM foo.objects, groups
WHERE objects.group_=groups.id;

COMMIT;

我的问题是，这种方法好吗？这些CREATE TEMPORARY VIEW语句会产生相当大的开销，特别是与一些简单查询相比吗？

此外，是否有一种方法将这些视图永久定义在我的数据库中，然后将一个值绑定到每个会话的用户名上？这样，每次用户加载页面时就不必创建所有这些视图。

Answer 1

这种方法存在几个问题：

1个用户web会议与1个database会议不相同。拥有某种固定装置的多个用户会立即失败。
管理开销创建/销毁视图。

相反,我要建议以下观点:

CREATE VIEW AllowedObjects
SELECT objects.*, users.name AS alloweduser
FROM objects
   INNER JOIN groups ON groups.id = objects.group_
   INNER JOIN acl ON acl.group_ = groups.id
   INNER JOIN users ON users.id = acl.user_

然后，你到哪里都可以选择物体：

SELECT * FROM AllowedObjects
WHERE alloweduser= Bob

这意味着假设Bob只能有一个ACL将他加入到特定的组中，否则需要使用DISTINCT。

这一点可以归纳为一种不太复杂的观点,可以用来便利核查普遍定期审议和预警程序许可:

CREATE VIEW AllowedUserGroup
SELECT groups.id AS allowedgroup, users.name AS alloweduser
FROM groups
   INNER JOIN acl ON acl.group_ = groups.id
   INNER JOIN users ON users.id = acl.user_

这提供了一个扁平化的视图，显示哪些用户在哪些群组中，您可以在 UPDATE/DELETE 期间针对对象表进行检查：

UPDATE objects SET foo= bar  WHERE id=42 AND EXISTS
(SELECT NULL FROM AllowedUserGroup 
 WHERE alloweduser= Bob  AND allowedgroup = objects.group_)

友情链接