如何管理数据库中的用户角色？ [英] How to manage User Roles in a Database?

问题描述

我正在创建一个网站，我将在其中管理用户和他们的权限。我正在寻求实现用户角色，似乎并不关心事情应该如何工作。我想要能够为用户分配一定的角色，并且每个角色都包含一到几个权限，可以通过我的脚本轻松阅读。只需要知道我应该如何设置我的数据库，以便轻松高效地执行此操作。

I'm creating a website in which I will be managing users and their permissions. I am looking to implement user roles, and can't seem to wrap my head around how things should work. I want to be able to assign a user a certain role, and have each role contain one to several permissions that can be read easily by my script. Just want to know how I should go about setting up my database to do this easily and efficiently.

在我的头脑中，我看到3个表（用户，角色和权限） 。我可以给每个用户一个加入角色表的角色ID，我只是不知道如何将角色链接到几个权限。

In my head I picture 3 tables (users, roles, and permissions). I could give each user a role id that joins the roles table, I just don't know how I can link roles to several permissions.

推荐答案

我只是不知道如何将角色
链接到多个权限。

I just don't know how I can link roles to several permissions.

您使用连接表：role_id和permission_id来标识与哪些角色相关联的权限

You use a join table: role_id and permission_id to identify what permissions are associated with which roles

编辑：

示例表

角色表

Role_ID Role_Name
1       Standard User
2       Super User
3       Guest

PERMISSION表

Permission_ID Permission_Name
1             View User List
2             Update Own User Account
3             Update Any User Account

ROLE_PERMISSION表

Role_ID Permission_ID
1       1    // Role 1 (Standard User) grants View User List
1       2    //        and Update Own User Account
2       1    // Role 2 (Super User) grants View User List,
2       2    //        Update Own User Account,
2       3    //        and Update Any User Account
3       1    // Role 3 (Guest) grants View User List

列出指定的Role_ID的权限

Listing the permissions for a specified Role_ID

select R.role_id,
       P.permission_id,
       P.permission_name
  from role R,
       permission P,
       role_permission RP
 where RP.permission_id = P.permission_id
   and RP.role_id = R.role_id
   and R.role_id = 1 

这篇关于如何管理数据库中的用户角色？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！