数据库设计 – 包含用户,角色和权限的数据库模型

我有一个带有用户表和角色表的数据库模型.我想控制最多10个不同元素的访问权限.可以将访问权限授予角色或单个用户.
以下是用户,角色和项目的表定义：

CREATE TABLE users
(
  id serial NOT NULL PRIMARY KEY,username character varying UNIQUE,password character varying,first_name character varying,last_name character varying,...
);

CREATE TABLE roles
(
  id serial NOT NULL PRIMARY KEY,name character varying NOT NULL,description character varying,...
);

CREATE TABLE element_1
(
  id serial NOT NULL PRIMARY KEY,...
);

...

现在我有两种不同的权利设计方式.一个表具有权限类型列或10个权限表 – 每个要控制访问权限的元素对应一个表.

每个元素的权限表与一个权限表的优缺点是什么？ – 或者是更合适的方式吗？

解决方法

首先,您计划实施哪种类型的安全模型？基于角色的访问控制(RBAC)或自主访问控制(DAC)？

RBAC in the Role-Based Access Control
(RBAC) model,access to resources is
based on the role assigned to a user.
In this model,an administrator
assigns a user to a role that has
certain predetermined right and
privileges. Because of the user’s
association with the role,the user
can access certain resources and
perform specific tasks. RBAC is also
known as Non-Discretionary Access
Control. The roles assigned to users
are centrally administered.

DAC In the Discretionary Access
Control (DAC) model,access to
resources is based on user’s identity.
A user is granted permissions to a
resource by being placed on an access
control list (ACL) associated with
resource. An entry on a resource’s ACL
is known as an Access Control Entry
(ACE). When a user (or group) is the
owner of an object in the DAC model,
the user can grant permission to other
users and groups. The DAC model is
based on resource ownership.

see source

1)在RBAC中：您需要ElementType表来为角色分配权限(用户被分配给角色). RBAC定义：“这个角色/用户可以做什么”.管理员为角色分配权限和权限,将用户分配给角色以访问资源.
2)在DAC中：用户和角色通过访问控制列表(所有权)拥有元素的权限. DAC定义：“谁有权访问我的数据”.用户(所有者)授予对所拥有资源的权限.

无论如何我建议这个数据模型：

CREATE TABLE ElementType
(
    Id (PK)
    Name
    ...
)

CREATE TABLE ElementBase
(
    Id (PK)
    Type (FK to ElementType)
    ...
)

(一对一的关系)

CREATE TABLE Element_A
(
    Id (PK,FK to ElementBase)
    ...
)

CREATE TABLE Element_B
(
    Id (PK,FK to ElementBase)
    ...
)

1)RBAC(多对多关系)

CREATE TABLE ElementType_To_Role_Rights
(
    RightId (PK)
    RoleId  (FK to Role)
    ElementTypeId (FK to ElementType)
    ...
)

2)DAC(多对多关系)

CREATE TABLE ElementBase_To_Actor_Rights
(
    RightId (PK)
    ElementBaseId (FK to ElementBase)
    ActorId (FK to Actor)
    ...
)

CREATE TABLE Actor
(
    Id (PK)
    Name
)

CREATE TABLE User
(
    Id (PK,FK to Actor)
    Password
    ...
)

CREATE TABLE Role
(
    Id (PK,FK to Actor)
    ...
)

原文链接：https://www.f2er.com/mssql/80110.html

数据库设计 – 包含用户,角色和权限的数据库模型

解决方法

猜你在找的MsSQL相关文章