如何管理数据库中的用户角色?

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.

我正在创建一个网站，在其中我将管理用户和他们的权限。我正在寻找实现用户角色的方法，并且似乎不能完全理解事情应该如何工作。我希望能够为用户分配一个特定的角色，并让每个角色包含一个到多个权限，这些权限可以被我的脚本轻松读取。我只是想知道我应该如何设置我的数据库来轻松高效地完成这个任务。

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.

在我的脑海中，我描绘了3个表(用户、角色和权限)。我可以给每个用户一个角色id来连接roles表，我只是不知道如何将角色链接到多个权限。

4 个解决方案

#1

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

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

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

您可以使用一个连接表:role_id和permission_id来标识与哪些角色关联的权限

EDIT:

编辑:

Example tables

例表

ROLE Table

角色表

Role_ID Role_Name
1       Standard User
2       Super User
3       Guest

PERMISSION Table

权限表

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

ROLE_PERMISSION Table

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

Listing the permissions for a specified Role_ID

列出指定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

#2

This is how I usually what I do:

我通常是这样做的:

You define a set of permissions whose meaning varies from target object to target object, but whose general meaning is the same. For instance:

定义一组权限，其含义从目标对象到目标对象不同，但其一般意义是相同的。例如:

read
读
write
写
append
附加
delete
删除
delete contents
删除内容
read permissions
阅读权限
change permissions
改变权限

Then you assign a bit to each of those:

然后你给每一个分配一点:

class Perms {
    const read           = 1;
    const write          = 2;
    const append         = 4;
    const delete         = 8;
    const deleteContents = 16;
    const readPerm       = 32;
    const changePerm     = 64;
    /* shortcuts */
    const fullControl    = 127; 
    const noControl      = 0;
}

Then for each type of object you have a table where you insert pairs (user, perms), (group, perms), (role, perms) or whatever you want to associate with the permissions.

然后，对于每种类型的对象，您都有一个表，其中插入对(用户、perms)、(组、perms)、(角色、perms)或您希望与权限关联的任何内容。

You can query the permissions of the user (which may have several roles) like this:

您可以查询用户(可能有多个角色)的权限如下:

//this will depend on the database
//you could also use whatever bitwise OR aggregate your database has
//to avoid the foreach loop below
$query = new Query(
    "select perm from objects_permissions as P ".
    "where P.id_object = \$1 and " .
    "   (P.role = any(\$2));",
    $obj->getId(), $user->getRoles()
);

$perms = 0;
foreach ($query as $row) {
    $perms |= $row['perm']; 
}

You can also add deny permissions with little difficulty.

您还可以毫不费力地添加拒绝权限。

#3

I Think bitwise operator are the best way to implement user permission. Here I am showing how we can implement it with Mysql.

我认为位运算符是实现用户权限的最佳方式。这里我将展示如何使用Mysql实现它。

Below is a sample tables with some sample data:

下面是一些样本数据的示例表:

Table 1 : Permission table to store permission name along with it bit like 1,2,4,8..etc (multiple of 2)

表1:用于存储权限名的权限表，以及1、2、4、8。等(2)的倍数

CREATE TABLE IF NOT EXISTS `permission` (
  `bit` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`bit`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Insert some sample data into the table.

向表中插入一些示例数据。

INSERT INTO `permission` (`bit`, `name`) VALUES
(1, 'User-Add'),
(2, 'User-Edit'),
(4, 'User-Delete'),
(8, 'User-View'),
(16, 'Blog-Add'),
(32, 'Blog-Edit'),
(64, 'Blog-Delete'),
(128, 'Blog-View');

Table 2: User table to store user id,name and role. Role will be calculated as sum of permissions.
Example :
If user 'Ketan' having permission of 'User-Add' (bit=1) and 'Blog-Delete' (bit-64) so role will be 65 (1+64).
If user 'Mehata' having permission of 'Blog-View' (bit=128) and 'User-Delete' (bit-4) so role will be 132 (128+4).

表2:用于存储用户id、名称和角色的用户表。角色将被计算为权限的总和。示例:如果用户“Ketan”具有“user - add”(bit- =1)和“Blog-Delete”(bit-64)的权限，那么角色将是65(1+64)。如果用户“Mehata”获得了“Blog-View”(bit=128)和“用户删除”(bit-4)的权限，那么角色将是132(128+4)。

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `role` int(11) NOT NULL,
  `created_date` datetime NOT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

Sample data-

样本数据,

INSERT INTO `user` (`id`, `name`, `role`, `created_date`)
   VALUES (NULL, 'Ketan', '65', '2013-01-09 00:00:00'),
   (NULL, 'Mehata', '132', '2013-01-09 00:00:00');

Loding permission of user After login if we want to load user permission than we can query below to get the permissions:

如果我们想加载用户权限，登录后进行用户权限划分，我们可以在下面查询以获得权限:

SELECT permission.bit,permission.name  
   FROM user LEFT JOIN permission ON user.role & permission.bit
 WHERE user.id = 1

Here user.role "&" permission.bit is a Bitwise operator which will give output as -

在这里用户。“&”角色的许可。位是位运算符，输出为-

User-Add - 1
Blog-Delete - 64

If we want to check weather a particular user have user-edit permission or not-

如果我们要检查特定用户是否有用户编辑权限-

  SELECT * FROM `user` 
     WHERE role & (select bit from permission where name='user-edit')

Output = No rows.

输出=没有行。

You can see also : http://goo.gl/ATnj6j

您还可以看到:http://goo.gl/ATnj6j

#4

If you want to go the route of the 3 tables, you could create your tables like so:

如果你想走这3个表格的路线，你可以这样创建你的表格:

Table       | Rows
User        | id ; name ; dob ; permission_id ; etc...  
Roles       | id ; add_post ; edit_post ; delete_post ; add_user ; etc...
Permissions | id ; user_id ; role_id

#1