如何在SQL Server数据库中设计用户/角色架构？

I want to design a user/role system:

我想设计一个用户/角色系统:

The users have a name and a password and then the user can have several roles like Admin.

用户具有名称和密码,然后用户可以拥有多个角色,如Admin。

For this I created a schema like this:

为此,我创建了一个这样的架构:

Users:

CREATE TABLE [dbo].[Users]
(
    [id] [int] NOT NULL,
    [name] [nvarchar](50) NULL,
    [password] [nvarchar](50) NULL,

    CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ([id] ASC)
)

Roles:

CREATE TABLE [dbo].[Roles]
(
    [id] [int] NOT NULL,
    [name] [nvarchar](50) NULL,

    CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED ([id] ASC)
)

user_roles:

CREATE TABLE [dbo].[User_Roles]
(
    [id] [int] NOT NULL,
    [User_id] [int] NOT NULL,
    [Role_id] [int] NOT NULL,

    CONSTRAINT [PK_User_Roles] PRIMARY KEY CLUSTERED ([id] ASC)
)

My question is: should I use foreign keys User_Roles.User_id -> User.Id

我的问题是:我应该使用外键User_Roles.User_id - > User.Id

If yes why?

如果是,为什么?

4 个解决方案

#1

Not quite sure what you mean, but...

不太清楚你的意思,但......

User_Roles should have 2 columns only User_id and Role_id
Both of these form the Primary Key

User_Roles应该只有2列User_id和Role_id这两个列都构成了主键

You do not need an extra id column User_Roles

您不需要额外的id列User_Roles

User_id is a foreign key to Users.id

User_id是Users.id的外键

Role_id is a foreign key to Roles.id

Role_id是Roles.id的外键

Edit: Now I understand. Yes, always use foreign keys

编辑:现在我明白了。是的,总是使用外键

Also...

if password is nvarchar(50), this implies plain text. This is bad.

如果密码是nvarchar(50),这意味着纯文本。这不好。

if you have duplicate name values in Users, how do you know which user is which?
Especially if they have the same password (which will happen because we meatsacks are stupid)

如果您在用户中有重复的名称值,您如何知道哪个用户是哪个?特别是如果他们有相同的密码(这将发生因为我们的meatsacks是愚蠢的)

Edit after comment after primary key creation...

主键创建后的注释后编辑...

CREATE TABLE [dbo].[User_Roles]
(
    [User_id] [int] NOT NULL,
    [Role_id] [int] NOT NULL,

    CONSTRAINT [PK_User_Roles] PRIMARY KEY CLUSTERED ([User_id], [Role_id]),
    CONSTRAINT [UQ_ReversePK] UNIQUE ([Role_id], [User_id])
)

#2

Spring Security makes this recommendation:

Spring Security提出了这个建议:

create table users(
    username varchar_ignorecase(50) not null primary key,
    password varchar_ignorecase(50) not null,
    enabled boolean not null
);

create table authorities (
    username varchar_ignorecase(50) not null,
    authority varchar_ignorecase(50) not null,
    constraint fk_authorities_users foreign key(username) references users(username)
);
create unique index ix_auth_username on authorities (username,authority);

#3

Always use foreign keys when the data models a relation. In your sample, if you don't create the foreign keys, there is nothing preventing you (or someone else with access to the database) from mistakenly (or deliberately) deleting a role which is currently used.

当数据为关系建模时,始终使用外键。在您的示例中,如果您不创建外键,则不会阻止您(或有权访问数据库的其他人)错误地(或故意)删除当前使用的角色。

Let's say you have many users and a few roles. One of the roles is called 'Admin' and is required in you application in order to do some tasks. If you don't have the foreign keys setup, there is nothing in the database to prevent someone from deleting the admin role, casuing your application to:

假设您有许多用户和一些角色。其中一个角色称为“管理员”,在您的应用程序中是必需的,以便执行某些任务。如果您没有设置外键,数据库中没有任何内容可以防止有人删除管理员角色,将您的应用程序归档到:

Probably crash since it will look for a role which is no longer in the database

可能崩溃,因为它将寻找不再在数据库中的角色

If not the above, then at least no users will have the 'Admin' role, closing down the parts of the application where it is required

如果不是上述情况,则至少没有用户具有“管理员”角色,关闭应用程序所需的部分

If, on the othe hand you HAVE setup the foreign keys, you will receive an error from the database if you try to delete a role which is currently assigned to some user (through the User_Roles table).

如果您已经设置了外键,如果您尝试删除当前分配给某个用户的角色(通过User_Roles表),则会从数据库收到错误。

#4

One of the best approach which is slightly different from @GBN as well will be:

与@GBN略有不同的最佳方法之一是:

I hope this helps you or someone else

我希望这可以帮助你或其他人

CREATE TABLE [dbo].[UserRoles](
    [roleId] [int] NOT NULL,
    [userId] [int] NOT NULL,
    [CreateDate] [datetime] NULL,
    [CreateUser] [nvarchar](30) NULL,
    [ModifyDate] [datetime] NULL,
    [ModifyUser] [nvarchar](30) NULL,
 CONSTRAINT [PK_User_Roles] PRIMARY KEY CLUSTERED 
(
    [roleId] ASC,
    [userId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UQ_ReversePK] UNIQUE NONCLUSTERED 
(
    [roleId] ASC,
    [userId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[UserRoles] ADD  CONSTRAINT [DF_UserRoles_ModifyDate]  DEFAULT (getdate()) FOR [ModifyDate]
GO

ALTER TABLE [dbo].[UserRoles]  WITH CHECK ADD  CONSTRAINT [FK_UserRoles_roleId] FOREIGN KEY([roleId])
REFERENCES [dbo].[Roles] ([roleId])
GO

ALTER TABLE [dbo].[UserRoles] CHECK CONSTRAINT [FK_UserRoles_roleId]
GO

ALTER TABLE [dbo].[UserRoles]  WITH CHECK ADD  CONSTRAINT [FK_UserRoles_userId] FOREIGN KEY([userId])
REFERENCES [dbo].[Users] ([uId])
GO

ALTER TABLE [dbo].[UserRoles] CHECK CONSTRAINT [FK_UserRoles_userId]
GO

#1